Databases with sqlx

sqlx lets you write plain SQL from async Rust, with a connection pool and results mapped onto your structs. Its README lists PostgreSQL, MySQL, MariaDB and SQLite, and says it is not an ORM. This page uses SQLite in memory, so the main program needs no database server and leaves nothing to clean up. It does need a Rust toolchain; the last section also needs the sqlite3 command-line tool and cargo install sqlx-cli (which compiles sqlx-cli too; I did not time it), and the Go, Python and TypeScript programs need those runtimes. The program is async, run by Tokio: Async Basics explains why a runtime is needed, and the Tokio guide shows one. The ? on each call is the lesson’s pass the error up; Errors in real apps covers combining sqlx::Error with your own error types.

RememberWrite SQL, bind the values, and query_as the rows straight into a struct.

Project layout

notes-db/
├── src/
│   └── main.rs                 the program on this page
├── Cargo.lock                  created by Cargo
└── Cargo.toml                  dependencies go here

RustCargo.tomlCopy

[package]
name = "notes-db"
version = "0.1.0"
edition = "2024"

[dependencies]
sqlx = { version = "0.9.0", features = ["runtime-tokio", "sqlite"] }
tokio = { version = "1.54.1", features = ["full"] }

The first build compiles sqlx and everything under it, including a copy of SQLite: the sqlite feature bundles it and links it into your program (the README says “bundled and statically-linked”), so expect it to take a while.

Rustsrc/main.rsCopy

use sqlx::FromRow;
use sqlx::sqlite::SqlitePoolOptions;

#[derive(FromRow)]
struct Note {
    id: i64,
    text: String,
    done: bool,
}

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    // An in-memory SQLite database, so there is nothing to set up or clean up.
    // For a file, see SqliteConnectOptions (it has create_if_missing).
    let pool = SqlitePoolOptions::new().connect("sqlite::memory:").await?;

    sqlx::query(
        "CREATE TABLE notes (
            id INTEGER PRIMARY KEY,
            text TEXT NOT NULL,
            done BOOLEAN NOT NULL DEFAULT 0
        )",
    )
    .execute(&pool)
    .await?;

    for text in ["learn ownership", "write an axum service"] {
        sqlx::query("INSERT INTO notes (text) VALUES ($1)")
            .bind(text) // the value travels separately from the SQL text
            .execute(&pool)
            .await?;
    }
    sqlx::query("UPDATE notes SET done = 1 WHERE id = $1")
        .bind(1)
        .execute(&pool)
        .await?;

    let notes: Vec<Note> = sqlx::query_as("SELECT id, text, done FROM notes ORDER BY id")
        .fetch_all(&pool)
        .await?;
    for note in &notes {
        let mark = if note.done { "x" } else { " " };
        println!("[{mark}] {} {}", note.id, note.text);
    }

    let open: i64 = sqlx::query_scalar("SELECT COUNT(*) FROM notes WHERE done = 0")
        .fetch_one(&pool)
        .await?;
    println!("{open} still open");
    Ok(())
}

$ cargo run

[x] 1 learn ownership
[ ] 2 write an axum service
1 still open

• Pools. SqlitePoolOptions::new().connect(...) gives you a pool. The docs say Pool is Send, Sync and Clone, that cloning is cheap because it is a reference-counted handle, and that you create it once and share it. Go’s *sql.DB is the same idea: its docs call it “a pool of zero or more underlying connections”, safe for concurrent use. For PostgreSQL, turn on the postgres feature and use PgPoolOptions.
• Bind your values. .bind(text) sends the value separately from the SQL text. The docs say a bound value “is entirely separate from the query and does not affect its syntax”. The placeholder depends on the database: the docs use $1, $2 for PostgreSQL and SQLite, and ? for MySQL and MariaDB (SQLite also accepts ?).
• #[derive(FromRow)] fills each field from the column with the same name (the docs: calls to Row::try_get using each field’s name), like an ORM’s row mapping, without the ORM. #[sqlx(rename = "...")] handles a column with a different name.
• Migrations. The sqlx-cli tool keeps SQL files in a migrations/ folder: sqlx migrate add <name> creates one and sqlx migrate run runs the pending ones (its README). The program can also embed and run the folder itself, as the last task below does.

