Which database should you use?

For new projects in 2026, start with PostgreSQL unless you have a specific reason not to. Postgres handles relational data, document-style JSON columns, full-text search, geospatial data, and vector search for AI applications. It scales to billions of rows with proper design. The ecosystem is huge; tooling is mature; hosting is cheap and widely available. ‘Why Postgres?’ is the wrong question — it’s ‘why not Postgres?’

Supabase bundles PostgreSQL with authentication, file storage, real-time subscriptions, edge functions, and vector search in one platform. Free tier works for real projects (500MB DB, 1GB storage, 50K auth users). Drop-in replacement for Firebase without the NoSQL lock-in. Most small-to-mid projects in 2026 should default to Supabase unless they need specific enterprise features. It accelerates delivery by weeks.

MongoDB: when your data really is document-shaped (user-generated content with dynamic schemas, though Postgres JSON columns often suffice). Redis: for caching, session storage, rate limiting — not primary storage. DynamoDB: for massive scale AWS apps with known access patterns. ClickHouse or BigQuery: for analytics workloads at scale. Specialized databases solve specialized problems — most apps don’t have those problems.

Normalize first, denormalize when performance requires it. Use foreign keys with cascade rules (delete/update). Index columns that appear in WHERE, JOIN, ORDER BY. Avoid indexing every column — indexes slow writes. Use UUID or Snowflake IDs for globally-unique identifiers (security, distributed systems). Avoid nullable columns where you can; a nullable column is a silent source of bugs.

Prisma: mature, full-featured, great tooling, declarative schema. Slight runtime overhead but saves enormous developer time. Drizzle: lighter, more SQL-like, better for performance-sensitive apps. Raw SQL: always an option for complex queries. Don’t avoid writing raw SQL — ORMs are for common operations, SQL is for complex queries. Knowing both makes you more productive than being strict about either.

Database migrations — changing schema without downtime — are where many projects crumble. Use a migration framework (Prisma Migrate, Supabase migrations, Flyway). Every schema change goes through migrations in version control. Test migrations on a production-like dataset before applying. Never manually edit production schemas. Plan for zero-downtime migrations (add nullable column → backfill → add constraint → remove old column across multiple deploys).