Databases

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

Naming Conventions

• Table names: singular. user, not users. Reads better in JOINs. Matches ORM class names.
• Column names: camelCase. Maps directly to JSON without transformation. Matches JavaScript conventions.
• Primary key: always id. Simple. No need to remember field names for joins.
• Foreign keys: tableName_id format. Explicit about which table the key references.
• Date fields end with At: createdAt, updatedAt, deletedAt. Always include timezone.
• Every table must have createdAt and updatedAt — essential for debugging.
• Status fields: booleans for single state (isActive). Enums for multi-state (status: draft/active/banned). Do not use a boolean when the field will eventually need three states.

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.

Evolution

• Feb 2026 — rewritten in rule-oriented format from database naming article, IDENTITY.md, and code review patterns.