SQLx: Async, Compile-Time-Checked SQL

20 min read

In the TypeScript world you talk to a SQL database through a query builder or an ORM — Knex, Prisma, TypeORM — and your queries are checked (if at all) only when they actually run against the database. SQLx takes a strikingly different position: you write raw SQL, but a macro connects to your real database at compile time and verifies the query and its result types before your program ever runs. This page is the on-ramp: what SQLx is, how to add it, and how to connect to PostgreSQL and SQLite.

Quick Overview

SQLx is an async, pure-Rust SQL toolkit. It is not an ORM — there is no entity mapping, no migration-from-decorators magic, no lazy-loaded relations. You write SQL strings, SQLx runs them, and it maps the rows back into Rust types. Its headline feature is the query!/query_as! macro family, which uses a live database connection during cargo build to type-check your SQL: a misspelled column or a type mismatch becomes a compile error, not a 500 at 3 a.m. SQLx is fully async (it runs on the Tokio runtime) and supports PostgreSQL, MySQL/MariaDB, and SQLite from a single API.

Note: Every Rust snippet on this page was compiled and run with cargo 1.96.0 (current stable; 2024 edition, which cargo new selects automatically). These examples pin SQLx 0.8 deliberately. SQLx 0.9 is what cargo add sqlx now resolves to (it requires Rust ≥ 1.94) and it renames the runtime/TLS feature flags — on 0.9 the combined runtime-tokio-rustls is split into runtime-tokio + tls-rustls, so a 0.9 dependency line reads sqlx = { version = "0.9", features = ["runtime-tokio", "tls-rustls", "sqlite"] }. The query/query_as/Pool/FromRow API shown here is unchanged across both releases. Examples use an in-memory or file-based SQLite database so they reproduce with no server to install.

TypeScript/JavaScript Example

Here is a typical Knex setup in TypeScript: a connection pool, a table, an insert with bound parameters, and a typed-ish read. Knex returns any[] from a raw query, so the as User[] is a cast you are trusting, not a check.

1
import knex from "knex";
2

3
interface User {
4
  id: number;
5
  name: string;
6
  email: string;
7
}
8

9
// A connection POOL, not a single connection (Knex manages it for you).
10
const db = knex({
11
  client: "pg",
12
  connection: "postgres://user:pass@localhost/app",
13
  pool: { min: 2, max: 10 },
14
});
15

16
async function main() {
17
  await db.raw(`
18
    CREATE TABLE IF NOT EXISTS users (
19
      id    SERIAL PRIMARY KEY,
20
      name  TEXT NOT NULL,
21
      email TEXT NOT NULL UNIQUE
22
    )
23
  `);
24

25
  // Bound parameters ($1, $2) — Knex escapes them, preventing SQL injection.
26
  await db.raw("INSERT INTO users (name, email) VALUES (?, ?)", [
27
    "Alice",
28
    "alice@example.com",
29
  ]);
30

31
  // The cast `as User[]` is a PROMISE to the compiler, checked by nobody.
32
  const rows = (await db.raw("SELECT id, name, email FROM users")).rows as User[];
33
  console.log(rows); // [ { id: 1, name: 'Alice', email: 'alice@example.com' } ]
34

35
  // A typo here ('usrname') compiles fine and blows up only at runtime:
36
  // const bad = await db.raw("SELECT usrname FROM users");
37

38
  await db.destroy();
39
}
40

41
main();

Three things to hold in mind, because SQLx changes all three:

The query string is opaque to TypeScript — SELECT usrname type-checks and ships.
db.raw(...).rows as User[] is an unchecked cast; the runtime shape may not match.
The pool is configured imperatively and db is a shared handle you clone references to everywhere.

Rust Equivalent

The same program in Rust with SQLx. Add the crate first — note the feature flags that select the runtime, TLS backend, and which databases you want compiled in:

1
[dependencies]
2
sqlx = { version = "0.8", features = ["runtime-tokio-rustls", "sqlite", "postgres"] }
3
tokio = { version = "1", features = ["full"] }

1
# Or, equivalently, from a shell:
2
cargo add sqlx@0.8 --features runtime-tokio-rustls,sqlite,postgres
3
cargo add tokio --features full

