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

## Transaction Management

### Transaction vs Query Execution

**Methods that START transactions:**

- Must NOT accept a specific connection/executor parameter, takes things like a database pool or client
- Create the transaction and then pass that transaction object to an underlying method that performs the actual query
- Usually exported or public

**Methods that PARTICIPATE in transactions:**

- MUST accept a generic connection/executor parameter
- Execute queries using the provided executor
- Usually unexported or private

**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:**