The same job in your language

I wrote the same notes program for the SQLite that the image has for Go, Python and TypeScript, and ran each one. The other cells only name common choices; nothing was run for them.

Language	Common choices (named)	Run for this page
Go	database/sql with a driver (pgx for PostgreSQL), or sqlc	database/sql with the pure-Go driver modernc.org/sqlite (its page says “CGo-free”)
Python	SQLAlchemy, asyncpg, psycopg	sqlite3, from the standard library
TypeScript	Prisma, Drizzle, Kysely	node:sqlite, built into Node 24 (its docs call it a release candidate)
Java	JDBC (part of the JDK), jOOQ, Hibernate	not run: no SQLite driver in the image
Kotlin	Exposed, jOOQ	not run: no SQLite driver in the image
C#	Entity Framework Core, Dapper	not run: no SQLite package in the image
C	libpq, the SQLite C API	not run: the image has the SQLite library but no header
C++	libpqxx, SOCI	not run: the image has the SQLite library but no header

All three programs print the same three lines as the Rust one (they are the tabs in the gotcha below). They differ in how a row reaches your code: Go’s Scan fills variables in column order, Python’s sqlite3.Row is read by column name, and Node returns objects keyed by column name that the program casts to its Note type (TypeScript does not check that cast). Rust’s FromRow names the fields once and sqlx fills them. Each program also passes its values as separate arguments (? in all three).

The next runs misspell the column text as txt. In all three the program ran up to the query and failed there. Python, tsc and go run had no complaint before that. (The TypeScript output is Node’s stack trace for the version in the image; yours will differ.)

terminal

$ sed -i 's/id, text, done/id, txt, done/' main.py && python3 main.py
Traceback (most recent call last):
  File "/home/you/notes-db/py/main.py", line 16, in <module>
    for row in db.execute("SELECT id, txt, done FROM notes ORDER BY id"):
               ~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
sqlite3.OperationalError: no such column: txt
$ sed -i 's/id, text, done/id, txt, done/' main.ts && tsc --strict --module nodenext --types node main.ts && node main.js
/home/you/notes-db/ts/main.js:15
const notes = db.prepare("SELECT id, txt, done FROM notes ORDER BY id").all();
                 ^

Error: no such column: txt
    at Object.<anonymous> (/home/you/notes-db/ts/main.js:15:18)
    at Module._compile (node:internal/modules/cjs/loader:1929:14)
    at Object..js (node:internal/modules/cjs/loader:2060:10)
    at Module.load (node:internal/modules/cjs/loader:1651:32)
    at Module._load (node:internal/modules/cjs/loader:1443:12)
    at wrapModuleLoad (node:internal/modules/cjs/loader:261:19)
    at Module.executeUserEntryPoint [as runMain] (node:internal/modules/run_main:154:5)
    at node:internal/main/run_main_module:33:47 {
  code: 'ERR_SQLITE_ERROR',
  errcode: 1,
  errstr: 'SQL logic error'
}

Node.js v24.22.1
$ sed -i 's/id, text, done/id, txt, done/' main.go && go run main.go
SQL logic error: no such column: txt (1)
exit status 1

(The first go run in a fresh container also prints go: downloading ... lines for the driver. The page filters them out.) Rust can move that failure to build time, and the gotcha below is about the price of that.

Try it yourself

Each task replaces src/main.rs with the program shown. I ran all of them in the image.

