Database (Postgres) with sqlx

Use case

You need to connect to PostgreSQL, run queries, and map rows to Rust types. The sqlx crate provides async, compile-time checked SQL with Postgres support.

In C#: Like Dapper or Npgsql: connection string, command/query, map rows to objects. In Rust, sqlx uses async and optional compile-time SQL checking.

Connection

Create a connection pool from a URL. Use sqlx::postgres::PgPoolOptions and connect or connect_lazy (connect on first use).

use sqlx::postgres::PgPoolOptions;

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let pool = PgPoolOptions::new()
        .max_connections(5)
        .connect("postgres://user:pass@localhost/dbname").await?;
    // use pool
    Ok(())
}

In C#: Like NpgsqlConnection or a connection string in app settings. The pool is similar to connection pooling in ADO.NET.

Querying and mapping

Use sqlx::query_as to map rows to a struct. The struct fields (or #[sqlx(rename = "col")]) must match column names or positions.

use sqlx::FromRow;

#[derive(Debug, FromRow)]
struct User {
    id: i32,
    name: String,
    email: Option<String>,
}

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

In C#: Like connection.Query<User>("SELECT id, name, email FROM users") in Dapper. Option<String> maps to nullable columns.

Executing (no rows)

Use sqlx::query for INSERT/UPDATE/DELETE. Call .execute(&pool).await? when you don’t need rows back.

sqlx::query("INSERT INTO users (name, email) VALUES ($1, $2)")
    .bind("Alice")
    .bind("alice@example.com")
    .execute(&pool)
    .await?;

In C#: Like ExecuteNonQuery with parameters. Use .bind(...) for parameters ($1, $2, …).

Parameters

Always use .bind(value) for parameters; never concatenate user input into SQL. Parameters are $1, $2, … in Postgres.

In C#: Same as parameterized queries in ADO.NET or Npgsql; prevents SQL injection.

Key takeaway

Use sqlx::PgPool (or PgPoolOptions::new().connect(...).await) for connections. Use query_as::<_, YourStruct>("SELECT ...") to map rows, and query("INSERT ...").bind(...).execute(&pool) for writes. All operations are async and return Result; use ? with an async main (e.g. #[tokio::main]).