JSON Columns in Kysely: jsonb, Path Queries, and TypeScript Safety

Written and reviewed by the Jsonic editorial team — every guide is verified against the official spec or runtime before publication.

Last updated: May 27, 2026

Kysely is a type-safe TypeScript SQL query builder — not an ORM — that generates precise TypeScript types from your database schema. For JSON columns, Kysely has no built-in JSON path operator API; use the sql tagged template literal for raw SQL fragments. Define metadata: ColumnType<ProductMetadata, string, string> in your database interface — ColumnType<SelectType, InsertType, UpdateType> lets you say the column reads as ProductMetadata (after driver parsing) but requires JSON.stringify(value) on insert. PostgreSQL's pg driver automatically parses json and jsonb columns to JavaScript objects, so the select type is the parsed shape. Filter with sql`metadata->>'plan' = 'pro'` inside a .where() call. For containment queries: sql`metadata @> '{"active":true}'::jsonb`. Kysely's ExpressionBuilder method eb.ref('metadata') generates safe, injection-proof column references. This guide covers Kysely database interface types for JSON columns, sql template tag path queries, ColumnType for serialization, GIN index migrations, and the camelCase plugin for property mapping.

Database Interface Types for JSON Columns

Bottom line: Kysely's type system starts with a hand-written Database interface. For JSON columns, use ColumnType<Select, Insert, Update> to define 3 distinct TypeScript types for 3 distinct operations — reading, inserting, and updating.

The 3 type parameters of ColumnType<Select, Insert, Update> are: (1) what TypeScript infers when you SELECT the column, (2) what TypeScript requires when you INSERT, and (3) what TypeScript requires on UPDATE. For jsonb columns with the pg driver, the driver parses the stored binary value to a JavaScript object before Kysely sees it — so your Select type is the parsed interface directly, not a string. Because pg also accepts plain objects on write for jsonb columns, the simplest typing is ColumnType<ProductMetadata, ProductMetadata, ProductMetadata>, which Kysely lets you shorten to just ProductMetadata as the column type. The longer 3-parameter form is useful when insert and update types differ — for example, if insert requires a string because you're manually calling JSON.stringify. Generated<T> wraps columns that the database generates automatically (serial IDs, DEFAULT NOW() timestamps) — you don't supply them on insert.

import { ColumnType, Generated, Selectable, Insertable, Updateable } from "kysely"

// --- TypeScript interface for the JSON column's shape ---
interface ProductMetadata {
  plan: "free" | "pro" | "enterprise"
  active: boolean
  score: number
  tags: string[]
}

// --- Approach 1: pg driver parses jsonb automatically ---
// Use the interface directly — no JSON.stringify needed on insert or update
interface ProductTable {
  id: Generated<number>             // auto-generated, omit on insert
  name: string
  metadata: ProductMetadata         // shorthand: all 3 ColumnType params are the same
}

// --- Approach 2: explicit ColumnType (if using a driver that returns JSON as string) ---
interface ProductTableExplicit {
  id: Generated<number>
  name: string
  // Select: pg parses to object; Insert/Update: driver expects serialized string
  metadata: ColumnType<ProductMetadata, string, string>
}

// --- Register the table in the Database interface ---
interface Database {
  products: ProductTable
  users: UserTable
}

// --- Utility types Kysely exports for working with table rows ---
type Product   = Selectable<ProductTable>   // SELECT result — Generated<T> unwrapped to T
type NewProduct = Insertable<ProductTable>  // INSERT values — Generated<T> omitted
type ProductUpdate = Updateable<ProductTable> // UPDATE .set() — all fields optional

// --- The Kysely client typed to your Database ---
import { Kysely, PostgresDialect } from "kysely"
import { Pool } from "pg"

const db = new Kysely<Database>({
  dialect: new PostgresDialect({
    pool: new Pool({ connectionString: process.env.DATABASE_URL }),
  }),
})

JSON Path Queries with the sql Template Tag

Bottom line: Kysely does not abstract JSON path operators. Write them in sql tagged template literals inside .where(), .orderBy(), and .select(). Template substitutions become parameterized query placeholders — not string concatenation — so they are safe from SQL injection.

