File: README.md Copy Chapter

SQLx

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

• Truly Asynchronous. Built from the ground-up using async/await for maximum concurrency.

• Compile-time checked queries (if you want). See SQLx is not an ORM.

• Database Agnostic. Support for PostgreSQL, MySQL, MariaDB, SQLite.

  • MSSQL was supported prior to version 0.7, but has been removed pending a full rewrite of the driver as part of our SQLx Pro initiative.

• Pure Rust. The Postgres and MySQL/MariaDB drivers are written in pure Rust using zero unsafe_†† code.

• Runtime Agnostic. Works on different runtimes (async-std / tokio / actix) and TLS backends (native-tls, rustls).

• Cross-platform. Being native Rust, SQLx will compile anywhere Rust is supported.

• Built-in connection pooling with sqlx::Pool.

• Row streaming. Data is read asynchronously from the database and decoded on demand.

• Automatic statement preparation and caching. When using the high-level query API (sqlx::query), statements are
  prepared and cached per connection.

• Simple (unprepared) query execution including fetching results into the same Row types used by
  the high-level API. Supports batch execution and returns results from all statements.

• Transport Layer Security (TLS) where supported (MySQL, MariaDB and PostgreSQL).

• Asynchronous notifications using LISTEN and NOTIFY for PostgreSQL.

• Nested transactions with support for save points.

• Any database driver for changing the database driver at runtime. An AnyPool connects to the driver indicated by the URL scheme.

Install Copy Chapter

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 choose a runtime feature that is runtime + tls.

# Cargo.toml
[dependencies]
# PICK ONE OF THE FOLLOWING:

# tokio (no TLS)
sqlx = { version = "0.9", features = [ "runtime-tokio" ] }
# tokio + native-tls
sqlx = { version = "0.9", features = [ "runtime-tokio", "tls-native-tls" ] }
# tokio + rustls with ring and WebPKI CA certificates
sqlx = { version = "0.9", features = [ "runtime-tokio", "tls-rustls-ring-webpki" ] }
# tokio + rustls with ring and platform's native CA certificates
sqlx = { version = "0.9", features = [ "runtime-tokio", "tls-rustls-ring-native-roots" ] }
# tokio + rustls with aws-lc-rs
sqlx = { version = "0.9", features = [ "runtime-tokio", "tls-rustls-aws-lc-rs" ] }

# async-std (no TLS)
sqlx = { version = "0.9", features = [ "runtime-async-std" ] }
# async-std + native-tls
sqlx = { version = "0.9", features = [ "runtime-async-std", "tls-native-tls" ] }
# async-std + rustls with ring and WebPKI CA certificates
sqlx = { version = "0.9", features = [ "runtime-async-std", "tls-rustls-ring-webpki" ] }
# async-std + rustls with ring and platform's native CA certificates
sqlx = { version = "0.9", features = [ "runtime-async-std", "tls-rustls-ring-native-roots" ] }
# async-std + rustls with aws-lc-rs
sqlx = { version = "0.9", features = [ "runtime-async-std", "tls-rustls-aws-lc-rs" ] }

Cargo Feature Flags

For backward-compatibility reasons, the runtime and TLS features can either be chosen together as a single feature,
or separately.

For forward compatibility, you should use the separate runtime and TLS features as the combination features may
be removed in the future.

• runtime-async-std: Use the async-std runtime without enabling a TLS backend.

• runtime-tokio: Use the tokio runtime without enabling a TLS backend.

  • Actix-web is fully compatible with Tokio and so a separate runtime feature is no longer needed.

• tls-native-tls: Use the native-tls TLS backend (OpenSSL on *nix, SChannel on Windows, Secure Transport on macOS).

• tls-rustls: Use the rustls TLS backend (cross-platform backend, only supports TLS 1.2 and 1.3).

• tls-rustls-aws-lc-rs: Use the rustls TLS backend with aws-lc-rs.

• postgres: Add support for the Postgres database server.

• mysql: Add support for the MySQL/MariaDB database server.

• Note: RSA auth without TLS requires mysql-rsa (not enabled by mysql).

• mysql-rsa: Enable RSA password encryption for caching_sha2_password/sha256_password when TLS is off. Only enable it if you must connect without TLS to servers that require RSA auth. Prefer using TLS.

• mssql: Add support for the MSSQL database server.

• sqlite: Add support for the self-contained SQLite database engine with SQLite bundled and statically-linked.

