07. Database with SQLx

🎁 SQLx checks your SQL queries at compile time against a real database. Not at runtime — at compile time. Typos in column names, wrong parameter counts, type mismatches — all caught before you ship.

SQLx Dependencies

[dependencies]
sqlx = { version = "0.8", features = ["runtime-tokio", "postgres", "uuid", "chrono"] }
uuid = { version = "1", features = ["v4"] }
chrono = { version = "0.4", features = ["serde"] }

• runtime-tokio — uses tokio's async runtime
• postgres — PostgreSQL driver
• uuid — for generating short codes
• chrono — for timestamps

For production databases that require TLS (Railway, Supabase, Render, Fly.io — basically any hosted PostgreSQL), add one of:

sqlx = { version = "0.8", features = ["runtime-tokio", "postgres", "uuid", "chrono", "tls-native-tls"] }
# or for a pure-Rust TLS stack:
sqlx = { version = "0.8", features = ["runtime-tokio", "postgres", "uuid", "chrono", "tls-rustls"] }

Without a TLS feature, connections to cloud databases that enforce SSL will fail at runtime. For local development with a plain PostgreSQL (no TLS), the feature is not required.

Connecting

use sqlx::postgres::PgPoolOptions;

pub async fn create_pool(database_url: &str) -> sqlx::PgPool {
    PgPoolOptions::new()
        .max_connections(10)
        .connect(database_url)
        .await
        .expect("failed to connect to database")
}

PgPool is a connection pool — multiple async tasks share a fixed set of connections. max_connections(10) is a reasonable default for most services.

The Link Model

use chrono::{DateTime, Utc};
use serde::Serialize;

#[derive(Debug, Serialize, sqlx::FromRow)]
pub struct Link {
    pub id: i64,
    pub code: String,
    pub url: String,
    pub clicks: i64,
    pub created_at: DateTime<Utc>,
}

sqlx::FromRow derives the mapping from a database row to your struct. Column names in the query must match field names.

Querying

Use sqlx::query_as! for compile-time checked queries:

use sqlx::PgPool;
use crate::error::AppError;

pub async fn find_by_code(pool: &PgPool, code: &str) -> Result<Option<Link>, AppError> {
    let link = sqlx::query_as!(
        Link,
        "SELECT id, code, url, clicks, created_at FROM links WHERE code = $1",
        code
    )
    .fetch_optional(pool)
    .await?;

    Ok(link)
}

$1 is the PostgreSQL placeholder. fetch_optional returns None if no row matches — no panics, no errors, just an empty result.

Inserting

pub async fn create(pool: &PgPool, url: &str) -> Result<Link, AppError> {
    let code = generate_code();

    let link = sqlx::query_as!(
        Link,
        "INSERT INTO links (code, url) VALUES ($1, $2)
         RETURNING id, code, url, clicks, created_at",
        code,
        url
    )
    .fetch_one(pool)
    .await?;

    Ok(link)
}

fn generate_code() -> String {
    use uuid::Uuid;
    Uuid::new_v4().to_string()[..8].to_string()
}

RETURNING gives you the inserted row back including database-generated values like id and created_at.

Updating

pub async fn increment_clicks(pool: &PgPool, code: &str) -> Result<(), AppError> {
    sqlx::query!(
        "UPDATE links SET clicks = clicks + 1 WHERE code = $1",
        code
    )
    .execute(pool)
    .await?;

    Ok(())
}

query! (without _as!) is for queries that don't return rows.

The DATABASE_URL Environment Variable

SQLx needs DATABASE_URL at compile time to check queries. Set it in .env:

DATABASE_URL=postgres://postgres:password@localhost:5432/urlshortener

Also set it in your shell or CI environment before running cargo build.

If you don't have a database available at compile time, use SQLX_OFFLINE=true and commit the .sqlx/ query cache that SQLx generates with cargo sqlx prepare.

Key Takeaways

• PgPool is a connection pool — pass it by reference to every query function
• sqlx::query_as! checks SQL against the real database at compile time
• $1, $2 are PostgreSQL parameter placeholders — SQLx maps them to Rust types
• fetch_optional returns None on no match; fetch_one errors on no match
• RETURNING in an INSERT gives you the full inserted row including generated fields
• Set DATABASE_URL in .env for compile-time query checking; use SQLX_OFFLINE=true in CI

🎁 Your queries work but your database has no tables yet. You created the schema by hand and the next developer starts fresh. Next up: migrations — version-controlled schema changes that run automatically.