Rust Databases: SQLite and Postgres with sqlx, Diesel and SeaORM

Sooner or later your program needs to remember things: users, orders, high scores, to-dos. When the program stops, a Vec or a HashMap is gone. A database keeps the data safe on disk, lets many programs share it, and answers questions like “show me every open task, newest first” very fast.

Rust has very good database crates. On this page you’ll pick one, then write real code: a small “repository” in plain Rust (runs right here in the Playground), then SQLite with sqlx, Postgres, migrations, and short tours of Diesel and SeaORM. Database crates aren’t available in the Playground, so those examples are for your own computer with cargo run. Every one of them was built and run, and the output shown is real.

What you can build

• The data layer of a web API. Take the to-do server from Project: Build a Web API and keep its data in Postgres instead of memory.
• Big, busy websites. crates.io, the site that hosts every Rust crate, stores its data in Postgres through Diesel. So does Lemmy, an open-source community platform.
• Desktop and command-line tools with a local SQLite file: a notes app, a budget tracker, a cache.
• Background workers and data jobs that read rows, process them, and write results back.

Where it’s harder: Rust has no single “official” stack like Entity Framework in .NET or ActiveRecord in Rails, so you choose between a few good crates. The first build of a database crate takes a minute or two. And compile-time checked SQL (a great feature) needs a little setup.

Which crate?

Crate	Sync or async	Checks your queries at compile time?	ORM?	Pick it when
rusqlite	sync	no	no, plain SQL	A small tool or desktop app with one SQLite file. No async needed.
sqlx	async	yes, with the query! macros (optional)	no, plain SQL	You like writing SQL and want async. SQLite, Postgres, MySQL. The usual first choice for web apps.
Diesel	sync (diesel-async adds async)	yes, the whole query builder is type-checked	ORM and query builder	You want the compiler to check every query and you don’t mind learning its query style. Very mature.
SeaORM	async (sea-orm-sync exists)	types yes, SQL no (checked when it runs)	yes, full ORM	You want entities, relations and “save this object” like Entity Framework. Built on sqlx.

All four are mature and widely used. If you’re not sure, start with sqlx: you write normal SQL, so what you learn works in every language. Coming from C#? The Rust for C# Developers page maps Entity Framework, Dapper and ADO.NET to these crates.

The shape first: a repository in plain Rust

Before any database, let’s look at a pattern almost every app uses: the repository. It’s one place that knows how to save and load your data. The rest of the program only calls methods like add and list_open, and doesn’t care whether the data lives in a HashMap, in SQLite or in Postgres.

In Rust, a trait describes what the repository can do, and a struct does the work. Here the storage is a HashMap in memory:

use std::collections::HashMap;

#[derive(Debug, Clone)]
struct Task {
    id: u32,
    title: String,
    done: bool,
}

// What the app needs from storage. Nothing here says HOW it's stored.
trait TaskRepository {
    fn add(&mut self, title: &str) -> u32;
    fn mark_done(&mut self, id: u32) -> Result<(), String>;
    fn list_open(&self) -> Vec<Task>;
}

struct InMemoryRepo {
    tasks: HashMap<u32, Task>,
    next_id: u32,
}

impl TaskRepository for InMemoryRepo {
    fn add(&mut self, title: &str) -> u32 {
        let id = self.next_id;
        self.tasks.insert(id, Task { id, title: title.to_string(), done: false });
        self.next_id += 1;
        id
    }

    fn mark_done(&mut self, id: u32) -> Result<(), String> {
        match self.tasks.get_mut(&id) {
            Some(task) => {
                task.done = true;
                Ok(())
            }
            None => Err(format!("no task with id {id}")),
        }
    }

    fn list_open(&self) -> Vec<Task> {
        let mut open: Vec<Task> = self.tasks.values().filter(|t| !t.done).cloned().collect();
        open.sort_by_key(|t| t.id); // a HashMap has no order, so sort
        open
    }
}