1
use sqlx::sqlite::SqlitePoolOptions;
2
use sqlx::{FromRow, Row};
3

4
// The same shape as the TypeScript `interface User`.
5
#[derive(Debug, FromRow)]
6
struct User {
7
    id: i64,
8
    name: String,
9
    email: String,
10
}
11

12
#[tokio::main]
13
async fn main() -> Result<(), sqlx::Error> {
14
    // Build a connection POOL (not a single connection). `sqlite::memory:`
15
    // is an in-memory DB so this runs with nothing installed.
16
    let pool = SqlitePoolOptions::new()
17
        .max_connections(5)
18
        .connect("sqlite::memory:")
19
        .await?;
20

21
    // A statement that returns no rows: `.execute(...)`.
22
    sqlx::query(
23
        "CREATE TABLE users (
24
            id    INTEGER PRIMARY KEY,
25
            name  TEXT NOT NULL,
26
            email TEXT NOT NULL UNIQUE
27
        )",
28
    )
29
    .execute(&pool)
30
    .await?;
31

32
    // Bound parameters via `.bind(...)`. Each `?` is filled positionally.
33
    let result = sqlx::query("INSERT INTO users (name, email) VALUES (?, ?)")
34
        .bind("Alice")
35
        .bind("alice@example.com")
36
        .execute(&pool)
37
        .await?;
38
    println!(
39
        "inserted {} row(s), last id = {}",
40
        result.rows_affected(),
41
        result.last_insert_rowid()
42
    );
43

44
    // Read MANY rows into the typed struct via `query_as` + `FromRow`.
45
    let users: Vec<User> = sqlx::query_as::<_, User>("SELECT id, name, email FROM users")
46
        .fetch_all(&pool)
47
        .await?;
48
    println!("{users:?}");
49

50
    // Read ONE row and pull a column out dynamically (no struct).
51
    let row = sqlx::query("SELECT COUNT(*) AS count FROM users")
52
        .fetch_one(&pool)
53
        .await?;
54
    let count: i64 = row.get("count");
55
    println!("user count = {count}");
56

57
    Ok(())
58
}

Real output:

1
inserted 1 row(s), last id = 1
2
[User { id: 1, name: "Alice", email: "alice@example.com" }]
3
user count = 1

The PostgreSQL version differs only in the connection string and the placeholder syntax ($1, $2 instead of ?):

1
use sqlx::postgres::PgPoolOptions;
2

3
#[tokio::main]
4
async fn main() -> Result<(), sqlx::Error> {
5
    // The connection string carries everything; the pool dials at `.connect().await`.
6
    let pool = PgPoolOptions::new()
7
        .max_connections(5)
8
        .connect("postgres://user:pass@localhost/app")
9
        .await?;
10

11
    // Postgres uses $1, $2 placeholders instead of `?`.
12
    let row = sqlx::query("SELECT id, name FROM users WHERE id = $1")
13
        .bind(1_i32)
14
        .fetch_one(&pool)
15
        .await?;
16
    println!("loaded user #{}", row.get::<i32, _>("id"));
17

18
    Ok(())
19
}

Note: This PostgreSQL snippet type-checks and builds, but it only attempts a TCP connection when .connect().await runs — so it needs a real server to execute. The SQLite examples above need no server, which is why this page demonstrates live output with SQLite.

Detailed Explanation

Feature flags decide what gets compiled

Unlike npm install knex (which always pulls everything), SQLx uses Cargo features to compile only what you ask for. The three categories you almost always set:

Runtime + TLS: runtime-tokio-rustls ties SQLx to the Tokio async runtime and the pure-Rust rustls TLS stack. (runtime-tokio-native-tls uses the system TLS instead.) SQLx does not bundle an executor, exactly like the rest of Rust async — see Tokio Setup.
Drivers: postgres, mysql, sqlite. Pick the databases you actually use; each is a separately compiled driver.
Extras (optional): macros (the query! family — on by default), migrate (the embedded migration runner), chrono/time/uuid/json to map those types to/from SQL columns.

The pool is a cheap, clonable handle