PostgreSQL provides 2 JSON text-extraction operators: -> returns a JSON-typed value and ->> returns a text string suitable for string comparison in .where(). For nested paths, chain them: metadata->'address'->>'city'. The @> containment operator checks whether the left jsonb value contains the right — useful for querying by a subset of JSON without extracting a single key. Use eb.ref('metadata') from theExpressionBuilder to reference a column inside a sql expression in a way Kysely can type-check. For MySQL, use JSON_EXTRACT(col, '$.key').

import { db } from "./db"
import { sql } from "kysely"

// --- Basic path query: ->> extracts value as text ---
const proProducts = await db
  .selectFrom("products")
  .selectAll()
  .where(sql`metadata->>'plan'`, "=", "pro")
  .execute()

// --- eb.ref() for type-checked column references inside sql ---
const withScore = await db
  .selectFrom("products")
  .selectAll()
  .where(({ eb }) =>
    eb(sql`${eb.ref("metadata")}->>'plan'`, "=", "pro")
  )
  .execute()

// --- Containment query: @> (jsonb only, uses GIN index) ---
// Finds rows where metadata contains { active: true }
const activeProducts = await db
  .selectFrom("products")
  .selectAll()
  .where(sql`metadata @> ${JSON.stringify({ active: true })}::jsonb`)
  .execute()

// --- Order by a JSON subfield ---
const ranked = await db
  .selectFrom("products")
  .selectAll()
  .orderBy(sql`metadata->>'score'`, "desc")
  .execute()

// --- Select a JSON subfield as a typed column ---
const plans = await db
  .selectFrom("products")
  .select([
    "id",
    "name",
    sql<string>`metadata->>'plan'`.as("plan"),  // inferred as string
  ])
  .execute()
// plans[0].plan is typed as string

// --- Nested path ---
const cities = await db
  .selectFrom("users")
  .select(sql<string>`metadata->'address'->>'city'`.as("city"))
  .execute()

// --- MySQL equivalent ---
// .where(sql`JSON_EXTRACT(metadata, '$.plan') = 'pro'`)
// .where(sql`JSON_UNQUOTE(JSON_EXTRACT(metadata, '$.plan'))`, "=", "pro")

Inserting and Updating JSON Columns

Bottom line: Insert a plain JavaScript object typed as ProductMetadata — thepg driver serializes it. For atomic partial updates without a read round-trip, use jsonb_set or the || merge operator inside a sql template.

When you type the column as ProductMetadata (all 3 ColumnType parameters identical), TypeScript checks the inserted object's shape at compile time and the pg driver handles serialization to jsonb automatically — no as any cast needed. A full document replace — read, merge in JavaScript, write back — is simple but requires 2 round-trips and is unsafe under concurrent writes. PostgreSQL's jsonb_set(target, path_array, new_value) replaces a single key atomically in 1 round-trip. The || merge operator merges a partial object, preserving all other keys. The - operator removes a key by name.

import { db } from "./db"
import { sql } from "kysely"

// --- Insert: pg driver handles serialization automatically ---
await db
  .insertInto("products")
  .values({
    name: "Widget",
    metadata: { plan: "pro", active: true, score: 9.5, tags: ["new"] },
  })
  .execute()

// --- Full document replace (2 round-trips, safest for simple cases) ---
async function upgradePlan(id: number, plan: "pro" | "enterprise") {
  const product = await db
    .selectFrom("products")
    .where("id", "=", id)
    .selectAll()
    .executeTakeFirstOrThrow()

  await db
    .updateTable("products")
    .set({ metadata: { ...product.metadata, plan } })
    .where("id", "=", id)
    .execute()
}

// --- Atomic subfield update: jsonb_set (1 round-trip) ---
await db
  .updateTable("products")
  .set({
    metadata: sql`jsonb_set(metadata, '{plan}', ${JSON.stringify("enterprise")}::jsonb)`,
  })
  .where("id", "=", 1)
  .execute()

// --- Merge a partial object: || operator preserves other keys ---
await db
  .updateTable("products")
  .set({
    metadata: sql`metadata || ${JSON.stringify({ active: false, score: 7 })}::jsonb`,
  })
  .where("id", "=", 1)
  .execute()

// --- Delete a key from jsonb ---
await db
  .updateTable("products")
  .set({
    metadata: sql`metadata - 'legacyField'`,
  })
  .where("id", "=", 1)
  .execute()