1. Do pooled connections share the in-memory database? The pool below holds three connections at once and asks each one whether the notes table exists. With sqlx’s sqlite::memory: all three said yes. (If each connection had its own empty database, at least one of the three would have said no.) The reason is in the source of sqlx-sqlite 0.9.0: it rewrites sqlite::memory: into a named, shared-cache in-memory database, so every connection in the pool opens the same one. The Go program in the table calls SetMaxOpenConns(1) and its comment says that is so every statement sees the same in-memory database; I did not run it without that line, so I only report the comment. The max_connections(1) in tasks 2 and 4 is not needed for that reason, because of the sharing above; it just keeps those snippets to one connection.

   Rustsrc/main.rsCopy

   use sqlx::sqlite::SqlitePoolOptions;
   
   #[tokio::main]
   async fn main() -> Result<(), sqlx::Error> {
       let pool = SqlitePoolOptions::new()
           .max_connections(3)
           .connect("sqlite::memory:")
           .await?;
       sqlx::query("CREATE TABLE notes (id INTEGER PRIMARY KEY, text TEXT NOT NULL)")
           .execute(&pool)
           .await?;
   
       // Hold three connections at once, so each of them is a different one.
       let mut a = pool.acquire().await?;
       let mut b = pool.acquire().await?;
       let mut c = pool.acquire().await?;
       for (name, conn) in [("a", &mut a), ("b", &mut b), ("c", &mut c)] {
           let found: i64 = sqlx::query_scalar("SELECT COUNT(*) FROM sqlite_master WHERE name = 'notes'")
               .fetch_one(&mut **conn)
               .await?;
           println!("connection {name} sees the notes table: {}", found == 1);
       }
       Ok(())
   }

   $ cargo run

   connection a sees the notes table: true
   connection b sees the notes table: true
   connection c sees the notes table: true

2. Paste a value into the SQL instead of binding it. First a version people often write, with format! and a String. In sqlx 0.9 it does not compile, because query takes only a &'static str unless you wrap the text in AssertSqlSafe to say you checked it:

   Rustsrc/main.rsCopy

   use sqlx::sqlite::SqlitePoolOptions;
   
   #[tokio::main]
   async fn main() -> Result<(), sqlx::Error> {
       let pool = SqlitePoolOptions::new().connect("sqlite::memory:").await?;
       let text = "hello";
       let sql = format!("INSERT INTO notes (text) VALUES ('{text}')");
       sqlx::query(sql).execute(&pool).await?;
       Ok(())
   }

   $ cargo build

   error[E0277]: dynamic SQL strings should be audited for possible injections
      --> src/main.rs:8:17
       |
     8 |     sqlx::query(sql).execute(&pool).await?;
       |     ----------- ^^^ dynamic SQL string
       |     |
       |     required by a bound introduced by this call
       |
       = help: the trait `SqlSafeStr` is not implemented for `std::string::String`
       = note: prefer literal SQL strings with bind parameters or `QueryBuilder` to add dynamic data to a query.
               
               To bypass this error, manually audit for potential injection vulnerabilities and wrap with `AssertSqlSafe()`.
               For details, see the docs for `SqlSafeStr`.
               
       = note: this trait is only implemented for `&'static str`, not all `&str` like the compiler error may suggest
     ... (notes pointing into library source code trimmed)

   With the wrapper it compiles. The program below then receives the input x'); DELETE FROM notes; -- once pasted into the SQL and once bound. In this run the pasted one ran the DELETE (0 notes left) and the bound one was stored as plain text. The query docs say the SQLite driver does not yet follow the one-statement rule of prepared statements, but that this behaviour is deprecated.

   Rustsrc/main.rsCopy

   use sqlx::{AssertSqlSafe, SqlitePool};
   use sqlx::sqlite::SqlitePoolOptions;
   
   // What a user typed into a form.
   const INPUT: &str = "x'); DELETE FROM notes; --";
   
   async fn fresh() -> Result<SqlitePool, sqlx::Error> {
       let pool = SqlitePoolOptions::new()
           .max_connections(1) // not required: pooled in-memory connections share one database (task 1)
           .connect("sqlite::memory:")
           .await?;
       sqlx::query("CREATE TABLE notes (id INTEGER PRIMARY KEY, text TEXT NOT NULL)")
           .execute(&pool)
           .await?;
       for text in ["learn ownership", "write an axum service"] {
           sqlx::query("INSERT INTO notes (text) VALUES ($1)")
               .bind(text)
               .execute(&pool)
               .await?;
       }
       Ok(pool)
   }
   
   async fn count(pool: &SqlitePool) -> Result<i64, sqlx::Error> {
       sqlx::query_scalar("SELECT COUNT(*) FROM notes").fetch_one(pool).await
   }
   
   #[tokio::main]
   async fn main() -> Result<(), sqlx::Error> {
       // Pasting the input into the SQL text. AssertSqlSafe is how you tell sqlx you meant it.
       let pool = fresh().await?;
       let sql = format!("INSERT INTO notes (text) VALUES ('{INPUT}')");
       sqlx::query(AssertSqlSafe(sql)).execute(&pool).await?;
       println!("pasted into the SQL: {} notes left", count(&pool).await?);
   
       // Binding it: the input is a value, whatever characters it contains.
       let pool = fresh().await?;
       sqlx::query("INSERT INTO notes (text) VALUES ($1)")
           .bind(INPUT)
           .execute(&pool)
           .await?;
       println!("bound: {} notes, the last one is {:?}", count(&pool).await?,
           sqlx::query_scalar::<_, String>("SELECT text FROM notes ORDER BY id DESC LIMIT 1").fetch_one(&pool).await?);
       Ok(())
   }

   $ cargo run

   pasted into the SQL: 0 notes left
   bound: 3 notes, the last one is "x'); DELETE FROM notes; --"

