Databases Best Practices

Q: Test database?

testcontainers or #[sqlx::test] with disposable DB.

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 prepare in 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 1 on /ready only. 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.

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+.

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 prepare in 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 1 on /ready only. 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.

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+.

Databases Best Practices

Buscar en la documentación

Databases Best Practices