The two ways database tests go wrong
You've written some code that talks to a database. A function that inserts a row, another that reads it back, maybe a third that runs a query with a join. You want to test it. The first instinct is to point your tests at your real Postgres, run the test suite, and see what happens.
Two things tend to go wrong.
First, the tests pass on your machine and fail in CI, or vice versa, because the database state isn't the same. One run leaves data behind that the next run trips over. Tests stop being deterministic, which means they stop being trustworthy.
Second, you start avoiding the tests. They're slow. They mess up your local dev database. Eventually you stop running them, and three weeks later something breaks in production that the tests would have caught if they'd been runnable.
The whole challenge of testing database code is making it fast, isolated, and not painful. Rust gives you the tools to do this well, but you have to wire them up. There's no single magic answer; there are a few patterns and you pick based on what you're testing.
The cheapest layer: pure logic
Before we touch a database, notice how much of your "database code" probably isn't database code. The function that builds a SQL string, the function that maps a result row into a struct, the function that decides which query to run based on a config: those don't need a database. They need plain unit tests.
// Build a WHERE clause from filter options. No DB involved; pure string work.
fn build_filter(name: Option<&str>, min_age: Option<u32>) -> String {
let mut parts = Vec::new();
if let Some(n) = name { parts.push(format!("name = '{}'", n)); }
if let Some(a) = min_age { parts.push(format!("age >= {}", a)); }
if parts.is_empty() { String::new() }
else { format!("WHERE {}", parts.join(" AND ")) }
}
#[cfg(test)]
mod tests {
use super::*;
#[test]
fn empty_filter_yields_empty_string() {
// No filters -> no WHERE clause at all.
assert_eq!(build_filter(None, None), "");
}
#[test]
fn name_and_age_combine_with_and() {
// Both filters -> AND-joined predicates.
assert_eq!(
build_filter(Some("alice"), Some(30)),
"WHERE name = 'alice' AND age >= 30"
);
}
}
These tests run in microseconds, never touch the network, and are completely deterministic. Push as much of your logic into pure functions as you can. The remaining code, the bit that actually executes SQL, will be smaller and easier to test.
Testing against a real database with rollback
For the SQL itself, you need a real database. Mocks lie. A mock that pretends to be Postgres will happily return what your code expects and then production will break because Postgres did something subtly different (date formatting, null handling, lock behaviour, take your pick).
The trick is transactional isolation. Each test starts a transaction, does its work, and rolls back at the end. The database is back to its starting state automatically. No cleanup logic, no test bleed.
use sqlx::{PgPool, Postgres, Transaction};
// Application code: takes a transaction (or executor) so it can be tested cleanly.
async fn insert_user(
tx: &mut Transaction<'_, Postgres>,
name: &str,
) -> sqlx::Result<i32> {
// Returning the new id makes the function easy to assert against.
let row: (i32,) = sqlx::query_as("INSERT INTO users (name) VALUES ($1) RETURNING id")
.bind(name)
.fetch_one(&mut **tx)
.await?;
Ok(row.0)
}
#[cfg(test)]
mod tests {
use super::*;
// sqlx::test sets up a connection pool against a test DB and gives us
// a fresh transaction per test. The transaction is rolled back automatically.
#[sqlx::test]
async fn insert_user_assigns_an_id(pool: PgPool) {
let mut tx = pool.begin().await.unwrap();
let id = insert_user(&mut tx, "alice").await.unwrap();
assert!(id > 0);
// Verify the row exists within the same transaction.
let count: (i64,) =
sqlx::query_as("SELECT COUNT(*) FROM users WHERE name = 'alice'")
.fetch_one(&mut *tx)
.await
.unwrap();
assert_eq!(count.0, 1);
// No commit. When tx is dropped, sqlx rolls back. The next test sees a
// clean DB without us writing any cleanup code.
}
}
#[sqlx::test] is doing several useful things: it reads a DATABASE_URL, optionally runs migrations from ./migrations, hands your test a PgPool, and isolates each test in its own database (sqlx creates a fresh DB per test if you ask it to). It's the cleanest setup in the Rust ecosystem right now.
If you're not using sqlx, the pattern is the same: take a transaction as a parameter to your application functions, start one in your test, and never commit it.
Containerised databases for true isolation
Sometimes a single shared test database is fine. Sometimes you want each test run to have a completely fresh Postgres or MySQL with no shared state at all. The testcontainers crate spins up Docker containers from your test code and tears them down after.
use testcontainers::{clients::Cli, images::postgres::Postgres};
#[tokio::test]
async fn full_stack_query_runs_against_clean_db() {
let docker = Cli::default();
// Start a fresh Postgres container. It's torn down when `node` is dropped.
let node = docker.run(Postgres::default());
let port = node.get_host_port_ipv4(5432);
let url = format!("postgres://postgres:postgres@localhost:{port}/postgres");
let pool = sqlx::PgPool::connect(&url).await.unwrap();
// Run schema migrations. In a real project, this comes from migrations/.
sqlx::query("CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL)")
.execute(&pool).await.unwrap();
// ... your assertions here ...
}
This is heavier (you pay Docker startup costs per test, or per test module if you share) but it's bulletproof. Use it for tests that exercise real SQL, real triggers, real indexes. For everything else, the rollback pattern is faster.
Common pitfalls
A few things that cause flaky database tests.
Auto-incrementing IDs across tests. If your tests assert assert_eq!(id, 1), they'll pass the first time and fail the second, because the sequence has advanced. Either don't assert on specific IDs (assert!(id > 0)) or use the rollback pattern so the sequence resets.
Time-of-day in tests. A test that creates a row with created_at = NOW() and then asserts it's "today" will fail at midnight. Either freeze the clock in your tests or make the timestamp injectable.
Connection pool exhaustion. If each test grabs a new pool of 10 connections and your local Postgres allows 100 total, you'll hit "too many connections" after 10 parallel tests. Configure a small pool per test, or use a single shared pool across the test run.
Forgetting #[tokio::test]. Async tests need an async runtime to actually run. A plain #[test] on an async function will compile but the future never gets polled. The compiler error if you mix them up looks like:
error[E0277]: `impl Future<Output = ()>` is not a future
Use #[tokio::test] (or #[sqlx::test] which wraps it) for async test functions.
Run them, but run them often
The most important rule of database testing isn't a code pattern. It's that you actually run the tests. If they take three minutes, nobody runs them. If they take three seconds, nobody minds. So push the pure logic into functions you can test without a database, use transactional rollback for the SQL layer, and reach for testcontainers only when you need full isolation. The faster the suite, the more often it runs, the more bugs you catch before they become someone's bad afternoon.