kysely-sqlite-json

Small Kysely plugin for SQLite that:

• serializes plain objects and arrays before insert and update
• parses JSON-looking string values back into JavaScript objects and arrays on reads

It is useful when SQLite columns are stored as TEXT, but you want to work with JSON values in application code.

Install

npm install kysely-sqlite-json kysely

For local examples and tests in this repo:

npm install

Usage

import Database from 'better-sqlite3'
import { Kysely, SqliteDialect } from 'kysely'
import { DatabaseJsonPlugin } from 'kysely-sqlite-json'

/**
 * @typedef {{
 *   id: number
 *   profile: string | { name: string, tags: string[] }
 *   settings: string | { theme: string, alerts: boolean }
 * }} PersonTable
 */

/** @type {import('kysely').Kysely<{ person: PersonTable }>} */
const db = new Kysely({
  dialect: new SqliteDialect({
    database: new Database(':memory:'),
  }),
  plugins: [new DatabaseJsonPlugin()],
})

await db.schema
  .createTable('person')
  .addColumn('id', 'integer', (col) => col.primaryKey().autoIncrement())
  .addColumn('profile', 'text', (col) => col.notNull())
  .addColumn('settings', 'text')
  .execute()

await db
  .insertInto('person')
  .values({
    profile: { name: 'Ada', tags: ['sql', 'json'] },
    settings: { theme: 'light', alerts: true },
  })
  .execute()

const row = await db.selectFrom('person').selectAll().executeTakeFirstOrThrow()

console.log(row.profile.name)
console.log(row.settings.theme)

What gets serialized

Serialized on writes:

• plain objects like { enabled: true }
• arrays like ['a', 'b']

Left untouched:

• strings
• numbers
• booleans
• null
• class instances like new Date()

Behavior notes

• Reads attempt JSON.parse only for trimmed strings that start with { or [.
• Invalid JSON-looking strings are returned unchanged.
• The plugin transforms insert and update query values, then post-processes result rows.
• Read parsing is applied to every selected column that contains a JSON-looking string.

Exports

import { DatabaseJsonPlugin } from 'kysely-sqlite-json'

Examples

• examples/basic.js
• examples/manual-json-read.js

Run it with:

npm install
node examples/basic.js
node examples/manual-json-read.js

Testing

npm test

This runs both runtime tests and a TypeScript API check against the published declarations.

License

MIT