SqlitePoolOptions/PgPoolOptions build a Pool<DB>. A Pool is an Arc inside, so cloning it is cheap and every clone shares the same set of connections — this is the idiomatic way to hand a database to many request handlers or background tasks. (The Arc/Mutex pattern explains the shared-ownership machinery; connection-pool sizing and lifecycle get their own page in Connection Pooling.) You pass &pool to every query; SQLx checks out a connection, runs the statement, and returns it to the pool.

`execute` vs `fetch_*`: pick by what comes back

Method	Returns	Use for
`.execute(&pool)`	a results struct (`rows_affected`, `last_insert_rowid`)	`INSERT`/`UPDATE`/`DELETE`/DDL
`.fetch_one(&pool)`	exactly one row (errors if 0 rows)	a guaranteed single result
`.fetch_optional(&pool)`	`Option<Row>`	a “maybe one” lookup
`.fetch_all(&pool)`	`Vec<Row>`	the whole result set
`.fetch(&pool)`	a `Stream` of rows	huge result sets, row-by-row

fetch_optional is the SQL analogue of TypeScript’s T | undefined: SQLx makes “no row” a first-class Option::None you must handle, instead of a rows[0] that is silently undefined.

Two query styles: dynamic `query()` vs the checked `query!` macro

There are two API layers, and the difference is the whole point of SQLx.

1. The function sqlx::query(...) / sqlx::query_as::<_, T>(...) takes a &str at runtime. It is flexible (you can build the SQL string dynamically) but the SQL is not validated until execution — much like Knex’s db.raw. You read columns either via FromRow (query_as) or by hand with row.get("col").

2. The macros sqlx::query!(...) / sqlx::query_as!(...) connect to your real database while the compiler runs and verify the SQL string against the live schema. Consider:

1
use sqlx::sqlite::SqlitePoolOptions;
2

3
#[tokio::main]
4
async fn main() -> Result<(), sqlx::Error> {
5
    // `std::env::var` reads the process env, NOT a .env file. Export
6
    // DATABASE_URL (or load .env yourself; see the note below) before running.
7
    let pool = SqlitePoolOptions::new()
8
        .connect(&std::env::var("DATABASE_URL").unwrap())
9
        .await?;
10

11
    // The macro returns an anonymous struct whose fields are named after the
12
    // columns and TYPED FROM THE SCHEMA (id: i64, name: String).
13
    let rows = sqlx::query!("SELECT id, name FROM users WHERE id > ?", 0)
14
        .fetch_all(&pool)
15
        .await?;
16

17
    for r in &rows {
18
        // r.id and r.name are statically typed; a typo is a COMPILE error.
19
        println!("{}: {}", r.id, r.name);
20
    }
21
    Ok(())
22
}

With a users table seeded with Alice and Bob, this prints:

1
1: Alice
2
2: Bob

The macro found the database via the DATABASE_URL environment variable. SQLx’s .env auto-loading is a compile-time convenience: the query!/query_as! macros read a .env file during cargo build to find the schema. It does not apply at runtime — the example’s std::env::var("DATABASE_URL") reads the process environment only, so you must export DATABASE_URL yourself (or load the .env in code, e.g. cargo add dotenvy and call dotenvy::dotenv().ok(); at the top of main). Otherwise the build succeeds but .connect(&std::env::var("DATABASE_URL").unwrap()) panics at runtime with a NotPresent error. The deep difference from every TypeScript tool is that the schema is consulted by the compiler. The mechanics of binding parameters, FromRow, and the full macro vocabulary are covered in SQLx Queries; this page only introduces the idea.

`?` propagates database errors like a thrown rejection

Each SQLx call returns a Result<_, sqlx::Error>. The ? operator forwards a failure up to the caller, the same way an awaited Promise rejection unwinds an async function. Because main here returns Result<(), sqlx::Error>, a failure prints a Debug of the error and exits non-zero. In real services you map sqlx::Error into your own error type — see Error Handling and the Real-World Example below.

Key Differences

Aspect	TypeScript (Knex / Prisma / TypeORM)	Rust (SQLx)
Category	query builder / ORM	SQL toolkit (not an ORM)
You write	builder calls or decorated entities	raw SQL strings
Query validation	at runtime (or never, for `raw`)	at compile time via `query!` macros
Result typing	`any` / a cast you trust	a real struct or a compiler-generated typed struct
Async model	Promises (eager, auto-scheduled)	Futures (lazy; need a runtime) — see Promises vs Futures
Migrations	framework-specific CLI	`sqlx migrate` (see Migrations)
Relations / eager loading	first-class (`include`, `relations`)	none — you write the `JOIN` yourself
Connection pool	configured object, often a singleton	`Pool<DB>`, a cheap clonable `Arc`