// Works with ANY repository: in memory today, SQLite or Postgres tomorrow.
fn print_open(repo: &impl TaskRepository) {
    for task in repo.list_open() {
        println!("[ ] {}: {}", task.id, task.title);
    }
}

fn main() {
    let mut repo = InMemoryRepo { tasks: HashMap::new(), next_id: 1 };
    repo.add("Buy milk");
    repo.add("Learn sqlx");
    repo.add("Walk the dog");

    repo.mark_done(1).unwrap();
    print_open(&repo);
    println!("{:?}", repo.mark_done(99));
}

Output:

[ ] 2: Learn sqlx
[ ] 3: Walk the dog
Err("no task with id 99")

▶ Try it Yourself

Look at what a database will give you here. The HashMap becomes a table, each Task becomes a row, and next_id becomes an auto-numbered id column. list_open becomes a query with WHERE done = FALSE ORDER BY id. The in-memory version is also handy later: it makes a fast fake for your tests.

sqlx with SQLite: your first real database

SQLite is a whole database in one file (or just in memory). There’s no server to install, which makes it perfect for learning, for tests, and for many real apps. Create a project and add sqlx and tokio:

cargo new todo-db
cd todo-db
cargo add sqlx --features runtime-tokio,sqlite
cargo add tokio --features full

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

The sqlite feature builds SQLite into your program, so you don’t need to install anything else. sqlx is async, which is why it needs tokio. Now replace src/main.rs:

use sqlx::sqlite::SqlitePool;

#[derive(Debug, sqlx::FromRow)]
struct Task {
    id: i64,
    title: String,
    done: bool,
}

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    // An in-memory database: perfect for learning, gone when the program ends.
    let pool = SqlitePool::connect("sqlite::memory:").await?;

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

    for title in ["Buy milk", "Learn sqlx", "Walk the dog"] {
        let result = sqlx::query("INSERT INTO tasks (title) VALUES (?)")
            .bind(title)
            .execute(&pool)
            .await?;
        println!("Inserted task {}", result.last_insert_rowid());
    }

    sqlx::query("UPDATE tasks SET done = TRUE WHERE id = ?")
        .bind(1_i64)
        .execute(&pool)
        .await?;

    let tasks: Vec<Task> = sqlx::query_as("SELECT id, title, done FROM tasks ORDER BY id")
        .fetch_all(&pool)
        .await?;

    for task in &tasks {
        let mark = if task.done { "x" } else { " " };
        println!("[{mark}] {}: {}", task.id, task.title);
    }

    let open: Option<Task> = sqlx::query_as("SELECT id, title, done FROM tasks WHERE id = ?")
        .bind(42_i64)
        .fetch_optional(&pool)
        .await?;
    println!("Task 42: {open:?}");

    Ok(())
}

Run it with cargo run:

Inserted task 1
Inserted task 2
Inserted task 3
[x] 1: Buy milk
[ ] 2: Learn sqlx
[ ] 3: Walk the dog
Task 42: None

Step by step:

• sqlx::query("...") holds the SQL. Each ? is a placeholder, and .bind(value) fills it in, in order.
• .execute(&pool) runs a statement that returns no rows (CREATE, INSERT, UPDATE, DELETE). The result tells you rows_affected(), and for SQLite last_insert_rowid().
• #[derive(sqlx::FromRow)] lets sqlx turn each row into a Task. The column names must match the field names.
• query_as + fetch_all gives a Vec<Task>. Use fetch_one when there must be exactly one row, and fetch_optional when there may be none. Notice how a missing row became None, an Option, not a crash.
• Every call returns a Result, so ? passes database errors up to main.

To keep the data after the program ends, use a file instead: SqlitePool::connect("sqlite:todos.db?mode=rwc"). The mode=rwc part means “read, write, and create the file if it’s missing”.

