data-table-sqlite

The data-table-sqlite package provides a SQLite adapter for the data-table query toolkit. It uses better-sqlite3 as the underlying driver.

Installation

npm install remix better-sqlite3
npm install -D @types/better-sqlite3

Or install the standalone package:

npm install @remix-run/data-table-sqlite better-sqlite3
npm install -D @types/better-sqlite3

Import

import { createSqliteDatabaseAdapter } from 'remix/data-table-sqlite'

Or from the standalone package:

import { createSqliteDatabaseAdapter } from '@remix-run/data-table-sqlite'

---

API

createSqliteDatabaseAdapter(database, options?)

Creates a DatabaseAdapter for use with createDatabase().

import SQLite from 'better-sqlite3'
import { createSqliteDatabaseAdapter } from 'remix/data-table-sqlite'
import { createDatabase } from 'remix/data-table'

let sqlite = new SQLite('app.db')
let adapter = createSqliteDatabaseAdapter(sqlite)
let db = createDatabase(adapter)

Parameters

Parameter	Type	Description
database	Database (from better-sqlite3)	An open better-sqlite3 database instance.
options	SqliteDatabaseAdapterOptions	Optional adapter configuration.

SqliteDatabaseAdapterOptions

Option	Type	Description
capabilities	AdapterCapabilityOverrides	Override detected adapter capabilities.

Capabilities

The SQLite adapter reports the following capabilities by default:

Capability	Default	Description
returning	true	SQLite supports RETURNING clauses.
savepoints	true	SQLite supports savepoints for nested transactions.
upsert	true	SQLite supports ON CONFLICT upsert.
transactionalDdl	true	SQLite supports DDL inside transactions.
migrationLock	false	No advisory lock support (single-process only).

---

SQLite-Specific Considerations

• Synchronous driver: better-sqlite3 is synchronous. The adapter wraps calls to match the async DatabaseAdapter interface.
• Single writer: SQLite allows only one write at a time. Concurrent writes will block. Consider enabling WAL mode for better read concurrency:

  sqlite.pragma('journal_mode = WAL')

• Boolean columns: SQLite stores booleans as integers (0/1). The adapter handles conversion automatically.
• No ILIKE: SQLite does not support ILIKE. The adapter translates ilike queries using LIKE with COLLATE NOCASE.
• Timestamps: SQLite stores timestamps as text. Consider using ISO 8601 format for portability.

---

Example

import SQLite from 'better-sqlite3'
import { createSqliteDatabaseAdapter } from 'remix/data-table-sqlite'
import { createDatabase, table, column as c } from 'remix/data-table'

// Open database with WAL mode
let sqlite = new SQLite('app.db')
sqlite.pragma('journal_mode = WAL')
sqlite.pragma('foreign_keys = ON')

// Create adapter and database
let adapter = createSqliteDatabaseAdapter(sqlite)
let db = createDatabase(adapter)

// Define a table
let users = table({
  name: 'users',
  columns: {
    id: c.integer().primaryKey().autoIncrement(),
    email: c.varchar(255).notNull(),
    name: c.text().notNull(),
  },
})

// Query
let allUsers = await db.findMany(users)

---

Related

• data-table --- The core query toolkit.
• data-table-postgres --- PostgreSQL adapter.
• data-table-mysql --- MySQL adapter.
• Database Guide --- Practical database patterns.