Persistence starts before the first INSERT. The schema needs an ordered history, application code needs a boundary that does not spread SQL everywhere, and a failed multi-statement write must leave a state readers can trust. This example joins those concerns with two migrations, a repository trait, an explicit transaction, and a fresh database fixture for every test.
It targets Rust 2024 with rustc and Cargo 1.98.1, SQLx 0.8.6, and Tokio 1.53.1. Direct dependencies use exact versions, and SQLx supplies the bundled SQLite driver.
1. Run migrations at the application boundary
sqlx::migrate!() embeds the project-root migrations directory in the binary. Migrator::run tracks applied versions and checksums in _sqlx_migrations. Running the same migration set again skips entries that are already present. The fixture calls migrate twice on one pool and asserts that exactly two versions were applied.
A deployed migration is history, not an initialization script to keep editing. Add a later numbered file instead. If the bytes of an applied migration change, SQLx reports the checksum mismatch rather than silently accepting schema drift.
CREATE TABLE checks (
id INTEGER PRIMARY KEY AUTOINCREMENT,
endpoint_id TEXT NOT NULL REFERENCES endpoints(id) ON DELETE CASCADE,
status INTEGER NOT NULL CHECK (status BETWEEN 100 AND 599),
checked_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX checks_endpoint_id_idx ON checks(endpoint_id);checks.endpoint_id depends on endpoints.id, which the first migration creates. The numeric order therefore carries a real schema dependency. The example does not generate down migrations. Forward changes should be reviewed alongside a separate backup and restore policy.
2. Pin versions and features in Cargo.toml
SQLx changes shape with its runtime, driver, migration, and macro features. The fixture declares only the pieces it uses.
[dependencies]
async-trait = "=0.1.89"
sqlx = { version = "=0.8.6", default-features = false, features = ["runtime-tokio", "sqlite", "migrate", "macros"] }
tokio = { version = "=1.53.1", features = ["macros", "rt-multi-thread"] }
[dev-dependencies]
tempfile = "=3.23.0"The fixture also keeps Cargo.lock. Exact direct versions plus the lockfile make the displayed commands resolve to the dependency graph that was tested.
3. Keep SQLx behind a repository boundary
EndpointRepository names the operations the application needs. The adapter owns SqlitePool, SQL placeholders, and row mapping. Callers do not need to know the SQLite schema to save or load an endpoint.
#[async_trait]
pub trait EndpointRepository {
async fn insert(&self, endpoint: &Endpoint) -> Result<(), sqlx::Error>;
async fn find_by_id(&self, id: &str) -> Result<Option<Endpoint>, sqlx::Error>;
async fn record_check(&self, id: &str, status: i64) -> Result<(), RepoError>;
}
#[derive(Clone)]
pub struct SqliteEndpointRepository {
pool: SqlitePool,
}A trait alone does not erase every database detail. In this small fixture, insert and find_by_id still return sqlx::Error. The transactional operation uses RepoError to distinguish a missing endpoint from a database failure. A service can extend that mapping for application-relevant cases such as unique conflicts while retaining the source error for unexpected failures.
4. Put writes that must agree in one transaction
Recording a check updates the endpoint's latest status and appends a history row. Leaving only one change behind would make the read model contradict its history. record_check starts one transaction, executes both statements through that transaction, and commits only at the end.
async fn record_check(&self, id: &str, status: i64) -> Result<(), RepoError> {
let mut transaction = self.pool.begin().await?;
let updated = sqlx::query("UPDATE endpoints SET last_status = ? WHERE id = ?")
.bind(status)
.bind(id)
.execute(&mut *transaction)
.await?;
if updated.rows_affected() != 1 {
return Err(RepoError::NotFound);
}
sqlx::query("INSERT INTO checks (endpoint_id, status) VALUES (?, ?)")
.bind(id)
.bind(status)
.execute(&mut *transaction)
.await?;
transaction.commit().await?;
Ok(())
}SQLx starts rollback when an in-progress transaction leaves scope without commit or rollback. The regression test passes status 700. The first UPDATE succeeds, but the second statement violates the history table's CHECK constraint. The test then finds last_status = NULL and zero history rows, proving that the earlier update did not leak through.
SQLite BEGIN transactions do not nest. Code that needs nested units of work should examine savepoint semantics instead. The default transaction mode is also deferred, so a production design with competing writers still needs an explicit policy for SQLITE_BUSY, busy timeouts, and retry ownership.
5. Give an in-memory fixture exactly one connection
Each plain SQLite :memory: connection owns a different database. A pool with several connections can therefore run migrations on one database and route a later query to another. The in-memory fixture prevents that split by setting max_connections(1).
pub async fn memory_pool() -> Result<SqlitePool, sqlx::Error> {
let options = SqliteConnectOptions::new()
.filename(":memory:")
.foreign_keys(true);
SqlitePoolOptions::new()
.max_connections(1)
.connect_with(options)
.await
}SQLite supports shared in-memory URI names, but a private single-connection database is easier to reason about for isolated tests. Every MemoryFixture::new call opens a new pool and runs migrations. One test inserts into the first fixture and confirms that the second cannot see the row.
6. Use a file fixture to check paths and cleanup
Memory-only tests do not exercise file creation or deletion. TempFileFixture creates fixture.sqlite inside a new temporary directory and explicitly requests create_if_missing(true) and foreign_keys(true).
let directory = tempfile::tempdir().expect("create temporary database directory");
let database_path = directory.path().join("fixture.sqlite");
let options = SqliteConnectOptions::new()
.filename(&database_path)
.create_if_missing(true)
.foreign_keys(true);
let pool = SqlitePoolOptions::new()
.max_connections(1)
.connect_with(options)
.await
.expect("open temporary SQLite database");
migrate(&pool)
.await
.expect("migrate temporary SQLite database");SQLite foreign-key enforcement is a per-connection setting. SQLx enables it by default, but the fixture states the requirement and checks that PRAGMA foreign_keys returns 1. Its cleanup test confirms that the database file exists, awaits pool.close(), and then checks that the temporary directory is gone. The ordering also works on platforms that will not delete an open database file.
7. Reproduce the commands and output
Run the following from the repository root.
cd examples/article-29-sqlx-sqlite-migrations-repository
cargo fmt --all -- --check
cargo check --all-targets --all-features
cargo clippy --all-targets --all-features -- -D warnings
cargo test --all-features
cargo test --release --all-features
cargo run --quietThe verified binary output is:
migrations=2
endpoint=alpha status=204 checks=1
rolled_back=trueSeven tests cover repeatable migrations, a round trip through the trait boundary, commit, rollback after a constraint failure, separate in-memory fixtures, foreign-key configuration and temporary-file cleanup, and exact binary output. They require neither an external database server nor a shared database file.
Do not copy the in-memory fixture's one-connection limit into production without measuring the real workload. A file-backed service still needs decisions about connection count, WAL, busy timeout, and write contention. It also needs one owner for migration execution, either application startup or a deployment job. The repository boundary separates those SQL decisions from application code; it does not make them disappear.
Full source code
The complete runnable source for this article is available in the Chapter 29 project on GitHub.
Leave a Reply