tdsql

tdsql is an async Microsoft SQL Server client for Rust, built on the tds-driver TDS driver.

You connect once and run statements on that connection. Queries return rows, and you read values out of them as ordinary Rust types:

use tdsql::{Client, Config};

#[tokio::main]
async fn main() -> tdsql::Result<()> {
    let mut client = Client::connect(
        &Config::new()
            .host("localhost")
            .port(1433)
            .database("master")
            .auth("sa", "YourStrong!Passw0rd")
            .trust_cert(),
    )
    .await?;

    let rows = client
        .query("SELECT id, name FROM users WHERE id > @P1", &[&10i32])
        .await?;

    for row in &rows {
        let id: i32 = row.get("id");
        let name: String = row.get("name");
        println!("{id}: {name}");
    }

    Ok(())
}

Installation

[dependencies]
tdsql = "0.8.0"
tokio = { version = "1", features = ["macros", "rt-multi-thread"] }

Connecting

Build a Config in code, or parse a connection string:

use tdsql::{Client, Config};

# async fn f() -> tdsql::Result<()> {
let config = Config::new()
    .host("localhost")
    .port(1433)
    .database("master")
    .auth("sa", "YourStrong!Passw0rd")
    .trust_cert();

let mut client = Client::connect(&config).await?;

// or, from an ADO.NET-style connection string:
let mut client = Client::connect_str(
    "Server=tcp:localhost,1433;Database=master;User Id=sa;\
     Password=YourStrong!Passw0rd;TrustServerCertificate=true",
)
.await?;
# Ok(())
# }

Config::from_jdbc_string parses JDBC-style strings too. A named instance without a port, such as Server=host\SQLEXPRESS, is found through SQL Server Browser, and connecting gives up after 15 seconds unless Config::connect_timeout says otherwise.

A Client is one connection, and it runs one statement at a time. For concurrency, use a pool such as bb8 or deadpool and give each task its own connection.

Queries

Parameters are positional: @P1 is the first, @P2 the second.

# use tdsql::Client;
# async fn f(client: &mut Client) -> tdsql::Result<()> {
// Every row.
let rows = client.query("SELECT id, name FROM users", &[]).await?;

// Exactly one row, or an error.
let row = client
    .query_one("SELECT name FROM users WHERE id = @P1", &[&7i32])
    .await?;

// At most one row.
let maybe = client
    .query_opt("SELECT name FROM users WHERE id = @P1", &[&7i32])
    .await?;

// One value.
let count: i32 = client
    .query_scalar("SELECT COUNT(*) FROM users", &[])
    .await?;

// Rows affected.
let updated = client
    .execute("UPDATE users SET active = 0 WHERE last_login < @P1", &[&"2024-01-01"])
    .await?;
# Ok(())
# }

Reading values

get takes a column name or a zero-based position, and converts the value to the type you ask for:

# use tdsql::Row;
# fn f(row: &Row) -> tdsql::Result<()> {
let id: i32 = row.get("id");
let name: String = row.get(1);

// `try_get` returns an error instead of panicking.
let id: i32 = row.try_get("id")?;

// A nullable column reads as `Option<T>`.
let note: Option<String> = row.try_get("note")?;
# Ok(())
# }

A value is never silently truncated to fit the type you ask for, and reading a NULL into anything but an Option is an error. Which Rust types each SQL Server type reads as is listed in Types.

Streaming large results

query gives you nothing until the last row has arrived. query_stream hands you each row as soon as the server sends it, so memory use stays flat however big the result is:

# use tdsql::Client;
# async fn f(client: &mut Client) -> tdsql::Result<()> {
let mut rows = client
    .query_stream("SELECT id, name FROM events WHERE day = @P1", &[&"2024-06-01"])
    .await?;

// The columns are known before the first row arrives.
for column in rows.columns() {
    println!("{}: {:?}", column.name(), column.sql_type());
}

while let Some(row) = rows.try_next().await? {
    let id: i64 = row.get("id");
    println!("{id}");
}
# Ok(())
# }

The stream borrows the client, so nothing else can run on the connection while you hold it. Drop it early and the query is cancelled on the server. RowStream is also a futures::Stream, if you'd rather use combinators.

Cancelling

Stop waiting for a request and it's cancelled on the server too. Drop its future, let tokio::time::timeout or select! give up on it, or drop a stream early. The server stops working on it and lets go of any locks it took, and the connection stays usable:

