$devvkit learn --librarie sqlx-guide

SQLx Guide

[rust][database][sql]

Rust

Install

cargo add sqlx --features runtime-tokio,postgres
cargo add sqlx-cli

SQLx is an async, pure-Rust SQL crate. No ORM: write SQL queries directly.

The sqlx::query! macro checks SQL syntax against your database at compile time.

Supports PostgreSQL, MySQL, SQLite, and MSSQL with connection pooling and migrations.

Setup

Dependencies· Cargo.toml setup.

cargo add sqlx --features runtime-tokio,postgres
cargo add sqlx-cli
cargo install sqlx-cli

Queries

Query (runtime)· Dynamic query with types.

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

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

Query (compile-time)· Checked at compile time.

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

Query scalar· Single value.

let count: i64 = sqlx::query_scalar("SELECT COUNT(*) FROM users")
    .fetch_one(&pool)
    .await?;

Migrations

Create migration· Add migration file.

sqlx migrate add create_users_table

Run migrations· Apply pending.

sqlx migrate run

// Or from code:
sqlx::migrate!().run(&pool).await?;

Pooling

Create pool· Connection pool.

use sqlx::postgres::PgPool;

let pool = PgPool::connect("postgres://user:pass@localhost/db").await?;

Transactions

Transaction· Atomic operations.

let mut tx = pool.begin().await?;
sqlx::query("UPDATE accounts SET balance = balance - 100 WHERE id = ")
    .bind(1)
    .execute(&mut *tx)
    .await?;
sqlx::query("UPDATE accounts SET balance = balance + 100 WHERE id = ")
    .bind(2)
    .execute(&mut *tx)
    .await?;
tx.commit().await?;

Testing

Test fixture· Test with DB.

#[sqlx::test]
async fn test_create_user(pool: PgPool) {
    sqlx::query("INSERT INTO users (name, email) VALUES (, )")
        .bind("Alice")
        .bind("alice@example.com")
        .execute(&pool)
        .await?;
}