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.
Type-safe SQL (if you want it) without DSLs. Use the query!() macro to check your SQL and bind parameters at
compile time. (You can still use dynamic SQL queries if you like.)
Database Agnostic. Support for [PostgreSQL], [MySQL], and [SQLite].
Pure Rust. The Postgres and MySQL/MariaDB drivers are written in pure Rust using zero unsafe†† code.
Runtime Agnostic. Works on async-std or tokio with the runtime-async-std or runtime-tokio cargo feature flag.
† 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.
SQLx is compatible with the [async-std] and [tokio] runtimes.
async-std
```toml
[dependencies] sqlx = "0.3" ```
tokio
```toml
[dependencies] sqlx = { version = "0.3", default-features = false, features = [ "runtime-tokio", "macros" ] } ```
runtime-async-std (on by default): Use the async-std runtime.
runtime-tokio: Use the tokio runtime. Mutually exclusive with the runtime-async-std feature.
postgres: Add support for the Postgres database server.
mysql: Add support for the MySQL (and MariaDB) database server.
sqlite: Add support for the self-contained SQLite database engine.
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, prefered by query! macro, if both enabled)
bigdecimal: Add support for NUMERIC using the bigdecimal 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.
```rust use std::env;
use sqlx::postgres::PgPool; // use sqlx::mysql::MySqlPool; // etc.
async fn main() -> Result<(), sqlx::Error> { // Create a connection pool let pool = PgPool::builder() .maxsize(5) // maximum number of connections in the pool .build(env::var("DATABASEURL")?).await?;
// Make a simple query to return the given parameter
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().
```rust use sqlx::Connect;
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::new("mysql://user:pass@host/database").await?;
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, fetch_all, and fetch_scalar 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 let mut cursor = sqlx::query("SELECT * FROM users WHERE email = ?") .bind(email) .fetch(&mut conn).await?;
while let Some(row) = cursor.next().await? { // map the row into a user-defined domain type } ```
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
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.
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() // -> 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
{ 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:
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 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() // -> Vec
// countries[0].country // countries[0].count ```
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.
Licensed under either of
at your option.
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.