Use Lowercase Identifiers for Compatibility

Why This Matters

Postgres folds unquoted identifiers to lowercase, but quoted identifiers preserve case exactly. This inconsistency causes bugs with ORMs, migration tools, and SQL generators that don't handle quoted identifiers correctly.

• ORM compatibility: Many ORMs expect lowercase identifiers and break with mixed-case names
• Migration tools: Schema diff tools struggle with quoted identifiers
• SQL portability: Quoted identifiers require quotes everywhere, making SQL harder to read
• Case sensitivity bugs: Mixing quoted and unquoted identifiers causes "relation does not exist" errors

The Problem

-- Quoted identifier preserves case
CREATE TABLE "UserAccounts" (
  "UserId" INT PRIMARY KEY,
  "FirstName" TEXT,
  "CreatedAt" TIMESTAMPTZ
);

-- Must quote everywhere (error-prone)
SELECT "UserId", "FirstName" FROM "UserAccounts";

-- Unquoted fails (case mismatch)
SELECT userid, firstname FROM useraccounts;  -- ERROR: relation "useraccounts" does not exist

-- ORMs generate wrong SQL
-- Prisma, TypeORM, etc. may not quote correctly

The Solution

-- Lowercase identifiers, no quotes needed
CREATE TABLE user_accounts (
  user_id INT PRIMARY KEY,
  first_name TEXT,
  created_at TIMESTAMPTZ
);

-- Works with or without quotes
SELECT user_id, first_name FROM user_accounts;
SELECT "user_id", "first_name" FROM "user_accounts";  -- Also works

-- ORMs work correctly
-- Prisma, TypeORM, Sequelize all handle lowercase well

Naming Conventions

• Tables: Use snake_case: user_accounts, order_items
• Columns: Use snake_case: user_id, created_at
• Indexes: Use descriptive names: user_accounts_email_idx
• Constraints: Use descriptive names: user_accounts_email_unique
• Avoid: Mixed case, camelCase, PascalCase, or any quoted identifiers

-- Good: lowercase, snake_case
CREATE TABLE order_items (
  id INT PRIMARY KEY,
  order_id INT REFERENCES orders(id),
  product_id INT REFERENCES products(id),
  quantity INT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX order_items_order_id_idx ON order_items (order_id);
CREATE UNIQUE INDEX order_items_order_product_unique ON order_items (order_id, product_id);

Common Mistakes

• Using camelCase: userId becomes userid (all lowercase), which is confusing. Use user_id instead.
• Quoting to preserve case: Quoted identifiers require quotes everywhere and break ORM compatibility.
• Mixed conventions: Using UserAccounts in one place and user_accounts in another causes confusion.
• Reserved words: Avoid Postgres reserved words like user, order. Use users, orders instead.