The mental model to adopt: SQLx is closer to a really good driver than to Prisma. If you want the ORM experience (entities, a query DSL, associations), that is Diesel or SeaORM. SQLx’s bet is that SQL is the right abstraction and the compiler should check it for you.

Tip: “Compile-time-checked” does not mean “compiled into the binary with no database.” It means the macro phoned a live database during cargo build. For builds in CI or Docker where no database is reachable, SQLx supports offline mode: run cargo sqlx prepare to cache the query metadata into a .sqlx/ directory that you commit, and the macros validate against that cache instead. More in Migrations and the SQLx docs.

Common Pitfalls

Forgetting `DATABASE_URL` (the macros need a live database to compile)

The single most common surprise: query!/query_as! will not compile unless they can reach a database. If DATABASE_URL is unset and there is no offline cache, the build fails. This is the real error message:

1
error: set `DATABASE_URL` to use query macros online, or run `cargo sqlx prepare` to update the query cache
2
  --> src/main.rs:17:17
3
   |
4
17 |       let users = sqlx::query_as!(
5
   |  _________________^
6
18 | |         User,
7
19 | |         r#"SELECT id as "id!", name, email FROM users ORDER BY id"#
8
...

Warning: This trips up CI pipelines and docker build constantly, because those environments often have no database. The fix is offline mode (cargo sqlx prepare → commit .sqlx/), or use the non-macro sqlx::query() API which never touches the database at build time.

A typo in your SQL is now a build error (this is the feature, not a bug)

With the macros, a wrong column name fails the build with the database’s own message embedded. Misspelling name as usrname produces this real compile error:

1
error: error returned from database: (code: 1) no such column: usrname
2
  --> src/main.rs:10:16
3
   |
4
10 |     let rows = sqlx::query!("SELECT id, usrname FROM users")
5
   |                ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

In TypeScript that same typo would compile and only fail when the query ran. Treat this error as SQLx doing its job.

SQLite reports primary keys as nullable — the `"col!"` cast

SQLite’s type inference is weaker than PostgreSQL’s, and it reports INTEGER PRIMARY KEY columns as nullable. So sqlx::query!("SELECT id FROM users") infers id: Option<i64>, and assigning it to an i64 field fails:

1
error[E0308]: mismatched types
2
37 |         Ok(rec.map(|r| User { id: r.id, name: r.name, email: r.email }))
3
   |                                   ^^^^ expected `i64`, found `Option<i64>`
4
   = note:    found enum `Option<i64>`

The fix is SQLx’s forced-non-null override in the SQL: alias the column with a trailing !. (PostgreSQL usually does not need this, because it tracks nullability precisely.)

1
// infers id: Option<i64> on SQLite
2
// let rec = sqlx::query!("SELECT id, name FROM users").fetch_one(&pool).await?;
3

4
// `as "id!"` tells the macro the column is non-null -> id: i64
5
// let rec = sqlx::query!(r#"SELECT id as "id!", name FROM users"#).fetch_one(&pool).await?;

Bind-parameter type mismatches with `query()` are caught at runtime, not compile time

The dynamic sqlx::query("... = ?").bind(value) API does not check that value’s Rust type matches the column. A wrong type surfaces as a sqlx::Error when the statement runs. If you want that checked at compile time, use the query! macros, which validate every bind against the schema. (This is the trade-off table in reverse: flexibility vs. compile-time safety.)

Forgetting the runtime/TLS feature flag

If you cargo add sqlx without a runtime-* feature, you get link/usage errors because SQLx has no executor selected. Always include exactly one runtime feature (e.g. runtime-tokio-rustls). This mirrors the broader Rust async rule: futures are lazy and need a runtime to drive them.

Best Practices

Pick query!/query_as! by default. The compile-time safety is the reason to choose SQLx over a bare driver. Drop to dynamic sqlx::query() only when the SQL genuinely must be built at runtime.
Commit your offline cache. Run cargo sqlx prepare and check in .sqlx/ so CI and Docker builds work without a live database. Re-run it whenever a query or the schema changes.
One pool per application, cloned everywhere. Build the Pool once at startup, store it in your shared state, and .clone() it into handlers — never open a fresh connection per request. See Connection Pooling.
Use a .env with DATABASE_URL. The query! macros and sqlx-cli read it automatically at compile time, but your runtime code does not — load it yourself with dotenvy::dotenv().ok(); (or export the variable) so std::env::var can see it. Keep .env out of version control.
Enable type-mapping features you need (uuid, chrono/time, json) so columns map to real Rust types instead of strings.
Let ? carry sqlx::Error up to a boundary, then convert to your domain error there (with thiserror or anyhow) rather than matching on it at every call site.

Real-World Example

A small UserRepo over a connection pool — the SQLx equivalent of a TypeORM repository. It uses the compile-time-checked macros, RETURNING to get the inserted row back in one round-trip, and fetch_optional for “maybe one” lookups. Errors flow through anyhow.

1
[dependencies]
2
sqlx = { version = "0.8", features = ["runtime-tokio-rustls", "sqlite"] }
3
tokio = { version = "1", features = ["full"] }
4
anyhow = "1"
5
dotenvy = "0.15"

1
use sqlx::sqlite::SqlitePoolOptions;
2
use sqlx::SqlitePool;
3

4
#[derive(Debug, Clone)]
5
struct User {
6
    id: i64,
7
    name: String,
8
    email: String,
9
}
10

11
// A repository holding a clonable pool handle — share one across the app.
12
#[derive(Clone)]
13
struct UserRepo {
14
    pool: SqlitePool,
15
}
16

17
impl UserRepo {
18
    fn new(pool: SqlitePool) -> Self {
19
        Self { pool }
20
    }
21

22
    async fn create(&self, name: &str, email: &str) -> anyhow::Result<User> {
23
        // RETURNING gives us the inserted row in a single round-trip.
24
        // `as "id!"` forces the SQLite primary key to non-null (i64, not Option).
25
        let rec = sqlx::query!(
26
            r#"INSERT INTO users (name, email)
27
               VALUES (?, ?)
28
               RETURNING id as "id!", name, email"#,
29
            name,
30
            email
31
        )
32
        .fetch_one(&self.pool)
33
        .await?;
34
        Ok(User { id: rec.id, name: rec.name, email: rec.email })
35
    }
36

37
    async fn find_by_email(&self, email: &str) -> anyhow::Result<Option<User>> {
38
        let rec = sqlx::query!(
39
            r#"SELECT id as "id!", name, email FROM users WHERE email = ?"#,
40
            email
41
        )
42
        .fetch_optional(&self.pool)
43
        .await?;
44
        Ok(rec.map(|r| User { id: r.id, name: r.name, email: r.email }))
45
    }
46
}
47

48
#[tokio::main]
49
async fn main() -> anyhow::Result<()> {
50
    // Load a .env into the process environment at RUNTIME. (SQLx's macros read
51
    // .env at compile time; `std::env::var` at runtime does not — so without
52
    // this line you would need to `export DATABASE_URL=...` first.)
53
    dotenvy::dotenv().ok();
54

55
    // In production DATABASE_URL comes from the environment / .env.
56
    let pool = SqlitePoolOptions::new()
57
        .max_connections(5)
58
        .connect(&std::env::var("DATABASE_URL")?)
59
        .await?;
60

61
    let repo = UserRepo::new(pool);
62

63
    let carol = repo.create("Carol", "carol@example.com").await?;
64
    println!("created: {carol:?}");
65

66
    match repo.find_by_email("carol@example.com").await? {
67
        Some(u) => println!("found: {} <{}>", u.name, u.email),
68
        None => println!("not found"),
69
    }
70
    Ok(())
71
}

With DATABASE_URL pointing at a SQLite file that has the users table, this prints (real output):

1
created: User { id: 3, name: "Carol", email: "carol@example.com" }
2
found: Carol <carol@example.com>

The UserRepo is Clone because the Pool inside it is a cheap Arc. In a web service you would build the pool once in main, wrap the repo in your application state, and clone it into every handler — exactly how a database handle is shared across routes in the web frameworks covered in Section 16.

Exercises

Exercise 1: Connect and count

Difficulty: Beginner

Objective: Get comfortable building a pool and running a no-row statement plus a single-row read.

Instructions: Using an in-memory SQLite database (sqlite::memory:), create a products table with columns id INTEGER PRIMARY KEY, name TEXT NOT NULL, and price REAL NOT NULL. Insert two products with bound parameters, then run SELECT COUNT(*) and print the count. Use the dynamic sqlx::query(...) API (no macros, so no DATABASE_URL needed).

Solution

1
[dependencies]
2
sqlx = { version = "0.8", features = ["runtime-tokio-rustls", "sqlite"] }
3
tokio = { version = "1", features = ["full"] }

1
use sqlx::sqlite::SqlitePoolOptions;
2
use sqlx::Row;
3

4
#[tokio::main]
5
async fn main() -> Result<(), sqlx::Error> {
6
    let pool = SqlitePoolOptions::new()
7
        .max_connections(5)
8
        .connect("sqlite::memory:")
9
        .await?;
10

11
    sqlx::query(
12
        "CREATE TABLE products (
13
            id    INTEGER PRIMARY KEY,
14
            name  TEXT NOT NULL,
15
            price REAL NOT NULL
16
        )",
17
    )
18
    .execute(&pool)
19
    .await?;
20

21
    for (name, price) in [("Keyboard", 49.99), ("Mouse", 19.50)] {
22
        sqlx::query("INSERT INTO products (name, price) VALUES (?, ?)")
23
            .bind(name)
24
            .bind(price)
25
            .execute(&pool)
26
            .await?;
27
    }
28

29
    let row = sqlx::query("SELECT COUNT(*) AS count FROM products")
30
        .fetch_one(&pool)
31
        .await?;
32
    let count: i64 = row.get("count");
33
    println!("product count = {count}");
34
    Ok(())
35
}

