← Back

Databases

Consistency matters more than which convention you pick. These are the conventions for a Node.js/TypeScript stack.

Naming Conventions

  • Table names: singular, snake_case. user, room_type, cancel_policy. Reads better in JOINs. Matches ORM class names.
  • Column names: snake_case. SQL is case insensitive — snake_case works naturally. ORM handles mapping to camelCase in application code.
  • Primary key: always id. Simple. No need to remember field names for joins.
  • Foreign keys: {table}_id format. user_id, hotel_id, post_id. Explicit about which table the key references.
  • Date fields end with _at: created_at, updated_at, deleted_at. Always include timezone.
  • Every table must have created_at and updated_at — essential for debugging.
  • Status fields: booleans for single state (is_active). Enums for multi-state (status: draft/active/banned). Do not use a boolean when the field will eventually need three states.
  • Enum values: snake_case, descriptive. 'active', 'pending_approval', 'out_of_stock'. The value should communicate its meaning without additional context.
  • Junction tables: compound name of both tables. user_post, booking_tour, user_hotel_wishlist. Use compound primary keys, not auto-increment IDs. The combination of foreign keys is already unique.

Database Choice

  • MySQL is reliable and battle-tested. Historical default.
  • PostgreSQL for new projects with AI needs — embeddings, richer features, better scale.
  • NoSQL for unstructured data only: logs, search results, cache. Not for core business data where relationships matter.

ORM First, Raw SQL as Exception

Use the ORM for all queries. Raw SQL is the exception, and the exception must be justified with performance data. Consistency and type safety matter more than marginal performance gains.

  • Each query that looks simple can usually be expressed with the ORM.
  • Always chunk large WHERE IN queries. Without a limit, production databases hit query size limits under load. Default to chunking — if it is less than 1K items, it is just one loop.
  • Question magic numbers. Timeout values and batch sizes must have data backing them.

Anti-patterns

  • Raw SQL for simple queries — the ORM handles it with type safety. Raw SQL breaks when schemas change.
  • Unchunked WHERE IN — works in development, fails in production under load.
  • Boolean for multi-state fields — is_active becomes is_active + is_banned + is_suspended. Use an enum from the start.
  • Missing created_at/updated_at — makes debugging production issues nearly impossible.
  • Auto-increment ID on junction tables — unnecessary column. Compound primary key is the natural key and prevents duplicates at the database level.
  • Exposing ORM internals outside its package — Prisma client should not leak into domain or application layers.

Experience Notes

Previously: MySQL as default for everything. Now: PostgreSQL for new projects, especially those with AI needs (embeddings, vector search). MySQL remains reliable for existing systems. The choice depends on project requirements, not preference.