# Step 5 — Talking to Postgres with SQLx

Our handlers return fake data. Let's connect a real PostgreSQL database using **SQLx** — an async, pure-Rust toolkit whose headline feature is **compile-time-checked SQL**.

## Set up a database

Get a Postgres running (Docker is easiest):

```bash
docker run --name tasks-db -e POSTGRES_PASSWORD=secret \
  -e POSTGRES_DB=tasks -p 5432:5432 -d postgres:16
```

Set the connection string SQLx reads:

```bash
export DATABASE_URL="postgres://postgres:secret@localhost:5432/tasks"
```

Create the table (via `psql`, a migration, or the SQLx CLI):

```sql
CREATE TABLE tasks (
    id         BIGSERIAL PRIMARY KEY,
    title      TEXT      NOT NULL,
    done       BOOLEAN   NOT NULL DEFAULT false,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
```

## The connection pool

Open **one pool** at startup and share it via `State` (Step 4). A pool holds a set of live connections and hands them out per query — opening a fresh connection per request would be far too slow.

```rust
use sqlx::postgres::PgPoolOptions;

#[tokio::main]
async fn main() {
    let pool = PgPoolOptions::new()
        .max_connections(5)
        .connect(&std::env::var("DATABASE_URL").unwrap())
        .await
        .expect("failed to connect to Postgres");

    let app = Router::new()
        .route("/tasks", get(list_tasks).post(create_task))
        .route("/tasks/{id}", get(get_task))
        .with_state(pool);
    // ... serve as before
}
```

## Mapping rows to structs

Add `sqlx::FromRow` (and `chrono` for the timestamp) so a database row can become a `Task`:

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

#[derive(Serialize, sqlx::FromRow)]
struct Task {
    id: i64,
    title: String,
    done: bool,
    created_at: DateTime<Utc>,
}
```

> **Type mapping matters.** SQLx maps Rust types to Postgres types exactly: `i64` ↔ `bigint`, `DateTime<Utc>` ↔ `timestamptz`, `String` ↔ `text`. A mismatch is a compile error with the macro form below — which is the whole point.

## Querying

### List and fetch

```rust
use axum::{extract::{State, Path}, Json};
use sqlx::PgPool;

async fn list_tasks(State(pool): State<PgPool>) -> Json<Vec<Task>> {
    let tasks = sqlx::query_as::<_, Task>("SELECT id, title, done, created_at FROM tasks ORDER BY id")
        .fetch_all(&pool)
        .await
        .unwrap();          // we'll replace unwrap with real error handling in Step 6
    Json(tasks)
}

async fn get_task(State(pool): State<PgPool>, Path(id): Path<i64>) -> Json<Task> {
    let task = sqlx::query_as::<_, Task>("SELECT id, title, done, created_at FROM tasks WHERE id = $1")
        .bind(id)           // $1 is bound safely — never string-concatenated
        .fetch_one(&pool)
        .await
        .unwrap();
    Json(task)
}
```

### Insert

```rust
async fn create_task(
    State(pool): State<PgPool>,
    Json(body): Json<NewTask>,
) -> Json<Task> {
    let task = sqlx::query_as::<_, Task>(
        "INSERT INTO tasks (title) VALUES ($1) RETURNING id, title, done, created_at",
    )
    .bind(&body.title)
    .fetch_one(&pool)
    .await
    .unwrap();
    Json(task)
}
```

The `$1`, `$2` placeholders with `.bind(...)` are **parameterized queries** — the values never get spliced into the SQL string, so SQL injection is structurally impossible. Never build a query with `format!()`.

## The superpower: compile-time-checked queries

Everything above used `query_as::<_, Task>("...")` — the string is validated at **runtime**. SQLx also offers **macro** forms that validate against your real database schema at **compile time**:

```rust
// query_as! (with the bang) checks the SQL against DATABASE_URL AT BUILD TIME:
//  - table/column names must exist
//  - result types must match the struct
//  - bind parameter types are verified
let task = sqlx::query_as!(
    Task,
    "SELECT id, title, done, created_at FROM tasks WHERE id = $1",
    id
)
.fetch_one(&pool)
.await?;
```

If you typo `titel`, **the project doesn't compile.** A schema drift becomes a build error, not a production 500.

### Offline mode (how CI builds without a database)

The macros normally need a live database at compile time. That's impractical in CI and Docker builds, so SQLx supports **offline mode**: you run

```bash
cargo sqlx prepare
```

once locally, which writes a `.sqlx/` directory of cached query metadata (JSON). Commit it. CI then builds with `SQLX_OFFLINE=true`, reading the cache instead of connecting.

> **Treat `.sqlx/` as source code, not a build artifact.** iKanban's guidelines are emphatic about this: deleting the cache breaks every Docker build immediately. When you add or change a `query!`/`query_as!` macro, regenerate the cache and commit the new files. When you *don't* use the macro (the plain `query_as::<_, Task>` string form), there's no cache to maintain — at the cost of losing compile-time checking.

## Where we are

Your API now reads and writes real rows, safely. But every query ends in `.unwrap()`, which crashes the handler on any error. Next: **Step 6 — Errors, Auth & JSON Contracts**, where we make failures return proper HTTP responses and lock the API down.