Real output:

1
product count = 2

Exercise 2: Typed reads with `FromRow`

Difficulty: Intermediate

Objective: Map result rows into your own struct instead of pulling columns by name.

Instructions: Extend Exercise 1. Define a Product struct deriving FromRow (fields id: i64, name: String, price: f64). Use sqlx::query_as::<_, Product>(...) with fetch_all to load every product ordered by price descending, and print each one. Then use fetch_optional to look up a product by name that does not exist, and confirm you get None.

Solution

1
use sqlx::sqlite::SqlitePoolOptions;
2
use sqlx::FromRow;
3

4
#[derive(Debug, FromRow)]
5
struct Product {
6
    id: i64,
7
    name: String,
8
    price: f64,
9
}
10

11
#[tokio::main]
12
async fn main() -> Result<(), sqlx::Error> {
13
    let pool = SqlitePoolOptions::new()
14
        .connect("sqlite::memory:")
15
        .await?;
16

17
    sqlx::query(
18
        "CREATE TABLE products (
19
            id    INTEGER PRIMARY KEY,
20
            name  TEXT NOT NULL,
21
            price REAL NOT NULL
22
        )",
23
    )
24
    .execute(&pool)
25
    .await?;
26

27
    for (name, price) in [("Keyboard", 49.99), ("Mouse", 19.50)] {
28
        sqlx::query("INSERT INTO products (name, price) VALUES (?, ?)")
29
            .bind(name)
30
            .bind(price)
31
            .execute(&pool)
32
            .await?;
33
    }
