Data Table SQLite Tutorial

In this tutorial you will set up a SQLite database from scratch, create tables, seed sample data, and run queries using the data-table toolkit.

Prerequisites

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

---

Step 1 --- Open a SQLite Database

Create a file called db.ts:

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

let sqlite = new SQLite('myapp.db')

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

// Enable foreign key enforcement
sqlite.pragma('foreign_keys = ON')

let adapter = createSqliteDatabaseAdapter(sqlite)
export let db = createDatabase(adapter)

In-memory databases

For tests, use new SQLite(':memory:') to create a temporary database that disappears when the process exits.

Step 2 --- Define Tables

// db/tables.ts
import { table, column as c } from 'remix/data-table'

export let authors = table({
  name: 'authors',
  columns: {
    id: c.integer().primaryKey().autoIncrement(),
    name: c.text().notNull(),
    email: c.varchar(255).notNull().unique('authors_email_uq'),
  },
})

export let books = table({
  name: 'books',
  columns: {
    id: c.integer().primaryKey().autoIncrement(),
    title: c.text().notNull(),
    authorId: c.integer().notNull().references('authors', 'fk_books_author'),
    published: c.boolean().notNull().default(false),
    createdAt: c.timestamp().notNull().defaultNow(),
  },
})

Step 3 --- Create the Tables with a Migration

// migrations/001-initial.ts
import { createMigration, column as c } from 'remix/data-table/migrations'

export default createMigration({
  up(ctx) {
    ctx.createTable('authors', {
      id: c.integer().primaryKey().autoIncrement(),
      name: c.text().notNull(),
      email: c.varchar(255).notNull().unique('authors_email_uq'),
    })

    ctx.createTable('books', {
      id: c.integer().primaryKey().autoIncrement(),
      title: c.text().notNull(),
      authorId: c.integer().notNull().references('authors', 'fk_books_author'),
      published: c.boolean().notNull().default(false),
      createdAt: c.timestamp().notNull().defaultNow(),
    })
  },

  down(ctx) {
    ctx.dropTable('books')
    ctx.dropTable('authors')
  },
})

Run the migration:

import { createMigrationRunner } from 'remix/data-table/migrations'
import { loadMigrations } from 'remix/data-table/migrations/node'

let migrations = await loadMigrations('./migrations')
let runner = createMigrationRunner(adapter)
await runner.up(migrations)

Step 4 --- Seed Data

import { db } from './db'
import { authors, books } from './db/tables'

let alice = await db.create(authors, {
  name: 'Alice',
  email: 'alice@example.com',
}, { returnRow: true })

let bob = await db.create(authors, {
  name: 'Bob',
  email: 'bob@example.com',
}, { returnRow: true })

await db.createMany(books, [
  { title: 'Getting Started with Remix', authorId: alice.id, published: true },
  { title: 'Advanced Patterns', authorId: alice.id, published: false },
  { title: 'Database Design', authorId: bob.id, published: true },
])

Step 5 --- Query Data

import { hasMany, belongsTo } from 'remix/data-table'
import { eq } from 'remix/data-table/operators'

// Find all published books
let published = await db.findMany(books, {
  where: { published: true },
  orderBy: ['title', 'asc'],
})

// Find a book with its author
let bookAuthor = belongsTo(books, authors, { foreignKey: 'authorId' })

let book = await db.findOne(books, {
  where: { title: 'Getting Started with Remix' },
  with: { author: bookAuthor },
})
// book.author.name === 'Alice'

// Find an author with all their books
let authorBooks = hasMany(authors, books, { foreignKey: 'authorId' })

let author = await db.find(authors, 1, {
  with: { books: authorBooks },
})
// author.books is Book[]

What You Learned

• Open a SQLite database with better-sqlite3 and enable WAL mode and foreign keys.
• Create a data-table-sqlite adapter and pass it to createDatabase.
• Define tables with typed columns and run migrations to create them.
• Seed data with db.create and db.createMany.
• Query with db.findMany, db.findOne, and eager-loaded relations.

Related

• Data Table SQLite Overview --- When to use SQLite and key considerations.
• Data Table SQLite Reference --- Full API reference.
• Data Table Tutorial --- Comprehensive tutorial covering all data-table features.