ed3dai/howto-develop-with-postgres

PostgreSQL Development Patterns

Overview

Enforce transaction safety, type safety, and naming conventions to prevent data corruption and runtime errors.

Core principles:

• Transactions prevent partial updates (data corruption)
• Type safety catches errors at compile time
• Naming conventions ensure consistency
• Read-write separation prevents accidental mutations

For TypeScript/Drizzle implementations: See typescript-drizzle.md for concrete patterns.

Transaction Management

TX_ Prefix Rule (STRICT ENFORCEMENT)

Methods that START transactions:

• Prefix method name with TX_
• Must NOT accept connection/executor parameter
• Call connection.transaction() or db.transaction() internally

Methods that PARTICIPATE in transactions:

• No TX_ prefix
• MUST accept connection/executor parameter with default value
• Execute queries using the provided executor

// GOOD: Starts transaction, has TX_ prefix, no executor parameter
async TX_createUserWithProfile(userData: UserData, profileData: ProfileData): Promise<User> {
  return this.db.transaction(async (tx) => {
    const user = await this.createUser(userData, tx);
    await this.createProfile(user.id, profileData, tx);
    return user;
  });
}

// GOOD: Participates in transaction, no TX_ prefix, takes executor
async createUser(userData: UserData, executor: Drizzle = this.db): Promise<User> {
  return executor.insert(USERS).values(userData).returning();
}

// BAD: Starts transaction but missing TX_ prefix
async createUserWithProfile(userData: UserData, profileData: ProfileData): Promise<User> {
  return this.db.transaction(async (tx) => { /* ... */ });
}

// BAD: Has TX_ prefix but takes executor parameter (allows nesting)
async TX_createUser(userData: UserData, executor: Drizzle = this.db): Promise<User> {
  return executor.transaction(async (tx) => { /* ... */ });
}

What DOES NOT count as "starting a transaction":

• Single INSERT/UPDATE/DELETE operations
• Atomic operations like onConflictDoUpdate
• SELECT queries

Type Safety

Primary Keys

Default: ULID stored as UUID

• When in doubt, use ULID: "Most things can leak in some way"
• Prevents ID enumeration attacks
• Time-sortable for indexing efficiency

Exceptions (context-dependent):

• Pure join tables (composite PK from both FKs)
• Small lookup tables (serial/identity acceptable)
• Internal-only tables with no user visibility (serial/identity acceptable)

Rule: If unsure whether data will be user-visible, use ULID.

Financial Data

Use exact decimal types (numeric/decimal) for monetary values:

• Never use float/double for money (causes rounding errors)
• Use numeric/decimal with appropriate precision and scale
• Example: numeric(19, 4) for general financial data

Why: Floating-point types accumulate rounding errors. Exact decimal types prevent financial discrepancies.

JSONB Columns

ALWAYS type JSONB columns in your ORM/schema:

• Use typed schema when structure is known
• Use Record<string, unknown> if truly schemaless
• Never leave JSONB untyped

Why: Prevents runtime errors from accessing undefined properties or wrong types.

Read-Write Separation

Maintain separate client types at compile time:

• Read-write client: Full mutation capabilities
• Read-only client: Mutation methods removed at type level
• Default to read-only for query methods
• Use read-write only when mutations needed

Why: Prevents accidental writes to replica, enforces deliberate mutation choices.

Naming Conventions

Database Identifiers

All database objects use snake_case:

• Tables: user_preferences, order_items
• Columns: created_at, user_id, is_active
• Indexes: idx_tablename_columns (e.g., idx_users_email)
• Foreign keys: fk_tablename_reftable (e.g., fk_orders_users)

Application code: Map to idiomatic case (camelCase in TypeScript, etc.)

Schema Patterns

Standard mixins:

• created_at, updated_at timestamps on all tables
• deleted_at for soft deletion when needed
• tenant_id for multi-tenant tables (project-dependent)

Proactive indexing:

• All foreign key columns
• Columns used in WHERE clauses
• Columns used in JOIN conditions
• Columns used in ORDER BY

Concurrency

Default isolation (Read Committed) for most operations.

Use stricter isolation when:

• Financial operations: Serializable isolation
• Inventory/count operations: Serializable isolation
• Critical sections: Pessimistic locking (SELECT ... FOR UPDATE)

Migrations

Always use generate + migrate workflow:

1. Change schema in code
2. Generate migration file
3. Review migration SQL
4. Apply migration to database

Never use auto-push workflow in production.

Common Mistakes

| Mistake | Reality | Fix | |---------|---------|-----| | "This is one operation, doesn't need transaction" | Multi-step operations without transactions cause partial updates and data corruption | Wrap in transaction with TX_ prefix | | "Single atomic operation needs TX_ prefix" | TX_ is for explicit transaction blocks, not atomic operations | No TX_ for single INSERT/UPDATE/DELETE | | "UUID is just a string" | Type confusion causes runtime errors (wrong ID formats, failed lookups) | Use strict UUID type in language | | "I'll type JSONB later when schema stabilizes" | Untyped JSONB leads to undefined property access and type errors | Type immediately with known fields or Record<string, unknown> | | "Read client vs write client doesn't matter" | Using wrong client bypasses separation, allows accidental mutations | Use read-only client by default, switch deliberately | | "I'll add indexes when we see performance issues" | Missing indexes on foreign keys cause slow queries from day one | Add indexes proactively for FKs and common filters | | "This table won't be user-visible, use serial" | Requirements change, IDs leak in logs/URLs/errors | Use ULID by default unless certain it's internal-only | | "Float/double is fine for money, close enough" | Rounding errors accumulate, causing financial discrepancies (0.01 differences multiply) | Use numeric/decimal types for exact arithmetic |

Red Flags - STOP and Refactor

Transaction management:

• Method calls .transaction() but no TX_ prefix
• Method has TX_ prefix but accepts executor parameter
• Multi-step operation without transaction wrapper

Type safety:

• JSONB column without type annotation
• UUID/ULID stored as plain string type
• No separation between read and write clients
• Float/double types for monetary values

Schema:

• Missing indexes on foreign keys
• No created_at/updated_at timestamps
• camelCase or PascalCase in database identifiers

All of these mean: Stop and fix immediately.

Reference

For TypeScript/Drizzle concrete implementations: typescript-drizzle.md