Compile-time checked queries. sqlx also has macros like sqlx::query_as!(Task, "SELECT id, title, done FROM tasks WHERE done = ?", done). At build time, the macro connects to your development database (from DATABASE_URL) and checks the SQL. A typo like titel stops the build with no such column: titel, before any user sees it. This needs a database with your tables while you build, so start with the plain query functions and add the macros once you have migrations (below).

Connecting to Postgres

Postgres (PostgreSQL) is the most popular choice for web apps. It runs as a server, so you need one running: install it from postgresql.org, use a cloud database, or start one with Docker:

docker run --name todo-pg -e POSTGRES_PASSWORD=secret -p 5432:5432 -d postgres

The code barely changes. Turn on the postgres feature (add a TLS feature such as tls-rustls for a cloud database, which usually requires encryption):

cargo add sqlx --features runtime-tokio,postgres,tls-rustls

Then connect with a connection string, a URL that says where the database is and how to log in:

postgres://USER:PASSWORD@HOST:PORT/DATABASE
postgres://postgres:secret@localhost:5432/postgres

Never write the password into your code. Put the URL in the DATABASE_URL environment variable and read it at startup (see Environment Variables):

use sqlx::postgres::PgPoolOptions;
use std::time::Duration;

let url = std::env::var("DATABASE_URL").expect("set DATABASE_URL first");

let pool = PgPoolOptions::new()
    .max_connections(5)
    .acquire_timeout(Duration::from_secs(3))
    .connect(&url)
    .await?;

// Postgres numbers its placeholders: $1, $2, ...
let tasks: Vec<Task> = sqlx::query_as("SELECT id, title, done FROM tasks WHERE done = $1")
    .bind(false)
    .fetch_all(&pool)
    .await?;

Two differences from SQLite to remember: Postgres placeholders are $1, $2 instead of ?, and an auto-numbered id is usually BIGINT GENERATED ALWAYS AS IDENTITY (or BIGSERIAL), which fits an i64. If your password has characters like @ or /, percent-encode them in the URL (@ becomes %40). Tools like sqlx-cli also read DATABASE_URL from a .env file in your project folder. Add .env to .gitignore so it never ends up on GitHub.

Migrations with sqlx-cli

Your tables will change over time: a new column here, a new table there. Migrations are numbered SQL files that describe each change. Every database (yours, your teammate’s, production) runs the same files in the same order, and the database remembers which ones it has already run. Install the command-line tool once:

# all databases
cargo install sqlx-cli

# or only what you need, for a faster build
cargo install sqlx-cli --no-default-features --features sqlite,rustls

Then, in your project folder:

echo "DATABASE_URL=sqlite:todos.db" > .env
sqlx database create
sqlx migrate add create_tasks

Creating migrations/20261018035858_create_tasks.sql

(Your number will be different: it’s the date and time.) Write the change into that file:

CREATE TABLE tasks (
    id    INTEGER PRIMARY KEY,
    title TEXT    NOT NULL,
    done  BOOLEAN NOT NULL DEFAULT FALSE
);

And apply it:

sqlx migrate run

Applied 20261018035858/migrate create tasks (1.449995ms)

Run it again and nothing happens, because that migration is already applied. sqlx migrate info lists what’s applied and what’s pending. Never edit a migration that already ran somewhere; add a new one instead.

You can also let your program apply the migrations when it starts. The migrate! macro bakes the files from ./migrations into your binary:

let pool = SqlitePool::connect("sqlite::memory:").await?;
sqlx::migrate!().run(&pool).await?; // runs only the migrations this database hasn't seen

Diesel in a nutshell

Diesel takes a different road: instead of writing SQL strings, you build queries with Rust methods, and the compiler checks them against a description of your tables. For example, done is a bool column, so tasks::done.eq(true) compiles, but tasks::done.eq("yes") is a compile error. Diesel is synchronous, which is fine for tools and for many servers.

