🎓 A1Learner
🔍

Rust Database Tools

Migrations are schema changes recorded as numbered steps. The steps run in order as tables change shape.

Read the schema version

Every database carries this number. A brand-new database reads 0.

let version: i32 = conn.query_row("PRAGMA user_version", (), |row| row.get(0))?;
println!("schema version = {version}");

Step one: create and stamp

execute_batch runs several statements in one call. This step creates the table. It also stamps the version as 1.

conn.execute_batch(
    "CREATE TABLE pet (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
     PRAGMA user_version = 1;",
)?;

Step two: add a column

Step two runs only when the stored version is 1. Old rows receive the default age of 0.

conn.execute_batch(
    "ALTER TABLE pet ADD COLUMN age INTEGER NOT NULL DEFAULT 0;
     PRAGMA user_version = 2;",
)?;

The same query in sqlx

An async app holds a pool, not one Connection. The turbofish names the database and the row shape. The count still arrives inside a Result.

let row: (i64,) = sqlx::query_as::<sqlx::Sqlite, (i64,)>("SELECT COUNT(*) FROM pet")
    .fetch_one(&pool)
    .await?;
println!("pets = {}", row.0);

Example

// Cargo.toml: rusqlite = { version = "0.40", features = ["bundled"] }
use rusqlite::{params, Connection, Result};

fn schema_version(conn: &Connection) -> Result<i32> {
    conn.query_row("PRAGMA user_version", (), |row| row.get(0))
}

fn main() -> Result<()> {
    let conn = Connection::open_in_memory()?;
    if schema_version(&conn)? == 0 {
        conn.execute_batch(
            "CREATE TABLE pet (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
             PRAGMA user_version = 1;",
        )?;
    }
    if schema_version(&conn)? == 1 {
        conn.execute_batch(
            "ALTER TABLE pet ADD COLUMN age INTEGER NOT NULL DEFAULT 0;
             PRAGMA user_version = 2;",
        )?;
    }
    conn.execute(
        "INSERT INTO pet (name, age) VALUES (?1, ?2)",
        params!["Miso", 3],
    )?;
    let (name, age): (String, i64) = conn.query_row("SELECT name, age FROM pet", (), |row| {
        Ok((row.get(0)?, row.get(1)?))
    })?;
    println!("schema version = {}", schema_version(&conn)?);
    println!("{name} is {age}");
    Ok(())
}

Key points

  • A migration is one small schema change.
  • Step 1 might create a table.
  • Step 2 might add a column.
  • The steps run in order.
  • SQLite stores a schema version number for you.
  • Read it with PRAGMA user_version.
  • A fresh database reports 0.
  • Apply each migration once, in order.
  • Then raise user_version to match.
  • Startup code can run this check on every launch.
  • Bigger projects use a migration tool instead.
  • The refinery crate runs numbered migration files for you.
  • It records which files already ran.
  • Diesel is an ORM for Rust.
  • It maps tables to structs.
  • It builds queries as Rust code.
  • It asks for more setup than rusqlite does.
  • sqlx talks to SQLite, Postgres and MySQL.
  • It uses async calls and a pool of connections.
  • Plain rusqlite stays the simpler fit for tools and tests.

In this example, the program opens a fresh database. The schema version reads 0. Step one runs. It creates the pet table and stamps version 1. The version now reads 1. Step two runs. It adds the age column and stamps version 2. A pet goes in through the new column. The prints confirm the end state. The schema stands at version 2. Miso is 3. Run the same code on a saved file later. Both steps skip themselves. The stored version already reports the work as done.

Note

Async servers often pick sqlx instead.

Try it Yourself

Edit the code, then press Run.

Running is simulated · Rust Playground

Exercise

A saved database reports user_version 1. Your code holds steps one and two. What should startup do?