SQLx

🧰 The Rust SQL Toolkit

Install | Usage | Docs | Ecosystem

Built with ❤️ byThe LaunchBadge team

Have a question? Be sure tocheck the FAQ first!

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.

  • MSSQLwas 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 usingzerounsafe_††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 portingallof SQLite to Rust).

†† SQLx uses#![forbid(unsafe_code)]unless thesqlitefeature 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 are prepared and cached per connection.

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

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

• Asynchronous notifications usingLISTENandNOTIFYforPostgreSQL.

• Nested transactions with support for save points.

• Anydatabase driver for changing the database driver at runtime. AnAnyPoolconnects to the driver indicated by the URL scheme.

Install

SQLx is compatible with theasync-std,tokio,andactixruntimes; and, thenative-tlsandrustlsTLS 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-tls
sqlx= {version="0.8",features= ["runtime-tokio","tls-native-tls"] }
#tokio + rustls with ring
sqlx= {version="0.8",features= ["runtime-tokio","tls-rustls-ring"] }
#tokio + rustls with aws-lc-rs
sqlx= {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-tls
sqlx= {version="0.8",features= ["runtime-async-std","tls-native-tls"] }
#async-std + rustls with ring
sqlx= {version="0.8",features= ["runtime-async-std","tls-rustls-ring"] }
#async-std + rustls with aws-lc-rs
sqlx= {version="0.8",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 theasync-stdruntime without enabling a TLS backend.

• runtime-async-std-native-tls:Use theasync-stdruntime andnative-tlsTLS backend (SOFT-DEPRECATED).

• runtime-async-std-rustls:Use theasync-stdruntime andrustlsTLS backend (SOFT-DEPRECATED).

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

• runtime-tokio-native-tls:Use thetokioruntime andnative-tlsTLS backend (SOFT-DEPRECATED).

• runtime-tokio-rustls:Use thetokioruntime andrustlsTLS backend (SOFT-DEPRECATED).

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

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

• tls-rustls:Use therustlsTLS 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-containedSQLitedatabase engine.

• any:Add support for theAnydatabase 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 (in Postgres).

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

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

• bstr:Add support forbstr::BString.

• bigdecimal:Add support forNUMERICusing thebigdecimalcrate.

• rust_decimal:Add support forNUMERICusing therust_decimalcrate.

• ipnetwork:Add support forINETandCIDR(in postgres) using theipnetworkcrate.

• json:Add support forJSONandJSONB(in postgres) using theserde_jsoncrate.

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

SQLx is not an ORM!

SQLx supportscompile-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 newEcosystem wiki page!

Usage

See theexamples/folder for more in-depth usage.

Quickstart

usesqlx::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.
letpool =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)
letrow:(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 callingconnect().

usesqlx::Connection;

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

letpool =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 plancached,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.,PRAGMAorSETorBEGIN).

SQLx supports all operations with both types of queries. In SQLx, a&stris treated as an unprepared query, and aQueryorQueryAsstruct 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-levelqueryinterface 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(&mutconn).await?;
sqlx::query("DELETE FROM table").execute(&pool).await?;

Theexecutequery finalizer returns the number of affected rows, if any, and drops all received results. In addition, there arefetch,fetch_one,fetch_optional,andfetch_allto receive results.

TheQuerytype returned fromsqlx::querywill returnRow<'conn>from the database. Column values can be accessed by ordinal or by name withrow.get().As theRowretains an immutable borrow on the connection, only one Rowmay exist at a time.

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

// provides `try_next`
usefutures::TryStreamExt;
// provides `try_get`
usesqlx::Row;

letmutrows = sqlx::query("SELECT * FROM users WHERE email =?")
.bind(email)
.fetch(&mutconn);

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

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

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

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

letmutstream = sqlx::query_as::<_,User>("SELECT * FROM users WHERE email =? OR name =?")
.bind(user_email)
.bind(user_name)
.fetch(&mutconn);

Instead of a stream of results, we can usefetch_oneorfetch_optionalto 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).

letcountries = 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 fromquery():

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

  {country:String,count:i64}

• TheDATABASE_URLenvironment 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 usea.envfile¹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 not officially supporting anonymous records). To address that, there is aquery_as!()macro that is mostly identical except that you can name the output type.

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

letcountries = 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 thesqlxcommand-line tool. See sqlx-cli/README.md.

Compile-time verified queries do quite a bit of work at compile time. Incremental actions like cargo checkandcargo buildcan be significantly faster when using an optimized build by putting the following in yourCargo.toml(More information in the Profiles sectionof The Cargo Book)

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

¹Thedotenvcrate itself appears abandoned as ofDecember 2021 so we now use thedotenvycrate instead. The file format is the same.

Safety

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

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

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

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.