34

35
    // Many rows into Product via FromRow.
36
    let products: Vec<Product> =
37
        sqlx::query_as::<_, Product>("SELECT id, name, price FROM products ORDER BY price DESC")
38
            .fetch_all(&pool)
39
            .await?;
40
    for p in &products {
41
        println!("{}: {} (${:.2})", p.id, p.name, p.price);
42
    }
43

44
    // A "maybe one" lookup that finds nothing -> None.
45
    let missing: Option<Product> =
46
        sqlx::query_as::<_, Product>("SELECT id, name, price FROM products WHERE name = ?")
47
            .bind("Monitor")
48
            .fetch_optional(&pool)
49
            .await?;
50
    println!("missing lookup = {missing:?}");
51
    Ok(())
52
}

Real output:

1
1: Keyboard ($49.99)
2
2: Mouse ($19.50)
3
missing lookup = None

Exercise 3: Compile-time-checked insert with `RETURNING`

Difficulty: Advanced

Objective: Use the query! macro against a real SQLite file database and handle SQLite’s nullable-primary-key quirk.

Instructions: Create a file database notes.db with a table notes (id INTEGER PRIMARY KEY, body TEXT NOT NULL). Set DATABASE_URL=sqlite://<absolute path>/notes.db (e.g. in a .env file). Write an async fn add_note(pool, body) that uses sqlx::query! with INSERT ... RETURNING to insert a note and return its new id as an i64. Remember the as "id!" cast. Call it twice in main and print both ids.

