drizzle-jsonb

A CLI tool that automatically generates SQL data migrations for JSONB columns in Drizzle ORM projects.

Drizzle Kit handles structural schema changes (adding columns, changing types, etc.) but has no awareness of the data inside JSONB columns. When your TypeScript type passed to .jsonb().$type<T>() changes shape, existing rows are left with stale data. drizzle-jsonb fills that gap.

Requires Drizzle ORM v1 beta (1.0.0-beta.x) or later. Not compatible with the stable 0.x release.

Currently supported dialects: PostgreSQL. The tool is architected to support additional dialects (SQLite, MySQL) in future releases — see Adding a New Dialect.

How It Works

1. You define your JSONB column types using Zod schemas co-located with your Drizzle schema
2. On first run, drizzle-jsonb init snapshots the current shape of every tracked JSONB column
3. When your Zod schema changes, drizzle-jsonb generate diffs against the snapshot, prompts you for any ambiguous changes (renames, removals, type coercions), and writes a SQL migration file into your existing drizzle migrations folder
4. drizzle-kit migrate picks up and runs the generated file automatically — no separate migration runner needed

Installation

npm install --save-dev drizzle-jsonb
# or
pnpm add -D drizzle-jsonb

Setup

1. Define your JSONB types with Zod

Co-locate a Zod schema with each JSONB column you want to track. The naming convention is <columnName>Schema:

// src/db/schema.ts
import { pgTable, serial, jsonb } from 'drizzle-orm/pg-core'
import { z } from 'zod'

export const profileSchema = z.object({
  firstName: z.string(),
  lastName: z.string(),
  age: z.number().optional(),
})

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  profile: jsonb('profile').$type<z.infer<typeof profileSchema>>(),
})

The column is named profile, so the paired Zod schema must be named profileSchema. This convention is configurable — see Configuration.

2. Initialise

npx drizzle-jsonb init

This reads your drizzle.config.ts, detects the dialect and migrations folder automatically, creates .drizzle-jsonb/, and snapshots the current shape of all tracked JSONB columns.

✔ Detected dialect: postgresql
✔ Detected migrations folder: ./drizzle
✔ Found 1 tracked JSONB column: users.profile
✔ Snapshot written to .drizzle-jsonb/snapshots/latest.json

3. Generate migrations when your types change

When you update a Zod schema, run:

npx drizzle-jsonb generate

The CLI diffs against the last snapshot, handles safe changes automatically, and prompts you for anything ambiguous:

✔ Detected dialect: postgresql (from drizzle.config.ts)

  users.profile
  ──────────────────────────────────────────
  + firstName   string    (new field)
  + lastName    string    (new field)
  - name        string    (removed)

? Was 'name' renamed to 'firstName'? Yes

✔ Generated: ./drizzle/20260617100000_jsonb-migration/migration.sql
✔ Snapshot updated: .drizzle-jsonb/snapshots/latest.json

4. Apply with drizzle-kit

npx drizzle-kit migrate

drizzle-kit picks up the generated file automatically — it lives in your migrations folder following the standard YYYYMMDDHHmmss_<name> convention used by drizzle v1 beta.

Commands

Use the local package binary through npm scripts, npx, or your package manager's equivalent:

npx drizzle-jsonb --help
npx drizzle-jsonb --version

drizzle-jsonb init

One-time setup. Reads your existing drizzle.config.ts, resolves the PostgreSQL dialect and migrations folder, discovers paired JSONB/Zod schemas, creates .drizzle-jsonb/, and writes the initial snapshot.

npx drizzle-jsonb init

init has no command-specific options. If a snapshot already exists, it is overwritten with the currently discovered shape. Commit .drizzle-jsonb/snapshots/ with your migrations so future generate runs diff against the same baseline on every machine.

drizzle-jsonb generate

Diffs the current Zod schemas against .drizzle-jsonb/snapshots/latest.json, generates a SQL data migration when needed, and advances the snapshot after successful generation.

npx drizzle-jsonb generate

Options:

| Option | Description | |---|---| | --name <name> | Migration folder suffix. Defaults to jsonb-migration, producing folders like 20260617100000_jsonb-migration. | | --dry-run, -d | Print the migration SQL preview without writing migration files or updating the snapshot. |

Examples:

# Generate db/migration SQL and update the drizzle-jsonb snapshot
npx drizzle-jsonb generate

# Use a custom migration folder suffix
npx drizzle-jsonb generate --name normalize-profile-jsonb

# Preview SQL and prompts without writing files
npx drizzle-jsonb generate --dry-run

Change handling:

| Change | Behaviour | |---|---| | New field added | Auto-generates jsonb_set with a type-appropriate default. Optional fields use JSON null; Zod .default(), .prefault(), and .catch() values are used when JSON-safe. | | Field removed | Prompts to confirm, then generates SQL that removes the key from existing rows. Declining leaves existing data untouched while the snapshot advances. | | Field renamed | Prompts Was 'old' renamed to 'new'?; if yes, generates rename SQL; if no, treats the change as add + prompted remove. | | Rename + type/value changed | Prompts for rename intent, then suggests a PostgreSQL JSONB coercion expression so the old value can be moved safely. You can accept, edit, or clear it. | | Type/value changed | Suggests a PostgreSQL JSONB coercion expression when possible. Unresolved changes block migration and snapshot writes. | | Optional/default metadata changed | No SQL is needed, but the change is tracked so the snapshot advances to the new Zod metadata. | | Nested object added | Auto-generates parent-object creation followed by nested leaf updates. |

generate is intentionally interactive when data could be lost or transformed: renames, removals, and type/value changes require an explicit decision in a terminal. Add-only and metadata-only changes can run without prompts. There is no --non-interactive mode; use status for CI checks and run generate in a terminal when prompts are required.

Coercion suggestions are PostgreSQL JSONB expressions. They are meant as a safe starting point, not a guarantee that the default data policy is correct for your application. For string → number and string → boolean, generated suggestions avoid cast failures by writing JSON null for uncastable values. Edit the expression when invalid existing values should map to a specific fallback instead.

drizzle-jsonb status

Shows tracked JSONB columns, pending schema diffs, skipped unpaired JSONB columns, and generated drizzle-jsonb migration folders.

npx drizzle-jsonb status

status has no command-specific options and does not write files. It is the safest command to run in CI before publishing or deploying.

  Tracked columns
  ────────────────────────────────────────
  users.profile                ✔ up to date
  posts.metadata               ✖ 2 change(s) detected
  events.rawPayload            ⚠ no paired Zod schema

  Generated jsonb migrations
  ────────────────────────────────────────
  20260617100000_jsonb-migration

Configuration

drizzle-jsonb reads your existing drizzle.config.ts automatically — no duplication needed. It uses:

• dialect — to select the correct SQL dialect (auto-detected, no manual config required)
• out — to know where to write the generated migration folder
• schema — to discover your schema files and find JSONB columns

For additional options, create an optional drizzle-jsonb.config.ts at your project root:

// drizzle-jsonb.config.ts
export default {
  // Override schema file discovery (supports globs)
  // Default: inherits 'schema' from drizzle.config.ts
  schemaPaths: ['./src/**/*.schema.ts'],

  // How Zod schemas are paired with JSONB columns
  // 'suffix' (default): column 'profile' → 'profileSchema'
  // 'export-map': provide an explicit map (see below)
  schemaConvention: 'suffix',

  // Rows to update per batch for large tables
  // Default: 1000
  batchSize: 1000,
}

Using export-map convention if your naming doesn't follow the suffix pattern:

export default {
  schemaConvention: 'export-map',
  columnSchemaMap: {
    'users.profile': 'profileShape',
    'posts.metadata': 'postMetaSchema',
  },
}

Example: End-to-End Walkthrough

Before — your original schema:

// src/db/schema.ts
export const profileSchema = z.object({
  name: z.string(),
  email: z.string(),
})

After — you split name into firstName + lastName and add avatarUrl:

// src/db/schema.ts
export const profileSchema = z.object({
  firstName: z.string(),
  lastName: z.string(),
  email: z.string(),
  avatarUrl: z.string().optional(),
})

Running drizzle-jsonb generate detects:

• name removed, firstName added → prompts for rename → you say yes
• lastName added → auto, defaults to ""
• avatarUrl added → auto, optional so it is backfilled as JSON null

Generated SQL:

-- drizzle-jsonb: users.profile
-- Generated: 2026-06-17T10:00:00Z
-- Changes: rename name→firstName, add lastName (default ""), add avatarUrl (optional)

-- Rename 'name' to 'firstName'
UPDATE "users"
SET "profile" = jsonb_set("profile" - 'name', '{firstName}', "profile"->'name')
WHERE "profile" ? 'name';

-- Add 'lastName' with empty string default
UPDATE "users"
SET "profile" = jsonb_set(CASE WHEN jsonb_typeof("profile") = 'object' THEN "profile" ELSE '{}'::jsonb END, '{lastName}', '""'::jsonb, true)
WHERE ("profile" IS NULL OR NOT "profile" ? 'lastName');

-- Add optional 'avatarUrl' with JSON null default
UPDATE "users"
SET "profile" = jsonb_set(CASE WHEN jsonb_typeof("profile") = 'object' THEN "profile" ELSE '{}'::jsonb END, '{avatarUrl}', 'null'::jsonb, true)
WHERE ("profile" IS NULL OR NOT "profile" ? 'avatarUrl');

Snapshot Format

Snapshots are stored in .drizzle-jsonb/snapshots/latest.json. A history of every snapshot is kept in .drizzle-jsonb/snapshots/history/<YYYYMMDDHHmmss>.json for auditability.