# use std::time::Duration;
# use tdsql::Client;
# async fn f(client: &mut Client) -> tdsql::Result<()> {
let report = tokio::time::timeout(
    Duration::from_secs(5),
    client.query("SELECT region, SUM(total) FROM orders GROUP BY region", &[]),
)
.await;

if report.is_err() {
    // Timed out and cancelled, so this runs straight away rather than
    // queueing behind the abandoned query.
    let count: i32 = client.query_scalar("SELECT COUNT(*) FROM orders", &[]).await?;
    println!("{count} orders; the report timed out");
}
# Ok(())
# }

Cancelling stops a statement, not the transaction around it, and commits and rollbacks always run. Put statements that must stand or fall together in a transaction.

Named parameters and stored procedures

Command takes named parameters. Stored procedures are called over RPC, which sends each parameter by name, so nothing gets pasted into an EXEC string:

# use tdsql::{Client, Command};
# use rust_decimal::Decimal;
# async fn f(client: &mut Client) -> tdsql::Result<()> {
let cmd = Command::stored_procedure("sp_upsert_order")
    .param("id", 1001)
    .param("status", "PAID")
    .param("amount", Decimal::new(1299, 2));

let affected = client.execute_command(&cmd).await?;
# Ok(())
# }

Text queries can use named parameters too. Names match whatever their case, so @Id in the SQL finds a parameter bound as id. To send SQL NULL, bind None:

# use tdsql::{Client, Command};
# async fn f(client: &mut Client) -> tdsql::Result<()> {
let cmd = Command::query("UPDATE users SET note = @note WHERE id = @id")
    .param("note", None::<String>)
    .param("id", 7);

let affected = client.execute_command(&cmd).await?;
# Ok(())
# }

Transactions

client.transaction() starts a transaction, and everything you run through the handle it returns is part of it. Call commit() to keep the work. If you drop the handle without committing, the transaction rolls back, so returning early with ? is safe:

# use tdsql::Client;
# async fn f(client: &mut Client) -> tdsql::Result<()> {
let mut tx = client.transaction().await?;

tx.execute("INSERT INTO orders (id) VALUES (@P1)", &[&1i32]).await?;
tx.execute("UPDATE stock SET qty = qty - 1 WHERE id = @P1", &[&7i32]).await?;

tx.commit().await?;   // or tx.rollback().await?
# Ok(())
# }

You can pass a &mut Transaction to other functions, so one unit of work can span several of them:

use tdsql::{Client, Transaction};

async fn add_order(tx: &mut Transaction<'_>, id: i32) -> tdsql::Result<()> {
    tx.execute("INSERT INTO orders (id) VALUES (@P1)", &[&id]).await?;
    Ok(())
}

# async fn f(client: &mut Client) -> tdsql::Result<()> {
let mut tx = client.transaction().await?;
add_order(&mut tx, 1).await?;
add_order(&mut tx, 2).await?;
tx.commit().await?;
# Ok(())
# }

You can also choose an isolation level, and use savepoints. A savepoint works like a nested transaction: rolling it back undoes its own work and leaves the outer transaction open.

# use tdsql::{Client, IsolationLevel};
# async fn f(client: &mut Client) -> tdsql::Result<()> {
let mut tx = client
    .transaction_with_isolation(IsolationLevel::Serializable)
    .await?;

{
    let mut sp = tx.savepoint("before_risky").await?;
    sp.execute("DELETE FROM audit WHERE id = @P1", &[&5i32]).await?;
    sp.rollback().await?;      // undone; `tx` is still live
}

tx.commit().await?;
# Ok(())
# }

If the server ends the transaction itself, after a deadlock for instance, any further statement on the handle fails with Error::TransactionAborted rather than running outside the transaction.

Writing helpers that work either way

A helper that takes &mut Client can't be called inside a transaction, and one that takes &mut Transaction can't be called outside one. Take &mut impl Executor instead, and it works with both:

# use tdsql::{Client, Executor, Result, Row};
async fn load_user(db: &mut impl Executor, id: i32) -> Result<Row> {
    db.query_one("SELECT id, name FROM users WHERE id = @P1", &[&id])
        .await
}

# async fn f(client: &mut Client) -> Result<()> {
// Straight to the connection...
let user = load_user(client, 1).await?;

// ...or inside a transaction, unchanged.
let mut tx = client.transaction().await?;
let user = load_user(&mut tx, 1).await?;
tx.commit().await?;
# Ok(())
# }

Executor has the same statement methods as Client, so you can make an existing helper generic by changing nothing but its signature. Its transaction() starts a transaction on a Client and a savepoint on a Transaction, so a helper can open a scope of its own without knowing whether it's already inside one.