3. Name a column differently from the field. FromRow looks columns up by the field’s name, so renaming the column in the query breaks it. This is a run-time error (ColumnNotFound), not a compile error, and main returns it, which is what the Error: line is. Fix it by writing SELECT id, text FROM notes again, or by adding #[sqlx(rename = "body")] to the field:

   Rustsrc/main.rsCopy

   use sqlx::FromRow;
   use sqlx::sqlite::SqlitePoolOptions;
   
   #[derive(FromRow)]
   struct Note {
       id: i64,
       text: String,
   }
   
   #[tokio::main]
   async fn main() -> Result<(), sqlx::Error> {
       let pool = SqlitePoolOptions::new().connect("sqlite::memory:").await?;
       sqlx::query("CREATE TABLE notes (id INTEGER PRIMARY KEY, text TEXT NOT NULL)")
           .execute(&pool)
           .await?;
       sqlx::query("INSERT INTO notes (text) VALUES ($1)")
           .bind("learn ownership")
           .execute(&pool)
           .await?;
   
       // The struct field is `text`; this query names the column `body`.
       let notes: Vec<Note> = sqlx::query_as("SELECT id, text AS body FROM notes")
           .fetch_all(&pool)
           .await?;
       println!("{}: {}", notes[0].id, notes[0].text);
       Ok(())
   }

   $ cargo run

   Error: ColumnNotFound("text")

4. Run a migration from the program. Put the CREATE TABLE in migrations/0001_create_notes.sql and let sqlx::migrate!() embed and run the folder. Running it twice applies the migration once. The program reads the _sqlx_migrations table, where sqlx records what it applied:

   Rust

   migrations/0001_create_notes.sqlsrc/main.rs

   Copy

   CREATE TABLE notes (
       id INTEGER PRIMARY KEY,
       text TEXT NOT NULL,
       done BOOLEAN NOT NULL DEFAULT 0
   );

   $ cargo run

   migration 1: create notes (success: true)
   1 note

Gotcha

The checked-query macro without a database

Looks fine: in Go, Python and TypeScript it runs fine. Rust won't compile it.

Works in Go. In Rust it's just a rule.Not run in Kotlin on this page.Works in Python. In Rust it's just a rule.Works in TypeScript. In Rust it's just a rule.Not run in Java on this page.Not run in C# on this page.Not run in C on this page.Not run in C++ on this page.

Rustsrc/main.rs✗ won't compileCopy

use sqlx::FromRow;
use sqlx::sqlite::SqlitePoolOptions;

#[derive(FromRow)]
struct Note {
    id: i64,
    text: String,
    done: bool,
}

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    // An in-memory SQLite database, so there is nothing to set up or clean up.
    // For a file, see SqliteConnectOptions (it has create_if_missing).
    let pool = SqlitePoolOptions::new().connect("sqlite::memory:").await?;

    sqlx::query(
        "CREATE TABLE notes (
            id INTEGER PRIMARY KEY,
            text TEXT NOT NULL,
            done BOOLEAN NOT NULL DEFAULT 0
        )",
    )
    .execute(&pool)
    .await?;

    for text in ["learn ownership", "write an axum service"] {
        sqlx::query("INSERT INTO notes (text) VALUES ($1)")
            .bind(text) // the value travels separately from the SQL text
            .execute(&pool)
            .await?;
    }
    sqlx::query("UPDATE notes SET done = 1 WHERE id = $1")
        .bind(1)
        .execute(&pool)
        .await?;

    let notes: Vec<Note> = sqlx::query_as!(Note, "SELECT id, text, done FROM notes ORDER BY id")
        .fetch_all(&pool)
        .await?;
    for note in &notes {
        let mark = if note.done { "x" } else { " " };
        println!("[{mark}] {} {}", note.id, note.text);
    }

    let open: i64 = sqlx::query_scalar("SELECT COUNT(*) FROM notes WHERE done = 0")
        .fetch_one(&pool)
        .await?;
    println!("{open} still open");
    Ok(())
}