[dependencies]
diesel = { version = "2.3", features = ["sqlite"] }
# Builds SQLite into your program. Only needed if you don't have SQLite installed:
libsqlite3-sys = { version = "0.38", features = ["bundled"] }

use diesel::prelude::*;
use diesel::sqlite::SqliteConnection;

// Normally `diesel migration run` generates this into src/schema.rs for you.
diesel::table! {
    tasks (id) {
        id -> Integer,
        title -> Text,
        done -> Bool,
    }
}

#[derive(Queryable, Selectable, Debug)]
#[diesel(table_name = tasks)]
#[diesel(check_for_backend(diesel::sqlite::Sqlite))]
struct Task {
    id: i32,
    title: String,
    done: bool,
}

#[derive(Insertable)]
#[diesel(table_name = tasks)]
struct NewTask<'a> {
    title: &'a str,
}

fn main() -> Result<(), Box<dyn std::error::Error>> {
    let mut conn = SqliteConnection::establish(":memory:")?;

    // A migration would normally do this.
    diesel::sql_query(
        "CREATE TABLE tasks (
            id INTEGER PRIMARY KEY NOT NULL,
            title TEXT NOT NULL,
            done BOOLEAN NOT NULL DEFAULT FALSE
        )",
    )
    .execute(&mut conn)?;

    let new_tasks = [NewTask { title: "Buy milk" }, NewTask { title: "Learn Diesel" }];
    diesel::insert_into(tasks::table)
        .values(&new_tasks[..])
        .execute(&mut conn)?;

    diesel::update(tasks::table.find(1))
        .set(tasks::done.eq(true))
        .execute(&mut conn)?;

    let all: Vec<Task> = tasks::table
        .order(tasks::id)
        .select(Task::as_select())
        .load(&mut conn)?;
    for task in &all {
        println!("{} {} done={}", task.id, task.title, task.done);
    }

    let open: i64 = tasks::table
        .filter(tasks::done.eq(false))
        .count()
        .get_result(&mut conn)?;
    println!("{open} task(s) still open");
    Ok(())
}

1 Buy milk done=true
2 Learn Diesel done=false
1 task(s) still open

In a real project, you install diesel_cli (cargo install diesel_cli --no-default-features --features sqlite), then diesel setup, diesel migration generate create_tasks and diesel migration run. The CLI writes src/schema.rs with the table! code for you, so it always matches the database. The Getting Started guide walks through all of it.

SeaORM in a nutshell

SeaORM is an async ORM: you describe each table as an entity (a struct with attributes), and work with objects instead of SQL. It feels close to Entity Framework. Version 2.0 can even create the tables from your entities.

[dependencies]
sea-orm = { version = "2", features = ["sqlx-sqlite", "runtime-tokio", "entity-registry", "schema-sync"] }
tokio = { version = "1", features = ["full"] }

mod task {
    use sea_orm::entity::prelude::*;

    #[sea_orm::model]
    #[derive(Clone, Debug, PartialEq, Eq, DeriveEntityModel)]
    #[sea_orm(table_name = "task")]
    pub struct Model {
        #[sea_orm(primary_key)]
        pub id: i32,
        pub title: String,
        pub done: bool,
    }

    impl ActiveModelBehavior for ActiveModel {}
}

use sea_orm::{entity::*, query::*, Database, DbErr};

#[tokio::main]
async fn main() -> Result<(), DbErr> {
    let db = &Database::connect("sqlite::memory:").await?;

    // Create tables for every entity in this crate. "seaorm_demo" is the
    // package name from Cargo.toml, with - changed to _.
    db.get_schema_registry("seaorm_demo::*").sync(db).await?;

    for title in ["Buy milk", "Learn SeaORM"] {
        task::ActiveModel {
            title: Set(title.to_owned()),
            done: Set(false),
            ..Default::default()
        }
        .insert(db)
        .await?;
    }

    let mut milk = task::Entity::find_by_id(1).one(db).await?.unwrap().into_active_model();
    milk.done = Set(true);
    milk.update(db).await?;

    let open = task::Entity::find()
        .filter(task::Column::Done.eq(false))
        .all(db)
        .await?;
    for t in &open {
        println!("open: {} {}", t.id, t.title);
    }
    Ok(())
}

