database-drizzle

Database Drizzle

The TypeScript schema is the contract. drizzle-kit migrations are the history. Queries must be predictable under load. Engine: postgres.

Stack and scope

Part	Role
drizzle-orm	Typed queries, relations, SQL builder
drizzle-kit	generate / migrate / push / studio
Postgres	Default (pg / postgres.js / neon / etc. of the project)
TS Schema	Tables, enums, indexes in versioned files

Preserve drivers and layout already adopted by the project. In new apps, prefer Postgres.

Recommended organization

src/db/
  schema/          # tables + relations por domínio
  index.ts         # client exportado
  migrations/      # SQL gerado pelo kit (se versionado no repo)
drizzle.config.ts

• One client per process (singleton in dev hot-reload).
• Do not expose the client to the UI; encapsulate it in repositories/services.
• Schema is the source of truth; do not edit SQL of an already applied migration — generate another.

Modeling

import { pgTable, uuid, text, timestamp, index } from "drizzle-orm/pg-core";
import { relations } from "drizzle-orm";

export const users = pgTable("users", {
  id: uuid("id").defaultRandom().primaryKey(),
  email: text("email").notNull().unique(),
  createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
  updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow().notNull(),
});

export const posts = pgTable(
  "posts",
  {
    id: uuid("id").defaultRandom().primaryKey(),
    authorId: uuid("author_id")
      .notNull()
      .references(() => users.id, { onDelete: "cascade" }),
    title: text("title").notNull(),
  },
  (t) => [index("posts_author_id_idx").on(t.authorId)],
);

export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts),
}));

• Clear table/column names; onDelete conscious (cascade only when correct).
• Prefer Postgres enums/pgEnum for closed sets.
• Timestamps createdAt / updatedAt on mutable entities.
• Normalize enough; denormalize only with a measured read reason.
• Declare relations() when you are going to use the relational API (db.query).

Migrations

• Production: drizzle-kit generate → review SQL → migrate (or equivalent pipeline).
• Avoid push in production; reserve it for explicit prototype/local use.
• Forward-safe migrations; destructive rewrites only with a data plan.
• On deploy: migrate → release app (or documented project strategy).

Efficient queries

• Select only necessary columns (select({ ... })).
• Avoid N+1: with in db.query or explicit joins / batch.
• Pagination with cursor or limited limit/offset ; reject abusive page size.
• Indexes for frequent filters/sorting and hot FKs.
• Transactions: db.transaction(async (tx) => { ... }) for multi-table writes.
• Prepared statements / pools according to the project driver.

const post = await db.query.posts.findFirst({
  where: eq(posts.id, id),
  with: { author: true },
  columns: { id: true, title: true },
});

Integration

• Validate input (Zod etc.) before assembling where / values / set.
• Handle driver errors (unique violation, FK) as domain (409, 404, etc.).
• Inference: use schema types (typeof users.$inferSelect / $inferInsert).
• Server Components / route handlers: open connection via singleton; close on shutdown if the runtime requires it.
• Embeddings / agent memory: pgvector.

Soft delete and multi-tenant

• Soft delete: consistent filter (isNull(deletedAt)) on all reads.
• Multi-tenant: always scope by tenantId/orgId in queries; never trust only the client.

Anti-patterns

• select() without limit on lists
• with deep “just in case”
• Business logic spread across opaque SQL helpers
• Alter production with drizzle-kit push
• Missing indexes on filtered foreign keys
• Expose raw row with internal fields (hash, tokens)

Completion criteria

• Coherent schema and relations
• Generated migration and reviewed SQL
• Queries without obvious N+1
• Indexes for hot paths
• DB errors mapped to the API/domain
• Encapsulated client; types derived from the schema