This is a fork of the original sqlx library by @launchbadge. sqlx v0.7 broke backwards compatibility with v0.6 in a way that makes it difficult to upgrade. sqlx v0.7 also removed support for Microsoft SQL Server (to sell it as a separate commercial product). This fork is intended to be a drop-in replacement for sqlx v0.6, with the following changes: - Updated to use the latest versions of dependencies, including - All missing security updates - Latest SQLite version - Improved support for Microsoft SQL Server, including: - Support for reading and writing binary and varbinary data - Support for reading and writing date, dateime, and datetimeoffset data using the chrono feature. - Multiple bug fixes around string handling, including better support for long strings - Support for packet chunking, which fixes a bug where large bound parameters or large queries would fail

SQLx

🧰 The Rust SQL Toolkit


actions status Crates.io version docs.rs docs Download

Install | Usage | Docs


Have a question? Be sure to check the FAQ first!


SQLx is an async, pure Rust† SQL crate featuring compile-time checked queries without a DSL.

† The SQLite driver uses the libsqlite3 C library as SQLite is an embedded database (the only way we could be pure Rust for SQLite is by porting all of SQLite to Rust).

†† SQLx uses #![forbid(unsafe_code)] unless the sqlite feature is enabled. As the SQLite driver interacts with C, those interactions are unsafe.

Install

SQLx is compatible with the [async-std], [tokio] and [actix] runtimes; and, the [native-tls] and [rustls] TLS backends. When adding the dependency, you must chose a runtime feature that is runtime + tls.

```toml

Cargo.toml

[dependencies]

tokio + rustls

sqlx = { package = "sqlx-oldapi", version = "0.6", features = [ "runtime-tokio-rustls" ] }

async-std + native-tls

sqlx = { package = "sqlx-oldapi", version = "0.6", features = [ "runtime-async-std-native-tls" ] } ```

The runtime and TLS backend not being separate feature sets to select is a workaround for a Cargo issue.

Cargo Feature Flags

SQLx is not an ORM!

SQLx supports compile-time checked queries. It does not, however, do this by providing a Rust API or DSL (domain-specific language) for building queries. Instead, it provides macros that take regular SQL as an input and ensure that it is valid for your database. The way this works is that SQLx connects to your development DB at compile time to have the database itself verify (and return some info on) your SQL queries. This has some potentially surprising implications:

If you are looking for an (asynchronous) ORM, you can check out [ormx] or [SeaORM], which is built on top of SQLx.

Usage

See the examples/ folder for more in-depth usage.

Quickstart

```toml [dependencies]

PICK ONE:

Async-std:

sqlx = { package = "sqlx-oldapi", version = "0.6", features = [ "runtime-async-std-native-tls", "postgres" ] } async-std = { version = "1", features = [ "attributes" ] }

Tokio:

sqlx = { package = "sqlx-oldapi", version = "0.6", features = [ "runtime-tokio-native-tls" , "postgres" ] } tokio = { version = "1", features = ["full"] }

Actix-web:

sqlx = { package = "sqlx-oldapi", version = "0.6", features = [ "runtime-actix-native-tls" , "postgres" ] } actix-web = "4" ```

```rust use sqlx::postgres::PgPoolOptions; // use sqlx::mysql::MySqlPoolOptions; // etc.

[async_std::main]

// or #[tokio::main] // or #[actixweb::main] async fn main() -> Result<(), sqlx::Error> { // Create a connection pool // for MySQL, use MySqlPoolOptions::new() // for SQLite, use SqlitePoolOptions::new() // etc. let pool = PgPoolOptions::new() .maxconnections(5) .connect("postgres://postgres:password@localhost/test").await?;

// Make a simple query to return the given parameter (use a question mark `?` instead of `$1` for MySQL)
let row: (i64,) = sqlx::query_as("SELECT $1")
    .bind(150_i64)
    .fetch_one(&pool).await?;

assert_eq!(row.0, 150);

Ok(())

} ```

Connecting

A single connection can be established using any of the database connection types and calling connect().

```rust use sqlx::Connection;

let conn = SqliteConnection::connect("sqlite::memory:").await?; ```

Generally, you will want to instead create a connection pool (sqlx::Pool) in order for your application to regulate how many server-side connections it's using.

rust let pool = MySqlPool::connect("mysql://user:pass@host/database").await?;

Querying