open: 2 Learn SeaORM

The Model is a row you read. The ActiveModel is a row you’re changing: Set(...) marks the fields to save, and ..Default::default() leaves id for the database to fill in. For bigger projects, sea-orm-cli generates entities from an existing database and manages migrations. See the SeaORM documentation.

SQL injection: always use bind parameters

This is the most important rule on the page. Never build SQL by pasting user input into the string with format!. Here’s why, in plain Rust (no database needed):

// What the database receives, built in two different ways.
fn pasted_query(name: &str) -> String {
    format!("SELECT * FROM users WHERE name = '{name}'")
}

fn bound_query(name: &str) -> (&'static str, Vec<String>) {
    ("SELECT * FROM users WHERE name = ?", vec![name.to_string()])
}

fn main() {
    for input in ["ana", "x' OR '1'='1"] {
        println!("input: {input}");
        println!("  pasted: {}", pasted_query(input));
        let (sql, params) = bound_query(input);
        println!("  bound:  {sql}   params: {params:?}");
    }
}

Output:

input: ana
  pasted: SELECT * FROM users WHERE name = 'ana'
  bound:  SELECT * FROM users WHERE name = ?   params: ["ana"]
input: x' OR '1'='1
  pasted: SELECT * FROM users WHERE name = 'x' OR '1'='1'
  bound:  SELECT * FROM users WHERE name = ?   params: ["x' OR '1'='1"]

▶ Try it Yourself

With pasting, the quote in the input ends the string early, and OR '1'='1' becomes part of the SQL. That condition is always true, so the query returns every user. An attacker can use the same trick to read other people’s data or delete tables. This is called SQL injection, and it’s one of the most common security bugs on the web.

With a bind parameter, the SQL text never changes. The input travels separately, and the database treats it only as a value. Here are both versions with sqlx, run against a real SQLite table with the users ana and root:

let user_input = "nobody' OR '1'='1"; // imagine this came from a login form

// DON'T: pasting input into the SQL text.
let sql = format!("SELECT name FROM users WHERE name = '{user_input}'");
let rows: Vec<(String,)> = sqlx::query_as(AssertSqlSafe(sql)).fetch_all(&pool).await?;
println!("format!: {rows:?}");

// DO: a fixed SQL text with a ? placeholder, and the input sent separately.
let rows: Vec<(String,)> = sqlx::query_as("SELECT name FROM users WHERE name = ?")
    .bind(user_input)
    .fetch_all(&pool)
    .await?;
println!("bind:    {rows:?}");

format!: [("ana",), ("root",)]
bind:    []

The pasted query leaked both users. The bound query correctly found nobody with that strange name. And notice the AssertSqlSafe(...) wrapper in the bad version: sqlx 0.9 protects you by default. sqlx::query and query_as only accept a fixed &'static str, so passing a format! string without that wrapper fails to compile:

error[E0277]: dynamic SQL strings should be audited for possible injections
    = 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.

So when would you ever use AssertSqlSafe? Only when you truly must build SQL at run time, say a sort column the user picks. Check the value against your own fixed list of allowed names first, then wrap the string to tell sqlx you’ve audited it. For values, always use .bind(). Diesel and SeaORM bind values for you, as long as you use their query builders and not raw SQL strings.

Connection pools

Opening a database connection is slow: there’s a network trip, a login, and some setup. A pool opens a few connections once and lends them out. Your code borrows a connection for one query and gives it back right away. That’s why every example above passed &pool, not a single connection.

use sqlx::sqlite::{SqliteConnectOptions, SqlitePoolOptions};

let options = SqliteConnectOptions::new()
    .filename("visits.db")
    .create_if_missing(true);

