Databases Best Practices
Safe schema changes, indexing, and query hygiene for Python services.
How to Use This List
- Review during design reviews and before production deploys.
- Convert repeat failures into automated CI policy where possible.
- Revisit after framework minor upgrades.
Schema
- Every change goes through Alembic or Django migrations.
- Add indexes for filters and joins you actually run.
- Prefer additive migrations before destructive cuts.
Queries
- Parameterize SQL; never interpolate user strings.
- Eager-load relationships used in list endpoints.
- Paginate large result sets at the database.
Operations
- Size pools from worker count and DB max connections.
- Monitor slow query logs and lock waits.
- Backup and test restores on a schedule.
Security
- Use least-privilege DB roles per service.
- Encrypt connections with TLS to the database.
- Rotate credentials without embedding in images.
FAQs
When should I adopt database best practices?
Use it when the patterns and trade-offs on this page match your API or data boundary.
What is the top production mistake with database best practices?
Skipping validation, timeouts, or explicit error contracts at the HTTP edge.
How do I test database best practices?
Use the framework test client, override dependencies, and assert status plus JSON shape.
Does database best practices work with Python 3.14?
Yes - examples target Python 3.14 with pinned framework versions from the stack footer.
How does database best practices relate to Pydantic 2?
Validate and serialize at boundaries; keep services working with typed domain objects.
Sync or async?
Prefer async routes when I/O dominates; keep CPU work small or offload to workers.
Where should business logic live?
Thin handlers; services own rules; repositories own queries.
How do I document APIs?
Publish OpenAPI or schema docs that match response models in code.
How do I handle versioning?
Explicit URL or header versioning with deprecation windows - avoid silent breaks.
What should I read next?
Follow the Related links for the next layer of depth in this section.
How do I stay secure?
Authenticate callers, authorize per resource, rate-limit, and never log secrets.
Performance first step?
Measure DB and upstream latency before swapping frameworks.
Related
- Databases Basics - Connections and transactions
- SQLAlchemy ORM 2.0 - ORM patterns
- Migrations with Alembic - Schema changes
- Async Database Access - asyncpg
Stack versions: This page was written for Python 3.14.0 (stable 3.14, maintenance 3.13), FastAPI 0.115+, Django 5.2, Flask 3.1, Pydantic 2, PyTorch 2.6+, pandas 2.2+, Polars 1.x, ruff 0.9+, and uv 0.6+.