data-table-sqlite

Overview

data-table-sqlite provides a SQLite-specific adapter for data-table using the better-sqlite3 package. It supports the core data-table API with SQLite-optimized features including RETURNING clauses, transactional DDL, and savepoints. Key Features:

Full RETURNING clause support for inserts, updates, and deletes
Savepoint support for nested transactions
Native upsert (ON CONFLICT) support
Transactional DDL for safe migrations
Synchronous API (wrapped in async for consistency)
Excellent for embedded databases and development

Limitations:

No migration lock support (single-process only)
Limited concurrent write access
No connection pooling (single database handle)

Installation

npm i remix
npm i better-sqlite3

API Reference

createSqliteDatabaseAdapter

Creates a SQLite database adapter.

database

SqliteDatabaseConnection

required

Better SQLite3 database instance.

options.capabilities

AdapterCapabilityOverrides

Optional capability overrides.

options.capabilities.returning

boolean

Enable/disable RETURNING clause support. Defaults to true.

options.capabilities.savepoints

boolean

Enable/disable savepoint support. Defaults to true.

options.capabilities.upsert

boolean

Enable/disable upsert support. Defaults to true.

options.capabilities.transactionalDdl

boolean

Enable/disable transactional DDL. Defaults to true.

options.capabilities.migrationLock

boolean

Enable/disable migration locking. Defaults to false (not supported).

adapter

SqliteDatabaseAdapter

A SQLite database adapter instance.

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

let sqlite = new Database('mydb.sqlite')
let adapter = createSqliteDatabaseAdapter(sqlite)
let db = createDatabase(adapter)

SqliteDatabaseAdapter

SQLite adapter class implementing the DatabaseAdapter interface.

class SqliteDatabaseAdapter implements DatabaseAdapter {
  dialect: 'sqlite'
  capabilities: AdapterCapabilities
  
  compileSql(
    operation: DataManipulationOperation | DataMigrationOperation
  ): SqlStatement[]
  
  execute(
    request: DataManipulationRequest
  ): Promise<DataManipulationResult>
  
  migrate(
    request: DataMigrationRequest
  ): Promise<DataMigrationResult>
  
  hasTable(
    table: TableRef,
    transaction?: TransactionToken
  ): Promise<boolean>
  
  hasColumn(
    table: TableRef,
    column: string,
    transaction?: TransactionToken
  ): Promise<boolean>
  
  beginTransaction(
    options?: TransactionOptions
  ): Promise<TransactionToken>
  
  commitTransaction(
    token: TransactionToken
  ): Promise<void>
  
  rollbackTransaction(
    token: TransactionToken
  ): Promise<void>
  
  createSavepoint(
    token: TransactionToken,
    name: string
  ): Promise<void>
  
  rollbackToSavepoint(
    token: TransactionToken,
    name: string
  ): Promise<void>
  
  releaseSavepoint(
    token: TransactionToken,
    name: string
  ): Promise<void>
}

Type Definitions

SqliteDatabaseConnection

Better SQLite3 database handle.

import type { Database as BetterSqliteDatabase } from 'better-sqlite3'

type SqliteDatabaseConnection = BetterSqliteDatabase

SqliteDatabaseAdapterOptions

SQLite adapter configuration.

type SqliteDatabaseAdapterOptions = {
  capabilities?: AdapterCapabilityOverrides
}

Capabilities

The SQLite adapter has the following default capabilities:

Capability	Default	Description
`returning`	`true`	Support for RETURNING clauses in INSERT/UPDATE/DELETE
`savepoints`	`true`	Support for nested transactions via savepoints
`upsert`	`true`	Support for ON CONFLICT upsert operations
`transactionalDdl`	`true`	DDL statements can be run in transactions
`migrationLock`	`false`	No migration locking (single-process only)

Usage Examples

Basic Setup

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

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

// Create database (in-memory for development)
let sqlite = new Database(':memory:')
let db = createDatabase(createSqliteDatabaseAdapter(sqlite))

