Crate tdsql

Expand description

§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};

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?;

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.

// 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?;

§Reading values

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

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")?;

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:

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}");
}

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:

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");
}

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:

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?;

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:

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?;

§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:

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?

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

let mut tx = client.transaction().await?;
add_order(&mut tx, 1).await?;
add_order(&mut tx, 2).await?;
tx.commit().await?;

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.

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?;

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:

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
}

// 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?;

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:

client
    .batch("CREATE PROCEDURE dbo.sp_demo @x INT AS BEGIN SELECT @x; END")
    .await?;

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:

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");

§Errors

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

use tdsql::{Client, Error};

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:

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.

Re-exports§

pub use chrono;
pub use rust_decimal;
pub use uuid;

Modules§

blockingblocking: A blocking client, for callers that are not async.
docs: Longer guides, also found in the repository’s docs/ directory.

Structs§

Client: A connection to SQL Server.
Column: Metadata for one column of a result set.
Command: A statement plus its named parameters.
Config: How to reach a SQL Server instance.
DataSet: Every result set produced by one command, in order.
DataTable: One result set: its columns and its rows.
Parameter: A named parameter and its value.
Row: One row of a result set.
RowStream: The rows of a query’s first result set, delivered as the server sends them.
Transaction: An in-progress transaction.

Enums§

CommandType: Whether a Command is a SQL batch or a stored procedure call.
DataValue: A value read from, or bound to, SQL Server.
DateTimeParameterType: The SQL type a NaiveDateTime parameter, or a DataValue::DateTime, is sent as. See Config::datetime_parameter_type.
Error: Errors returned by this crate.
IsolationLevel: The isolation level a transaction runs at.
SqlType: The SQL Server type of a column, as reported by the server, or of a DataValue::TypedNull parameter.
StatementKind: How a failing statement was sent to the server.

Traits§

Executor: Anything statements can be run against: a Client or a Transaction.
FromSql: A Rust type that can be produced from a SQL value.
RowIndex: A value that can address a column: either a positional usize or a &str name.
ToSql: A Rust value that can be bound as a SQL parameter.

Type Aliases§

Result: A Result alias using this crate’s Error.

Crate tdsql

Crate tdsql Copy item path

§tdsql

§Installation

§Connecting

§Queries

§Reading values

§Streaming large results

§Cancelling

§Named parameters and stored procedures

§Transactions

§Writing helpers that work either way

§Raw batches

§Multiple result sets

§Errors

§Blocking client

§Types

§Learn more

§License

Re-exports§

Modules§

Structs§

Enums§

Traits§

Type Aliases§

Crate tdsql