Go✓KotlinPython✓TypeScript✓JavaC#CC++

Copy

package main

import (
  "database/sql"
  "fmt"
  "log"

  _ "modernc.org/sqlite"
)

type Note struct {
  ID   int64
  Text string
  Done bool
}

func main() {
  log.SetFlags(0) // no timestamp in error lines
  db, err := sql.Open("sqlite", ":memory:")
  if err != nil {
    log.Fatal(err)
  }
  defer db.Close()
  db.SetMaxOpenConns(1) // one connection, so every statement sees the same in-memory database

  _, err = db.Exec(`CREATE TABLE notes (
    id INTEGER PRIMARY KEY,
    text TEXT NOT NULL,
    done BOOLEAN NOT NULL DEFAULT 0
  )`)
  if err != nil {
    log.Fatal(err)
  }
  for _, text := range []string{"learn ownership", "write an axum service"} {
    if _, err := db.Exec("INSERT INTO notes (text) VALUES (?)", text); err != nil {
      log.Fatal(err)
    }
  }
  if _, err := db.Exec("UPDATE notes SET done = 1 WHERE id = ?", 1); err != nil {
    log.Fatal(err)
  }

  rows, err := db.Query("SELECT id, text, done FROM notes ORDER BY id")
  if err != nil {
    log.Fatal(err)
  }
  defer rows.Close()
  for rows.Next() {
    var n Note
    if err := rows.Scan(&n.ID, &n.Text, &n.Done); err != nil {
      log.Fatal(err)
    }
    mark := " "
    if n.Done {
      mark = "x"
    }
    fmt.Printf("[%s] %d %s\n", mark, n.ID, n.Text)
  }
  if err := rows.Err(); err != nil {
    log.Fatal(err)
  }

  var open int64
  if err := db.QueryRow("SELECT COUNT(*) FROM notes WHERE done = 0").Scan(&open); err != nil {
    log.Fatal(err)
  }
  fmt.Printf("%d still open\n", open)
}

$ go run main.go✓ runs

[x] 1 learn ownership
[ ] 2 write an axum service
1 still open

Kotlin

Not run for this page: the image has no SQLite driver for Kotlin.

import sqlite3

db = sqlite3.connect(":memory:")
db.execute(
    """CREATE TABLE notes (
        id INTEGER PRIMARY KEY,
        text TEXT NOT NULL,
        done BOOLEAN NOT NULL DEFAULT 0
    )"""
)
for text in ["learn ownership", "write an axum service"]:
    db.execute("INSERT INTO notes (text) VALUES (?)", (text,))
db.execute("UPDATE notes SET done = 1 WHERE id = ?", (1,))

db.row_factory = sqlite3.Row
for row in db.execute("SELECT id, text, done FROM notes ORDER BY id"):
    mark = "x" if row["done"] else " "
    print(f"[{mark}] {row['id']} {row['text']}")

(open_count,) = db.execute("SELECT COUNT(*) FROM notes WHERE done = 0").fetchone()
print(f"{open_count} still open")

$ python3 main.py✓ runs

[x] 1 learn ownership
[ ] 2 write an axum service
1 still open

import { DatabaseSync } from "node:sqlite";

type Note = { id: number; text: string; done: number };

const db = new DatabaseSync(":memory:");
db.exec(`CREATE TABLE notes (
  id INTEGER PRIMARY KEY,
  text TEXT NOT NULL,
  done BOOLEAN NOT NULL DEFAULT 0
)`);
const insert = db.prepare("INSERT INTO notes (text) VALUES (?)");
for (const text of ["learn ownership", "write an axum service"]) {
  insert.run(text);
}
db.prepare("UPDATE notes SET done = 1 WHERE id = ?").run(1);

