KYSELY.md

Migrations and Kysely-CTL

kysely-ctl should be used to make and run migrations. No code should be written that runs migrations in nodejs.

Plugins

The Kysely client should use (at least) the following plugins: CamelCasePlugin and JSONARrayPlugin.

CamelCasePlugin

Columns in the database use snake_case. The CamelCasePlugin automatically converts column names to camelCase in query results and back to snake_case when writing.

Write all TypeScript types and Kysely queries using camelCase — never reference snake_case column names directly.

JSONArrayPlugin (custom)

The pg driver serialises plain objects to JSON strings natively (via prepareObject) and deserialises jsonb query results automatically (via its OID 3802 type parser). The one gap is JavaScript arrays: pg formats them as PostgreSQL array literals ({el1,el2,...}) rather than JSON strings, which causes an invalid input syntax for type json error on jsonb columns that store JSON arrays.

JSONArrayPlugin fills that gap by serialising array values to JSON strings before they reach the driver. Together with pg's native behaviour, this means:

• jsonb columns can be typed as their actual TypeScript types (Address[], Settings, etc.) rather than string in the Kysely Database interface.
• There is no need to call JSON.stringify or JSON.parse manually anywhere in application code.

Use this plugin in every project. Copy the implementation verbatim into src/server/db/jsonArrayPlugin.ts:

import {
  OperationNodeTransformer,
  type KyselyPlugin,
  type PluginTransformQueryArgs,
  type PluginTransformResultArgs,
  type PrimitiveValueListNode,
  type QueryResult,
  type RootOperationNode,
  type UnknownRow,
  type ValueNode,
} from "kysely";

/**
 * Serializes JavaScript arrays to JSON strings before they reach the `pg` driver.
 *
 * `pg` handles plain objects correctly (via `prepareObject` → `JSON.stringify`) and
 * deserializes `jsonb` results automatically. However, it formats JavaScript arrays as
 * PostgreSQL array literals (`{el1,el2,...}`) rather than JSON strings, which causes
 * "invalid input syntax for type json" errors on `jsonb` columns that store JSON arrays.
 *
 * Two code paths require handling:
 * - `transformPrimitiveValueList`: covers INSERT ... VALUES (...), used by Kysely for
 *   both single and multi-row inserts. Individual values do not pass through `transformValue`
 *   in this path.
 * - `transformValue`: covers UPDATE SET clauses and `sql` template literal interpolations.
 */
class JsonArrayTransformer extends OperationNodeTransformer {
  // Handles array values in UPDATE SET clauses and sql`` template literals
  protected override transformValue(node: ValueNode): ValueNode {
    return Array.isArray(node.value)
      ? { ...node, value: JSON.stringify(node.value) }
      : node;
  }

  // Handles array values in INSERT ... VALUES (...) — both single and multi-row
  protected override transformPrimitiveValueList(
    node: PrimitiveValueListNode,
  ): PrimitiveValueListNode {
    return {
      ...node,
      values: node.values.map((v) =>
        Array.isArray(v) ? JSON.stringify(v) : v,
      ),
    };
  }
}

const transformer = new JsonArrayTransformer();

export class JSONArrayPlugin implements KyselyPlugin {
  transformQuery(args: PluginTransformQueryArgs): RootOperationNode {
    return transformer.transformNode(args.node);
  }

  async transformResult(
    args: PluginTransformResultArgs,
  ): Promise<QueryResult<UnknownRow>> {
    return args.result;
  }
}

Assume all object/array fields in the database are jsonb columns. Do not pass plain objects to non-jsonb columns.

Database types

Database types should extend the corresponding Zod-inferred type, overriding only properties that differ at the database layer (e.g. columns with database-generated defaults can use the Generated<...> wrapper). jsonb columns use their TypeScript types directly — do not use string as a stand-in.

E.g.

import User from '@/models/User'
export interface UserTable extends Omit<User, 'id' | 'createdAt' | 'updatedAt'> {
  id: Generated<string>;        // Generated by the database
  passwordHash: string | null;  // Not surfaced to application layer
  createdAt: Generated<Date>;   // Generated by the database
  updatedAt: Generated<Date>;   // Generated by the database
}