Storage with Postgres

The MVP forgets everything the moment it stops. Restart the process and every shortened link is gone, because the only “storage” so far is the program’s memory. That’s the core reason we need a database: data must outlive the process. Servers crash, deploy, and scale up and down constantly; the link someone shortened last week has to survive all of that. So Snip keeps links in Postgres, a durable, transactional SQL database.

If you’re weighing why SQL here at all, see SQL vs NoSQL — Snip’s data is small, relational, and benefits from strong consistency, which is squarely SQL’s home turf. And because Postgres gives us transactions and ACID guarantees, a write either fully lands or doesn’t land at all.

We talk to Postgres through sqlx — an async, pure-Rust SQL toolkit. No heavy ORM; you write SQL, sqlx runs it and maps the rows back to Rust types.

Connecting: a pool, not a connection

Opening a database connection is expensive (a TCP handshake, authentication, session setup). If every request opened its own, you’d spend more time connecting than querying. So we open a connection pool once at startup and lend connections out as needed:

    let db = PgPoolOptions::new()
        .max_connections(10)
        .connect(&database_url)
        .await
        .expect("connect to postgres");

PgPoolOptions::new() configures the pool; .max_connections(10) caps it at ten concurrent connections; .connect(&database_url) actually dials Postgres. It’s async, so we .await. The result is a PgPool — exactly the db field we put in AppState last chapter. Because a pool is shared and cheap to clone, every handler can borrow a connection from it without coordination.

:::tip Why 10? The cap protects Postgres. A database has a finite number of connection slots; if every app replica opened hundreds, you’d exhaust the server. Ten per replica is a reasonable starting point — small enough to be safe, large enough to keep requests from queuing. We revisit this when we scale out. :::

Creating the schema on startup

Snip applies its schema every time it boots:

    // Create the schema on startup (idempotent).
    sqlx::query(include_str!("../migrations/0001_init.sql"))
        .execute(&db)
        .await
        .expect("run schema");

Two things are happening. include_str!("../migrations/0001_init.sql") reads the SQL file at compile time and bakes its contents into the binary as a string — so there’s no file to ship alongside the program. Then sqlx::query(...).execute(&db) runs that SQL against the pool. This is safe to run on every startup because the migration is idempotent:

