tdsql

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

Connect once, then run statements against the connection. Queries hand back rows directly, and values come out typed:

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.1"
tokio = { version = "1", features = ["macros", "rt-multi-thread"] }

Connecting

Build the configuration fluently, 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 also accepts a JDBC-style string via Config::from_jdbc_string. The password is redacted from the Debug output, so logging a config is safe.

One Client is one connection, and it is reused for every statement. Because TDS has no MARS, a connection carries one in-flight request at a time — for concurrency, use a pool such as bb8 or deadpool and check out a connection per task.

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 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(())
# }

Integers widen (a tinyint reads fine as i64) but never narrow, so a value is never silently truncated. Reading a NULL into a non-optional type is an error rather than a default value.

Columns keep their order, and SELECT a, a stays addressable by position.

Named parameters and stored procedures

Command carries named parameters. A stored procedure is sent as a real RPC, so its parameters are genuinely named on the wire rather than 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(())
# }

A text batch may also use named parameters; they are rewritten to the positional form the protocol expects:

# use tdsql::{Client, Command};
# async fn f(client: &mut Client) -> tdsql::Result<()> {
let cmd = Command::query("SELECT @id AS id, @flag AS flag")
    .param("id", 7)
    .param("flag", false);

let ds = client.query_dataset(&cmd).await?;
# Ok(())
# }

Binding None sends SQL NULL:

use tdsql::Command;

let cmd = Command::query("UPDATE users SET note = @note WHERE id = @id")
    .param("note", None::<String>)
    .param("id", 7);

Transactions

client.transaction() returns a handle that scopes every statement run through it. Commit to keep the work; dropping without committing rolls back.

# 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(())
# }

A transaction never commits implicitly, so an early ? return is safe — the insert below is discarded, not half-applied:

# use tdsql::Client;
async fn transfer(client: &mut Client) -> tdsql::Result<()> {
    let mut tx = client.transaction().await?;
    tx.execute("INSERT INTO orders (id) VALUES (@P1)", &[&1i32]).await?;
    tx.execute("INSERT INTO nonexistent VALUES (1)", &[]).await?;  // fails
    tx.commit().await
}

A &mut Transaction can be passed around, so one unit of work can span several functions:

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(())
# }

Isolation levels and savepoints are both available. A savepoint is a nested transaction: rolling it back undoes only 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(())
# }

How rollback-on-drop works

Drop cannot run async code, so the rollback is enqueued rather than awaited — the send is synchronous and non-blocking. The connection lives in a background task that serves requests in order, so the rollback is guaranteed to reach the server before any later statement on that connection. Use rollback() explicitly when you want to observe an error from it.

Writing helpers that work either way

A helper written against &mut Client cannot be called inside a transaction, and one written against &mut Transaction cannot be called without one. The Executor trait removes the choice: take &mut impl Executor and the caller decides.

# 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 carries the statement methods — query, query_one, query_opt, query_scalar, execute, batch, batch_dataset, query_dataset, execute_command — so an existing helper becomes generic by changing only its signature.

It also carries transaction(), which is the interesting one: on a Client it begins a real transaction, and on a Transaction it opens a savepoint. A helper that wants its own atomic scope therefore nests correctly instead of trying to begin a second transaction on a connection that already has one:

# use tdsql::{Client, Executor, Result};
/// Rolls back its own work without disturbing whatever it was called from.
async fn try_it(db: &mut impl Executor) -> Result<()> {
    let mut scope = db.transaction().await?;
    scope.execute("INSERT INTO audit (id) VALUES (@P1)", &[&1i32]).await?;
    scope.rollback().await
}

# async fn f(client: &mut Client) -> Result<()> {
// Here the scope is a transaction, and rolling it back discards the insert.
try_it(client).await?;