// --- Append to a jsonb array without reading first ---
await db
  .updateTable("products")
  .set({
    metadata: sql`jsonb_set(metadata, '{tags}', (metadata->'tags') || '["featured"]'::jsonb)`,
  })
  .where("id", "=", 1)
  .execute()

Migrations with Kysely Migrator

Bottom line: Kysely's Migrator manages schema changes with plain TypeScript files. Use db.schema.createTable() with addColumn('metadata', 'jsonb') for the column, then db.schema.createIndex().using('gin') for a GIN index in the same migration.

Each migration file exports 2 functions: up (apply the change) and down (revert it). Kysely tracks which migrations have run in a kysely_migration table it creates automatically. FileMigrationProvider reads migration files from a directory — name them with a timestamp prefix so they run in order (e.g., 2026_05_27_add_products.ts). For jsonb_path_ops GIN indexes — smaller, faster for @>, but no ? support — use a raw sql expression because Kysely's schema builder does not expose index operator classes directly.

// migrations/2026_05_27_add_products.ts
import { Kysely, sql } from "kysely"

export async function up(db: Kysely<any>): Promise<void> {
  // 1. Create the products table with a jsonb column
  await db.schema
    .createTable("products")
    .addColumn("id", "serial", col => col.primaryKey())
    .addColumn("name", "varchar(255)", col => col.notNull())
    .addColumn("metadata", "jsonb", col => col.defaultTo(sql`'{}'`))
    .execute()

  // 2. GIN index using Kysely's schema builder
  await db.schema
    .createIndex("idx_products_metadata_gin")
    .on("products")
    .using("gin")
    .column("metadata")
    .execute()

  // 3. jsonb_path_ops GIN index (smaller, @> only) — requires raw sql
  await sql`
    CREATE INDEX idx_products_metadata_path_ops
    ON products USING GIN (metadata jsonb_path_ops)
  `.execute(db)
}

export async function down(db: Kysely<any>): Promise<void> {
  await db.schema.dropTable("products").execute()
}

// ---

// Adding a jsonb column to an existing table
export async function up(db: Kysely<any>): Promise<void> {
  await db.schema
    .alterTable("users")
    .addColumn("preferences", "jsonb")
    .execute()
}

// ---

// Running migrations in your application startup
import { Migrator, FileMigrationProvider } from "kysely"
import * as path from "path"
import * as fs from "fs/promises"

const migrator = new Migrator({
  db,
  provider: new FileMigrationProvider({
    fs,
    path,
    migrationFolder: path.join(__dirname, "migrations"),
  }),
})

const { error, results } = await migrator.migrateToLatest()

results?.forEach(result => {
  if (result.status === "Success") {
    console.log(`Migration ${result.migrationName} applied`)
  } else if (result.status === "Error") {
    console.error(`Migration ${result.migrationName} failed`)
  }
})

if (error) throw error

TypeScript End-to-End: Inferring Result Types

Bottom line: Kysely infers return types from the query structure. selectAll() returns Promise<Selectable<ProductTable>[]>. Narrowed selects produce objects with only the selected columns. The CamelCasePlugin maps snake_case DB columns to camelCase TypeScript properties across all queries.

Kysely exports 3 utility types for working with table rows: Selectable<T> unwraps Generated<T> to T (the column is always present in SELECT results); Insertable<T> omits Generated<T> columns and marks nullable columns optional; Updateable<T> makes all columns optional for partial updates. These 3 types cover the 3 shapes your application code works with — reads, inserts, and updates — without any duplication. The CamelCasePlugin translates snake_case to camelCase bidirectionally: queries read from TypeScript camelCase property names, and the plugin converts them to SQL column names before sending the query.

import { Kysely, PostgresDialect, CamelCasePlugin } from "kysely"
import { Selectable, Insertable, Updateable } from "kysely"
import { Pool } from "pg"

// --- CamelCasePlugin: snake_case DB columns → camelCase TypeScript ---
const db = new Kysely<Database>({
  dialect: new PostgresDialect({
    pool: new Pool({ connectionString: process.env.DATABASE_URL }),
  }),
  plugins: [new CamelCasePlugin()],
  // Now: DB column "user_id" → TypeScript property "userId"
})