CREATE TABLE IF NOT EXISTS links (
    code       TEXT PRIMARY KEY,
    url        TEXT NOT NULL,
    clicks     BIGINT NOT NULL DEFAULT 0,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS means “make this table unless it already exists” — so the first boot creates it and every later boot is a harmless no-op. The links table is the heart of Snip: code is the short string in the URL and the primary key (unique, indexed, the thing we look up by); url is the destination and NOT NULL so you can never store a link with no target; clicks is a counter the background worker will increment; created_at records when the link was made, defaulting to now().

:::note Real migrations Schema-on-startup is great for a tutorial. Production systems use a migration tool (sqlx has one) that tracks which migrations have run and applies new ones in order — so you can evolve the schema over time without losing data. The single-file approach here is the simplest thing that works. :::

Writing a link: the `shorten` handler

Here is the insert path. Focus on the database call:

async fn shorten(
    State(state): State<AppState>,
    Json(req): Json<ShortenRequest>,
) -> Result<Json<ShortenResponse>, (StatusCode, String)> {
    if !req.url.starts_with("http://") && !req.url.starts_with("https://") {
        return Err((
            StatusCode::BAD_REQUEST,
            "url must start with http:// or https://".to_string(),
        ));
    }
    let code = nanoid::nanoid!(7);
    sqlx::query("INSERT INTO links (code, url) VALUES ($1, $2)")
        .bind(&code)
        .bind(&req.url)
        .execute(&state.db)
        .await
        .map_err(|e| (StatusCode::INTERNAL_SERVER_ERROR, e.to_string()))?;
    state.metrics.shortens.fetch_add(1, Ordering::Relaxed);
    Ok(Json(ShortenResponse {
        short_url: format!("{}/{}", state.base_url, code),
        code,
    }))
}

The two extractors in the signature pull request data apart: State(state) hands us the shared AppState, and Json(req) parses the request body into a ShortenRequest (that’s serde’s Deserialize at work). The handler first validates the URL has an http/https scheme, returning a 400 Bad Request otherwise. Then nanoid::nanoid!(7) generates a random 7-character code.

The query itself is the lesson:

    sqlx::query("INSERT INTO links (code, url) VALUES ($1, $2)")
        .bind(&code)
        .bind(&req.url)

Notice the SQL contains $1 and $2 — placeholders, not the actual values. We supply the values separately with .bind(&code) and .bind(&req.url). This is a parameterized query, and it is how you prevent SQL injection. If we instead built the string with format!("... VALUES ('{}', '{}')", code, url), a malicious url like '); DROP TABLE links; -- would be executed as SQL. With binding, the value is sent to Postgres separately from the query text and treated strictly as data — it can never become code. Always bind; never interpolate user input into SQL.

Error handling: `Result`, `?`, and `map_err`

The handler returns Result<Json<ShortenResponse>, (StatusCode, String)> — either a JSON success body or a status-code-plus-message error. Rust functions that can fail return Result<T, E>. The .execute(&state.db).await call returns a Result, and we handle it with two operators:

.map_err(|e| (StatusCode::INTERNAL_SERVER_ERROR, e.to_string())) converts sqlx’s error type into our error type (the status/message tuple). The closure runs only on the error case.
The trailing ? then says: if this is an error, return it from the function now; otherwise unwrap the success value and keep going. It’s early-return-on-error in one character.

On success we bump the shortens metric and return the JSON. format! builds the full short URL from base_url and the new code.

Reading a link: the `SELECT` inside `redirect`

The redirect handler does more (it checks Redis first — that’s the next chapter), but the database fallback is the part that matters here:

        let row: Option<(String,)> = sqlx::query_as("SELECT url FROM links WHERE code = $1")
            .bind(&code)
            .fetch_optional(&state.db)
            .await
            .map_err(|_| StatusCode::INTERNAL_SERVER_ERROR)?;
        match row {
            Some((url,)) => {
                let _: Result<(), _> = conn.set_ex(&cache_key, &url, 3600u64).await;
                url
            }
            None => {
                state.metrics.not_found.fetch_add(1, Ordering::Relaxed);
                return Err(StatusCode::NOT_FOUND);
            }
        }

Again the query is parameterized: WHERE code = $1 with .bind(&code). Two differences from the insert:

sqlx::query_as (instead of query) maps each row into a Rust type. Here the target is a tuple (String,) — a one-column row holding the url. The type annotation Option<(String,)> tells sqlx what to build.
.fetch_optional(...) returns Option<Row>: Some(row) if a matching code exists, None if not. This is exactly right for a lookup that may miss — a non-existent code isn’t an error, it’s a legitimate “not found.”

The match then branches: on Some, we cache the URL and return it; on None, we bump the not_found metric and return 404 Not Found. (The set_ex call is the cache write — covered next chapter.) Using Option here, rather than treating a missing row as a failure, is idiomatic Rust: absence is a value you handle, not an exception you catch.

Try it

Bring up Postgres in Docker — plus Redis, since Snip connects to both at startup (Redis earns its keep in the next chapter) — then run Snip and create a link:

docker run --rm -d --name snip-pg \
  -e POSTGRES_USER=snip -e POSTGRES_PASSWORD=snip -e POSTGRES_DB=snip \
  -p 5432:5432 postgres:16

docker run --rm -d --name snip-redis -p 6379:6379 redis:7

cargo run --bin snip

# in another terminal:
curl -X POST localhost:8080/shorten \
  -H 'content-type: application/json' \
  -d '{"url":"https://www.rust-lang.org"}'
# → {"code":"aB3xK7q","short_url":"http://0.0.0.0:8080/aB3xK7q"}

The link now lives in Postgres. Stop and restart cargo run --bin snip and the same code still resolves — because the data outlived the process, which was the whole point.

Check your understanding

Why does Snip use a connection pool instead of opening a connection per request?
What makes the schema-on-startup query safe to run on every boot?
Explain how $1 + .bind() prevents SQL injection. What would go wrong if you used format! to build the query string instead?
What does .fetch_optional() return, and why is Option a better fit than an error for a missing code?
What do map_err and the ? operator each do in the shorten handler’s database call?

Show answers

Opening a connection is expensive — a TCP handshake, authentication, and session setup — so if every request opened its own, you’d spend more time connecting than querying. A pool opens connections once at startup (max_connections(10)) and lends them out; it’s shared and cheap to clone, so every handler borrows a connection without coordination, and the cap protects Postgres from connection exhaustion.
The migration is idempotent: CREATE TABLE IF NOT EXISTS links (...) means “make this table unless it already exists,” so the first boot creates it and every later boot is a harmless no-op. That’s why running the schema on every startup is safe.
With $1 + .bind(), the value is sent to Postgres separately from the query text and treated strictly as data — it can never become SQL. If you used format! to interpolate user input instead, a malicious url like '); DROP TABLE links; -- would be parsed as SQL and executed — that’s a SQL-injection vulnerability. Always bind; never interpolate user input.
.fetch_optional() returns Option<Row> — Some(row) if a matching code exists, None if not. Option is the better fit because a non-existent code isn’t a failure, it’s a legitimate “not found” you handle as a value (returning 404) rather than an exception you catch — idiomatic Rust treats absence as a value.
.map_err(|e| (StatusCode::INTERNAL_SERVER_ERROR, e.to_string())) converts sqlx’s error type into our error type (the status/message tuple), running only on the error case. The trailing ? then early-returns that error from the function if present, or unwraps the success value and continues — early-return-on-error in one character.