let pool = SqlitePoolOptions::new()
    .max_connections(4) // never more than 4 at once
    .connect_with(options)
    .await?;

sqlx::query("CREATE TABLE IF NOT EXISTS visits (page TEXT NOT NULL)")
    .execute(&pool)
    .await?;

// 20 tasks share one pool. Cloning a pool is cheap: it's just a handle.
let mut handles = Vec::new();
for n in 0..20 {
    let pool = pool.clone();
    handles.push(tokio::spawn(async move {
        sqlx::query("INSERT INTO visits (page) VALUES (?)")
            .bind(format!("/page/{}", n % 3))
            .execute(&pool)
            .await
    }));
}
for handle in handles {
    handle.await.expect("task panicked")?; // wait for all 20 inserts
}

The 20 tasks run at the same time, but at most 4 talk to the database at any moment. The others wait a tiny bit for a free connection. Counting the rows afterwards (with a fresh visits.db) gives:

/page/0: 7 visits
/page/1: 7 visits
/page/2: 6 visits

A few rules of thumb:

• Create one pool when the program starts and share it. In an axum app, put it in your app state (see the web API project).
• Don’t create a new pool per request. That throws away the whole benefit.
• max_connections should stay well below what the database allows. A Postgres server allows 100 connections by default, and every copy of your app has its own pool.
• Set an acquire_timeout, so that when all connections are busy, a request fails with a clear error instead of waiting forever.

Practice

1. Search the repository

Take the in-memory repository idea and add a search method: it returns the tasks whose title contains a word, ignoring upper and lower case, sorted by id. Test it with “milk”. Hint: title.to_lowercase().contains(&word.to_lowercase()), and a Vec<Task> is enough storage here.

Show solution

#[derive(Debug, Clone)]
struct Task {
    id: u32,
    title: String,
}

trait TaskRepository {
    fn add(&mut self, title: &str) -> u32;
    fn search(&self, word: &str) -> Vec<Task>;
}

struct VecRepo {
    tasks: Vec<Task>,
}

impl TaskRepository for VecRepo {
    fn add(&mut self, title: &str) -> u32 {
        let id = self.tasks.len() as u32 + 1;
        self.tasks.push(Task { id, title: title.to_string() });
        id
    }

    fn search(&self, word: &str) -> Vec<Task> {
        let word = word.to_lowercase();
        let mut found: Vec<Task> = self
            .tasks
            .iter()
            .filter(|t| t.title.to_lowercase().contains(&word))
            .cloned()
            .collect();
        found.sort_by_key(|t| t.id);
        found
    }
}

fn main() {
    let mut repo = VecRepo { tasks: Vec::new() };
    repo.add("Buy milk");
    repo.add("Walk the dog");
    repo.add("Milk the cow");

    for task in repo.search("milk") {
        println!("{}: {}", task.id, task.title);
    }
    println!("{} result(s) for \"cat\"", repo.search("cat").len());
}

Output:

1: Buy milk
3: Milk the cow
0 result(s) for "cat"

▶ Try it Yourself

The storage changed from a HashMap to a Vec, but code that calls the trait doesn’t need to know. In SQL, the same search is WHERE title LIKE ? with .bind(format!("%{word}%")). Note that the % signs go into the bound value, not into the SQL text.

2. A real repository with sqlx (on your machine)

Write a struct SqliteTaskRepo that holds a SqlitePool and has three async methods: add(title) returns the new id, mark_done(id) returns true if a row changed, and list_open() returns the open tasks. Create the table in main, add three tasks, and mark task 1 and task 99 as done. Hint: rows_affected() on the result of execute tells you how many rows an UPDATE changed.

Show solution

use sqlx::sqlite::SqlitePool;

#[derive(Debug, sqlx::FromRow)]
struct Task {
    id: i64,
    title: String,
    done: bool,
}

struct SqliteTaskRepo {
    pool: SqlitePool,
}

