Database postgresql

PostgreSQL Database Standards

Priority: P0 (FOUNDATIONAL)

Integration patterns and ORM standards for PostgreSQL applications.

Patterns & Architecture

• Repository Pattern: Isolate database logic. Use @InjectRepository() or PrismaService.
• Relationship Integrity: Avoid redundant raw ID columns. Favor relation properties.

Migrations (Strict Rules)

• NEVER use synchronize: true in production.
• Generation: Modify .entity.ts -> run pnpm migration:generate.
• Zero-Downtime: Use Expand-Contract pattern (Add -> Backfill -> Drop) for destructive changes.
• RLS: typeorm migration:generate cannot detect Row-Level Security. Use raw queryRunner.query() SQL for RLS.

Performance & Gotchas

• Pagination: Mandatory. Use limit/offset or cursor-based pagination.
• Indexing: Define indexes in code for frequently filtered columns. RLS columns MUST be indexed.
• Transactions: Use QueryRunner or $transaction for multi-step mutations.

Anti-Patterns

• No N+1 queries: Use query builders or eager-load relations instead of lazy-loading in loops.
• No heavy RLS joins: Keep RLS predicates simple; move complex logic to the query/view layer.
• No synchronize in production: Always run explicit migrations; synchronize: true is destructive.

References

• SQL Gotchas (UPDATE FROM)