Drizzle Schema Analyzer

Analyze Drizzle ORM schema definitions for best practices, performance, type safety, and migration risks. Checks index strategy, relation definitions, column...

Analyze Drizzle ORM schema definitions for correctness, performance, type safety, and migration safety. Reviews table definitions, column types, indexes, relations, constraints, and migration files. Acts as a senior database engineer auditing your Drizzle schema for production readiness.

Usage

Invoke this skill when you need to review Drizzle ORM schemas, optimize query performance, or validate migration safety.

Basic invocation:

Analyze the Drizzle schemas in /path/to/src/db/schema/ Review this Drizzle schema for best practices Check migration safety for the latest Drizzle migration

Focused analysis:

Check index strategy across all Drizzle tables Audit relation definitions for N+1 risks Review column type choices for data integrity Validate migration files for destructive changes

The agent reads Drizzle schema TypeScript files, parses table and relation definitions, and produces a comprehensive quality report.

How It Works

Step 1: Discover and Parse Schema Files

The agent locates all Drizzle schema definitions:

# Find all schema files
find /path/to/src/db/ -name "*.ts" -type f

# Identify files with Drizzle table definitions
grep -rl "pgTable\|mysqlTable\|sqliteTable\|createTable" /path/to/src/db/ --include="*.ts"

# Find relation definitions
grep -rl "relations\|defineRelations" /path/to/src/db/ --include="*.ts"

# Locate migration files
find /path/to/drizzle/ -name "*.sql" -type f | sort

The agent parses each schema file to extract:

• Table definitions (pgTable, mysqlTable, sqliteTable)
• Column types and constraints (notNull, default, primaryKey, unique)
• Index definitions (index, uniqueIndex, composite indexes)
• Relation definitions (one-to-one, one-to-many, many-to-many)
• Enums and custom types (pgEnum, mysqlEnum)
• Timestamps and audit columns (createdAt, updatedAt, deletedAt)
• Export patterns (barrel exports, circular references)

Step 2: Audit Table Definitions

The agent checks each table's column configuration:

Column type analysis:

// GOOD: Proper type usage with constraints
export const users = pgTable("users", {
  id: uuid("id").defaultRandom().primaryKey(),
  email: varchar("email", { length: 255 }).notNull().unique(),
  name: varchar("name", { length: 100 }).notNull(),
  status: userStatusEnum("status").notNull().default("active"),
  metadata: jsonb("metadata").$type<UserMetadata>(),
  createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
  updatedAt: timestamp("updated_at", { withTimezone: true }).notNull().defaultNow(),
});

// PROBLEMS the agent detects:

FAIL: Table "users"
  Column "email" — varchar without length constraint
  RISK: Defaults to unlimited length, allows data quality issues
  FIX: Add length: varchar("email", { length: 255 })

FAIL: Table "orders"
  Column "amount" — real() used for monetary value
  RISK: Floating point precision loss on financial calculations
  FIX: Use numeric("amount", { precision: 12, scale: 2 }) or integer (cents)

FAIL: Table "events"
  Column "created_at" — timestamp without timezone
  RISK: Timezone ambiguity, especially across regions
  FIX: timestamp("created_at", { withTimezone: true })

WARN: Table "posts"
  Column "body" — text() without .notNull()
  Nullable text columns consume extra storage and complicate queries
  FIX: Add .notNull().default("") if empty string is acceptable

WARN: Table "products"
  Column "price" — no .notNull() constraint
  RISK: NULL prices bypass application validation, break calculations
  FIX: Add .notNull() and handle at application layer

WARN: Table "sessions"
  No "id" column or composite primary key
  RISK: No unique row identifier for updates or deletes
  FIX: Add primary key column or composite primaryKey()

Primary key strategy audit:

Primary Key Analysis:

  Table "users": uuid("id").defaultRandom().primaryKey()
    PASS: UUID v4 — good for distributed systems
    WARN: Random UUIDs cause B-tree index fragmentation
    CONSIDER: Use uuid v7 (time-ordered) for better index locality:
      id: uuid("id").$defaultFn(() => uuidv7()).primaryKey()

  Table "orders": serial("id").primaryKey()
    PASS: Auto-increment — simple and efficient for single-node
    WARN: Exposes record count and insertion order to API consumers
    CONSIDER: Add a separate uuid column for public-facing identifiers

  Table "order_items": No primary key defined
    FAIL: Drizzle requires explicit primary key for proper type inference
    FIX: Add composite key or surrogate key:
      (t) => ({ pk: primaryKey(t.orderId, t.productId) })

Step 3: Analyze Index Strategy

The agent evaluates index coverage against likely query patterns:

Index Analysis:

  Table "users" (estimated rows: high-traffic table)
    Indexes:
      - PRIMARY KEY on "id" (implicit)
      - UNIQUE on "email"
    
    FAIL: No index on "status"
      Query pattern: WHERE status = 'active' (common filter)
      FIX: Add .index() or create composite index on (status, created_at)
    
    WARN: No index on "created_at"
      Query pattern: ORDER BY created_at DESC (pagination, feeds)
      FIX: index("users_created_at_idx").on(users.createdAt)

  Table "orders"
    Indexes:
      - PRIMARY KEY on "id"
      - INDEX on "user_id"
    
    FAIL: Missing composite index for common query pattern
      Pattern: WHERE user_id = ? AND status = ? ORDER BY created_at DESC
      FIX: index("orders_user_status_created_idx")
             .on(orders.userId, orders.status, orders.createdAt)
    
    WARN: Index on "user_id" alone may be redundant
      If composite index (user_id, status, created_at) exists,
      it covers user_id-only lookups via leftmost prefix

  Table "audit_logs"
    FAIL: No indexes defined on high-write table
      Common queries: by entity_type + entity_id, by user_id + date range
      FIX: Add at minimum:
        index("audit_entity_idx").on(auditLogs.entityType, auditLogs.entityId)
        index("audit_user_date_idx").on(auditLogs.userId, auditLogs.createdAt)

  Table "products"
    WARN: Index on "name" for text search
      B-tree index on text column only supports prefix matching (LIKE 'term%')
      For full text search, use PostgreSQL GIN index with tsvector
      OR: Use a search service (Meilisearch, Typesense)

Composite Index Ordering Rules Applied:
  1. Equality columns first (WHERE x = ?)
  2. Range/sort columns last (WHERE y > ? ORDER BY z)
  3. High-cardinality columns before low-cardinality in equality position

Step 4: Review Relation Definitions

The agent audits Drizzle relation definitions:

// The agent checks for correct relation patterns:
export const usersRelations = relations(users, ({ one, many }) => ({
  profile: one(profiles, {
    fields: [users.id],
    references: [profiles.userId],
  }),
  orders: many(orders),
  posts: many(posts),
}));

Relation Analysis:

  FAIL: Relation "users.profile" (one-to-one) — missing unique constraint
    profiles.userId must have a UNIQUE constraint for true one-to-one
    Without it, multiple profiles per user are possible at the DB level
    FIX: Add .unique() to profiles.userId column definition

  FAIL: Relation "orders.items" defined but foreign key missing
    Drizzle relations are application-level only — they don't create FK constraints
    No references() found on orderItems.orderId
    FIX: Add foreign key:
      orderId: integer("order_id").notNull().references(() => orders.id)

  WARN: Relation "users.orders" — no cascade behavior specified
    Default: no cascade — orphaned orders if user is deleted
    FIX: Add onDelete behavior:
      .references(() => users.id, { onDelete: "cascade" })
      OR: .references(() => users.id, { onDelete: "set null" })

  WARN: Many-to-many "users <-> roles" without junction table relation
    Junction table "user_roles" exists but no relations() defined on it
    FIX: Define relations for the junction table to enable nested queries

  FAIL: Circular relation detected
    users -> teams -> projects -> users (via assignee)
    RISK: Infinite recursion in nested with() queries
    FIX: Limit query depth or break the cycle with explicit query boundaries

  WARN: Self-referencing relation "categories.parent"
    categories.parentId references categories.id
    Ensure application code handles max depth to prevent infinite recursion

Step 5: Validate Type Safety Patterns

The agent checks TypeScript type integration:

Type Safety Analysis:

  FAIL: JSON column without $type annotation
    Table "users", column "metadata" — jsonb("metadata")
    RISK: Type is inferred as `unknown`, no compile-time safety
    FIX: jsonb("metadata").$type<UserMetadata>()
    AND: Define the interface:
      interface UserMetadata { preferences: Preferences; flags: string[] }

  FAIL: Enum mismatch between Drizzle and TypeScript
    pgEnum "status" has values: ["active", "inactive", "banned"]
    TypeScript union type has: "active" | "inactive" | "suspended"
    FIX: Derive TypeScript type from Drizzle enum:
      export type Status = (typeof statusEnum.enumValues)[number]

  WARN: Using InferModel (deprecated)
    FIX: Use InferSelectModel and InferInsertModel:
      export type User = InferSelectModel<typeof users>
      export type NewUser = InferInsertModel<typeof users>

  WARN: Missing Zod schema integration
    No validation schemas found for insert types
    RECOMMEND: Use drizzle-zod for runtime validation:
      export const insertUserSchema = createInsertSchema(users)
      export const selectUserSchema = createSelectSchema(users)

  PASS: All table exports properly typed
  PASS: Relation field references match column types
  PASS: No "any" type assertions in schema files