impl SqliteTaskRepo {
    async fn add(&self, title: &str) -> Result<i64, sqlx::Error> {
        let result = sqlx::query("INSERT INTO tasks (title) VALUES (?)")
            .bind(title)
            .execute(&self.pool)
            .await?;
        Ok(result.last_insert_rowid())
    }

    async fn mark_done(&self, id: i64) -> Result<bool, sqlx::Error> {
        let result = sqlx::query("UPDATE tasks SET done = TRUE WHERE id = ?")
            .bind(id)
            .execute(&self.pool)
            .await?;
        Ok(result.rows_affected() == 1)
    }

    async fn list_open(&self) -> Result<Vec<Task>, sqlx::Error> {
        sqlx::query_as("SELECT id, title, done FROM tasks WHERE done = FALSE ORDER BY id")
            .fetch_all(&self.pool)
            .await
    }
}

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let pool = SqlitePool::connect("sqlite::memory:").await?;
    sqlx::query("CREATE TABLE tasks (id INTEGER PRIMARY KEY, title TEXT NOT NULL, done BOOLEAN NOT NULL DEFAULT FALSE)")
        .execute(&pool)
        .await?;

    let repo = SqliteTaskRepo { pool };
    repo.add("Buy milk").await?;
    repo.add("Learn sqlx").await?;
    repo.add("Walk the dog").await?;

    println!("mark 1 done: {}", repo.mark_done(1).await?);
    println!("mark 99 done: {}", repo.mark_done(99).await?);
    for task in repo.list_open().await? {
        println!("[{}] {}: {}", if task.done { "x" } else { " " }, task.id, task.title);
    }
    Ok(())
}

mark 1 done: true
mark 99 done: false
[ ] 2: Learn sqlx
[ ] 3: Walk the dog

It’s the same shape as the in-memory version, only async and returning Result, because a real database can fail (a full disk, a lost network). Next step: swap the Vec in your web API project for this repository.

Recap

• rusqlite for simple sync SQLite, sqlx for async plain SQL (the usual first choice), Diesel for a compile-time checked query builder, SeaORM for an async ORM like Entity Framework.
• A repository hides where data lives behind a few methods. Start with a HashMap and swap in a database later.
• In sqlx: query + .bind() + .execute() for changes, query_as + fetch_all / fetch_one / fetch_optional to read rows into a #[derive(FromRow)] struct.
• Keep the connection string in DATABASE_URL, not in your code. SQLite uses ? placeholders, Postgres uses $1, $2.
• Change your tables with migrations (sqlx migrate add / run), never by hand.
• Always bind user input. Never paste it into SQL with format!. Create one pool at startup and share it.

Quick check

Quick check

1. You want to write plain SQL, use async, and have the option of compile-time checked queries. Which crate fits best?

rusqlite Diesel sqlx SeaORM

2. Which sqlx method should you use when a query may return zero or one row?

fetch_optional, which gives an Option fetch_all, then take the first item execute fetch_one, which returns None if nothing is found

3. Why is format!("... WHERE name = '{name}'") dangerous?

It's slower than a bind parameter, but otherwise fine User input can end the string and change the SQL itself (SQL injection) format! can't be used with async code

4. What does a connection pool do?

It copies your database to several servers It runs migrations automatically It caches query results in memory It keeps a few open connections and lends them out, so you don't connect for every query

Score: 0 / 4

Read more

• sqlx on docs.rs: the API reference, with the query, query_as and query! macros, pools, and every supported database.
• sqlx-cli on docs.rs: how to install the CLI, create databases, run and revert migrations, and use offline mode for the query! macros.
• Getting Started with Diesel: the official step-by-step guide, from diesel setup to inserting, updating and deleting rows.
• SeaORM documentation: entities, relations, migrations and the sea-orm-cli code generator.
• OWASP SQL Injection Prevention Cheat Sheet: how SQL injection works and how parameterized queries stop it, for any language.