drizzle

The Drizzle ORM Schema Style Guide codifies how LobeHub defines PostgreSQL tables, columns, and queries with Drizzle. It documents where schema code lives: schemas under packages/database/src/schemas/, migrations under packages/database/migrations/, and configuration in drizzle.config.ts using the postgresql dialect in strict mode. Shared helpers in _helpers.ts provide timestamptz, createdAt, updatedAt, accessedAt, and a spreadable timestamps object.

Naming follows plural snake_case tables and snake_case columns, and new tables should preserve the established noun family and suffix (for example workspace_xxx_logs alongside user_xxx_logs). For columns, the guide steers away from auto-incrementing primary keys (serial/bigserial) because sequence state is fragile across migrations and restores, preferring application-generated text IDs (idGenerator, createNanoId) or uuid for internal tables while keeping $defaultFn so explicit inserts can still override the id. It also discourages sentinel strings for missing values in favor of nullable columns or concrete status types, discourages pgEnum in favor of text/varchar with a TypeScript $type value type kept in a shared module, recommends JSDoc on non-obvious fields, and requires concrete interfaces for JSONB columns instead of loose Record types. Indexes use the array-return style.

Reach for this guide when writing or reviewing pgTable schemas, foreign keys with onDelete cascade, junction/many-to-many tables, indexes, or inferred insert and select types via createInsertSchema and $inferInsert/$inferSelect. It also reminds contributors that every new model or repository file needs a matching sibling test in the same pull request.