JSON and JSONB Columns in Drizzle ORM

JSON Columns in Drizzle

JSON columns are increasingly common in modern databases. Drizzle provides dialect-specific builders for JSON data that integrate with TypeScript's type system.

PostgreSQL: json() and jsonb()

PostgreSQL distinguishes between JSON (stored as text) and JSONB (stored in binary, supports indexing). Drizzle provides separate builders for each:

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  attributes JSON,
  metadata JSONB NOT NULL DEFAULT '{}'
);

import { json, jsonb, pgTable, serial } from "drizzle-orm/pg-core";

export const products = pgTable("products", {
  id: serial("id").primaryKey(),
  attributes: json("attributes"),
  metadata: jsonb("metadata").notNull().default({}),
});

MySQL: json()

MySQL has a single JSON type with binary storage (similar to PostgreSQL's JSONB):

CREATE TABLE settings (
  id INT AUTO_INCREMENT PRIMARY KEY,
  config JSON NOT NULL
);

import { json, mysqlTable, serial } from "drizzle-orm/mysql-core";

export const settings = mysqlTable("settings", {
  id: serial("id").primaryKey(),
  config: json("config").notNull(),
});

SQLite: text()

SQLite has no native JSON type. JSON data is stored as TEXT, and SQLite provides JSON functions for querying. The converter maps JSON columns to text() for SQLite:

import { integer, sqliteTable, text } from "drizzle-orm/sqlite-core";

export const settings = sqliteTable("settings", {
  id: integer("id").primaryKey(),
  config: text("config").notNull(),
});

Type-Safe JSON with Drizzle

One of Drizzle's strengths is the ability to type JSON columns. After conversion, you can add TypeScript generics:

interface ProductAttributes {
  color: string;
  size: string;
  weight: number;
}

export const products = pgTable("products", {
  id: serial("id").primaryKey(),
  attributes: json("attributes").$type<ProductAttributes>(),
  metadata: jsonb("metadata").$type<Record<string, unknown>>().notNull().default({}),
});

The .$type<T>() method gives you full TypeScript autocompletion and type checking when reading and writing JSON columns.

JSONB Indexing

If your PostgreSQL schema includes GIN indexes on JSONB columns, note that these must be handled separately with raw SQL migrations. Drizzle's schema definition does not directly support GIN index syntax.