// Or use file-based database
let fileSqlite = new Database('./data/myapp.db')
let fileDb = createDatabase(createSqliteDatabaseAdapter(fileSqlite))

Using RETURNING Clauses

SQLite’s RETURNING support allows you to get inserted/updated rows.

// Insert with RETURNING
let createdUser = await db.create(
  users,
  {
    email: '[email protected]',
    name: 'Ada Lovelace',
  },
  { returnRow: true }
)

// Bulk insert with RETURNING
let insertedUsers = await db.createMany(
  users,
  [
    { email: '[email protected]', name: 'Grace Hopper' },
    { email: '[email protected]', name: 'Alan Turing' },
  ],
  { returnRows: true }
)

// Update with RETURNING
let result = await db
  .query(users)
  .where({ email: '[email protected]' })
  .update({ name: 'Ada King' }, { returning: '*' })

Transactions and Savepoints

SQLite supports nested transactions via savepoints.

// Simple transaction
await db.transaction(async (tx) => {
  await tx.create(users, { email: '[email protected]', name: 'Test User' })
  await tx.update(users, userId, { name: 'Updated Name' })
})

// Nested transactions with savepoints
await db.transaction(async (tx) => {
  await tx.create(users, { email: '[email protected]', name: 'Outer' })
  
  // Inner transaction uses a savepoint
  await tx.transaction(async (inner) => {
    await inner.create(users, { email: '[email protected]', name: 'Inner' })
    // If this throws, only the inner transaction is rolled back
  })
})

Upserts with ON CONFLICT

SQLite’s ON CONFLICT is exposed via the upsert method.

// Upsert by primary key
await db.query(users).upsert(
  {
    id: 1,
    email: '[email protected]',
    name: 'Updated Name',
  },
  {
    update: { name: 'Updated Name' },
  }
)

// Upsert with explicit conflict target
await db.query(users).upsert(
  {
    email: '[email protected]',
    name: 'New Name',
  },
  {
    conflictTarget: ['email'],
    update: { name: 'New Name' },
  }
)

// Upsert with RETURNING
let result = await db.query(users).upsert(
  { id: 1, email: '[email protected]', name: 'Test' },
  {
    update: { email: '[email protected]' },
    returning: '*',
  }
)

Transactional DDL

SQLite supports DDL in transactions, allowing safe schema changes.

// Schema changes in transaction
await db.transaction(async (tx) => {
  await tx.exec(sql`
    CREATE TABLE new_users (
      id INTEGER PRIMARY KEY,
      email TEXT NOT NULL UNIQUE,
      name TEXT NOT NULL
    )
  `)
  
  await tx.exec(sql`
    INSERT INTO new_users SELECT id, email, name FROM users
  `)
  
  await tx.exec(sql`DROP TABLE users`)
  await tx.exec(sql`ALTER TABLE new_users RENAME TO users`)
  
  // If any step fails, all changes are rolled back
})

Auto-Increment Columns

SQLite uses AUTOINCREMENT for identity columns.

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

// Insert without specifying id
let result = await db.create(users, {
  email: '[email protected]',
  name: 'Auto User',
})

console.log('Generated ID:', result.insertId)

In-Memory Database

Use :memory: for fast in-memory databases (great for testing).

import Database from 'better-sqlite3'

let sqlite = new Database(':memory:')
let db = createDatabase(createSqliteDatabaseAdapter(sqlite))

// Run migrations
await runMigrations(db)

// Use for tests
let user = await db.create(users, { ... })

File-Based Database

Use file paths for persistent databases.

import Database from 'better-sqlite3'
import { resolve } from 'node:path'

let dbPath = resolve('./data/app.db')
let sqlite = new Database(dbPath)

// Enable WAL mode for better concurrency
sqlite.pragma('journal_mode = WAL')

let db = createDatabase(createSqliteDatabaseAdapter(sqlite))

