SQLx is an asynchronous SQL toolkit for connecting a Rust HTTP API to a relational database. For a SQLite-backed service, enable its Tokio runtime, SQLite, chrono, and macros features: `cargo add sqlx --features runtime-tokio-rustls,sqlite,chrono,macros`. section 9.4
Initialize a `SqlitePool` from `DATABASE_URL`, defaulting to `sqlite:db.sqlite` if the variable is unset. `SqliteConnectOptions::create_if_missing(true)` lets the application create the database file; `sqlx::migrate!().run(&dbpool).await` then applies migrations. Check the target database before running either operation: database creation and migrations change persistent state, and migration mistakes have no automatic undo. section 9.5.3
Read a row into a Rust type
Derive `sqlx::FromRow` on a result struct, then query through the pool: `query_as("select * from todos where id = ?").bind(id).fetch_one(&dbpool).await`. `fetch_all` serves list queries; `fetch_one` serves a single-record lookup. Convert a returned `sqlx::Error` into an application error with `.map_err(Into::into)`. section 9.6.2
Use `.bind()` for values in SQL statements, including inserts and updates. With SQLite’s positional placeholders, binding order must match placeholder order. Pass a pool handle—or a connection—to each database operation. The optional `sqlx-cli` can create and drop databases and run migrations; it is not required to run the service. section 9.6.2 section 9.4
Listing 9.4 API service init_dbpool() function in src/main.rs
async fn init_dbpool() -> Result<sqlx::Pool<sqlx::Sqlite>, sqlx::Error> {
use sqlx::sqlite::{SqliteConnectOptions, SqlitePoolOptions};
use std::str::FromStr;
let db_connection_str =
std::env::var("DATABASE_URL")
.unwrap_or_else(|_| "sqlite:db.sqlite".to_string()); #1
let dbpool = SqlitePoolOptions::new()
.connect_with(SqliteConnectOptions::from_str(&db_connection_str)?
.create_if_missing(true)) #2
.await
.expect("can’t connect to database");
sqlx::migrate!() #3
.run(&dbpool) #4
.await
.expect("database migration failed");
Ok(dbpool)
}
The pool initializer shows the connection-string fallback, database-file creation option, and migration call together. section 9.5.3
Listing 9.7 API service Todo struct impl read block from src/todo.rs
impl Todo {
pub async fn list(dbpool: SqlitePool) -> Result<Vec<Todo>, Error> {
query_as("select * from todos") #1
.fetch_all(&dbpool)
.await
.map_err(Into::into)
}
pub async fn read(dbpool: SqlitePool, id: i64) -> Result<Todo, Error> {
query_as("select * from todos where id = ?") #2
.bind(id)
.fetch_one(&dbpool)
.await
.map_err(Into::into)
}
}
The read methods show how `query_as`, positional binding, fetch methods, and error conversion fit into asynchronous model methods. section 9.6.2
