SQLite supplies the database layer for a Rust HTTP API, storing service state in a local database while running within the application process. The architecture can add API instances horizontally, with each instance independently accessing the database. section 9.2 section 9.5.3
Use SQLite with a small, explicit initialization workflow:
- Read the connection string from DATABASE_URL, defaulting to sqlite:db.sqlite. section 9.5.3
- Create a SQLx connection pool; pooling is convenient but less essential than with network-connected databases. section 9.5.3
- Enable create_if_missing(true) when appropriate, then apply correct and idempotent migrations. section 9.5.3
- Choose one database driver deliberately because SQL dialects and driver behavior differ despite shared SQL foundations. section 9.5.3
Todo storage
A todos migration defines an auto-incrementing integer primary key, required body and completion fields, timestamp defaults, and an updated_at value maintained by Rust code. SQLx applies the migration when the database is initialized. section 9.6.1
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 initialization listing connects environment configuration, SQLite file creation, SQLx pooling, and migration execution in one Rust function. section 9.5.3