Read-Only Database

Open database in read-only mode.

import Database from 'better-sqlite3'

let sqlite = new Database('mydb.sqlite', { readonly: true })
let db = createDatabase(createSqliteDatabaseAdapter(sqlite))

// Only read operations work
let users = await db.findMany(users)

// Write operations throw
try {
  await db.create(users, { ... })
} catch (error) {
  // Error: attempt to write a readonly database
}

SQLite-Specific Features

Pragma Statements

Configure SQLite behavior with pragma statements.

import Database from 'better-sqlite3'

let sqlite = new Database('mydb.sqlite')

// Enable WAL mode for better concurrency
sqlite.pragma('journal_mode = WAL')

// Enable foreign keys (disabled by default)
sqlite.pragma('foreign_keys = ON')

// Set busy timeout
sqlite.pragma('busy_timeout = 5000')

let db = createDatabase(createSqliteDatabaseAdapter(sqlite))

JSON Support

SQLite has built-in JSON functions.

let posts = table({
  name: 'posts',
  columns: {
    id: c.integer().primaryKey(),
    metadata: c.json(),
  },
})

await db.create(posts, {
  metadata: { tags: ['typescript', 'sqlite'], views: 0 },
})

// Query JSON fields with raw SQL
let results = await db.exec(sql`
  SELECT * FROM posts
  WHERE json_extract(metadata, '$.tags') LIKE '%typescript%'
`)

Full-Text Search

SQLite supports FTS5 for full-text search.

// Create FTS table with raw SQL
await db.exec(sql`
  CREATE VIRTUAL TABLE posts_fts USING fts5(
    title,
    content,
    content=posts,
    content_rowid=id
  )
`)

// Search
let results = await db.exec(sql`
  SELECT * FROM posts_fts
  WHERE posts_fts MATCH 'typescript AND sqlite'
`)

Attached Databases

Attach multiple databases.

import Database from 'better-sqlite3'

let main = new Database('main.db')
main.exec('ATTACH DATABASE "archive.db" AS archive')

let db = createDatabase(createSqliteDatabaseAdapter(main))

// Query across databases
await db.exec(sql`
  INSERT INTO archive.users
  SELECT * FROM main.users WHERE created_at < ?
`, [oldDate])

Performance Tips

Enable WAL Mode

Write-Ahead Logging improves concurrent access.

let sqlite = new Database('mydb.sqlite')
sqlite.pragma('journal_mode = WAL')

Batch Inserts

Use createMany for bulk inserts.

// Good: Batch insert
await db.createMany(users, [
  { email: '[email protected]', name: 'User 1' },
  { email: '[email protected]', name: 'User 2' },
  // ... many more
])

// Bad: Individual inserts
for (let user of users) {
  await db.create(users, user)
}

Use Transactions for Multiple Writes

Transactions dramatically improve write performance.

// Good: Wrapped in transaction
await db.transaction(async (tx) => {
  for (let i = 0; i < 1000; i++) {
    await tx.create(users, { email: `user${i}@example.com`, name: `User ${i}` })
  }
})

// Bad: Individual transactions (very slow)
for (let i = 0; i < 1000; i++) {
  await db.create(users, { email: `user${i}@example.com`, name: `User ${i}` })
}

Analyze for Query Optimization

Run ANALYZE to update query planner statistics.

await db.exec(sql`ANALYZE`)

Differences from Other Adapters

vs. PostgreSQL Adapter

Feature	SQLite	PostgreSQL
RETURNING	✅ Yes	✅ Yes
Savepoints	✅ Yes	✅ Yes
Transactional DDL	✅ Yes	✅ Yes
Migration Lock	❌ No	Advisory Lock
Concurrent Writes	⚠️ Limited	✅ Excellent
Connection Pooling	❌ N/A	✅ Yes
Deployment	Embedded	Server

vs. MySQL Adapter

