drizzle-schema-analyzer

by charlie-morrison

Install this Skill

curl -fsSL -o drizzle-schema-analyzer.zip https://skill.seembox.com/api/v1/skills/drizzle-schema-analyzer/download && unzip drizzle-schema-analyzer.zip

Download drizzle-schema-analyzer.zip

SKILL.md About

---
name: cm-drizzle-schema-analyzer
description: Analyze Drizzle ORM schema definitions for best practices, performance, type safety, and migration risks. Checks index strategy, relation definitions, column types, constraint completeness, query patterns, and migration safety. Use when asked to review Drizzle schemas, audit database design, check Drizzle best practices, optimize query performance, review migrations, or troubleshoot type errors. Triggers on "drizzle", "drizzle orm", "drizzle schema", "drizzle migration", "drizzle relations", "drizzle index", "drizzle audit", "drizzle review", "drizzle performance", "drizzle types".
metadata:
  tags: ["drizzle", "orm", "database", "typescript", "schema", "migration", "postgresql", "mysql", "sqlite", "type-safety"]
---

# Drizzle Schema Analyzer

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:

```bash
# 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:**

```typescript
// 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:

```typescript
// 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

_meta.json Metadata

{
  "ownerId": "kn7d5eszdfwftk153ymhdm4qhs83qsqy",
  "publishedAt": 1777607718585,
  "slug": "drizzle-schema-analyzer",
  "version": "1.0.0"
}