Inserting and reading links

Step 8 of 15. Seven steps in, every handler still answers hardcoded data — /health is the only query that reaches Postgres, and it selects a constant. This step connects the two SQL paths the whole service is: the INSERT behind POST /links and the lookup behind /{code}. The listing stops being a hardcoded row at the same time, because it is one more SELECT and the same compiler check for free. When the step is done the database is the only source of truth, and GET /{code} does the thing a link shortener exists to do — a real 302 redirect.

Start from step 7’s end state: git checkout api-ch07 in your clone of the examples repository, inside axum-sqlx-api/, with the Postgres container from step 5 running and DATABASE_URL exported. Nothing is added to Cargo.toml this step; the file below is the one the service has carried since step 5, shown because every block in this article compiles against exactly it:

Cargo.toml

[dependencies]
axum = { version = "0.8", features = ["macros"] }
serde = { version = "1", features = ["derive"] }
sqlx = { version = "0.9", features = ["runtime-tokio", "tls-rustls", "postgres", "macros"] }
tokio = { version = "1", features = ["macros", "rt-multi-thread"] }

The insert behind POST /links

create_link gains the pool as an Extension argument and makes one checked query. The stub code from step 4 finally becomes a row:

src/main.rs

async fn create_link(
    Extension(pool): Extension<PgPool>,
    Json(input): Json<CreateLink>,
) -> Result<ApiResponse<Link>, StatusCode> {
    // The code is still the stub from step 4 - generating a real one is
    // step 9. Everything else about the insert is final.
    let row = sqlx::query!(
        "INSERT INTO links (code, url) VALUES ('stub', $1) RETURNING id, code, url",
        input.url
    )
    .fetch_one(&pool)
    .await
    .map_err(|_| StatusCode::INTERNAL_SERVER_ERROR)?;

    Ok(ApiResponse::with_status(
        StatusCode::CREATED,
        Link {
            code: row.code,
            url: row.url,
        },
    ))
}

The full file is one git checkout api-ch08 away once this article lands.

Three things in those twenty lines carry the step. First, RETURNING: Postgres hands the inserted row back, id included — the primary key the database assigned — and the macro types it straight off the schema: id is i64, code and url are Strings, because the migration declared them NOT NULL. No row.get, no decode step where a typo can hide. Second, the 'stub' literal sits in the SQL on purpose: code generation is step 9, so every row gets the same code for now, and since there is no unique index yet, Postgres accepts the duplicates without complaint. Third, the return type: Result<ApiResponse<Link>, StatusCode>. Axum implements IntoResponse for Result, so the Ok side goes through the envelope and the Err side through the bare status — and ? works on the query because map_err made the sqlx error a StatusCode. The error loses its body on the way out, which is a real loss; giving it a readable one is step 10’s whole job.

A 302 the contract actually asks for

The overview’s contract says GET /9xK2mQz -> 302, Location: .... Axum has three constructors for redirects, and none of them is 302 — worth seeing before you reach for one:

use axum::http::StatusCode;
use axum::response::{IntoResponse, Redirect};

fn main() {
    let target = "https://example.com/very/long/path";
    let helpers = [
        ("Redirect::to", Redirect::to(target).into_response()),
        ("Redirect::temporary", Redirect::temporary(target).into_response()),
        ("Redirect::permanent", Redirect::permanent(target).into_response()),
    ];
    for (name, response) in helpers {
        println!("{:<20} {}", name, response.status());
    }

    let manual = (StatusCode::FOUND, [("Location", target)]).into_response();
    let location = manual.headers()["location"].to_str().unwrap();
    println!("{:<20} {} location: {location}", "(status, [(..)])", manual.status());
}

Redirect::to         303 See Other
Redirect::temporary  307 Temporary Redirect
Redirect::permanent  308 Permanent Redirect
(status, [(..)])     302 Found location: https://example.com/very/long/path

Redirect::to is 303 — a deliberate choice for form submissions, and wrong for a link that browsers, curl and chat scrapers should follow as a plain redirect. The status comes from the method, not from you. Since the contract wants 302, the handler builds the tuple directly: StatusCode::FOUND is 302, and the header array [( "Location", url )] attaches the target. Axum’s tuple impls for response parts do the rest.

The lookup behind /{code}

Now the resolver, which is a SELECT with an Option at the end of it:

src/main.rs

async fn resolve_link(
    Path(code): Path<String>,
    Extension(pool): Extension<PgPool>,
) -> Result<Response, StatusCode> {
    let row = sqlx::query!("SELECT url FROM links WHERE code = $1", code)
        .fetch_optional(&pool)
        .await
        .map_err(|_| StatusCode::INTERNAL_SERVER_ERROR)?;

    match row {
        Some(row) => Ok((
            StatusCode::FOUND,
            [("Location", row.url.as_str())],
        )
            .into_response()),
        None => Err(StatusCode::NOT_FOUND),
    }
}

fetch_optional is the whole shape of a lookup: one row or None, and the macro’s checked output means a misspelled column or a wrong type would have failed the build, not this request. A hit becomes the 302 from the last section; a miss becomes a bare 404 with no body; a dead database becomes a bare 500. Those bare error bodies are honest about being unfinished — step 10 replaces them with real error responses.