• sqlite-unbundled: The same as above (sqlite), but link SQLite from the system instead of the bundled version.

  • Allows updating SQLite independently of SQLx or using forked versions.
  • You must have SQLite installed on the system or provide a path to the library at build time.
    See the rusqlite README for details.
  • May result in link errors if the SQLite version is too old. Version 3.20.0 or newer is recommended.
  • Can increase build time due to the use of bindgen.

• sqlite-preupdate-hook: enables SQLite's preupdate hook API.

  • Exposed as a separate feature because it's generally not enabled by default.
  • Using this feature with sqlite-unbundled may cause linker failures if the system SQLite version does not support it.

• any: Add support for the Any database driver, which can proxy to a database driver at runtime.

• derive: Add support for the derive family macros, those are FromRow, Type, Encode, Decode.

• macros: Add support for the query*! macros, which allows compile-time checked queries.

• migrate: Add support for the migration management and migrate! macro, which allow compile-time embedded migrations.

• uuid: Add support for UUID.

• chrono: Add support for date and time types from chrono.

• time: Add support for date and time types from time crate (alternative to chrono, which is preferred by query! macro, if both enabled)

• bstr: Add support for bstr::BString.

• bigdecimal: Add support for NUMERIC using the bigdecimal crate.

• rust_decimal: Add support for NUMERIC using the rust_decimal crate.

• ipnet: Add support for INET and CIDR (in postgres) using the ipnet crate.

• ipnetwork: Add support for INET and CIDR (in postgres) using the ipnetwork crate.

• json: Add support for JSON and JSONB (in postgres) using the serde_json crate.

• Offline mode is now always enabled. See sqlx-cli/README.md.

SQLx is not an ORM! Copy Chapter

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

• Since SQLx never has to parse the SQL string itself, any syntax that the development DB accepts
  can be used (including things added by database extensions)
• Due to the different amount of information databases let you retrieve about queries, the extent of
  SQL verification you get from the query macros depends on the database

If you are looking for an (asynchronous) ORM, you can check out our new Ecosystem wiki page!

Usage Copy Chapter

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

Quickstart Copy Section

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

#[async_std::main] // Requires the `attributes` feature of `async-std`
// or #[tokio::main]
// or #[actix_web::main]
async fn main() -> Result<(), sqlx::Error> {
    // Create a connection pool
    //  for MySQL/MariaDB, use MySqlPoolOptions::new()
    //  for SQLite, use SqlitePoolOptions::new()
    //  etc.
    let pool = PgPoolOptions::new()
        .max_connections(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/MariaDB)
    let row: (i64,) = sqlx::query_as("SELECT $1")
        .bind(150_i64)
        .fetch_one(&pool).await?;

    assert_eq!(row.0, 150);

    Ok(())
}

Connecting Copy Section

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

use sqlx::Connection;

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

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

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

Querying Copy Section

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

// 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.

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.

// provides `try_next`
use futures_util::TryStreamExt;
// provides `try_get`
use sqlx::Row;

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

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

To assist with mapping the row into a domain type, one of two idioms may be used:

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

#[derive(sqlx::FromRow)]
struct User { name: String, id: i64 }

let mut stream = sqlx::query_as::<_, User>("SELECT * FROM users WHERE email = ? OR name = ?")
    .bind("[email protected]")
    .bind("example_username")
    .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 Copy Section

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).

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 input (or bind) parameters must be given all at once (and they are compile-time validated to be
  the right number and the right type).

• The output type is an anonymous record. In the above example the type would be similar to:

  rust Copy

  { country: String, count: i64 }

• The DATABASE_URL environment variable must be set at build time to a database which it can prepare
  queries against; the database does not have to contain any data but must be the same
  kind (MySQL, Postgres, etc.) and have the same schema as the database you will be connecting to at runtime.

  For convenience, you can use a .env file¹ to set DATABASE_URL so that you don't have to pass it every time:

  text Copy

  DATABASE_URL=mysql://localhost/my_database

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.

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

let countries = sqlx::query_as!(Country,
        "
SELECT country, COUNT(*) as count
FROM users
GROUP BY country
WHERE organization = ?
        ",
        organization
    )
    .fetch_all(&pool) // -> Vec<Country>
    .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)

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

¹ 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 Copy Chapter

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 Copy Chapter

Licensed under either of

