kysely-generic-sqlite

Generic Kysely dialect for SQLite, supporting execution in the main thread or a worker.

Install

``sh
pnpm install kysely kysely-generic-sqlite
`

Usage

This is a abstraction for sqlite dialect, so the dialect is not worked out-of-box.

Below is the guide to create a new dialect, use better-sqlite3 as example.

$3

First of all, you need to create a function that returns a IGenericSqlite

`ts
import type { Database } from 'better-sqlite3'
import type { IGenericSqlite } from 'kysely-generic-sqlite'

function createSqliteExecutor(db: Database): IGenericSqlite {
const getStmt = (sql: string) => db.prepare(sql)

return {
db,
query: (_isSelect, sql, parameters) => {
const stmt = getStmt(sql)
if (stmt.reader) {
return { rows: stmt.all(parameters) }
}
const { changes, lastInsertRowid } = stmt.run(parameters)
return {
rows: [],
numAffectedRows: parseBigInt(changes),
insertId: parseBigInt(lastInsertRowid),
}
},
close: () => db.close(),
iterator: (isSelect, sql, parameters) => {
if (!isSelect) {
throw new Error('Only support select in stream()')
}
return getStmt(sql).iterate(parameters) as any
},
}
}
`

For client that does not support stmt.reader, there are 2 utils with same parameters:

- buildQueryFn: Support returning, get insertId and numAffectedRows by calling select 1
- buildQueryFnAlt: Do not support returning

`ts
import type { IGenericSqlite } from 'kysely-generic-sqlite'

import Database from 'bun:sqlite'
import { parseBigInt } from 'kysely-generic-sqlite'

function createSqliteExecutor(db: Database, cache: boolean): IGenericSqlite {
const fn = cache ? 'query' : 'prepare'
const getStmt = (sql: string) => dbfn

return {
db,
query: buildQueryFn({
all: (sql, parameters) => getStmt(sql).all(...parameters || []),
run: (sql, parameters) => {
const { changes, lastInsertRowid } = getStmt(sql).run(...parameters || [])
return {
insertId: parseBigInt(lastInsertRowid),
numAffectedRows: parseBigInt(changes),
}
},
}),
close: () => db.close(),
}
}
`

$3

To create a dialect that run SQLs in current thread, you can use built-in dialect GenericSqliteDialect:

`ts
import type { DatabaseConnection } from 'kysely'

import { CompiledQuery, Kysely } from 'kysely'
import { GenericSqliteDialect } from 'kysely-generic-sqlite'

const dialect = new GenericSqliteDialect(
createSqliteExecutor,
// optional on created callback
(conn: DatabaseConnection) => {
await conn.execute(CompiledQuery.raw('PRAGMA optimize'))
}
)

const db = new Kysely({ dialect })
`

$3

To create a dialect that run SQLs in nodejs's worker_threads, you can use built-in dialect GenericSqliteWorkerDialect:

`ts
import { Worker } from 'node:worker_threads'

import { GenericSqliteWorkerDialect } from 'kysely-generic-sqlite/worker'
import { createNodeWorkerExecutor } from 'kysely-generic-sqlite/worker-helper-node'

const worker = new Worker('./worker.js')
const dialect = new GenericSqliteWorkerDialect(
createNodeWorkerExecutor({
worker,
// Optional extra data.
// You can also transport data using require('node:worker_threads').workerData
// or directly define in worker file
data: { fileName: ':memory:' },
}),
// optional on created callback
(conn: DatabaseConnection) => {
await conn.execute(CompiledQuery.raw('PRAGMA optimize'))
}
)
`

in worker.ts

`ts
import type { Database } from 'better-sqlite3'

import BetterSqlite3Database from 'better-sqlite3'
import { createNodeOnMessageCallback } from 'kysely-generic-sqlite/worker-helper-node'

createNodeOnMessageCallback<{ fileName: string }>(
data => createSqliteExecutor(data.fileName)
)
`

$3

To create a dialect that run SQLs in web worker, you can use built-in dialect GenericSqliteWorkerDialect:

`ts
import { GenericSqliteWorkerDialect } from 'kysely-generic-sqlite/worker'
import { createWebWorkerExecutor } from 'kysely-generic-sqlite/worker-helper-web'
import { mitt } from 'zen-mitt'

const worker = new Worker(new URL('./worker.js', import.meta.url), { type: 'module' })
const dialect = new GenericSqliteWorkerDialect(
createWebWorkerExecutor({
worker,
// Event Emitter to handle worker messages
mitt: mitt(),
// Optional extra data.
data: { fileName: ':memory:' },
}),
// optional on created callback
(conn: DatabaseConnection) => {
await conn.execute(CompiledQuery.raw('PRAGMA optimize'))
}
)
`

in worker.ts

`ts
import { createWebOnMessageCallback } from 'kysely-generic-sqlite/worker-helper-web'

async function createSqliteExecutor(fileName: string) {
// your implemention...
}

createWebOnMessageCallback<{ fileName: string }>(
(data) => {
const db = createSqliteExecutor(data.fileName)

// more handle with db instance...

return db
}
)
`

$3

dialect:

`ts
import { GenericSqliteWorkerDialect } from 'kysely-generic-sqlite/worker'
import { createNodeMitt, handleNodeWorker } from 'kysely-generic-sqlite/worker-helper-node'

const outer = createNodeMitt()
const dialect = new GenericSqliteWorkerDialect(
async () => {
const m = createNodeMitt()
m.on('test', console.log)
outer.on('call-test', () => m.emit('test', 'your-data'))
return {
worker: new Worker('./worker.js'),
mitt: m,
handle: handleNodeWorker
}
}
)
`

in worker.ts

`ts
import type { Database } from 'better-sqlite3'

import { createNodeOnMessageCallback } from 'kysely-generic-sqlite/worker-helper-node'

createNodeOnMessageCallback<{}, Database>(
data => createSqliteExecutor(':memory:'),
(exec, type, data1, data2, data3) => {
if (type === 'test') {
exec.db.pragma('optimize')
console.log(data1) // 'your-data'
return 'CUSTOM'
}
}
)
`

$3

`ts
import { BaseSqliteDialect } from 'kysely-generic-sqlite'

const dialect = new BaseSqliteDialect(() => new YourCustomDriver())
``

Dialects Based On This dialect

- kysely-dialect-tauri
- kysely-sqlite-worker
- kysely-bun-worker
- kysely-wasqlite-worker
- kysely-wasm