One consequence of the stub codes shows up the first time you resolve. Insert two links, and both rows have the code stub — so which one does GET /stub redirect to? The query has no ORDER BY and the schema has no unique index, so the answer is: whatever Postgres returns first, which here was the row inserted first. SQL does not promise that, and nothing in the current schema forbids the duplicates. Step 9’s unique index is what makes the question disappear.

The listing

list_links swaps its hardcoded row for the same checked SELECT, with the pagination translated into SQL:

src/main.rs

async fn list_links(
    Extension(pool): Extension<PgPool>,
    Query(p): Query<Pagination>,
) -> Result<ApiResponse<LinkPage>, StatusCode> {
    let page = p.page.unwrap_or(1).max(1) as i64;
    let limit = p.limit.unwrap_or(20) as i64;

    let rows = sqlx::query!(
        "SELECT code, url FROM links ORDER BY id LIMIT $1 OFFSET $2",
        limit,
        (page - 1) * limit
    )
    .fetch_all(&pool)
    .await
    .map_err(|_| StatusCode::INTERNAL_SERVER_ERROR)?;

    Ok(ApiResponse::ok(LinkPage {
        page: page as usize,
        limit: limit as usize,
        links: rows
            .into_iter()
            .map(|row| Link {
                code: row.code,
                url: row.url,
            })
            .collect(),
    }))
}

fetch_all returns every matching row. LIMIT $1 OFFSET $2 is the database doing the paging, so a ?page=2&limit=1 never drags the whole table across the pool. The .max(1) on page is not decoration: ?page=0 would compute a negative OFFSET, which Postgres rejects with an error — clamping to the first page turns that into a normal answer. The as i64 conversions exist because LIMIT and OFFSET take BIGINT, and the macro holds you to it: bind a usize and the build fails with a type error, which is the compiler doing its new job.

Refresh the cache, then build offline

Three new macro queries means .sqlx/ is stale the moment you edit. With the container running:

cargo sqlx prepare

query data written to .sqlx in the current directory; please check this into version control

The directory now holds one file per query — step 7’s SELECT 1 plus the insert, the lookup and the listing:

ls .sqlx

query-3fb22cbbeecbbe7e15b924b453d8296fdc7d3722987a336937646dab7ad84691.json
query-e004ebd5b5532a4b85984a62f8ad48a81aa3460c1ca07701f386135d72cdecf5.json
query-e6917d4446f6873dc9643cdbf8c75c1cb4bc2cf980339c41823393fac45d60ee.json
query-fda3cd9b694eb99a6cc1e2430b52d691a3835dfd5cc38d4d8e763286a0339638.json

And the offline story still holds, which is the point of the cache:

touch src/main.rs && env -u DATABASE_URL cargo build

   Compiling shortlink v0.1.0 (/Users/rango/www/hackingwithrust/code/axum-sqlx-api)
    Finished `dev` profile [unoptimized + debuginfo] target(s) in 0.59s

The service, end to end

Start the server and post two links — different URLs, same stub code, because generation is still a step away:

cargo run

curl -si -X POST http://127.0.0.1:8000/links \
  -H 'content-type: application/json' \
  -d '{"url":"https://example.com/very/long/path"}'

HTTP/1.1 201 Created
content-type: application/json
content-length: 58
date: Sun, 18 Oct 2026 09:30:17 GMT

{"code":"stub","url":"https://example.com/very/long/path"}

curl -si -X POST http://127.0.0.1:8000/links \
  -H 'content-type: application/json' \
  -d '{"url":"https://example.com/other"}'

HTTP/1.1 201 Created
content-type: application/json
content-length: 49
date: Sun, 18 Oct 2026 09:30:17 GMT

{"code":"stub","url":"https://example.com/other"}

Now the resolver — the row that comes back is the first one inserted, per the caveat above:

curl -si http://127.0.0.1:8000/stub

HTTP/1.1 302 Found
location: https://example.com/very/long/path
content-length: 0
date: Sun, 18 Oct 2026 09:30:29 GMT

The header arrives lowercase — the http crate normalises header names, and what curl prints is exactly what went on the wire. A code that is not in the table gets the bare 404:

curl -si http://127.0.0.1:8000/nope

HTTP/1.1 404 Not Found
content-length: 0
date: Sun, 18 Oct 2026 09:30:29 GMT

And the listing reads from the table now, paging included:

curl -si 'http://127.0.0.1:8000/links?page=2&limit=1'

HTTP/1.1 200 OK
content-type: application/json
content-length: 80
date: Sun, 18 Oct 2026 09:30:29 GMT

{"page":2,"limit":1,"links":[{"code":"stub","url":"https://example.com/other"}]}

Where this leaves the service

Every route now answers from Postgres: POST /links inserts and hands back the stored row, GET /{code} redirects browsers to a real target, GET /links pages through the table, and /health guards the pool. The hardcoded row from step 4 is gone, and the offline build still works because the refreshed .sqlx/ travels with the code.

The end state is tagged api-ch08 in the examples repository — git checkout api-ch08 inside axum-sqlx-api/. With no DATABASE_URL set, cargo build succeeding is the smoke test for the cache; with the container up, the curl sequence above replays the whole service.

Next: generating short codes. The stub is now a real liability — two posts produced two rows with the same code — so step 9 adds base62 encoding, a unique index that turns a duplicate into a database error, and a retry around it. The track overview keeps the full list current as articles land.