// Here it is a savepoint: the insert is discarded, the outer work is not.
let mut tx = client.transaction().await?;
tx.execute("INSERT INTO orders (id) VALUES (@P1)", &[&1i32]).await?;
try_it(&mut tx).await?;
tx.commit().await?;
# Ok(())
# }

The blocking client has the same trait as tdsql::blocking::Executor, with the async/await removed.

The trait is sealed — it describes the two types this crate provides rather than an extension point — and it is generics-only, not dyn-compatible, so use &mut impl Executor or <E: Executor> rather than &mut dyn Executor.

DDL and raw batches

Parameterised statements are sent as an RPC, and some statements — CREATE PROCEDURE, CREATE VIEW, CREATE TRIGGER — must be the first statement of their own batch, so they cannot go that route. batch sends SQL verbatim:

# 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(())
# }

Because the SQL is sent verbatim, never interpolate untrusted input into it — use parameters for values.

Multiple result sets

Most commands return one result set, which is why query hands back a plain Vec<Row>. When a batch or procedure genuinely returns several, use query_dataset and index them 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");

// Or look one up by name.
let by_name = ds.table_named("table0");
# Ok(())
# }

Errors

Every fallible call returns Error, so failures can be matched rather than string-compared:

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}"),
}
# }

A statement that the server rejects fails with Error::Query, which names what was running and repeats what the server said, so printing the error alone is enough to diagnose it:

query failed [SELECT id FROM nope]: Invalid object name 'nope'. [error 208, state 1, severity 16, line 1]

The same details are readable field by field, which is how to branch on a specific server error rather than on its message text:

# 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.
    }
}
# }

Parameter values are never captured into the message — they routinely hold personal data — but parameter_count() records how many were bound, and statement_kind() says whether the failure came from a query, a batch, a stored procedure, or transaction control.

Error is Send + Sync + 'static, so it also works with anyhow, eyre and friends via ?.

Blocking client

If you are not in an async program, enable the blocking feature:

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

tdsql::blocking::Client mirrors the async client with the async/await removed. Everything else — typed rows, Command, transactions, savepoints, the Executor trait — is the same, and Row, DataSet and Error are the very same types.

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 owns a small runtime, so it must not be used from inside an async runtime — driving a runtime from within another one panics. Client::connect detects that case and returns Error::BlockingInAsync up front rather than letting a later call blow up. In async code, use tdsql::Client directly.

Type mapping

Any column reads as DataValue if you would rather match on it yourself.

Time zones

A chrono::DateTime<Tz> binds directly for any time zone -- Utc, Local, a FixedOffset, or a named zone from chrono-tz. It is normalised to its UTC offset and sent as datetimeoffset, so the instant survives the round trip whatever zone you wrote it in:

use chrono::{Local, Utc};
use tdsql::{Client, Config};

# async fn run(config: &Config) -> tdsql::Result<()> {
let mut client = Client::connect(config).await?;

client
    .execute("INSERT INTO events (at) VALUES (@P1)", &[&Utc::now()])
    .await?;

let rows = client.query("SELECT at FROM events", &[]).await?;
let at: chrono::DateTime<Utc> = rows[0].get("at");
let same_instant: chrono::DateTime<Local> = rows[0].get("at");
assert_eq!(at, same_instant);
# Ok(())
# }

Reading back into DateTime<Utc> or DateTime<Local> re-projects the offset the server sent, which names the same instant.

datetime2 and friends are the exception, in both directions: they carry no offset, so calling one UTC would be a guess rather than a conversion. Those columns bind from and read as NaiveDateTime, and you attach the zone yourself -- naive.and_utc() going out, .and_utc() on the way back in.

Known limitation: untyped NULLs

Binding DataValue::Null (or None) sends an untyped NULL, which travels as a NULL int. SQL Server coerces that for most targets, but a column that needs a specific type may reject it. Bind a typed Option<T> when that matters.

Examples

Runnable examples live in examples/. The tests directory holds integration tests that exercise the full type mapping against a live server.

License

MIT — see LICENSE.