Learn Rust Coding · Lesson

Database Integration

Persist data.

Database Integration is a free Learn Rust Coding lesson on CoddyKit — lesson 3 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the Learn Rust Coding learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Persisting Data

So far our API stored data in memory, which vanishes on restart. Real services need a database. In this lesson we connect an Axum API to PostgreSQL using sqlx, an async, compile-time-checked SQL toolkit for Rust.

You will learn connection pools, queries, mapping rows to structs, and using the pool as shared state.

Adding sqlx

Add sqlx with the features you need: a runtime, TLS, and a database driver. Below is a Postgres setup using Tokio.

// Cargo.toml
// [dependencies]
// sqlx = { version = "0.7", features = [
//   "runtime-tokio", "tls-rustls", "postgres", "macros"
// ] }

The Connection Pool

Opening a new connection per request is slow. A connection pool keeps a set of reusable connections. PgPoolOptions builds one from a database URL. The pool is cheap to clone (it is reference-counted internally), making it ideal as shared state.

use sqlx::postgres::PgPoolOptions;

async fn make_pool(url: &str) -> sqlx::PgPool {
    PgPoolOptions::new()
        .max_connections(5)
        .connect(url)
        .await
        .expect("failed to connect")
}

Pool as Application State

Pass the pool to Axum with .with_state(pool). Handlers then take State(pool): State<PgPool>. Because the pool clones cheaply, every request shares the same underlying connections.

use axum::{routing::get, Router};
use sqlx::PgPool;

fn build(pool: PgPool) -> Router {
    Router::new()
        .route("/todos", get(list_todos))
        .with_state(pool)
}

Running a Query

The sqlx::query function runs raw SQL. Bind parameters with .bind(value) to avoid SQL injection; Postgres uses $1, $2 placeholders. Use .execute(&pool) for writes that do not return rows.

use sqlx::PgPool;

async fn insert_todo(pool: &PgPool, title: &str) -> Result<(), sqlx::Error> {
    sqlx::query("INSERT INTO todos (title, done) VALUES ($1, $2)")
        .bind(title)
        .bind(false)
        .execute(pool)
        .await?;
    Ok(())
}

Mapping Rows to Structs

Derive sqlx::FromRow on your model so query results map directly into it. Use query_as::<_, Todo> with fetch_all to get a Vec<Todo>, or fetch_one for a single row.

use sqlx::{PgPool, FromRow};

#[derive(FromRow, serde::Serialize)]
struct Todo { id: i32, title: String, done: bool }

async fn all_todos(pool: &PgPool) -> Result<Vec<Todo>, sqlx::Error> {
    let rows = sqlx::query_as::<_, Todo>("SELECT id, title, done FROM todos")
        .fetch_all(pool)
        .await?;
    Ok(rows)
}

A Handler That Reads the Database

Combine the pieces: a handler takes the pool from state, runs a query, and returns JSON. Map database errors to a 500 status so the client gets a clean response instead of a panic.

use axum::{extract::State, Json, http::StatusCode};
use sqlx::PgPool;

async fn list_todos(
    State(pool): State<PgPool>,
) -> Result<Json<Vec<Todo>>, StatusCode> {
    match all_todos(&pool).await {
        Ok(todos) => Ok(Json(todos)),
        Err(_) => Err(StatusCode::INTERNAL_SERVER_ERROR),
    }
}

Returning Inserted Rows

Postgres can return the row it just inserted with RETURNING. Combine it with query_as and fetch_one to get the new record, including its generated id, in a single round trip.

use sqlx::PgPool;

async fn create(pool: &PgPool, title: &str) -> Result<Todo, sqlx::Error> {
    let todo = sqlx::query_as::<_, Todo>(
        "INSERT INTO todos (title, done) VALUES ($1, false) \
         RETURNING id, title, done")
        .bind(title)
        .fetch_one(pool)
        .await?;
    Ok(todo)
}

Migrations

Your schema must exist before queries run. sqlx supports migrations: SQL files in a migrations/ folder applied in order. Run them at startup with sqlx::migrate!() so a fresh database is set up automatically.

use sqlx::PgPool;

async fn run_migrations(pool: &PgPool) {
    sqlx::migrate!("./migrations")
        .run(pool)
        .await
        .expect("migrations failed");
}
// migrations/0001_init.sql contains the CREATE TABLE statements.

Transactions

When several writes must succeed together, wrap them in a transaction. Begin with pool.begin(), run queries against the transaction handle, then commit. If you drop it without committing, sqlx rolls back automatically, keeping data consistent.

use sqlx::PgPool;

async fn transfer(pool: &PgPool) -> Result<(), sqlx::Error> {
    let mut tx = pool.begin().await?;
    sqlx::query("UPDATE accounts SET balance = balance - 10 WHERE id = 1")
        .execute(&mut *tx).await?;
    sqlx::query("UPDATE accounts SET balance = balance + 10 WHERE id = 2")
        .execute(&mut *tx).await?;
    tx.commit().await?;
    Ok(())
}

Configuration and Secrets

Never hardcode database credentials. Read the DATABASE_URL from the environment, often loaded from a .env file with the dotenvy crate during development. In production, the platform injects it as an environment variable.

use std::env;

async fn connect_from_env() -> sqlx::PgPool {
    let url = env::var("DATABASE_URL")
        .expect("DATABASE_URL must be set");
    make_pool(&url).await
}

Quick Check

Test your understanding of database integration.

Recap

You integrated a database:

Use a PgPool connection pool and share it via .with_state.
query/execute for writes; query_as with FromRow for typed reads.
Always bind parameters to prevent SQL injection.
RETURNING fetches inserted rows; transactions group writes atomically.
Run migrations at startup and read DATABASE_URL from the environment.

Frequently asked questions

Is the “Database Integration” lesson free?

Yes — the full text of “Database Integration” is free to read here on the web, and the Learn Rust Coding course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the Learn Rust Coding course, upgrade to CoddyKit PRO.

What will I learn in “Database Integration”?

Persist data. You practise Learn Rust Coding with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start Learn Rust Coding?

No prior experience is required. Learn Rust Coding on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Database Integration” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this Learn Rust Coding lesson?

Yes. Every Learn Rust Coding lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to Learn Rust Coding