const notes = db.prepare("SELECT id, text, done FROM notes ORDER BY id").all() as Note[];
for (const note of notes) {
  const mark = note.done ? "x" : " ";
  console.log(`[${mark}] ${note.id} ${note.text}`);
}

const row = db.prepare("SELECT COUNT(*) AS n FROM notes WHERE done = 0").get() as { n: number };
console.log(`${row.n} still open`);

$ tsc --strict --module nodenext --types node main.ts && node main.js✓ runs

[x] 1 learn ownership
[ ] 2 write an axum service
1 still open

Java

Not run for this page: the image has no SQLite driver for Java.

C#

Not run for this page: the image has no SQLite package for C#.

C

Not run for this page: the image has the SQLite library but no header file to compile against.

C++

Not run for this page: the image has the SQLite library but no header file to compile against.

What does the compiler say? Guess first, then open.

$ cargo build

error: set `DATABASE_URL` to use query macros online, or run `cargo sqlx prepare` to update the query cache
  --> src/main.rs:38:28
   |
38 |     let notes: Vec<Note> = sqlx::query_as!(Note, "SELECT id, text, done FROM notes ORDER BY id")
   |                            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
   |
   = note: this error originates in the macro `$crate::sqlx_macros::expand_query` which comes from the expansion of the macro `sqlx::query_as` (in Nightly builds, run with -Z macro-backtrace for more info)

What happens in Go

go run on this database/sql program does not talk to a database until the query runs: it builds with nothing set up, and a misspelled column is reported when the query runs (see the runs below).

What happens in Kotlin

Not run for this page: the image has no SQLite driver for Kotlin.

What happens in Python

Python has no build step either. The sqlite3 module runs the SQL when the line executes, and a misspelled column raised an error at that point (see the runs below).

What happens in TypeScript

Node's built-in node:sqlite prepares the SQL when the line executes, and a misspelled column threw at that point (see the runs below). Nothing needs a database to build.

What happens in Java

Not run for this page: the image has no SQLite driver for Java.

What happens in C#

Not run for this page: the image has no SQLite package for C#.

What happens in C

Not run for this page: the image has the SQLite library but no header file to compile against.

What happens in C++

Not run for this page: the image has the SQLite library but no header file to compile against.

What Rust sees

query_as! (with the !) is a macro. While compiling, it connects to a database, asks it about the query and checks the column names and types against your struct. It finds that database through the DATABASE_URL environment variable (or saved query data), and with neither the build stops.

Why Rust says no

This is something sqlx offers, not a rule of the language: a misspelled column becomes a compile error instead of an error when that line runs. The price is that the build needs a database to ask, or saved answers. The two ways to give it one are below this section; the plain query_as (no !) used on this page needs neither.

The fix

Compiles and runs.

Rust, fixedsrc/main.rsRun ↗Copy

use sqlx::FromRow;
use sqlx::sqlite::SqlitePoolOptions;

#[derive(FromRow)]
struct Note {
    id: i64,
    text: String,
    done: bool,
}

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    // An in-memory SQLite database, so there is nothing to set up or clean up.
    // For a file, see SqliteConnectOptions (it has create_if_missing).
    let pool = SqlitePoolOptions::new().connect("sqlite::memory:").await?;

    sqlx::query(
        "CREATE TABLE notes (
            id INTEGER PRIMARY KEY,
            text TEXT NOT NULL,
            done BOOLEAN NOT NULL DEFAULT 0
        )",
    )
    .execute(&pool)
    .await?;

    for text in ["learn ownership", "write an axum service"] {
        sqlx::query("INSERT INTO notes (text) VALUES ($1)")
            .bind(text) // the value travels separately from the SQL text
            .execute(&pool)
            .await?;
    }
    sqlx::query("UPDATE notes SET done = 1 WHERE id = $1")
        .bind(1)
        .execute(&pool)
        .await?;

    let notes: Vec<Note> = sqlx::query_as("SELECT id, text, done FROM notes ORDER BY id")
        .fetch_all(&pool)
        .await?;
    for note in &notes {
        let mark = if note.done { "x" } else { " " };
        println!("[{mark}] {} {}", note.id, note.text);
    }

    let open: i64 = sqlx::query_scalar("SELECT COUNT(*) FROM notes WHERE done = 0")
        .fetch_one(&pool)
        .await?;
    println!("{open} still open");
    Ok(())
}

