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, SQLite, and MSSQL.

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

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

• 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 returning results from all statements.

• Transport Layer Security (TLS) where supported (MySQL 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.

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.

# Cargo.toml
[dependencies]
# tokio + rustls
sqlx = { version = "0.6", features = [ "runtime-tokio-rustls" ] }
# async-std + native-tls
sqlx = { 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.

• runtime-async-std-native-tls: Use the async-std runtime and native-tls TLS backend.

• runtime-async-std-rustls: Use the async-std runtime and rustls TLS backend.

• runtime-tokio-native-tls: Use the tokio runtime and native-tls TLS backend.

• runtime-tokio-rustls: Use the tokio runtime and rustls TLS backend.

• runtime-actix-native-tls: Use the actix runtime and native-tls TLS backend.

• runtime-actix-rustls: Use the actix runtime and rustls TLS backend.

• postgres: Add support for the Postgres database server.

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

• mssql: Add support for the MSSQL database server.

• sqlite: Add support for the self-contained SQLite database engine.

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

• macros: Add support for the query*! macros, which allow 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 (in Postgres).

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

• git2: Add support for git2::Oid.

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

• decimal: Add support for NUMERIC using the rust_decimal 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.

• tls: Add support for TLS connections.

• offline: Enables building the macros in offline mode when a live database is not available (such as CI).

  • Requires sqlx-cli installed to use. See sqlx-cli/README.md.

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:

• 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 ormx or SeaORM, which is built on top of SQLx.

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

[dependencies]
# PICK ONE:
# Async-std:
sqlx = { version = "0.6", features = [  "runtime-async-std-native-tls", "postgres" ] }
async-std = { version = "1", features = [ "attributes" ] }

# Tokio:
sqlx = { version = "0.6", features = [ "runtime-tokio-native-tls" , "postgres" ] }
tokio = { version = "1", features = ["full"] }

# Actix-web:
sqlx = { version = "0.6", features = [ "runtime-actix-native-tls" , "postgres" ] }
actix-web = "4"

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

#[async_std::main]
// or #[tokio::main]
// or #[actix_web::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()
        .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)
    let row: (i64,) = sqlx::query_as("SELECT $1")
        .bind(150_i64)
        .fetch_one(&pool).await?;

    assert_eq!(row.0, 150);

    Ok(())
}

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) in order for your application to regulate how many server-side connections it's using.

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