In SQL, queries can be separated into prepared (parameterized) or unprepared (simple). Prepared queries have their query plan cached, use a binary mode of communication (lower bandwidth and faster decoding), and utilize parameters to avoid SQL injection. Unprepared queries are simple and intended only for use case where a prepared statement will not work, such as various database commands (e.g., PRAGMA or SET or BEGIN).

SQLx supports all operations with both types of queries. In SQLx, a &str is treated as an unprepared query and a Query or QueryAs struct is treated as a prepared query.

rust // low-level, Executor trait conn.execute("BEGIN").await?; // unprepared, simple query conn.execute(sqlx::query("DELETE FROM table")).await?; // prepared, cached query

We should prefer to use the high level, query interface whenever possible. To make this easier, there are finalizers on the type to avoid the need to wrap with an executor.

rust sqlx::query("DELETE FROM table").execute(&mut conn).await?; sqlx::query("DELETE FROM table").execute(&pool).await?;

The execute query finalizer returns the number of affected rows, if any, and drops all received results. In addition, there are fetch, fetch_one, fetch_optional, and fetch_all to receive results.

The Query type returned from sqlx::query will return Row<'conn> from the database. Column values can be accessed by ordinal or by name with row.get(). As the Row retains an immutable borrow on the connection, only one Row may exist at a time.

The fetch query finalizer returns a stream-like type that iterates through the rows in the result sets.

``rust // providestry_next` use futures::TryStreamExt;

let mut rows = sqlx::query("SELECT * FROM users WHERE email = ?") .bind(email) .fetch(&mut conn);

while let Some(row) = rows.trynext().await? { // map the row into a user-defined domain type let email: &str = row.tryget("email")?; } ```

To assist with mapping the row into a domain type, there are two idioms that may be used:

rust let mut stream = sqlx::query("SELECT * FROM users") .map(|row: PgRow| { // map the row into a user-defined domain type }) .fetch(&mut conn);

```rust

[derive(sqlx::FromRow)]

struct User { name: String, id: i64 }

let mut stream = sqlx::queryas::<_, User>("SELECT * FROM users WHERE email = ? OR name = ?") .bind(useremail) .bind(user_name) .fetch(&mut conn); ```

Instead of a stream of results, we can use fetch_one or fetch_optional to request one required or optional result from the database.

Compile-time verification

We can use the macro, sqlx::query! to achieve compile-time syntactic and semantic verification of the SQL, with an output to an anonymous record type where each SQL column is a Rust field (using raw identifiers where needed).

```rust let countries = sqlx::query!( " SELECT country, COUNT(*) as count FROM users GROUP BY country WHERE organization = ? ", organization ) .fetch_all(&pool) // -> Vec<{ country: String, count: i64 }> .await?;

// countries[0].country // countries[0].count ```

Differences from query():

The biggest downside to query!() is that the output type cannot be named (due to Rust not officially supporting anonymous records). To address that, there is a query_as!() macro that is mostly identical except that you can name the output type.

```rust // no traits are needed struct Country { country: String, count: i64 }

let countries = sqlx::queryas!(Country, " SELECT country, COUNT(*) as count FROM users GROUP BY country WHERE organization = ? ", organization ) .fetchall(&pool) // -> Vec .await?;

// countries[0].country // countries[0].count ```

To avoid the need of having a development database around to compile the project even when no modifications (to the database-accessing parts of the code) are done, you can enable "offline mode" to cache the results of the SQL query analysis using the sqlx command-line tool. See sqlx-cli/README.md.

Compile time verified queries do quite a bit of work at compile time. Incremental actions like cargo check and cargo build can be significantly faster when using an optimized build by putting the following in your Cargo.toml (More information in the Profiles section of The Cargo Book)

toml [profile.dev.package.sqlx-macros] opt-level = 3

1 The dotenv crate itself appears abandoned as of December 2021 so we now use the dotenvy crate instead. The file format is the same.

Safety

This crate uses #![forbid(unsafe_code)] to ensure everything is implemented in 100% Safe Rust.

If the sqlite feature is enabled, this is downgraded to #![deny(unsafe_code)] with #![allow(unsafe_code)] on the sqlx::sqlite module. There are several places where we interact with the C SQLite API. We try to document each call for the invariants we're assuming. We absolutely welcome auditing of, and feedback on, our unsafe code usage.

License

Licensed under either of

at your option.

Contribution

Unless you explicitly state otherwise, any contribution intentionally submitted for inclusion in the work by you, as defined in the Apache-2.0 license, shall be dual licensed as above, without any additional terms or conditions.