$ cargo run

[x] 1 learn ownership
[ ] 2 write an axum service
1 still open

Making the checked macro build

The macros need to ask a database about your query at build time. The query! docs give two ways to provide that (query_as!, which the gotcha uses, works the same way), and I ran both.

1. Point DATABASE_URL at a database that has the tables. The session makes a SQLite file, build.db, with the sqlite3 command-line tool and builds with DATABASE_URL=sqlite:build.db (the docs say a .env file works too). That file exists only for the compiler. The program still creates its own in-memory database when it runs. Then sed misspells text as txt and the build fails with the database’s own message:

terminal

$ sqlite3 build.db "CREATE TABLE notes (id INTEGER PRIMARY KEY, text TEXT NOT NULL, done BOOLEAN NOT NULL DEFAULT 0)"
$ DATABASE_URL=sqlite:build.db cargo run
[x] 1 learn ownership
[ ] 2 write an axum service
1 still open
$ sed -i 's/id, text, done/id, txt, done/' src/main.rs
$ DATABASE_URL=sqlite:build.db cargo build
error: error returned from database: (code: 1) no such column: txt
  --> src/main.rs:38:28
   |
38 |     let notes: Vec<Note> = sqlx::query_as!(Note, "SELECT id, txt, done FROM notes ORDER BY id")
   |                            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
   |
   = note: this error originates in the macro `$crate::sqlx_macros::expand_query` which comes from the expansion of the macro `sqlx::query_as` (in Nightly builds, run with -Z macro-backtrace for more info)

error: could not compile `notes-db` (bin "notes-db") due to 1 previous error

2. Save the answers, and build without a database. cargo sqlx prepare comes from sqlx-cli. The session installs it with only the sqlite feature, so it builds one driver instead of the default three (its README lists the plain cargo install sqlx-cli and the --features forms), and with --quiet to keep the output short. It connects to build.db, writes a JSON file into .sqlx (here one, for the page’s one query), and then the session deletes build.db and builds. The build succeeded with no database in sight, from .sqlx alone. The docs say to commit .sqlx and to run cargo sqlx prepare --check in CI, to catch a file that is out of date. If DATABASE_URL is set, it wins over .sqlx; the sqlx-cli README says SQLX_OFFLINE=true forces offline mode.

terminal

$ sqlite3 build.db "CREATE TABLE notes (id INTEGER PRIMARY KEY, text TEXT NOT NULL, done BOOLEAN NOT NULL DEFAULT 0)"
$ cargo install sqlx-cli --version 0.9.0 --locked --quiet --no-default-features --features sqlite
$ DATABASE_URL=sqlite:build.db cargo sqlx prepare
query data written to .sqlx in the current directory; please check this into version control
$ ls .sqlx
query-2f9c92e4584b998de4089aff33ebb44af7ab6a03ce5ed4e6a983d025e6643f8a.json
$ rm build.db
$ cargo run
[x] 1 learn ownership
[ ] 2 write an axum service
1 still open

Read more

• sqlx on docs.rs (0.9.0) and the project README, for the version this page pins.
• sqlx::query: placeholders, bind and the SQL injection section. AssertSqlSafe is the wrapper from task 2.
• query!, including its Offline Mode section, and migrate!.
• Pool, FromRow and SqliteConnectOptions (the URL forms, create_if_missing).
• sqlx-cli README: migrate add, migrate run, prepare.
• For the other languages: Go’s database/sql, Python’s sqlite3 and Node’s node:sqlite.
• In this course: Async Basics, Async with Tokio, Errors and Errors in real apps. To put a database behind a web service, see An HTTP service with axum.

The versions this page is about are sqlx 0.9.0, tokio 1.54.1 and, for Go, modernc.org/sqlite v1.61.0, as pinned in Cargo.lock and go.sum. Other versions can print different messages.