• Apache License, Version 2.0
  (LICENSE-APACHE or http://www.apache.org/licenses/LICENSE-2.0)
• MIT license
  (LICENSE-MIT or http://opensource.org/licenses/MIT)

at your option.

Contribution Copy Chapter

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.

File: examples/mysql/todos/README.md Copy Chapter

TODOs Example

Setup Copy Chapter

1. Declare the database URL

   text Copy

   export DATABASE_URL="mysql://root:password@localhost/todos"

2. Create the database.

   text Copy

   $ sqlx db create

3. Run sql migrations

   text Copy

   $ sqlx migrate run

Usage Copy Chapter

Add a todo

cargo run -- add "todo description"

Complete a todo.

cargo run -- done <todo id>

List all todos

cargo run

File: examples/postgres/transaction/README.md Copy Chapter

Postgres Transaction Example

A simple example demonstrating how to obtain and roll back a transaction with postgres.

Usage Copy Chapter

Declare the database URL. This example does not include any reading or writing of data.

export DATABASE_URL="postgres://postgres@localhost/postgres"

Run.

cargo run

File: examples/postgres/todos/README.md Copy Chapter

TODOs Example

Setup Copy Chapter

1. Declare the database URL

   text Copy

   export DATABASE_URL="postgres://postgres:password@localhost/todos"

2. Create the database.

   text Copy

   $ sqlx db create

3. Run sql migrations

   text Copy

   $ sqlx migrate run

Usage Copy Chapter

Add a todo

cargo run -- add "todo description"

Complete a todo.

cargo run -- done <todo id>

List all todos

cargo run

File: examples/postgres/mockable-todos/README.md Copy Chapter

Mockable TODOs Example

Description Copy Chapter

This example is based on the ideas in this blog post. The value here is that the business logic can be unit tested independently from the database layer. Otherwise it is identical to the todos example.

Setup Copy Chapter

1. Run docker-compose up -d to run Postgres in the background.

2. Declare the database URL, either by exporting it:

   text Copy

   export DATABASE_URL="postgres://postgres:password@localhost/todos"

   or by making a .env file:

   text Copy

   cp .env.example .env

3. Create the database.

   text Copy

   $ sqlx db create

4. Run sql migrations

   text Copy

   $ sqlx migrate run

Usage Copy Chapter

Add a todo

cargo run -- add "todo description"

Complete a todo.

cargo run -- done <todo id>

List all todos

cargo run

Cleanup Copy Chapter

To destroy the Postgres database, run:

docker-compose down --volumes

File: examples/postgres/listen/README.md Copy Chapter

Postgres LISTEN/NOTIFY

Usage Copy Chapter

Declare the database URL. This example does not include any reading or writing of data.

export DATABASE_URL="postgres://postgres@localhost/postgres"

Run.

cargo run

The example program should connect to the database, and create a LISTEN loop on a predefined set of channels. A NOTIFY task will be spawned which will connect to the same database and will emit notifications on a 5 second interval.

File: examples/postgres/json/README.md Copy Chapter

JSON Example

Setup Copy Chapter

1. Declare the database URL

   text Copy

   export DATABASE_URL="postgres://postgres:password@localhost/json"

2. Create the database.

   text Copy

   $ sqlx db create

3. Run sql migrations

   text Copy

   $ sqlx migrate run

Usage Copy Chapter

Add a person

echo '{ "name": "John Doe", "age": 30 }' | cargo run -- add

or with extra keys

echo '{ "name": "Jane Doe", "age": 25, "array": ["string", true, 0] }' | cargo run -- add

List all people

cargo run

File: examples/postgres/files/README.md Copy Chapter

Query files Example

Description Copy Chapter

This example demonstrates storing external files to use for querying data.
Encapsulating your SQL queries can be helpful in several ways, assisting with intellisense,
etc.

Setup Copy Chapter

1. Declare the database URL

   text Copy

   export DATABASE_URL="postgres://postgres:password@localhost/files"

2. Create the database.

   text Copy

   $ sqlx db create

3. Run sql migrations

   text Copy

   $ sqlx migrate run

Usage Copy Chapter

Run the project

cargo run files

File: examples/postgres/chat/README.md Copy Chapter

Chat Example

Note: this example has an interactive TUI which is not trivial to test automatically,
so our CI currently only checks whether or not it compiles.

Description Copy Chapter

This example demonstrates how to use PostgreSQL channels to create a very simple chat application.

Setup Copy Chapter

1. Declare the database URL

   text Copy

   export DATABASE_URL="postgres://postgres:password@localhost/files"

Usage Copy Chapter

Run the project

cargo run -p sqlx-examples-postgres-chat

File: examples/postgres/axum-social-with-tests/README.md Copy Chapter

This example demonstrates how to write integration tests for an API build with Axum and SQLx using #[sqlx::test].

See also: https://github.com/tokio-rs/axum/blob/main/examples/testing

Warning

For the sake of brevity, this project omits numerous critical security precautions. You can use it as a starting point,
but deploy to production at your own risk!