// --- Utility types for application code ---
type Product       = Selectable<ProductTable>   // what .selectAll().execute() returns
type NewProduct    = Insertable<ProductTable>   // Generated<> omitted, nullables optional
type ProductUpdate = Updateable<ProductTable>   // all fields optional

// --- Fully typed helper function ---
async function getProduct(id: number): Promise<Product | undefined> {
  return db
    .selectFrom("products")
    .where("id", "=", id)
    .selectAll()
    .executeTakeFirst()
}

// --- Narrowed select: result type has only the selected columns ---
const rows = await db
  .selectFrom("products")
  .select(["id", "name"])
  .execute()
// typeof rows = Array<{ id: number; name: string }>

// --- Mixed select with a sql expression ---
const withPlan = await db
  .selectFrom("products")
  .select([
    "id",
    "name",
    sql<string>`metadata->>'plan'`.as("plan"),
  ])
  .execute()
// typeof withPlan = Array<{ id: number; name: string; plan: string }>

// --- executeTakeFirstOrThrow: throws if 0 rows ---
async function requireProduct(id: number): Promise<Product> {
  return db
    .selectFrom("products")
    .where("id", "=", id)
    .selectAll()
    .executeTakeFirstOrThrow()
}

// --- Custom KyselyPlugin for transformations ---
import type { KyselyPlugin, PluginTransformQueryArgs, PluginTransformResultArgs } from "kysely"

class LoggingPlugin implements KyselyPlugin {
  transformQuery(args: PluginTransformQueryArgs) {
    // inspect or modify the query AST before execution
    return args.node
  }
  async transformResult(args: PluginTransformResultArgs) {
    // inspect or modify the result rows after execution
    return args.result
  }
}

Comparison with Drizzle and Knex for JSON

Bottom line: Kysely, Drizzle, and Knex each take a different approach to JSON columns and TypeScript types. Kysely gives the tightest compile-time guarantees without a build step. Drizzle is schema-file-driven with ORM features. Knex has no TypeScript types by default.

Kysely's Database interface is written by hand — 0 codegen steps — and Kysely derives all query return types from it at compile time. A typo in a column name or a type mismatch in a .where() clause is a TypeScript error, not a runtime crash. Kysely has no ORM features (no relations, no eager loading, no active record pattern) — it is a query builder and nothing more. Drizzle is closer to an ORM: you define tables in a schema file, run drizzle-kit for migrations, and use relational query helpers for joins. Knex requires Objection.js on top to add model-level features, and you write TypeScript types separately. For JSON path queries, all 3 use raw SQL via sql or knex.raw().

// ── The same JSON path query in Kysely, Drizzle, and Knex ──

// --- Kysely: typed from Database interface, sql for path operators ---
import { db } from "./kysely-client"
import { sql } from "kysely"

const kyselyResult = await db
  .selectFrom("products")
  .selectAll()
  .where(sql`metadata->>'plan'`, "=", "pro")
  .execute()
// kyselyResult is typed as Selectable<ProductTable>[]

// --- Drizzle: typed from schema file, sql for path operators ---
import { drizzleDb } from "./drizzle-client"
import { sql } from "drizzle-orm"
import { productsTable } from "./drizzle-schema"

const drizzleResult = await drizzleDb
  .select()
  .from(productsTable)
  .where(sql`${productsTable.metadata}->>'plan' = 'pro'`)
// drizzleResult is typed from the .$type<>() annotation

// --- Knex: no types by default, knex.raw() for path operators ---
import knex from "knex"
const knexDb = knex({ client: "pg", connection: process.env.DATABASE_URL })

const knexResult = await knexDb("products")
  .whereRaw("metadata->>'plan' = ?", ["pro"])
  .select("*")
// knexResult is typed as any[] — no compile-time safety

// ── Kysely adapters ──
// pg (PostgreSQL):
import { PostgresDialect } from "kysely"

// mysql2 (MySQL):
import { MysqlDialect } from "kysely"
import { createPool } from "mysql2"

const mysqlDb = new Kysely<Database>({
  dialect: new MysqlDialect({ pool: createPool(process.env.DATABASE_URL!) }),
})

// Turso/libSQL (edge):
// import { LibsqlDialect } from "@libsql/kysely-libsql"
// const tursoDb = new Kysely<Database>({
//   dialect: new LibsqlDialect({ url: process.env.TURSO_URL!, authToken: "..." }),
// })

FAQ