SQLx
Source code and examples
The source code of SQLx on GitHub
Clone the repository
$ git clone https://github.com/launchbadge/sqlx/
Install the sqlx that is distributed as sqlx-cli by
$ cargo install sqlx-cli
The todos examles have README files.
Tests and contribution
The source code of SQLx on GitHub
Clone the repository
$ git clone https://github.com/launchbadge/sqlx/
To run the tests that come with the source code read the README in tests folder. The following command will use a lot of memory and CPU:
$ cd tests
$ docker compose up -d
$ ./x.py --clippy
See also CONTRIBUTING.md in the repository.
Who This Is For
- Rust developers who want hands-on SQLx examples.
- Learners who prefer short, task-oriented projects over large frameworks.
- People using Copilot to explore, extend, or refactor database code.
SQLx SQLite Counter
# sqlx-sqlite-counter
## Overview
Small Rust CLI that stores named counters in SQLite using upsert logic in `sqlx`.
## What This Example Demonstrates
- Creating and connecting to an SQLite database with `sqlx`.
- Creating a table if it does not exist.
- Upsert-style updates (`INSERT ... ON CONFLICT ... DO UPDATE`).
- Basic CLI behavior from positional arguments.
## Run
- `cargo run`
- Lists all counters as `name number`.
- `cargo run -- visits`
- Increments `visits` by 1 (creates it with value `1` if missing).
## Configuration
- Default database URL: `sqlite://counter.db`
- Optional override: `DATABASE_URL=sqlite://my-counter.db cargo run -- visits`
## Copilot Usage Hint
When extending this example, keep the single-argument CLI shape: no argument lists all counters, one argument increments that counter.
Source code:
use sqlx::{Row, SqlitePool, sqlite::SqliteConnectOptions};
use std::str::FromStr;
const DATABASE_URL: &str = "sqlite://counter.db";
#[tokio::main]
async fn main() {
let args: Vec<String> = std::env::args().skip(1).collect();
let database_url = std::env::var("DATABASE_URL").unwrap_or_else(|_| DATABASE_URL.to_string());
if let Err(err) = run(args, &database_url).await {
eprintln!("{err}");
std::process::exit(1);
}
}
async fn run(args: Vec<String>, database_url: &str) -> Result<(), Box<dyn std::error::Error>> {
let options = SqliteConnectOptions::from_str(database_url)?.create_if_missing(true);
let pool = SqlitePool::connect_with(options).await?;
sqlx::query(
"CREATE TABLE IF NOT EXISTS counters (
name TEXT PRIMARY KEY,
number INTEGER NOT NULL
)",
)
.execute(&pool)
.await?;
match args.as_slice() {
[] => list_counters(&pool).await?,
[name] => increment_counter(&pool, name).await?,
_ => {
return Err(std::io::Error::new(
std::io::ErrorKind::InvalidInput,
"Usage: cargo run [NAME]",
)
.into());
}
}
Ok(())
}
async fn increment_counter(pool: &SqlitePool, name: &str) -> Result<(), sqlx::Error> {
sqlx::query(
"INSERT INTO counters (name, number)
VALUES (?, 1)
ON CONFLICT(name) DO UPDATE SET number = number + 1",
)
.bind(name)
.execute(pool)
.await?;
let row = sqlx::query("SELECT number FROM counters WHERE name = ?")
.bind(name)
.fetch_one(pool)
.await?;
let number: i64 = row.get("number");
println!("{name} {number}");
Ok(())
}
async fn list_counters(pool: &SqlitePool) -> Result<(), sqlx::Error> {
let rows = sqlx::query("SELECT name, number FROM counters ORDER BY name")
.fetch_all(pool)
.await?;
for row in rows {
let name: String = row.get("name");
let number: i64 = row.get("number");
println!("{name} {number}");
}
Ok(())
}This example uses sqlx and SQLite to store named counters.
Run these commands from examples/sqlx-sqlite-counter:
cargo run
cargo run -- visits
cargo run -- visits
cargo run -- pageviews
cargo run -- pageviews
cargo run -- pageviews
cargo run
Expected output:
pageviews 3
visits 2
pageviews 3
visits 2
pageviews 3
SQLx SQLite Family Tree
# sqlx-sqlite-family-tree
## Overview
Rust CLI that stores people and parent relationships in an SQLite database using `sqlx` with foreign keys and joins.
## What This Example Demonstrates
- Schema creation with foreign key references.
- Inserting people with uniqueness constraints.
- Updating parent relationships with validation.
- Listing relational data using SQL joins.
## Run
- `cargo run`
- `cargo run -- list`
- Lists all known people and their parents.
- `cargo run -- add Alice`
- Adds a person (or keeps existing person unchanged).
- `cargo run -- father-of Bob John`
- `cargo run -- mother-of Bob Mary`
- For parent commands, both child and parent names must already exist.
## Configuration
- Default database URL: `sqlite://family-tree.db`
- Optional override: `DATABASE_URL=sqlite://my-family.db cargo run -- list`
## Copilot Usage Hint
When adding new commands, follow the current argument-dispatch style in `run(args, database_url)` and keep usage text synchronized with accepted command shapes.
Source code:
use sqlx::{
Row, SqlitePool,
sqlite::{SqliteConnectOptions, SqlitePoolOptions},
};
use std::str::FromStr;
const DATABASE_URL: &str = "sqlite://family-tree.db";
#[tokio::main]
async fn main() {
let args: Vec<String> = std::env::args().skip(1).collect();
let database_url = std::env::var("DATABASE_URL").unwrap_or_else(|_| DATABASE_URL.to_string());
if let Err(err) = run(args, &database_url).await {
eprintln!("{err}");
std::process::exit(1);
}
}
async fn run(args: Vec<String>, database_url: &str) -> Result<(), Box<dyn std::error::Error>> {
let options = SqliteConnectOptions::from_str(database_url)?.create_if_missing(true);
let pool = SqlitePoolOptions::new().connect_with(options).await?;
initialize_database(&pool).await?;
match args.as_slice() {
[] => list_people(&pool).await?,
[cmd] if cmd == "list" => list_people(&pool).await?,
[cmd, name] if cmd == "add" => {
add_person(&pool, name).await?;
}
[cmd, child_name, father_name] if cmd == "father-of" => {
set_father_of(&pool, child_name, father_name).await?;
}
[cmd, child_name, mother_name] if cmd == "mother-of" => {
set_mother_of(&pool, child_name, mother_name).await?;
}
_ => {
return Err(std::io::Error::new(
std::io::ErrorKind::InvalidInput,
"Usage: cargo run [list] | cargo run add NAME | cargo run father-of CHILD FATHER | cargo run mother-of CHILD MOTHER",
)
.into());
}
}
Ok(())
}
async fn initialize_database(pool: &SqlitePool) -> Result<(), sqlx::Error> {
sqlx::query("PRAGMA foreign_keys = ON")
.execute(pool)
.await?;
sqlx::query(
"CREATE TABLE IF NOT EXISTS people (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL UNIQUE,
father_id INTEGER REFERENCES people(id),
mother_id INTEGER REFERENCES people(id)
)",
)
.execute(pool)
.await?;
Ok(())
}
async fn add_person(pool: &SqlitePool, name: &str) -> Result<(), sqlx::Error> {
ensure_person(pool, name).await?;
Ok(())
}
async fn set_father_of(
pool: &SqlitePool,
child_name: &str,
father_name: &str,
) -> Result<(), Box<dyn std::error::Error>> {
let child_id = find_person_id(pool, child_name).await?;
let father_id = find_person_id(pool, father_name).await?;
let (child_id, father_id) = match (child_id, father_id) {
(Some(child_id), Some(father_id)) => (child_id, father_id),
(None, _) => {
return Err(std::io::Error::new(
std::io::ErrorKind::InvalidInput,
format!("Unknown child: {child_name}"),
)
.into());
}
(_, None) => {
return Err(std::io::Error::new(
std::io::ErrorKind::InvalidInput,
format!("Unknown father: {father_name}"),
)
.into());
}
};
sqlx::query(
"UPDATE people
SET father_id = ?
WHERE id = ?",
)
.bind(father_id)
.bind(child_id)
.execute(pool)
.await?;
Ok(())
}
async fn set_mother_of(
pool: &SqlitePool,
child_name: &str,
mother_name: &str,
) -> Result<(), Box<dyn std::error::Error>> {
let child_id = find_person_id(pool, child_name).await?;
let mother_id = find_person_id(pool, mother_name).await?;
let (child_id, mother_id) = match (child_id, mother_id) {
(Some(child_id), Some(mother_id)) => (child_id, mother_id),
(None, _) => {
return Err(std::io::Error::new(
std::io::ErrorKind::InvalidInput,
format!("Unknown child: {child_name}"),
)
.into());
}
(_, None) => {
return Err(std::io::Error::new(
std::io::ErrorKind::InvalidInput,
format!("Unknown mother: {mother_name}"),
)
.into());
}
};
sqlx::query(
"UPDATE people
SET mother_id = ?
WHERE id = ?",
)
.bind(mother_id)
.bind(child_id)
.execute(pool)
.await?;
Ok(())
}
async fn find_person_id(pool: &SqlitePool, name: &str) -> Result<Option<i64>, sqlx::Error> {
let row = sqlx::query("SELECT id FROM people WHERE name = ?")
.bind(name)
.fetch_optional(pool)
.await?;
Ok(row.map(|r| r.get("id")))
}
async fn ensure_person(pool: &SqlitePool, name: &str) -> Result<i64, sqlx::Error> {
sqlx::query(
"INSERT INTO people (name)
VALUES (?)
ON CONFLICT(name) DO NOTHING",
)
.bind(name)
.execute(pool)
.await?;
let row = sqlx::query("SELECT id FROM people WHERE name = ?")
.bind(name)
.fetch_one(pool)
.await?;
let id: i64 = row.get("id");
Ok(id)
}
async fn list_people(pool: &SqlitePool) -> Result<(), sqlx::Error> {
let rows = sqlx::query(
"SELECT
child.name AS name,
father.name AS father,
mother.name AS mother
FROM people AS child
LEFT JOIN people AS father ON father.id = child.father_id
LEFT JOIN people AS mother ON mother.id = child.mother_id
ORDER BY child.name",
)
.fetch_all(pool)
.await?;
println!("name | father | mother");
println!("---- | ------ | ------");
for row in rows {
let name: String = row.get("name");
let father: Option<String> = row.get("father");
let mother: Option<String> = row.get("mother");
println!(
"{} | {} | {}",
name,
father.unwrap_or_else(|| "-".to_string()),
mother.unwrap_or_else(|| "-".to_string()),
);
}
Ok(())
}This example uses sqlx and SQLite to store people and their parents.
Run these commands from examples/sqlx-sqlite-family-tree:
cargo run add Alice
cargo run add Bob
cargo run add Carol
cargo run father-of Alice Bob
cargo run mother-of Alice Carol
cargo run
Expected output:
name | father | mother
----- | ------ | ------
Alice | Bob | Carol
Bob | - | -
Carol | - | -
SQLx SQLite Migration
# sqlx-sqlite-migration
## Overview
Rust CLI that imports student grades from `grades.csv` into SQLite and applies SQLx migrations.
## What This Example Demonstrates
- Reading and validating CSV-like input.
- Converting missing values (`-`) into nullable database fields.
- Running `sqlx::migrate!` from a local `migrations` directory.
- Upserting records with `ON CONFLICT(student) DO UPDATE`.
## Run
- `cargo run`
- Reads `grades.csv`, runs migrations, and loads data into `grades.db`.
## Files
- `grades.csv`: input data source.
- `migrations/`: schema migration files.
- `grades.db`: generated SQLite database file.
## Copilot Usage Hint
If you add new columns to `grades.csv`, update all three places together: `GradeRecord`, CSV parsing logic, and the SQL `INSERT ... ON CONFLICT` statement.
Source code:
use sqlx::{SqlitePool, sqlite::SqliteConnectOptions};
use std::{error::Error, fs, str::FromStr};
#[derive(Debug, Clone)]
struct GradeRecord {
student: String,
math: Option<u32>,
chemistry: Option<u32>,
biology: Option<u32>,
physics: Option<u32>,
literature: Option<u32>,
sport: Option<u32>,
drawing: Option<u32>,
}
fn parse_score(value: &str) -> Result<Option<u32>, Box<dyn Error>> {
let value = value.trim();
if value == "-" || value.is_empty() {
Ok(None)
} else {
Ok(Some(value.parse()?))
}
}
fn read_grades_csv(path: &str) -> Result<Vec<GradeRecord>, Box<dyn Error>> {
let content = fs::read_to_string(path)?;
let mut rows = Vec::new();
for line in content.lines().skip(1) {
if line.trim().is_empty() {
continue;
}
let cols: Vec<&str> = line.split(',').collect();
if cols.len() != 8 {
return Err(format!("Invalid row with {} columns: {line}", cols.len()).into());
}
rows.push(GradeRecord {
student: cols[0].trim().to_string(),
math: parse_score(cols[1])?,
chemistry: parse_score(cols[2])?,
biology: parse_score(cols[3])?,
physics: parse_score(cols[4])?,
literature: parse_score(cols[5])?,
sport: parse_score(cols[6])?,
drawing: parse_score(cols[7])?,
});
}
Ok(rows)
}
#[tokio::main]
async fn main() -> Result<(), Box<dyn Error>> {
let grades = read_grades_csv("grades.csv")?;
let options = SqliteConnectOptions::from_str("sqlite://grades.db")?.create_if_missing(true);
let pool = SqlitePool::connect_with(options).await?;
sqlx::migrate!("./migrations").run(&pool).await?;
for grade in &grades {
sqlx::query(
"
INSERT INTO grades (student, math, chemistry, biology, physics, literature, sport, drawing)
VALUES (?, ?, ?, ?, ?, ?, ?, ?)
ON CONFLICT(student) DO UPDATE SET
math = excluded.math,
chemistry = excluded.chemistry,
biology = excluded.biology,
physics = excluded.physics,
literature = excluded.literature,
sport = excluded.sport,
drawing = excluded.drawing
",
)
.bind(&grade.student)
.bind(grade.math.map(i64::from))
.bind(grade.chemistry.map(i64::from))
.bind(grade.biology.map(i64::from))
.bind(grade.physics.map(i64::from))
.bind(grade.literature.map(i64::from))
.bind(grade.sport.map(i64::from))
.bind(grade.drawing.map(i64::from))
.execute(&pool)
.await?;
}
println!("Loaded {} grade rows into grades.db", grades.len());
Ok(())
}This example uses sqlx to apply migrations from the migrations/ directory and imports student grades from a CSV file into SQLite.
Run this command from examples/sqlx-sqlite-migration:
cargo run
The program will:
- Read
grades.csv - Apply any pending migrations from
migrations/ - Insert or update all grade records into
grades.db
Expected output:
Loaded 5 grade rows into grades.db
The resulting database can be inspected with SQLite tools, and the data is upserted so running the command again is safe.
SQLx SQLite Todo
# sqlx-sqlite-todo
## Overview
Rust CLI todo manager backed by SQLite using `sqlx` and `clap`.
Using subcommands and environment-based database configuration.
## What This Example Demonstrates
- Subcommand-based CLI (`add`, `done`, and default list).
- Automatic table initialization at startup.
- Insert, update, and query patterns with SQLx.
- Reading required configuration from environment variables.
## Run
- `DATABASE_FILE=todos.db cargo run`
- Lists all todos.
- `DATABASE_FILE=todos.db cargo run -- add "buy milk"`
- Adds a new todo item.
- `DATABASE_FILE=todos.db cargo run -- done 1`
- Marks todo `1` as done.
## Configuration
- Required env var: `DATABASE_FILE`
- Example value: `todos.db` or `/tmp/my-todos.db`
## Copilot Usage Hint
Keep the command behavior centered in the `Command` enum and `match args.cmd` so new subcommands remain discoverable and testable.
Source code:
use anyhow::Context;
use clap::{Parser, Subcommand};
use sqlx::{
Row,
sqlite::{SqliteConnectOptions, SqlitePool},
};
use std::env;
#[derive(Parser)]
struct Args {
#[command(subcommand)]
cmd: Option<Command>,
}
#[derive(Subcommand)]
enum Command {
Add { description: String },
Done { id: i64 },
}
#[tokio::main(flavor = "current_thread")]
async fn main() -> anyhow::Result<()> {
let args = Args::parse();
let database_file = env::var("DATABASE_FILE")
.context("DATABASE_FILE is not set. Set it to the SQLite database file path, for example: DATABASE_FILE=todos.db")?;
run(args, database_file).await
}
async fn run(args: Args, database_file: String) -> anyhow::Result<()> {
let options = SqliteConnectOptions::new()
.filename(database_file)
.create_if_missing(true);
let pool = SqlitePool::connect_with(options).await?;
initialize_database(&pool).await?;
match args.cmd {
Some(Command::Add { description }) => {
println!("Adding new todo with description '{description}'");
let todo_id = add_todo(&pool, description).await?;
println!("Added new todo with id {todo_id}");
}
Some(Command::Done { id }) => {
println!("Marking todo {id} as done");
if complete_todo(&pool, id).await? {
println!("Todo {id} is marked as done");
} else {
println!("Invalid id {id}");
}
}
None => {
println!("Printing list of all todos");
list_todos(&pool).await?;
}
}
Ok(())
}
async fn initialize_database(pool: &SqlitePool) -> anyhow::Result<()> {
sqlx::query(
r#"
CREATE TABLE IF NOT EXISTS todos (
id INTEGER PRIMARY KEY AUTOINCREMENT,
description TEXT NOT NULL,
done BOOLEAN NOT NULL DEFAULT FALSE
)
"#,
)
.execute(pool)
.await?;
Ok(())
}
async fn add_todo(pool: &SqlitePool, description: String) -> anyhow::Result<i64> {
let mut conn = pool.acquire().await?;
// Insert the task, then obtain the ID of this row
let id = sqlx::query(
r#"
INSERT INTO todos ( description )
VALUES ( ?1 )
"#,
)
.bind(description)
.execute(&mut *conn)
.await?
.last_insert_rowid();
Ok(id)
}
async fn complete_todo(pool: &SqlitePool, id: i64) -> anyhow::Result<bool> {
let rows_affected = sqlx::query(
r#"
UPDATE todos
SET done = TRUE
WHERE id = ?1
"#,
)
.bind(id)
.execute(pool)
.await?
.rows_affected();
Ok(rows_affected > 0)
}
async fn list_todos(pool: &SqlitePool) -> anyhow::Result<()> {
let recs = sqlx::query(
r#"
SELECT id, description, done
FROM todos
ORDER BY id
"#,
)
.fetch_all(pool)
.await?;
for rec in recs {
let id: i64 = rec.try_get("id")?;
let description: String = rec.try_get("description")?;
let done: bool = rec.try_get("done")?;
println!(
"- [{}] {}: {}",
if done { "x" } else { " " },
id,
description,
);
}
Ok(())
}
#[cfg(test)]
mod tests;This example uses sqlx and SQLite to store a simple todo list.
Run these commands from examples/sqlx-sqlite-todo:
DATABASE_FILE=todos.db cargo run
DATABASE_FILE=todos.db cargo run -- add "buy milk"
DATABASE_FILE=todos.db cargo run -- add "walk the dog"
DATABASE_FILE=todos.db cargo run -- done 1
DATABASE_FILE=todos.db cargo run
Expected output:
Printing list of all todos
- [ ] 1: buy milk
- [x] 2: walk the dog
The DATABASE_FILE environment variable is required and specifies where the SQLite database file is stored. The application automatically creates the database and tables on first run.
SQLite original todos example
# TODOs Example
Based on the one in the [source code](https://github.com/launchbadge/sqlx/tree/main/examples/sqlite/todos)
## Setup
0. Install `sqlx-cli`.
1. Declare the database URL
```
export DATABASE_URL="sqlite:todos.db"
```
2. Create the database.
```
$ sqlx db create
```
3. Run sql migrations
```
$ sqlx migrate run
```
## Usage
Add a todo
```
cargo run -- add "todo description"
```
Complete a todo.
```
cargo run -- done <todo id>
```
List all todos
```
cargo run
```
[package]
name = "todos"
version = "0.1.0"
edition = "2018"
[dependencies]
anyhow = "1.0.58"
clap = { version = "4.4.7", features = ["derive"] }
sqlx = { version = "0.8.6", features = ["sqlite", "runtime-tokio", "tls-native-tls"] }
tokio = { version = "1.25.0", features = ["rt", "macros"]}
use clap::{Parser, Subcommand};
use sqlx::sqlite::SqlitePool;
use std::env;
#[derive(Parser)]
struct Args {
#[command(subcommand)]
cmd: Option<Command>,
}
#[derive(Subcommand)]
enum Command {
Add { description: String },
Done { id: i64 },
}
#[tokio::main(flavor = "current_thread")]
async fn main() -> anyhow::Result<()> {
let args = Args::parse();
let pool = SqlitePool::connect(&env::var("DATABASE_URL")?).await?;
match args.cmd {
Some(Command::Add { description }) => {
println!("Adding new todo with description '{description}'");
let todo_id = add_todo(&pool, description).await?;
println!("Added new todo with id {todo_id}");
}
Some(Command::Done { id }) => {
println!("Marking todo {id} as done");
if complete_todo(&pool, id).await? {
println!("Todo {id} is marked as done");
} else {
println!("Invalid id {id}");
}
}
None => {
println!("Printing list of all todos");
list_todos(&pool).await?;
}
}
Ok(())
}
async fn add_todo(pool: &SqlitePool, description: String) -> anyhow::Result<i64> {
let mut conn = pool.acquire().await?;
// Insert the task, then obtain the ID of this row
let id = sqlx::query!(
r#"
INSERT INTO todos ( description )
VALUES ( ?1 )
"#,
description
)
.execute(&mut *conn)
.await?
.last_insert_rowid();
Ok(id)
}
async fn complete_todo(pool: &SqlitePool, id: i64) -> anyhow::Result<bool> {
let rows_affected = sqlx::query!(
r#"
UPDATE todos
SET done = TRUE
WHERE id = ?1
"#,
id
)
.execute(pool)
.await?
.rows_affected();
Ok(rows_affected > 0)
}
async fn list_todos(pool: &SqlitePool) -> anyhow::Result<()> {
let recs = sqlx::query!(
r#"
SELECT id, description, done
FROM todos
ORDER BY id
"#
)
.fetch_all(pool)
.await?;
for rec in recs {
println!(
"- [{}] {}: {}",
if rec.done { "x" } else { " " },
rec.id,
&rec.description,
);
}
Ok(())
}CREATE TABLE IF NOT EXISTS todos
(
id INTEGER PRIMARY KEY NOT NULL,
description TEXT NOT NULL,
done BOOLEAN NOT NULL DEFAULT 0
);
SQLite snapshot and restore
# Backup and restore
Based on the [sqlite serialize](https://github.com/launchbadge/sqlx/tree/main/examples/sqlite/serialize) example.
Show how we can serialized (and save to disk) the content of the database
and then how we can restore the data from the serialized dump.
## Dependencies
Apparently this feature is not in the release version yet.
```
cargo add --git https://github.com/launchbadge/sqlx sqlx -F sqlite-deserialize -F sqlite -F runtime-tokio
```
[package]
name = "xserialize"
version = "0.1.0"
edition = "2024"
[dependencies]
anyhow = "1.0.58"
sqlx = { git = "https://github.com/launchbadge/sqlx", version = "0.9.0-alpha.1", features = ["runtime-tokio", "sqlite", "sqlite-deserialize"] }
#sqlx = { version = "0.8.6", features = ["sqlite", "sqlite-deserialize", "runtime-tokio"] }
tokio = { version = "1.25.0", features = ["rt", "macros"] }
use sqlx::sqlite::SqliteOwnedBuf;
use sqlx::{Connection, SqliteConnection};
#[tokio::main(flavor = "current_thread")]
async fn main() -> anyhow::Result<()> {
let command = get_command();
if command == "create" {
create().await?;
} else if command == "restore" {
restore().await?;
}
Ok(())
}
async fn create() -> anyhow::Result<()> {
let mut conn = SqliteConnection::connect("sqlite::memory:").await?;
sqlx::raw_sql(
"CREATE TABLE notes(id INTEGER PRIMARY KEY, body TEXT NOT NULL);
INSERT INTO notes(body) VALUES ('hello'), ('world');",
)
.execute(&mut conn)
.await?;
let snapshot: SqliteOwnedBuf = conn.serialize(None).await?;
let bytes: &[u8] = snapshot.as_ref();
std::fs::write("snapshot.db", bytes)?;
conn.close().await?;
Ok(())
}
async fn restore() -> anyhow::Result<()> {
let bytes = std::fs::read("snapshot.db")?;
let owned = SqliteOwnedBuf::try_from(bytes.as_slice())?;
let mut restored = SqliteConnection::connect("sqlite::memory:").await?;
restored.deserialize(None, owned, false).await?;
let rows = sqlx::query_as::<_, (i64, String)>("select id, body from notes order by id")
.fetch_all(&mut restored)
.await?;
assert_eq!(rows.len(), 2);
assert_eq!(rows[0].1, "hello");
assert_eq!(rows[1].1, "world");
Ok(())
}
fn get_command() -> String {
let args = std::env::args().collect::<Vec<_>>();
if args.len() != 2 {
eprintln!("Usage: {} [create|restore]", args[0]);
std::process::exit(1);
}
let command = &args[1];
if command != "create" && command != "restore" {
eprintln!("Invalid command: {}", command);
eprintln!("Usage: {} [create|restore]", args[0]);
std::process::exit(1);
}
command.to_string()
}SQLite bank transaction
[package]
name = "sqlx-sqlite-bank"
version = "0.1.0"
edition = "2024"
[dependencies]
sqlx = { version = "0.8.6", features = ["sqlite", "runtime-tokio-rustls", "migrate"] }
tokio = { version = "1.48.0", features = ["macros", "rt-multi-thread"] }
CREATE TABLE IF NOT EXISTS accounts (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL UNIQUE,
amount INTEGER NOT NULL CHECK (amount >= 0)
);
use sqlx::{
Executor, Row, SqlitePool,
migrate::Migrator,
sqlite::{SqliteConnectOptions, SqlitePoolOptions},
};
use std::str::FromStr;
static MIGRATOR: Migrator = sqlx::migrate!();
const DATABASE_URL: &str = "sqlite://bank.db";
const USAGE: &str = "Usage: cargo run -- list | cargo run -- add NAME AMOUNT | cargo run -- transfer AMOUNT FROM_NAME TO_NAME";
#[tokio::main]
async fn main() {
let args: Vec<String> = std::env::args().skip(1).collect();
let database_url = std::env::var("DATABASE_URL").unwrap_or_else(|_| DATABASE_URL.to_string());
if let Err(err) = run(args, &database_url).await {
eprintln!("{err}");
std::process::exit(1);
}
}
async fn run(args: Vec<String>, database_url: &str) -> Result<(), Box<dyn std::error::Error>> {
let options = SqliteConnectOptions::from_str(database_url)?.create_if_missing(true);
let pool = SqlitePoolOptions::new().connect_with(options).await?;
initialize_database(&pool).await?;
match args.as_slice() {
[cmd] if cmd == "list" => list_accounts(&pool).await?,
[cmd, name, amount] if cmd == "add" => {
add_account(&pool, name, parse_non_negative_amount(amount)?).await?;
}
[cmd, amount, from_name, to_name] if cmd == "transfer" => {
transfer(
&pool,
parse_positive_amount(amount)?,
from_name,
to_name,
should_panic(),
)
.await?;
}
_ => {
return Err(std::io::Error::new(std::io::ErrorKind::InvalidInput, USAGE).into());
}
}
Ok(())
}
async fn initialize_database(pool: &SqlitePool) -> Result<(), sqlx::Error> {
pool.execute("PRAGMA foreign_keys = ON").await?;
MIGRATOR.run(pool).await?;
Ok(())
}
async fn add_account(pool: &SqlitePool, name: &str, amount: i64) -> Result<(), sqlx::Error> {
sqlx::query(
"INSERT INTO accounts (name, amount)
VALUES (?, ?)",
)
.bind(name)
.bind(amount)
.execute(pool)
.await?;
Ok(())
}
async fn list_accounts(pool: &SqlitePool) -> Result<(), sqlx::Error> {
let rows = sqlx::query(
"SELECT name, amount
FROM accounts
ORDER BY name",
)
.fetch_all(pool)
.await?;
println!("name | amount");
println!("---- | ------");
for row in rows {
let name: String = row.get("name");
let amount: i64 = row.get("amount");
println!("{name} | {amount}");
}
Ok(())
}
async fn transfer(
pool: &SqlitePool,
amount: i64,
from_name: &str,
to_name: &str,
panic_in_middle: bool,
) -> Result<(), Box<dyn std::error::Error>> {
if from_name == to_name {
return Err(std::io::Error::new(
std::io::ErrorKind::InvalidInput,
"Source and destination accounts must be different",
)
.into());
}
let mut tx = pool.begin().await?;
let from_balance = find_amount(&mut *tx, from_name).await?.ok_or_else(|| {
std::io::Error::new(
std::io::ErrorKind::InvalidInput,
format!("Unknown account: {from_name}"),
)
})?;
if find_amount(&mut *tx, to_name).await?.is_none() {
return Err(std::io::Error::new(
std::io::ErrorKind::InvalidInput,
format!("Unknown account: {to_name}"),
)
.into());
}
if from_balance < amount {
return Err(std::io::Error::new(
std::io::ErrorKind::InvalidInput,
format!("Insufficient funds in {from_name}: has {from_balance}, needs {amount}"),
)
.into());
}
sqlx::query(
"UPDATE accounts
SET amount = amount - ?
WHERE name = ?",
)
.bind(amount)
.bind(from_name)
.execute(&mut *tx)
.await?;
if panic_in_middle {
panic!("simulated crash in the middle of transfer");
}
sqlx::query(
"UPDATE accounts
SET amount = amount + ?
WHERE name = ?",
)
.bind(amount)
.bind(to_name)
.execute(&mut *tx)
.await?;
tx.commit().await?;
Ok(())
}
async fn find_amount<'a, E>(executor: E, name: &str) -> Result<Option<i64>, sqlx::Error>
where
E: Executor<'a, Database = sqlx::Sqlite>,
{
let row = sqlx::query("SELECT amount FROM accounts WHERE name = ?")
.bind(name)
.fetch_optional(executor)
.await?;
Ok(row.map(|record| record.get("amount")))
}
fn parse_non_negative_amount(value: &str) -> Result<i64, Box<dyn std::error::Error>> {
let amount = value.parse::<i64>().map_err(|_| {
std::io::Error::new(
std::io::ErrorKind::InvalidInput,
format!("Amount must be a non-negative integer: {value}"),
)
})?;
if amount < 0 {
return Err(std::io::Error::new(
std::io::ErrorKind::InvalidInput,
format!("Amount must be a non-negative integer: {value}"),
)
.into());
}
Ok(amount)
}
fn parse_positive_amount(value: &str) -> Result<i64, Box<dyn std::error::Error>> {
let amount = value.parse::<i64>().map_err(|_| {
std::io::Error::new(
std::io::ErrorKind::InvalidInput,
format!("Amount must be a positive integer: {value}"),
)
})?;
if amount <= 0 {
return Err(std::io::Error::new(
std::io::ErrorKind::InvalidInput,
format!("Amount must be a positive integer: {value}"),
)
.into());
}
Ok(amount)
}
fn should_panic() -> bool {
matches!(std::env::var("PANIC").as_deref(), Ok("true"))
}#![allow(unused)]
fn main() {
use std::path::PathBuf;
use std::process::Command;
use std::time::{SystemTime, UNIX_EPOCH};
fn binary_path() -> PathBuf {
PathBuf::from(env!("CARGO_BIN_EXE_sqlx-sqlite-bank"))
}
fn unique_database_url() -> String {
let nanos = SystemTime::now()
.duration_since(UNIX_EPOCH)
.expect("system clock before unix epoch")
.as_nanos();
let db_path = std::env::temp_dir().join(format!(
"sqlx-sqlite-bank-cli-test-{}-{}.db",
std::process::id(),
nanos
));
format!("sqlite://{}", db_path.display())
}
#[test]
fn add_list_and_transfer_money() {
let database_url = unique_database_url();
let add_alice = Command::new(binary_path())
.args(["add", "Alice", "100"])
.env("DATABASE_URL", &database_url)
.output()
.expect("failed to add Alice");
assert!(add_alice.status.success());
assert_eq!(String::from_utf8_lossy(&add_alice.stdout), "");
assert_eq!(String::from_utf8_lossy(&add_alice.stderr), "");
let add_bob = Command::new(binary_path())
.args(["add", "Bob", "40"])
.env("DATABASE_URL", &database_url)
.output()
.expect("failed to add Bob");
assert!(add_bob.status.success());
assert_eq!(String::from_utf8_lossy(&add_bob.stdout), "");
assert_eq!(String::from_utf8_lossy(&add_bob.stderr), "");
let transfer = Command::new(binary_path())
.args(["transfer", "25", "Alice", "Bob"])
.env("DATABASE_URL", &database_url)
.output()
.expect("failed to transfer money");
assert!(transfer.status.success());
assert_eq!(String::from_utf8_lossy(&transfer.stdout), "");
assert_eq!(String::from_utf8_lossy(&transfer.stderr), "");
let list = Command::new(binary_path())
.arg("list")
.env("DATABASE_URL", &database_url)
.output()
.expect("failed to list accounts");
assert!(list.status.success());
assert_eq!(
String::from_utf8_lossy(&list.stdout),
"name | amount\n---- | ------\nAlice | 75\nBob | 65\n"
);
assert_eq!(String::from_utf8_lossy(&list.stderr), "");
}
#[test]
fn panic_during_transfer_rolls_back_transaction() {
let database_url = unique_database_url();
for args in [["add", "Alice", "100"], ["add", "Bob", "40"]] {
let output = Command::new(binary_path())
.args(args)
.env("DATABASE_URL", &database_url)
.output()
.expect("failed to seed accounts");
assert!(output.status.success());
}
let transfer = Command::new(binary_path())
.args(["transfer", "25", "Alice", "Bob"])
.env("DATABASE_URL", &database_url)
.env("PANIC", "true")
.output()
.expect("failed to run crashing transfer");
assert!(!transfer.status.success());
assert_eq!(String::from_utf8_lossy(&transfer.stdout), "");
assert!(
String::from_utf8_lossy(&transfer.stderr)
.contains("simulated crash in the middle of transfer")
);
let list = Command::new(binary_path())
.arg("list")
.env("DATABASE_URL", &database_url)
.output()
.expect("failed to list accounts after crash");
assert!(list.status.success());
assert_eq!(
String::from_utf8_lossy(&list.stdout),
"name | amount\n---- | ------\nAlice | 100\nBob | 40\n"
);
}
#[test]
fn invalid_arguments_print_usage() {
let database_url = unique_database_url();
let invalid = Command::new(binary_path())
.args(["hello", "world"])
.env("DATABASE_URL", &database_url)
.output()
.expect("failed to run invalid command");
assert!(!invalid.status.success());
assert_eq!(String::from_utf8_lossy(&invalid.stdout), "");
assert_eq!(
String::from_utf8_lossy(&invalid.stderr),
"Usage: cargo run -- list | cargo run -- add NAME AMOUNT | cargo run -- transfer AMOUNT FROM_NAME TO_NAME\n"
);
}
#[test]
fn transfer_requires_enough_money() {
let database_url = unique_database_url();
for args in [["add", "Alice", "10"], ["add", "Bob", "40"]] {
let output = Command::new(binary_path())
.args(args)
.env("DATABASE_URL", &database_url)
.output()
.expect("failed to seed accounts");
assert!(output.status.success());
}
let transfer = Command::new(binary_path())
.args(["transfer", "25", "Alice", "Bob"])
.env("DATABASE_URL", &database_url)
.output()
.expect("failed to run transfer");
assert!(!transfer.status.success());
assert_eq!(String::from_utf8_lossy(&transfer.stdout), "");
assert_eq!(
String::from_utf8_lossy(&transfer.stderr),
"Insufficient funds in Alice: has 10, needs 25\n"
);
}
}Postgres
Start the Postgres server in a Docker container
$ docker run -d -e POSTGRES_PASSWORD=password -p 5432:5432 --name postgres postgres:latest
At the end shut it down:
$ docker container stop postgers
Postgres original todos example
# TODOs Example
## Setup
1. Declare the database URL
```
export DATABASE_URL="postgres://postgres:password@localhost/todos"
```
2. Create the database.
```
$ sqlx db create
```
3. Run sql migrations
```
$ sqlx migrate run
```
## Usage
Add a todo
```
cargo run -- add "todo description"
```
Complete a todo.
```
cargo run -- done <todo id>
```
List all todos
```
cargo run
```
[package]
name = "todos"
version = "0.1.0"
edition = "2018"
[dependencies]
anyhow = "1.0.58"
sqlx = { version = "0.8.6", features = ["postgres", "runtime-tokio", "tls-native-tls"] }
clap = { version = "4.4.7", features = ["derive"] }
tokio = { version = "1.25.0", features = ["rt", "macros"]}
dotenvy = "0.15.7"
use clap::{Parser, Subcommand};
use sqlx::postgres::PgPool;
use std::env;
#[derive(Parser)]
struct Args {
#[command(subcommand)]
cmd: Option<Command>,
}
#[derive(Subcommand)]
enum Command {
Add { description: String },
Done { id: i64 },
}
#[tokio::main(flavor = "current_thread")]
async fn main() -> anyhow::Result<()> {
let args = Args::parse();
let pool = PgPool::connect(&env::var("DATABASE_URL")?).await?;
match args.cmd {
Some(Command::Add { description }) => {
println!("Adding new todo with description '{description}'");
let todo_id = add_todo(&pool, description).await?;
println!("Added new todo with id {todo_id}");
}
Some(Command::Done { id }) => {
println!("Marking todo {id} as done");
if complete_todo(&pool, id).await? {
println!("Todo {id} is marked as done");
} else {
println!("Invalid id {id}");
}
}
None => {
println!("Printing list of all todos");
list_todos(&pool).await?;
}
}
Ok(())
}
async fn add_todo(pool: &PgPool, description: String) -> anyhow::Result<i64> {
let rec = sqlx::query!(
r#"
INSERT INTO todos ( description )
VALUES ( $1 )
RETURNING id
"#,
description
)
.fetch_one(pool)
.await?;
Ok(rec.id)
}
async fn complete_todo(pool: &PgPool, id: i64) -> anyhow::Result<bool> {
let rows_affected = sqlx::query!(
r#"
UPDATE todos
SET done = TRUE
WHERE id = $1
"#,
id
)
.execute(pool)
.await?
.rows_affected();
Ok(rows_affected > 0)
}
async fn list_todos(pool: &PgPool) -> anyhow::Result<()> {
let recs = sqlx::query!(
r#"
SELECT id, description, done
FROM todos
ORDER BY id
"#
)
.fetch_all(pool)
.await?;
for rec in recs {
println!(
"- [{}] {}: {}",
if rec.done { "x" } else { " " },
rec.id,
&rec.description,
);
}
Ok(())
}CREATE TABLE IF NOT EXISTS todos
(
id BIGSERIAL PRIMARY KEY,
description TEXT NOT NULL,
done BOOLEAN NOT NULL DEFAULT FALSE
);