SQLx: The Rust SQL Toolkit

When the migrate feature is enabled, #[sqlx::test] can automatically create isolated test databases for every annotated function. This ensures tests are isolated from one another.

To activate this, change your test function signature to one of the following:

• async fn(Pool<DB>) -> Ret (Pools share a single connection limit)
• async fn(PoolConnection<DB>) -> Ret
• async fn(PoolOptions<DB>, impl ConnectOptions<DB>) -> Ret (Use this if you need to customize the pool, e.g., setting max_connections)

Database Requirements:

• Postgres/MySQL: Requires a DATABASE_URL environment variable (or .env file) providing a superuser connection to manage the test databases.
• SQLite: Does not require DATABASE_URL. Test databases are stored in target/sqlx/test-dbs/<path>.sqlite, where <path> is the function path.

Cleanup:

• Successful tests are automatically cleaned up.
• Failed tests (including panics) leave databases in place for debugging.
• Previous test databases are deleted the next time a test binary using #[sqlx::test] is run to save disk space.

use sqlx::{pool::PoolOptions, postgres::PgConnectOptions, Postgres};

#[sqlx::test]
async fn basic_test(
    pool_options: PoolOptions<Postgres>,
    connect_options: PgConnectOptions,
) -> sqlx::Result<()> {
    let pool = pool_options
        .max_connections(1)
        .connect_with(connect_options)
        .await?;

    sqlx::query("SELECT 1").execute(&pool).await?;

    Ok(())
}

In complex projects, you can manage multiple schemas or databases by using separate sqlx.toml files in different subcrates. This allows each crate to own its own schema and its own set of migrations.

In this pattern:

• A main crate can own the public schema and manage its migrations in a specific directory (e.g., using the migrate.migrations-dir config key).
• Subcrates (like accounts or payments) can own specific schemas (e.g., accounts or payments) and manage their own migrations independently.

Important: Use Schema-Qualified Names When working with multiple schemas, avoid relying on changing the connection's search_path to eliminate schema prefixes. Relying on search_path can lead to errors if names conflict or if the migrator attempts to reference a table unqualified when the _sqlx_migrations table exists in multiple schemas. It is best practice to use fully qualified names (e.g., schema_name.table_name) for clarity and reliability.

SQLx distinguishes between two types of queries:

1. Prepared (Parameterized) Queries: Use sqlx::query or sqlx::query_as. These are cached, use binary communication for speed, and protect against SQL injection via parameters. In SQLx, a Query or QueryAs struct represents a prepared query.
2. Unprepared (Simple) Queries: Use a &str directly with an executor. These are intended for database commands that do not support prepared statements, such as PRAGMA, SET, or BEGIN.

Query Finalizers

Use high-level finalizers to execute queries against a connection (&mut conn) or a pool (&pool):

• .execute(): Returns the number of affected rows.
• .fetch(): Returns a stream of rows.
• .fetch_one(): Returns exactly one row (errors if zero or multiple found).
• .fetch_optional(): Returns an Option<Row> (useful when zero rows is not an error).
• .fetch_all(): Returns a Vec<Row>.

SQLx is not an ORM. It does not provide a DSL (Domain Specific Language) for building queries. Instead, it uses the macros feature to provide query*! macros that take raw SQL strings as input.

How it works: At compile time, SQLx connects to your development database. The database itself verifies the SQL syntax and returns information about the query (such as expected types). This allows SQLx to ensure your queries are valid and type-safe without needing to implement its own SQL parser.

Implications:

• Database-Specific Syntax: Since the database performs the verification, you can use any syntax supported by your specific database (including extensions).
• Verification Depth: The level of verification depends on how much information your specific database engine provides about its queries.
• Requirement: You must have a running development database available during the compilation process.

SQLx provides macros for compile-time syntactic and semantic verification of your SQL queries. This ensures your queries are valid and that the types match your Rust code.

sqlx::query!

Returns an anonymous record type where each SQL column is a field. Because the type is anonymous, you cannot name it in a function signature.

sqlx::query_as!

Identical to query!, but allows you to map the results into a named struct.

Requirements for Compile-time Verification

1. DATABASE_URL: This environment variable must be set at build time. It should point to a database with the same schema as your production database so SQLx can prepare the queries against it.
2. Offline Mode: To avoid requiring a live database during CI/CD or builds, you can use the sqlx-cli to enable "offline mode," which caches query analysis results in a JSON file.

Performance Tip

To speed up incremental builds (like cargo check), add the following to your Cargo.toml to optimize the macro expansion:

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

// query! returns an anonymous record
let countries = sqlx::query!(
    "SELECT country, COUNT(*) as count FROM users GROUP BY country WHERE organization = ?",
    organization
)
.fetch_all(&pool)
.await?;
// countries[0].country

// query_as! maps to a named struct
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)
.await?;

There are three main ways to map database rows to Rust types:

1. Manual mapping with Row::get or Row::try_get

Access columns by name or ordinal index using the sqlx::Row trait.

2. Using the map closure on a stream

You can transform rows as they are fetched using the .map() method on a fetch() stream.

3. Using sqlx::FromRow and query_as

Derive sqlx::FromRow on your struct and use sqlx::query_as to automatically map columns to fields.

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

let users = sqlx::query_as::<_, User>("SELECT name, id FROM users")
    .fetch_all(&pool)
    .await?;

// Using map on a stream
use futures_util::TryStreamExt;
use sqlx::Row;

let mut rows = sqlx::query("SELECT * FROM users WHERE email = ?")
    .bind("user@example.com")
    .fetch(&mut conn);

while let Some(row) = rows.try_next().await? {
    let email: &str = row.try_get("email")?;
}

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

let mut stream = sqlx::query_as::<_, User>("SELECT * FROM users WHERE email = ? OR name = ?")
    .bind("user@example.com")
    .bind("example_username")
    .fetch(&mut conn);