Repository files navigation

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). SeeSQLx is not an ORM.

• Database Agnostic. Support forPostgreSQL,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 ourSQLx Pro initiative.

• Pure Rust. The Postgres and MySQL/MariaDB drivers are written in pure Rust usingzero 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 waywe could be pure Rust for SQLite is by portingall of SQLite to Rust).

†† SQLx uses#![forbid(unsafe_code)] unless thesqlite feature is enabled.The SQLite driver directly invokes the SQLite3 API vialibsqlite3-sys, which requiresunsafe.

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

• Built-in connection pooling withsqlx::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 areprepared and cached per connection.

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

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

• Asynchronous notifications usingLISTEN andNOTIFY forPostgreSQL.

• Nested transactions with support for save points.

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

SQLx is compatible with theasync-std,tokio, andactix runtimes; and, thenative-tls andrustls TLS backends. When adding the dependency, you must choose a runtime feature that isruntime +tls.

# Cargo.toml[dependencies]# PICK ONE OF THE FOLLOWING:# tokio (no TLS)sqlx = {version ="0.8",features = ["runtime-tokio" ] }# tokio + native-tlssqlx = {version ="0.8",features = ["runtime-tokio","tls-native-tls" ] }# tokio + rustls with ring and WebPKI CA certificatessqlx = {version ="0.8",features = ["runtime-tokio","tls-rustls-ring-webpki" ] }# tokio + rustls with ring and platform's native CA certificatessqlx = {version ="0.8",features = ["runtime-tokio","tls-rustls-ring-native-roots" ] }# tokio + rustls with aws-lc-rssqlx = {version ="0.8",features = ["runtime-tokio","tls-rustls-aws-lc-rs" ] }# async-std (no TLS)sqlx = {version ="0.8",features = ["runtime-async-std" ] }# async-std + native-tlssqlx = {version ="0.8",features = ["runtime-async-std","tls-native-tls" ] }# async-std + rustls with ring and WebPKI CA certificatessqlx = {version ="0.8",features = ["runtime-async-std","tls-rustls-ring-webpki" ] }# async-std + rustls with ring and platform's native CA certificatessqlx = {version ="0.8",features = ["runtime-async-std","tls-rustls-ring-native-roots" ] }# async-std + rustls with aws-lc-rssqlx = {version ="0.8",features = ["runtime-async-std","tls-rustls-aws-lc-rs" ] }

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 maybe removed in the future.

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

• runtime-tokio: Use thetokio 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 thenative-tls TLS backend (OpenSSL on *nix, SChannel on Windows, Secure Transport on macOS).

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

• 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-containedSQLite 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.Seetherusqlite README for details.
  • May result in link errors if the SQLite version is too old. Version3.20.0 or newer is recommended.
  • Can increase build time due to the use of bindgen.

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

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

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

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

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

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

• uuid: Add support for UUID.

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

• time: Add support for date and time types fromtime crate (alternative tochrono, which is preferred byquery! macro, if both enabled)

• bstr: Add support forbstr::BString.

• bigdecimal: Add support forNUMERIC using thebigdecimal crate.

• rust_decimal: Add support forNUMERIC using therust_decimal crate.

• ipnet: Add support forINET andCIDR (in postgres) using theipnet crate.

• ipnetwork: Add support forINET andCIDR (in postgres) using theipnetwork crate.

• json: Add support forJSON andJSONB (in postgres) using theserde_json crate.

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

SQLx supportscompile-time checked queries. It does not, however, do this by providing a RustAPI or DSL (domain-specific language) for building queries. Instead, it provides macros that takeregular SQL as input and ensure that it is valid for your database. The way this works is thatSQLx connects to your development DB at compile time to have the database itself verify (and returnsome 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 acceptscan be used (including things added by database extensions)
• Due to the different amount of information databases let you retrieve about queries, the extent ofSQL verification you get from the query macros depends on the database

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

See theexamples/ folder for more in-depth usage.

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

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

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 toregulate how many server-side connections it's using.

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

In SQL, queries can be separated into prepared (parameterized) or unprepared (simple). Prepared queries have theirquery plancached, use a binary mode of communication (lower bandwidth and faster decoding), and utilize parametersto avoid SQL injection. Unprepared queries are simple and intended only for use where a prepared statementwill not work, such as various database commands (e.g.,PRAGMA orSET orBEGIN).

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

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

We should prefer to use the high-levelquery interface whenever possible. To make this easier, there are finalizerson 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?;

Theexecute query finalizer returns the number of affected rows, if any, and drops all received results.In addition, there arefetch,fetch_one,fetch_optional, andfetch_all to receive results.

TheQuery type returned fromsqlx::query will returnRow<'conn> from the database. Column values can be accessedby ordinal or by name withrow.get(). As theRow retains an immutable borrow on the connection, only oneRow may exist at a time.

Thefetch 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;letmut rows = sqlx::query("SELECT * FROM users WHERE email = ?").bind(email).fetch(&mut conn);whileletSome(row) = rows.try_next().await?{// map the row into a user-defined domain typelet email:&str = row.try_get("email")?;}

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

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

#[derive(sqlx::FromRow)]structUser{name:String,id:i64}letmut stream = sqlx::query_as::<_,User>("SELECT * FROM users WHERE email = ? OR name = ?").bind(user_email).bind(user_name).fetch(&mut conn);

Instead of a stream of results, we can usefetch_one orfetch_optional to request one required or optional resultfrom the database.

We can use the macro,sqlx::query! to achieve compile-time syntactic and semantic verification of the SQL, withan 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 countFROM usersGROUP BY countryWHERE organization = ?        ",        organization).fetch_all(&pool)// -> Vec<{ country: String, count: i64 }>.await?;// countries[0].country// countries[0].count

Differences fromquery():

• The input (or bind) parameters must be given all at once (and they are compile-time validated to bethe right number and the right type).

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

  { country:String, count:i64}

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

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

  DATABASE_URL=mysql://localhost/my_database

The biggest downside toquery!() is that the output type cannot be named (due to Rust notofficially supporting anonymous records). To address that, there is aquery_as!() macro that ismostly identical except that you can name the output type.

// no traits are neededstructCountry{country:String,count:i64}let countries = sqlx::query_as!(Country,"SELECT country, COUNT(*) as countFROM usersGROUP BY countryWHERE 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 nomodifications (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 thesqlx command-line tool. Seesqlx-cli/README.md.

Compile-time verified queries do quite a bit of work at compile time. Incremental actions likecargo check andcargo build can be significantly faster when using an optimized build byputting the following in yourCargo.toml (More information in theProfiles section of The Cargo Book)

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

¹ Thedotenv crate itself appears abandoned as ofDecember 2021so we now use thedotenvy crate instead. The file format is the same.

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

If thesqlite feature is enabled, this is downgraded to#![deny(unsafe_code)] with#![allow(unsafe_code)] on thesqlx::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.

Licensed under either of

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

at your option.

Unless you explicitly state otherwise, any Contribution intentionally submittedfor inclusion in the work by you, as defined in the Apache-2.0 license, shall bedual licensed as above, without any additional terms or conditions.