Databases Best Practices
Rules for reliable schema evolution, pool sizing, and safe SQL in Rust services.
How to Use This List
- Apply at schema design, query review, and incident postmortems.
- Automate migration runs and
cargo sqlx preparein CI. - Profile SQL before blaming the web framework for latency.
A - Schema & Migrations
- Every schema change is a forward migration file. Never edit applied migrations.
- Additive changes first. Nullable columns, backfill, then constraints.
- Index foreign keys and frequent filters. EXPLAIN slow queries in staging.
- One migration tool per repository. sqlx OR Diesel OR SeaORM migrations - not all three.
- Run migrations in deploy pipeline before traffic shift.
B - Connections & Pools
- One pool per process in
AppState. Never per request. - Size pool from
instances × max_connections ≤ DB limit. Leave 20-30% headroom. - Set
acquire_timeout. Fail fast with 503 instead of hanging. - Close pool on graceful shutdown. Drain in-flight queries.
- Use PgBouncer when many small services share PostgreSQL.
C - Queries & Transactions
- Parameterized queries only. No format! for SQL values.
- Eliminate N+1. JOIN or
ANY($1)batch loads. - Keep transactions short. No external HTTP inside
begin/commit. - Paginate large lists. Keyset pagination over OFFSET for big tables.
- Map rows to DTOs at API boundary. Hide internal columns.
D - Caching & Redis
- SQL is source of truth for durable data. Redis for cache and ephemeral state.
- TTL on every cache key. Prevent unbounded growth.
- Cache invalidation strategy documented. Version keys or pub/sub.
- Monitor hit rate and memory. Alert on eviction storms.
- Do not store secrets in Redis without encryption at rest.
E - Operations & Security
- Separate DB roles for app vs migrations vs admin. Least privilege.
- Rotate credentials via secrets manager. Not plain env in repos.
- Log slow queries with request ID. Redact parameter values with PII.
- Backup and test restore quarterly. Migrations are not backups.
-
SELECT 1on/readyonly. Distinguish liveness from DB dependency.
FAQs
sqlx or ORM?
sqlx default for async services; ORM when relations dominate and team prefers builders.
Biggest DB mistake?
Oversized pools exhausting PostgreSQL connections under load.
SQLite in prod?
Acceptable for edge/single-tenant; PostgreSQL for multi-tenant SaaS.
When raw SQL?
Reports, bulk ingest, and performance-critical paths - keep in repository module.
Test database?
testcontainers or #[sqlx::test] with disposable DB.
Read replicas?
Separate pools; tolerate replication lag on dashboards.
Connection string in logs?
Never - redact passwords in tracing subscribers.
Deadlocks?
Retry transactions; keep lock order consistent across code paths.
ORM migration from Rails?
Port schema via sql migrations; rewrite queries incrementally.
Observability?
Pool acquire time, query duration histograms, active connections gauge.
Related
- Databases Basics - Getting started
- sqlx - Primary driver
- Migrations - Schema workflow
- Connection Pooling - Sizing
- Web Backends Best Practices - HTTP layer
Stack versions: This page was written for Rust 1.97.0 (edition 2024), Tokio 1.x, Axum 0.8, serde 1.0, sqlx 0.8, clap 4, and Polars 0.46+.