Solution

Setup (run once before building, since the macro needs the schema at compile time):

1
sqlite3 notes.db "CREATE TABLE notes (id INTEGER PRIMARY KEY, body TEXT NOT NULL);"
2
echo "DATABASE_URL=sqlite://$(pwd)/notes.db" > .env

1
[dependencies]
2
sqlx = { version = "0.8", features = ["runtime-tokio-rustls", "sqlite"] }
3
tokio = { version = "1", features = ["full"] }
4
anyhow = "1"
5
dotenvy = "0.15"

1
use sqlx::sqlite::SqlitePoolOptions;
2
use sqlx::SqlitePool;
3

4
async fn add_note(pool: &SqlitePool, body: &str) -> anyhow::Result<i64> {
5
    // `as "id!"` overrides SQLite's nullable inference so `id` is i64, not Option<i64>.
6
    let rec = sqlx::query!(
7
        r#"INSERT INTO notes (body) VALUES (?) RETURNING id as "id!""#,
8
        body
9
    )
10
    .fetch_one(pool)
11
    .await?;
12
    Ok(rec.id)
13
}
14

15
#[tokio::main]
16
async fn main() -> anyhow::Result<()> {
17
    // Load the .env at runtime so `std::env::var` can see DATABASE_URL.
18
    // (The query! macro already read it at compile time; this is for runtime.)
19
    dotenvy::dotenv().ok();
20

21
    let pool = SqlitePoolOptions::new()
22
        .connect(&std::env::var("DATABASE_URL")?)
23
        .await?;
24

25
    let first = add_note(&pool, "buy milk").await?;
26
    let second = add_note(&pool, "call dentist").await?;
27
    println!("inserted note ids: {first}, {second}");
28
    Ok(())
29
}

Real output (ids depend on existing rows; on a fresh notes.db):

1
inserted note ids: 1, 2

Tip: If you remove the as "id!" cast, the build fails with expected i64, found Option<i64> — proof that the macro inferred the column type from the live schema at compile time.

SQLx: Async, Compile-Time-Checked SQL

Quick Overview

TypeScript/JavaScript Example

Rust Equivalent

Detailed Explanation

Feature flags decide what gets compiled

The pool is a cheap, clonable handle

execute vs fetch_*: pick by what comes back

Two query styles: dynamic query() vs the checked query! macro

? propagates database errors like a thrown rejection

Key Differences

Common Pitfalls

Forgetting DATABASE_URL (the macros need a live database to compile)

A typo in your SQL is now a build error (this is the feature, not a bug)

SQLite reports primary keys as nullable — the "col!" cast

Bind-parameter type mismatches with query() are caught at runtime, not compile time

Forgetting the runtime/TLS feature flag

Best Practices

Real-World Example

Further Reading

Exercises

Exercise 1: Connect and count

Exercise 2: Typed reads with FromRow

Exercise 3: Compile-time-checked insert with RETURNING

`execute` vs `fetch_*`: pick by what comes back

Two query styles: dynamic `query()` vs the checked `query!` macro

`?` propagates database errors like a thrown rejection

Forgetting `DATABASE_URL` (the macros need a live database to compile)

SQLite reports primary keys as nullable — the `"col!"` cast

Bind-parameter type mismatches with `query()` are caught at runtime, not compile time

Exercise 2: Typed reads with `FromRow`

Exercise 3: Compile-time-checked insert with `RETURNING`