{
  "version": 1,
  "generatedAt": "2026-06-17T10:00:00Z",
  "dialect": "postgresql",
  "tables": {
    "users": {
      "profile": {
        "dbName": "profile",
        "fields": {
          "firstName": { "type": "string", "optional": false },
          "lastName":  { "type": "string", "optional": false },
          "age":       { "type": "number", "optional": true }
        }
      }
    }
  }
}

Commit .drizzle-jsonb/snapshots/ to version control alongside your drizzle migrations folder.

Dependencies

drizzle-jsonb uses the same CLI libraries as drizzle-kit itself, so the tool feels native to the Drizzle ecosystem:

• @drizzle-team/brocli — command registration and argument parsing. The same library drizzle-kit v1 beta uses internally. Fully type-safe, zero-dependency, with built-in option validation:

  import { command, string, boolean, run } from '@drizzle-team/brocli'
  
  const generate = command({
    name: 'generate',
    options: {
      name: string().desc('Migration name'),
      dryRun: boolean().default(false).desc('Preview without writing files'),
    },
    handler: (opts) => { ... },
  })
  
  run([init, generate, status])

• hanji — interactive prompts and spinners. Also by the Drizzle Team, used in drizzle-kit for the rename/confirm prompts you see during drizzle-kit generate. Keeps the UX consistent between the two tools.

• jiti — loads drizzle.config.ts and schema files at runtime without requiring a compile step.

• zod — introspecting JSONB type shapes at runtime via ._def.

• picocolors — terminal colours, matching drizzle-kit's minimal colour usage.

Project Structure

your-project/
├── drizzle/
│   ├── 20260610120000_init/
│   │   ├── migration.sql
│   │   └── snapshot.json
│   └── 20260617100000_jsonb-migration/    ← generated by drizzle-jsonb
│       └── migration.sql
├── .drizzle-jsonb/
│   └── snapshots/
│       ├── latest.json
│       └── history/
│           └── 20260617100000.json
├── src/
│   └── db/
│       └── schema.ts
├── drizzle.config.ts
└── drizzle-jsonb.config.ts                ← optional

Adding a New Dialect

drizzle-jsonb resolves the dialect once at startup from drizzle.config.ts and injects it through the entire pipeline. Core logic (snapshotting, diffing, classifying changes, prompting) is fully dialect-agnostic.

To add a new dialect, implement the JsonbDialect interface:

// src/dialects/types.ts
export interface JsonbDialect {
  readonly name: Dialect

  // Mutations — return a complete SQL statement
  setField(table: string, col: string, path: string[], valueExpr: string, condition?: string): string
  removeField(table: string, col: string, path: string[]): string
  renameField(table: string, col: string, from: string[], to: string[]): string[]

  // Predicates & reads — return SQL expression fragments
  hasField(col: string, path: string[]): string
  notHasField(col: string, path: string[]): string
  readField(col: string, path: string[]): string

  // Helpers
  ensureParents(table: string, col: string, path: string[]): string[]
  defaultLiteral(value: unknown): string
}

Every method returns a raw SQL string, and path is always an array so nested JSONB paths (['a', 'b', 'c']) work uniformly. renameField and ensureParents return multiple statements because a nested rename/add has to create intermediate objects first.

Then add a new file (e.g. src/dialects/sqlite.ts) implementing those methods with the correct SQL functions for that dialect, and register it in the dialect resolver:

// src/dialects/index.ts
import { postgres } from './postgres'
import { sqlite } from './sqlite'

const registry: Partial<Record<Dialect, JsonbDialect>> = {
  postgresql: postgres,
  sqlite,
}

export function resolveDialect(dialect: Dialect | undefined): JsonbDialect {
  const impl = dialect && registry[dialect]
  if (!impl) throw new Error(`Unsupported dialect: ${dialect}`)
  return impl
}

No changes are needed anywhere else in the codebase.

Limitations

• Only jsonb columns are tracked — json columns (which store raw text with no indexing or operators) are intentionally excluded
• drizzle-jsonb does not run migrations itself — it generates SQL that drizzle-kit migrate runs
• The tool does not modify drizzle-kit's own snapshot.json files
• There is no non-interactive generate mode for destructive or ambiguous changes. Run drizzle-jsonb status in CI and run generate interactively when prompts are needed
• Type coercion suggestions are conservative PostgreSQL expressions. Review them before applying migrations, especially casts like string → number or string → boolean; uncastable values default to JSON null unless you edit the expression

Why Not Just Edit the SQL Manually?

You can — and for one-off changes that is perfectly reasonable. drizzle-jsonb adds value when:

• You have multiple JSONB columns across multiple tables evolving over time
• You want the shape changes tracked in version control alongside your drizzle migrations
• You want the interactive rename detection to avoid accidental data loss (treating a rename as drop + add when it was meant to be a rename)
• You want the generated SQL to be correct and idempotent without having to remember the exact JSONB operator syntax every time