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.

Supported Function Signatures

To activate this feature, 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 to create a custom Pool, e.g., to set max_connections(1) to avoid exceeding server limits during parallel tests).

Database Requirements

• Postgres/MySQL: Requires DATABASE_URL to be set in your environment or a .env file so the driver can manage test databases.
• SQLite: Does not require DATABASE_URL. It defaults to target/sqlx/test-dbs/<path>.sqlite where <path> is the function path.

Cleanup Behavior

• Success: Test databases are automatically cleaned up.
• Failure/Panic: Databases are left in place to facilitate debugging.
• Disk Space: Previously created test databases are deleted the next time a test binary using #[sqlx::test] is run.

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

SQLx provides the sqlx::query! and sqlx::query_as! macros to verify your SQL syntax and types at compile time.

sqlx::query!

Returns an anonymous record type where each SQL column is a field.

• Requirement: The DATABASE_URL environment variable must be set at build time to a database with the correct schema.
• Usage: Parameters must be provided all at once.

sqlx::query_as!

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

Offline Mode

To compile without a live database, you can use the sqlx CLI to enable "offline mode," which caches query analysis results in a file.

// Using query! (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

// Using query_as! (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?;

To communicate securely with database servers over untrusted networks, you must enable a TLS feature.

Available TLS Features:

• tls-native-tls: Uses the OS-native TLS capabilities (SecureTransport on macOS, SChannel on Windows, or OpenSSL on other platforms).
• tls-rustls: Uses the cross-platform rustls library. Supports TLS 1.2 and 1.3.
  • tls-rustls-ring: The default rustls provider using ring. Has fewer build-time dependencies.
  • tls-rustls-aws-lc-rs: Uses the aws-lc-rs provider. Enables additional cipher suites but has more complex build requirements.

Precedence and Troubleshooting:

• If multiple TLS features are enabled, tls-native-tls takes precedence.
• If you encounter HandshakeFailure errors while using tls-rustls, your database server may not support TLS 1.2 or 1.3. Switching to tls-native-tls often resolves this.
• If your connection configuration requires a TLS upgrade but no TLS feature is enabled, the connection attempt will fail with an error.

# Example: Enabling native-tls
sqlx = { version = "0.9.0", features = ["runtime-tokio", "postgres", "tls-native-tls"] }

To build your project without an active database connection (useful for CI/CD), you can use "offline mode". This involves saving query metadata to a .sqlx directory.

Steps to enable offline mode

1. Save metadata: Run cargo sqlx prepare. This saves metadata to a .sqlx directory in your current directory. For workspaces, use the --workspace flag to generate a single directory at the root.
2. Check in metadata: Commit the .sqlx directory to version control.
3. Build: Use cargo build as usual; SQLx will now use the metadata instead of connecting to a database.

Verification and CI

Use cargo sqlx prepare --check (or --check --workspace) in CI environments. This command exits with a non-zero status if the .sqlx metadata is out of sync with your current database schema or queries.

Advanced Configuration

• Force offline mode: If a DATABASE_URL is present, SQLx will try to connect to a database by default. To prevent this and force offline mode, set the SQLX_OFFLINE environment variable to true (you can add this to your .env file).
• Include test/feature-flagged queries: To ensure queries inside tests or behind feature flags are captured, pass arguments through to cargo: cargo sqlx prepare -- --all-targets --all-features

# Save metadata for the current crate
cargo sqlx prepare

# Save metadata for the entire workspace
cargo sqlx prepare --workspace

# Check if metadata is out of date (use in CI)
cargo sqlx prepare --check

# Prepare including all targets and features
cargo sqlx prepare -- --all-targets --all-features