Rust databasesmigrationsSQLxtransactions

Article & Tutorial

Database Access in Rust with SQLx

Use SQLx pools, parameterized queries, migrations, and transactions to give a Rust service a clear database boundary.

Melochip Editorial

@melochip-editorial

Published Sep 8, 2026 4 min read
React Comments Share More
Database Access in Rust with SQLx featured illustration
Database Access in Rust with SQLx featured illustration
Contents

SQLx gives Rust applications an asynchronous database interface without hiding SQL behind an ORM abstraction. The important design work is still yours: configure a bounded pool, bind untrusted values, separate migrations from runtime queries, and make transaction boundaries explicit.

Learning outcomes

  • create a pool from configuration rather than source-controlled secrets;
  • use parameterized queries for user-controlled values;
  • distinguish compile-time query checking from runtime database availability;
  • run a transaction and understand commit and rollback behavior;
  • set bounded timeouts and map failures safely.

Prerequisites

Have stable Rust, Cargo, and access to a disposable local database for the hands-on portion. Choose the SQLx driver and runtime features deliberately; the examples below use PostgreSQL types and Tokio, but the same design principles apply to other supported drivers with syntax differences.

text
[dependencies]
sqlx = { version = "0.8", features = ["runtime-tokio", "postgres", "migrate"] }
tokio = { version = "1", features = ["macros", "rt-multi-thread"] }

Verify current versions and feature names before implementation.

Keep connection settings out of code

Use a secret manager or protected environment configuration in the application runtime. Never place a password in source, a tutorial URL, a shell transcript, or an error message.

rust
use sqlx::{postgres::PgPoolOptions, PgPool};
use std::time::Duration;

async fn connect(database_url: &str) -> Result<PgPool, sqlx::Error> {
    PgPoolOptions::new()
        .max_connections(8)
        .acquire_timeout(Duration::from_secs(5))
        .connect(database_url)
        .await
}

The pool limit is an application decision. It should reflect database capacity, request concurrency, and timeout behavior rather than an arbitrary large value.

Parameterize values

Diagram showing bound values and an allowlisted structure entering a transaction-safe database path.
Parameterized values protect the value boundary; authorization remains application work.

Use bind parameters for values supplied by a caller or another untrusted source:

rust
use sqlx::PgPool;

async fn find_note(pool: &PgPool, owner_id: i64) -> Result<Option<String>, sqlx::Error> {
    sqlx::query_scalar("SELECT body FROM notes WHERE owner_id = $1 ORDER BY id LIMIT 1")
        .bind(owner_id)
        .fetch_optional(pool)
        .await
}

Binding protects the value boundary; it does not validate authorization or make an arbitrary query safe. Identifiers such as table names need a separate, allowlisted design because they cannot normally be bound as values.

Migrations are a separate lifecycle

Keep schema changes versioned and reviewed. Run migrations through the approved deployment process, not opportunistically from every request handler. A local development migration might create a notes table, but this editorial package does not prescribe a production schema or execute a migration.

When using SQLx’s compile-time query macros, understand what database metadata must be available during compilation and how the lockfile and offline metadata are maintained. A compile-time check is not a substitute for runtime tests.

Use transactions for related changes

rust
use sqlx::{PgPool, Postgres, Transaction};

async fn create_note(
    pool: &PgPool,
    owner_id: i64,
    body: &str,
) -> Result<(), sqlx::Error> {
    let mut tx: Transaction<'_, Postgres> = pool.begin().await?;

    sqlx::query("INSERT INTO notes (owner_id, body) VALUES ($1, $2)")
        .bind(owner_id)
        .bind(body)
        .execute(&mut *tx)
        .await?;

    tx.commit().await
}

If a later operation returns an error before commit, dropping the transaction rolls it back according to SQLx’s transaction behavior. Test both success and failure paths; do not assume rollback if the connection has already failed.

Handle errors without leaking internals

Classify errors for the caller and logs separately. A duplicate-key error may become a bounded conflict response, while a database-unavailable error may become a retryable service error. Logs should contain a correlation identifier and safe classification, not passwords, connection strings, query parameters, or full database responses.

Common mistakes and troubleshooting

  • Pool exhaustion: lower request concurrency, inspect slow queries, and choose pool and timeout bounds together.
  • Compile-time query failure: confirm the selected driver, schema metadata, feature flags, and SQL dialect.
  • Transaction appears committed: verify the code reaches commit and that all related statements use the same transaction executor.
  • Unsafe string interpolation: replace value interpolation with .bind(); use allowlists for structural SQL choices.
  • Migration drift: inspect the migration history and lockfile rather than editing a live schema manually.

Security and production considerations

Use least-privilege database identities, TLS where supported, secret rotation, bounded pool sizes, statement and request timeouts, backups, migration review, audit-safe logs, and authorization checks at the application boundary. SQLx does not decide whether a caller may read or mutate a row.

Summary

SQLx keeps SQL visible while providing async pooling and optional compile-time checking. Safe database access still depends on parameter binding, explicit ownership of transactions, bounded resources, and careful operational review.

Next step and technical review flags

Continue with Unsafe Rust and FFI. Verify current SQLx feature flags, driver support, query-macro behavior, transaction semantics, and migration workflow against the official SQLx repository and documentation. Compile the examples with the exact approved dependency versions and a disposable database before publication.

Did this help?

Yes No

Join the discussion

0 Comments

Ask questions, add context, or share what worked.

Sign in to join the discussion

No comments yet. Start the discussion.

More from Melochip Editorial

Related articles

Stay in the loop. Get new Melochip articles

Receive Melochip article updates when new articles are available. Explicit opt-in only.

No pre-checked boxes. Unsubscribe support is part of the subscriber foundation.