Step 6: Audit Migration Safety

The agent reviews migration files for destructive operations:

Migration Safety Analysis:

  Migration: 0005_add_status_column.sql
    PASS: ALTER TABLE ADD COLUMN with DEFAULT — safe, non-blocking (PostgreSQL 11+)

  Migration: 0006_rename_column.sql
    FAIL: ALTER TABLE RENAME COLUMN "name" TO "full_name"
    RISK: Breaks all application code referencing "name" column
    REQUIRES: Zero-downtime deployment strategy:
      1. Add new column "full_name"
      2. Backfill data
      3. Update application to read/write both columns
      4. Remove old column in separate migration

  Migration: 0007_drop_table.sql
    FAIL: DROP TABLE "legacy_sessions"
    RISK: Irreversible data loss — no rollback possible
    REQUIRES: Verify table is unused, take backup before running
    RECOMMEND: Rename to "_deprecated_legacy_sessions" first, drop after 30 days

  Migration: 0008_change_type.sql
    FAIL: ALTER COLUMN "price" TYPE numeric USING price::numeric
    RISK: Full table rewrite, acquires ACCESS EXCLUSIVE lock
    For tables > 1M rows, this blocks all reads/writes during migration
    RECOMMEND: Create new column, backfill async, swap columns

  Migration: 0009_add_not_null.sql
    WARN: ALTER COLUMN "email" SET NOT NULL
    Requires: Full table scan to verify no NULL values exist
    Pre-check: SELECT COUNT(*) FROM users WHERE email IS NULL
    FIX: Add NOT NULL constraint with NOT VALID first, then VALIDATE separately

  Migration: 0010_create_index.sql
    WARN: CREATE INDEX on "orders"(created_at)
    RISK: Blocks writes on table during index creation
    FIX: Use CREATE INDEX CONCURRENTLY (not inside a transaction)
    NOTE: Drizzle migrations run in transactions by default — must override

  Migration Order:
    PASS: Migrations are sequentially numbered (0001-0010)
    PASS: No gaps in migration sequence
    WARN: Migration 0006 and 0007 should not be deployed together
      Column rename + table drop in same deployment increases rollback complexity

Step 7: Check Query Pattern Alignment

The agent identifies schema-query misalignments:

Query Pattern Analysis (based on application code scan):

  Pattern: db.select().from(users).where(eq(users.email, email))
    Index: UNIQUE on "email" — PASS: Covered, uses index scan

  Pattern: db.select().from(orders).where(and(eq(orders.userId, id), eq(orders.status, "active"))).orderBy(desc(orders.createdAt))
    Index: Only "user_id" indexed
    FAIL: Partial coverage — filters userId via index, then scans for status
    FIX: Composite index on (userId, status, createdAt)

  Pattern: db.query.users.findMany({ with: { orders: { with: { items: true } } } })
    WARN: Three-level nested eager loading
    Generates: 3 separate queries (users + orders + items)
    RISK: N+1 if not batched — Drizzle batches by default, but verify
    RECOMMEND: For large datasets, use explicit joins:
      db.select().from(users)
        .leftJoin(orders, eq(users.id, orders.userId))
        .leftJoin(items, eq(orders.id, items.orderId))

  Pattern: db.select({ count: count() }).from(orders).where(gte(orders.createdAt, date))
    WARN: COUNT on range scan without covering index
    FIX: Index on createdAt enables index-only count scan

  Pattern: db.select().from(products).where(ilike(products.name, `%${term}%`))
    FAIL: Leading wildcard ILIKE — cannot use B-tree index
    Causes: Full sequential table scan on every search query
    FIX: Use PostgreSQL trigram index:
      CREATE INDEX products_name_trgm_idx ON products USING gin (name gin_trgm_ops)
    OR: Use a dedicated search engine

Step 8: Audit Constraints and Data Integrity

The agent validates database-level constraints:

Constraint Analysis:

  Table "users":
    PASS: email — UNIQUE constraint
    PASS: id — PRIMARY KEY
    FAIL: No CHECK constraint on email format
      FIX: sql`CHECK (email ~* '^[^@]+@[^@]+\.[^@]+$')`
    WARN: No CHECK constraint on name length
      FIX: sql`CHECK (length(name) >= 1 AND length(name) <= 100)`

  Table "orders":
    FAIL: "amount" has no CHECK constraint for positive values
      Negative order amounts are possible at DB level
      FIX: sql`CHECK (amount > 0)`
    FAIL: "status" is varchar without CHECK constraint
      Any string value is valid — application bugs can insert garbage
      FIX: Use pgEnum or add CHECK:
        sql`CHECK (status IN ('pending', 'processing', 'completed', 'cancelled'))`

  Table "subscriptions":
    FAIL: "start_date" and "end_date" have no relationship constraint
      end_date can be before start_date at DB level
      FIX: sql`CHECK (end_date > start_date)`

  Foreign Key Completeness:
    FAIL: 4 columns appear to be foreign keys (name ends in "_id")
      but lack .references() definitions:
        - comments.postId — no FK to posts
        - order_items.productId — no FK to products
        - notifications.userId — no FK to users
        - payments.orderId — no FK to orders
      RISK: Orphaned records, referential integrity violations
      FIX: Add .references(() => targetTable.id) to each

