Data Table SQLite Overview

The data-table-sqlite package connects the data-table query toolkit to SQLite via the better-sqlite3 driver. SQLite is a file-based database that runs in-process --- no server required.

When to Use SQLite

Use Case	SQLite Fits?
Local development	Yes --- zero setup, just a file on disk.
Small to medium production apps	Yes --- handles thousands of concurrent readers with WAL mode.
Embedded or edge deployments	Yes --- runs anywhere Node.js runs, including serverless.
Large-scale multi-server production	No --- use PostgreSQL or MySQL instead. SQLite does not support networked connections.

What is better-sqlite3?

better-sqlite3 is a synchronous, high-performance SQLite driver for Node.js. It is faster than async alternatives because SQLite itself is an in-process library. The data-table-sqlite adapter wraps it to match the async DatabaseAdapter interface.

Quick Setup

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

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

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

// Define a table
let todos = table({
  name: 'todos',
  columns: {
    id: c.integer().primaryKey().autoIncrement(),
    title: c.text().notNull(),
    done: c.boolean().notNull().default(false),
  },
})

// Query
let all = await db.findMany(todos)

Key Considerations

• WAL mode: Always enable journal_mode = WAL for web applications. It allows concurrent reads during writes.
• Foreign keys: SQLite disables foreign keys by default. Enable them with sqlite.pragma('foreign_keys = ON').
• Booleans: SQLite stores booleans as 0/1. The adapter converts automatically.
• No ILIKE: SQLite does not support ILIKE. The adapter translates ilike to LIKE with COLLATE NOCASE.
• Single writer: Only one write can happen at a time. Reads are concurrent in WAL mode.

Installation

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

Related

• Data Table SQLite Tutorial --- Step-by-step setup, table creation, seeding, and querying.
• Data Table SQLite Reference --- Full API reference.
• Data Table Overview --- The core query toolkit.
• Data Table PostgreSQL --- PostgreSQL adapter for production scaling.
• Data Table MySQL --- MySQL adapter.