Raw batches

A statement with parameters runs through sp_executesql. Some statements, like CREATE PROCEDURE, CREATE VIEW and CREATE TRIGGER, have to come first in a batch of their own, so they can't take parameters at all. batch sends SQL exactly as you wrote it and returns every row of every result set:

# use tdsql::Client;
# async fn f(client: &mut Client) -> tdsql::Result<()> {
client
    .batch("CREATE PROCEDURE dbo.sp_demo @x INT AS BEGIN SELECT @x; END")
    .await?;
# Ok(())
# }

Since the SQL goes over untouched, never build it from untrusted input. Values belong in parameters.

Multiple result sets

Most statements return a single result set, which is why query returns a plain Vec<Row>. When a batch or procedure returns several, use query_dataset. They come back in the order the server sent them:

# use tdsql::{Client, Command};
# async fn f(client: &mut Client) -> tdsql::Result<()> {
let ds = client
    .query_dataset(&Command::query("SELECT 1 AS a; SELECT 2 AS b"))
    .await?;

let first: i32 = ds[0][0].get("a");
let second: i32 = ds[1][0].get("b");
# Ok(())
# }

Errors

Everything that can fail returns an Error, so you can match on what went wrong:

use tdsql::{Client, Error};

# async fn f(client: &mut Client) {
match client.query_one("SELECT name FROM users WHERE id = @P1", &[&7i32]).await {
    Ok(row) => println!("{}", row.get::<String, _>("name")),
    Err(Error::UnexpectedRowCount { found }) => eprintln!("expected 1 row, got {found}"),
    Err(Error::ColumnNotFound(col)) => eprintln!("no column {col}"),
    Err(e) => eprintln!("query failed: {e}"),
}
# }

When the server rejects a statement, the error's message names the statement and repeats what the server said, so printing it is usually all you need. The same details are available as fields, for handling a specific server error without parsing the message:

# use tdsql::Client;
# async fn f(client: &mut Client) {
if let Err(e) = client.execute("INSERT INTO users (id) VALUES (@P1)", &[&7i32]).await {
    eprintln!("{} failed", e.statement().unwrap_or("statement"));
    if let (Some(code), Some(message)) = (e.code(), e.server_message()) {
        eprintln!("  server said {code}: {message} (line {})", e.line().unwrap_or(0));
    }
    if e.is_deadlock() {
        // Deadlock victim (error 1205): the transaction is gone, retry it.
    }
    if e.is_connection_lost() {
        // The connection is gone: open a new one.
    }
}
# }

Error is Send + Sync + 'static, so ? works with anyhow, eyre and similar crates.

Blocking client

If your program isn't async, turn on the blocking feature:

[dependencies]
tdsql = { version = "0.8.0", features = ["blocking"] }

tdsql::blocking::Client is the async client without the async and .await. Typed rows, Command, transactions, savepoints and the Executor trait all work the same way, and Row, DataSet and Error are shared between the two.

use tdsql::blocking::Client;
use tdsql::Config;

fn main() -> tdsql::Result<()> {
    let mut client = Client::connect(
        &Config::new()
            .host("localhost")
            .database("master")
            .auth("sa", "YourStrong!Passw0rd")
            .trust_cert(),
    )?;

    let row = client.query_one("SELECT @P1 AS n", &[&42i32])?;
    let n: i32 = row.get("n");

    let mut tx = client.transaction()?;
    tx.execute("INSERT INTO orders (id) VALUES (@P1)", &[&1i32])?;
    tx.commit()?;

    Ok(())
}

Each blocking client runs its own small runtime, so don't use one from async code. In async code, use tdsql::Client.

Types

SQL Server types read as and bind from the Rust types you would expect: the integer types, f64, bool, String, Vec<u8>, rust_decimal::Decimal, uuid::Uuid and the chrono date and time types. Import those three crates through tdsql so the versions always match:

use tdsql::chrono::Utc;
use tdsql::uuid::Uuid;
use tdsql::rust_decimal::Decimal;

The full mapping, and how time zones, datetime columns and NULL parameters are handled, is in docs/types.md.

Learn more

• examples/ has runnable programs: a basic query, a stored procedure, a transaction and the blocking client.
• docs/types.md covers the type mapping in detail.
• docs/how-it-works.md explains what happens on the connection: cancellation, streaming, rollback on drop, batches and the blocking runtime.
• docs.rs/tdsql has the API reference.

License

MIT. See LICENSE.