Step 9: Review Schema Organization

The agent checks project structure and maintainability:

Schema Organization:

  WARN: All tables defined in single schema.ts file (847 lines)
    RECOMMEND: Split by domain:
      schema/users.ts — users, profiles, sessions
      schema/orders.ts — orders, order_items, payments
      schema/products.ts — products, categories, inventory
      schema/index.ts — barrel export

  WARN: Relations defined inline with tables
    RECOMMEND: Separate relations into dedicated files:
      schema/relations/users.relations.ts
    This prevents circular import issues when schemas reference each other

  FAIL: Circular import detected
    schema/users.ts imports from schema/orders.ts
    schema/orders.ts imports from schema/users.ts
    FIX: Move shared references to schema/index.ts
    OR: Use Drizzle's forward reference pattern

  PASS: Consistent naming convention — snake_case for DB, camelCase for TS
  PASS: All tables exported from barrel file
  WARN: No schema documentation (JSDoc comments) on table definitions

Step 10: Produce the Analysis Report

The agent generates a comprehensive report:

# Drizzle Schema Analysis Report
# Schema Path: /src/db/schema/ | Date: April 30, 2026

## Overview
  Tables: 14
  Relations: 11
  Migrations: 10
  Enums: 3
  Total columns: 87

## Overall Health Score: 62/100

## Category Scores
  Column Types & Constraints: 5/10  (missing NOT NULL, wrong types for money)
  Primary Keys:               7/10  (UUID fragmentation, missing keys)
  Index Strategy:             4/10  (missing composites, no covering indexes)
  Relations:                  6/10  (missing FKs, no cascade rules)
  Type Safety:                7/10  (missing $type, deprecated InferModel)
  Migration Safety:           5/10  (destructive ops, locking risks)
  Query Alignment:            4/10  (full scans, N+1 patterns)
  Data Integrity:             5/10  (missing CHECK constraints)
  Schema Organization:        6/10  (monolithic file, circular imports)

## Critical Issues
  1. Monetary values using floating point — precision loss
  2. 4 foreign keys missing .references() — orphan risk
  3. Migration 0006 renames column without zero-downtime strategy
  4. Leading wildcard ILIKE without trigram index — full table scan
  5. No CHECK constraints on business-critical columns

## Recommendations Summary
  Estimated effort: 2-4 days for critical + high priority fixes
  Expected improvement: 62 -> 85 health score
  Risk reduction: Eliminates data corruption and performance degradation

Output

The agent produces:

• Health score: 0-100 overall schema quality rating
• Category scores: granular ratings for each quality dimension
• Critical issues: problems that pose data integrity or performance risk
• Per-table analysis: column, constraint, and index audit for each table
• Relation map: visual representation of table relationships
• Migration risk assessment: safety evaluation for each migration file
• Type safety gaps: TypeScript integration issues with fix code
• Remediation code: exact Drizzle schema code to fix each issue
• Priority matrix: issues ranked by risk and effort

Scope Options

Scope	What It Covers
Full (default)	All schema files, relations, and migrations
Single table	Deep analysis of one table definition
Migrations only	Safety review of migration files
Index audit	Index coverage analysis against query patterns
Type safety	TypeScript type integration review
Changed	Only schema files changed in current git branch

Database Dialect Support

The agent adapts recommendations based on the Drizzle dialect detected:

Feature	PostgreSQL	MySQL	SQLite
UUID primary keys	uuid().defaultRandom()	varchar(36) + app-generated	text + app-generated
JSON columns	jsonb() with GIN indexes	json() (no indexing)	text (no native JSON)
Enums	pgEnum (native)	mysqlEnum (native)	CHECK constraint
Full-text search	tsvector + GIN	FULLTEXT index	FTS5 extension
Partial indexes	Supported	Not supported	Supported
Concurrent index creation	CONCURRENTLY	ALGORITHM=INPLACE	N/A (no locking)

Tips for Best Results

• Point the agent at your entire db/schema directory for full coverage
• Include migration files in the scan path for safety analysis
• Share application query code so the agent can verify index coverage
• Run before merging schema changes to catch issues early
• For the deepest analysis, also share your drizzle.config.ts for dialect detection
• Combine with application code analysis to identify unused columns and indexes