# tdsql
`tdsql` is an async Microsoft SQL Server client for Rust, built on the
[tds-driver](https://github.com/CommonwealthPasta/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:
```rust,no_run
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
```toml
[dependencies]
tdsql = "0.8.0"
tokio = { version = "1", features = ["macros", "rt-multi-thread"] }
```
## Connecting
Build a `Config` in code, or parse a connection string:
```rust,no_run
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`](https://crates.io/crates/bb8) or
[`deadpool`](https://crates.io/crates/deadpool) and give each task its own
connection.
## Queries
Parameters are positional: `@P1` is the first, `@P2` the second.
```rust,no_run
# 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:
```rust,no_run
# 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](#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:
```rust,no_run
# 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:
```rust,no_run
# 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:
```rust,no_run
# 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`:
```rust,no_run
# 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:
```rust,no_run
# 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:
```rust,no_run
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.
```rust,no_run
# 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:
```rust,no_run
# 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:
```rust,no_run
# 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:
```rust,no_run
# 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:
```rust,no_run
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:
```rust,no_run
# 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:
```toml
[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.
```rust,no_run
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:
```rust
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](https://docs.rs/tdsql) has the API reference.
## License
MIT. See [LICENSE](https://github.com/CommonwealthPasta/tdsql/tree/master/LICENSE).
[`Error`]: https://docs.rs/tdsql/latest/tdsql/enum.Error.html
[`Config::from_jdbc_string`]: https://docs.rs/tdsql/latest/tdsql/struct.Config.html#method.from_jdbc_string
[`examples/`]: https://github.com/CommonwealthPasta/tdsql/tree/master/examples
[docs/types.md]: https://github.com/CommonwealthPasta/tdsql/blob/master/docs/types.md
[docs/how-it-works.md]: https://github.com/CommonwealthPasta/tdsql/blob/master/docs/how-it-works.md