SqliteDialect

SQLite dialect using the better-sqlite3 library

Overview

SQLite dialect that uses the better-sqlite3 library. This dialect is ideal for embedded databases, testing, and small-scale applications.

Source: src/dialect/sqlite/sqlite-dialect.ts:38

Installation

Install the required peer dependency:

npm install better-sqlite3
npm install --save-dev @types/better-sqlite3

Basic Usage

File Database

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

const db = new Kysely<DatabaseSchema>({
  dialect: new SqliteDialect({
    database: new Database('path/to/database.db')
  })
})

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

const db = new Kysely<DatabaseSchema>({
  dialect: new SqliteDialect({
    database: new Database(':memory:')
  })
})

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

// Database is created only when first query is executed
const db = new Kysely<DatabaseSchema>({
  dialect: new SqliteDialect({
    database: async () => new Database('path/to/database.db')
  })
})

Configuration

SqliteDialectConfig

Source: src/dialect/sqlite/sqlite-dialect-config.ts:6

database · SqliteDatabase | (() => Promise<SqliteDatabase>) · required

An SQLite Database instance or a function that returns one.

If a function is provided, it’s called once when the first query is executed.

See better-sqlite3 documentation for available database options.

onCreateConnection · (connection: DatabaseConnection) => Promise<void>

Called once when the first query is executed. This is a Kysely-specific feature and does not come from the better-sqlite3 module.

Useful for setting up database-level configuration like PRAGMA statements.

new SqliteDialect({
  database: new Database('db.sqlite'),
  onCreateConnection: async (connection) => {
    await connection.executeQuery(
      CompiledQuery.raw('PRAGMA foreign_keys = ON')
    )
  }
})

Database Options

When creating a better-sqlite3 Database instance, you can pass various options:

import Database from 'better-sqlite3'

const database = new Database('mydb.db', {
  readonly: false, // Open in read-only mode
  fileMustExist: false, // Throw error if file doesn't exist
  timeout: 5000, // Milliseconds to wait for lock before throwing SQLITE_BUSY
  verbose: console.log, // Log every SQL statement
})

Common Patterns

Enable Foreign Keys

SQLite requires foreign key constraints to be enabled per connection:

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

const db = new Kysely<DatabaseSchema>({
  dialect: new SqliteDialect({
    database: new Database('db.sqlite'),
    onCreateConnection: async (connection) => {
      await connection.executeQuery(
        CompiledQuery.raw('PRAGMA foreign_keys = ON')
      )
    }
  })
})

Write-Ahead Logging (WAL)

Enable WAL mode for better concurrency:

import Database from 'better-sqlite3'

const database = new Database('mydb.db')
database.pragma('journal_mode = WAL')

const db = new Kysely<DatabaseSchema>({
  dialect: new SqliteDialect({ database })
})

Performance Optimization

Optimize SQLite for better performance:

import Database from 'better-sqlite3'

const database = new Database('mydb.db')

// Performance PRAGMAs
database.pragma('journal_mode = WAL')
database.pragma('synchronous = NORMAL')
database.pragma('cache_size = -64000') // 64MB cache
database.pragma('temp_store = MEMORY')

const db = new Kysely<DatabaseSchema>({
  dialect: new SqliteDialect({ database })
})

In-Memory Database

Use in-memory databases for testing or temporary data:

import Database from 'better-sqlite3'

const db = new Kysely<DatabaseSchema>({
  dialect: new SqliteDialect({
    database: new Database(':memory:')
  })
})

Read-Only Access

Open database in read-only mode:

import Database from 'better-sqlite3'

const db = new Kysely<DatabaseSchema>({
  dialect: new SqliteDialect({
    database: new Database('mydb.db', { readonly: true })
  })
})

Testing

SQLite is excellent for testing due to its in-memory mode:

import { beforeEach, describe, it } from 'vitest'
import { Kysely, SqliteDialect } from 'kysely'
import Database from 'better-sqlite3'

describe('User tests', () => {
  let db: Kysely<DatabaseSchema>

  beforeEach(async () => {
    // Create fresh in-memory database for each test
    db = new Kysely<DatabaseSchema>({
      dialect: new SqliteDialect({
        database: new Database(':memory:')
      })
    })

    // Run migrations
    await db.schema
      .createTable('users')
      .addColumn('id', 'integer', col => col.primaryKey())
      .addColumn('name', 'text', col => col.notNull())
      .execute()
  })

  it('should insert user', async () => {
    await db.insertInto('users')
      .values({ name: 'Alice' })
      .execute()
  })
})

Transactions

better-sqlite3 is synchronous and uses immediate transactions by default. Kysely handles this automatically:

await db.transaction().execute(async (trx) => {
  await trx.insertInto('users').values({ name: 'Alice' }).execute()
  await trx.insertInto('posts').values({ userId: 1, title: 'Hello' }).execute()
})

Backup and Restore

Use better-sqlite3’s backup API:

import Database from 'better-sqlite3'

const source = new Database('source.db')
const destination = new Database('backup.db')

source.backup(destination)
  .then(() => console.log('Backup complete'))
  .catch(err => console.error('Backup failed:', err))

Limitations

SQLite has some limitations compared to client-server databases:

• No concurrent writes (only one writer at a time)
• Limited ALTER TABLE support
• No built-in user authentication
• Not suitable for high-traffic web applications
• File-based, so not ideal for distributed systems

Related Types

SqliteDatabase

Source: src/dialect/sqlite/sqlite-dialect-config.ts:32

The subset of the better-sqlite3 Database interface that Kysely requires:

interface SqliteDatabase {
  close(): void
  prepare(sql: string): SqliteStatement
}

SqliteStatement

Source: src/dialect/sqlite/sqlite-dialect-config.ts:37

interface SqliteStatement {
  readonly reader: boolean
  all(parameters: ReadonlyArray<unknown>): unknown[]
  run(parameters: ReadonlyArray<unknown>): {
    changes: number | bigint
    lastInsertRowid: number | bigint
  }
  iterate(parameters: ReadonlyArray<unknown>): IterableIterator<unknown>
}