Feature	SQLite	MySQL
RETURNING	✅ Yes	❌ No
Savepoints	✅ Yes	✅ Yes
Transactional DDL	✅ Yes	❌ No
Migration Lock	❌ No	Table Lock
Concurrent Writes	⚠️ Limited	✅ Good
Connection Pooling	❌ N/A	✅ Yes
Deployment	Embedded	Server

Best Practices

Use for Embedded Applications

SQLite is ideal for:

Desktop applications
Mobile apps
Edge computing
Development and testing
Small web apps (< 100k requests/day)

// Good use case: Electron app
let sqlite = new Database(app.getPath('userData') + '/app.db')
let db = createDatabase(createSqliteDatabaseAdapter(sqlite))

Enable Foreign Keys

Foreign keys are disabled by default in SQLite.

let sqlite = new Database('mydb.sqlite')
sqlite.pragma('foreign_keys = ON')
let db = createDatabase(createSqliteDatabaseAdapter(sqlite))

Single Process Only

SQLite is single-process by design.

// Good: Single process
let sqlite = new Database('mydb.sqlite')
let db = createDatabase(createSqliteDatabaseAdapter(sqlite))

// Bad: Multiple processes accessing same file
// Process 1: new Database('mydb.sqlite')
// Process 2: new Database('mydb.sqlite')
// This can cause database corruption!

Close Database on Shutdown

Always close the database when your application exits.

let sqlite = new Database('mydb.sqlite')
let db = createDatabase(createSqliteDatabaseAdapter(sqlite))

process.on('exit', () => {
  sqlite.close()
})

process.on('SIGINT', () => {
  sqlite.close()
  process.exit(0)
})

Use In-Memory for Tests

In-memory databases are fast and isolated.

import { beforeEach } from 'node:test'

let db: Database

beforeEach(async () => {
  let sqlite = new Database(':memory:')
  db = createDatabase(createSqliteDatabaseAdapter(sqlite))
  await runMigrations(db)
})

Routing

Components

Data

Server

Middleware

Utilities

​Overview

​Installation

​API Reference

​createSqliteDatabaseAdapter

​SqliteDatabaseAdapter

​Type Definitions

​SqliteDatabaseConnection

​SqliteDatabaseAdapterOptions

​Capabilities

​Usage Examples

​Basic Setup

​Using RETURNING Clauses

​Transactions and Savepoints

​Upserts with ON CONFLICT

​Transactional DDL

​Auto-Increment Columns

​In-Memory Database

​File-Based Database

​Read-Only Database

​SQLite-Specific Features

​Pragma Statements

​JSON Support

​Full-Text Search

​Attached Databases

​Performance Tips

​Enable WAL Mode

​Batch Inserts

​Use Transactions for Multiple Writes

​Analyze for Query Optimization

​Differences from Other Adapters

​vs. PostgreSQL Adapter

​vs. MySQL Adapter

​Best Practices

​Use for Embedded Applications

​Enable Foreign Keys

​Single Process Only

​Close Database on Shutdown

​Use In-Memory for Tests

Build docs developers (and LLMs) love

Overview

Installation

API Reference

createSqliteDatabaseAdapter

SqliteDatabaseAdapter

Type Definitions

SqliteDatabaseConnection

SqliteDatabaseAdapterOptions

Capabilities

Usage Examples

Basic Setup

Using RETURNING Clauses

Transactions and Savepoints

Upserts with ON CONFLICT

Transactional DDL

Auto-Increment Columns

In-Memory Database

File-Based Database

Read-Only Database

SQLite-Specific Features

Pragma Statements

JSON Support

Full-Text Search

Attached Databases

Performance Tips

Enable WAL Mode

Batch Inserts

Use Transactions for Multiple Writes

Analyze for Query Optimization

Differences from Other Adapters

vs. PostgreSQL Adapter

vs. MySQL Adapter

Best Practices

Use for Embedded Applications

Enable Foreign Keys

Single Process Only

Close Database on Shutdown

Use In-Memory for Tests