Data Table Tutorial

In this tutorial you will build a task manager database with three tables: users, projects, and tasks. You will learn how to define tables, set up relations, perform CRUD operations, filter with operators, use transactions, and run migrations.

Prerequisites

Install Remix and the SQLite adapter (we use SQLite for simplicity --- the API is identical for PostgreSQL and MySQL):

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

---

Step 1 --- Define the Tables

Create a file for your table definitions. Each table uses the table function and the column builder (aliased as c):

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

export let users = table({
  name: 'users',
  columns: {
    id: c.integer().primaryKey().autoIncrement(),
    email: c.varchar(255).notNull().unique('users_email_uq'),
    name: c.text().notNull(),
    role: c.enum(['admin', 'member']).notNull().default('member'),
    createdAt: c.timestamp().notNull().defaultNow(),
    updatedAt: c.timestamp().notNull().defaultNow(),
  },
  timestamps: true,
})

export let projects = table({
  name: 'projects',
  columns: {
    id: c.integer().primaryKey().autoIncrement(),
    name: c.text().notNull(),
    description: c.text().nullable(),
    ownerId: c.integer().notNull().references('users', 'fk_projects_owner'),
    createdAt: c.timestamp().notNull().defaultNow(),
    updatedAt: c.timestamp().notNull().defaultNow(),
  },
  timestamps: true,
})

export let tasks = table({
  name: 'tasks',
  columns: {
    id: c.integer().primaryKey().autoIncrement(),
    title: c.text().notNull(),
    description: c.text().nullable(),
    status: c.enum(['todo', 'in_progress', 'done']).notNull().default('todo'),
    priority: c.integer().notNull().default(0),
    projectId: c.integer().notNull().references('projects', 'fk_tasks_project'),
    assigneeId: c.integer().nullable().references('users', 'fk_tasks_assignee'),
    createdAt: c.timestamp().notNull().defaultNow(),
    updatedAt: c.timestamp().notNull().defaultNow(),
  },
  timestamps: true,
})

What does .references() do?

It declares a foreign key constraint. The first argument is the target table name, and the second is a constraint name. This tells the database to enforce referential integrity --- you cannot insert a projectId that does not exist in the projects table.

Step 2 --- Set Up the Database

Create a database instance using the SQLite adapter:

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

let sqlite = new SQLite('taskmanager.db')
sqlite.pragma('journal_mode = WAL')
sqlite.pragma('foreign_keys = ON')

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

What is WAL mode?

WAL (Write-Ahead Logging) is a SQLite journal mode that allows concurrent reads while a write is in progress. It improves performance for web applications where multiple requests may read at the same time.

Step 3 --- Define Relations

Relations tell data-table how tables connect. Define them alongside your tables:

// db/relations.ts
import { hasMany, belongsTo } from 'remix/data-table'
import { users, projects, tasks } from './tables'

// A user owns many projects
export let userProjects = hasMany(users, projects, { foreignKey: 'ownerId' })

// A project belongs to a user (its owner)
export let projectOwner = belongsTo(projects, users, { foreignKey: 'ownerId' })

// A project has many tasks
export let projectTasks = hasMany(projects, tasks, { foreignKey: 'projectId' })

// A task belongs to a project
export let taskProject = belongsTo(tasks, projects, { foreignKey: 'projectId' })

// A task optionally belongs to an assignee
export let taskAssignee = belongsTo(tasks, users, { foreignKey: 'assigneeId' })

// A user has many assigned tasks
export let userTasks = hasMany(users, tasks, { foreignKey: 'assigneeId' })

Step 4 --- Create Data (INSERT)

Use db.create() to insert a single row and db.createMany() for bulk inserts:

import { db } from './db'
import { users, projects, tasks } from './db/tables'

// Create a user and get the full row back
let alice = await db.create(users, {
  email: 'alice@example.com',
  name: 'Alice',
  role: 'admin',
}, { returnRow: true })

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

// Create a project
let project = await db.create(projects, {
  name: 'Website Redesign',
  description: 'Rebuild the marketing site with Remix V3.',
  ownerId: alice.id,
}, { returnRow: true })

// Create several tasks at once
await db.createMany(tasks, [
  { title: 'Design mockups', projectId: project.id, assigneeId: alice.id, priority: 3 },
  { title: 'Set up routing', projectId: project.id, assigneeId: bob.id, priority: 2 },
  { title: 'Write copy', projectId: project.id, priority: 1 },
  { title: 'Deploy to staging', projectId: project.id, status: 'todo', priority: 0 },
])

returnRow: true

By default, db.create() returns a WriteResult with affectedRows and insertId. Pass { returnRow: true } to get the full inserted row with all default values resolved.

Step 5 --- Read Data (SELECT)

Find by primary key

let user = await db.find(users, 1)
// { id: 1, email: 'alice@example.com', name: 'Alice', ... }

Find one by condition

let admin = await db.findOne(users, {
  where: { role: 'admin' },
})

Find many with filtering and sorting

let openTasks = await db.findMany(tasks, {
  where: { status: 'todo' },
  orderBy: [['priority', 'desc']],
  limit: 10,
})

Count rows

let totalTasks = await db.count(tasks)
let doneTasks = await db.count(tasks, { where: { status: 'done' } })

Step 6 --- Filter with Operators

For complex conditions, use operators from remix/data-table/operators:

import { eq, gt, like, inList, and, or } from 'remix/data-table/operators'

// High-priority incomplete tasks
let urgent = await db.findMany(tasks, {
  where: and(
    gt('priority', 2),
    inList('status', ['todo', 'in_progress']),
  ),
  orderBy: [['priority', 'desc']],
})

// Tasks matching a search term
let results = await db.findMany(tasks, {
  where: like('title', '%routing%'),
})

// Tasks that are done OR unassigned
let filtered = await db.findMany(tasks, {
  where: or(
    eq('status', 'done'),
    eq('assigneeId', null),
  ),
})

Operator reference

The full list of operators is: eq, ne, gt, gte, lt, lte, like, ilike, inList, notInList, between, isNull, notNull, and, or. See the reference for details.

Step 7 --- Eager Loading with Relations

Load related data in a single query using the with option:

import { userProjects, projectTasks, projectOwner, taskAssignee } from './db/relations'

// Load a user with all their projects
let userWithProjects = await db.find(users, 1, {
  with: { projects: userProjects },
})
// userWithProjects.projects is Project[]

// Load a project with its owner and tasks (including each task's assignee)
let fullProject = await db.find(projects, 1, {
  with: {
    owner: projectOwner,
    tasks: projectTasks.with({ assignee: taskAssignee }),
  },
})
// fullProject.owner is a User
// fullProject.tasks[0].assignee is a User | null

Filtering and limiting relations

// Only the 5 most recent tasks for a project
let recentTasks = projectTasks
  .where({ status: 'todo' })
  .orderBy('createdAt', 'desc')
  .limit(5)

let project = await db.find(projects, 1, {
  with: { recentTasks },
})

Step 8 --- Update Data

Update a single row by primary key

let updated = await db.update(tasks, 1, {
  status: 'in_progress',
})
// Returns the updated row

Update many rows by condition

let result = await db.updateMany(tasks, { status: 'done' }, {
  where: { projectId: 1, status: 'in_progress' },
})
// result.affectedRows = number of rows updated

Step 9 --- Delete Data

Delete a single row

let deleted = await db.delete(tasks, 4)  // true or false

Delete many rows

let result = await db.deleteMany(tasks, {
  where: { status: 'done' },
})

Step 10 --- Use the Query Builder

The query builder provides a fluent interface for more complex queries:

// Find all tasks assigned to Alice in the "Website Redesign" project
let aliceTasks = await db.query(tasks)
  .join(projects, { 'tasks.projectId': 'projects.id' })
  .where(and(
    eq('tasks.assigneeId', alice.id),
    eq('projects.name', 'Website Redesign'),
  ))
  .select(['tasks.id', 'tasks.title', 'tasks.status'])
  .orderBy('tasks.priority', 'desc')
  .all()

// Check if any task exists
let hasTasks = await db.query(tasks)
  .where({ projectId: 1 })
  .exists()

Step 11 --- Transactions

Wrap multiple operations in a transaction. If any operation throws, everything rolls back:

await db.transaction(async (tx) => {
  // Create a project and its first task atomically
  let project = await tx.create(projects, {
    name: 'New Feature',
    ownerId: alice.id,
  }, { returnRow: true })

  await tx.create(tasks, {
    title: 'Plan feature scope',
    projectId: project.id,
    assigneeId: alice.id,
    priority: 3,
  })

  await tx.create(tasks, {
    title: 'Write tests',
    projectId: project.id,
    priority: 2,
  })
})

Nested transactions

data-table supports nested transactions via savepoints. If an inner tx.transaction() throws, only the inner savepoint rolls back.

Step 12 --- Migrations

Migrations create and modify database tables over time. Define them with createMigration:

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

export default createMigration({
  up(ctx) {
    ctx.createTable('users', {
      id: c.integer().primaryKey().autoIncrement(),
      email: c.varchar(255).notNull().unique('users_email_uq'),
      name: c.text().notNull(),
      role: c.enum(['admin', 'member']).notNull().default('member'),
      createdAt: c.timestamp().notNull().defaultNow(),
      updatedAt: c.timestamp().notNull().defaultNow(),
    })

    ctx.createTable('projects', {
      id: c.integer().primaryKey().autoIncrement(),
      name: c.text().notNull(),
      description: c.text().nullable(),
      ownerId: c.integer().notNull().references('users', 'fk_projects_owner'),
      createdAt: c.timestamp().notNull().defaultNow(),
      updatedAt: c.timestamp().notNull().defaultNow(),
    })

    ctx.createTable('tasks', {
      id: c.integer().primaryKey().autoIncrement(),
      title: c.text().notNull(),
      description: c.text().nullable(),
      status: c.enum(['todo', 'in_progress', 'done']).notNull().default('todo'),
      priority: c.integer().notNull().default(0),
      projectId: c.integer().notNull().references('projects', 'fk_tasks_project'),
      assigneeId: c.integer().nullable().references('users', 'fk_tasks_assignee'),
      createdAt: c.timestamp().notNull().defaultNow(),
      updatedAt: c.timestamp().notNull().defaultNow(),
    })

    ctx.createIndex('tasks', ['projectId'], { name: 'idx_tasks_project' })
    ctx.createIndex('tasks', ['assigneeId'], { name: 'idx_tasks_assignee' })
    ctx.createIndex('tasks', ['status'], { name: 'idx_tasks_status' })
  },

  down(ctx) {
    ctx.dropTable('tasks')
    ctx.dropTable('projects')
    ctx.dropTable('users')
  },
})

Running migrations

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

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

// Apply all pending migrations
await runner.up(migrations)

// Roll back the last migration
await runner.down(migrations)

What You Learned

• Table definitions: Use table() and column builders to declare your schema with full TypeScript inference.
• Relations: hasMany, belongsTo, and the .with() option for eager loading.
• CRUD: db.create(), db.find(), db.findMany(), db.update(), db.delete().
• Operators: eq, gt, like, inList, and, or for complex WHERE clauses.
• Query builder: Fluent API with .join(), .select(), .orderBy(), .limit().
• Transactions: db.transaction() with automatic rollback and nested savepoints.
• Migrations: createMigration with up/down for schema changes, loadMigrations for loading from disk.

Related

• Data Table Overview --- Feature summary and architecture.
• Data Table Reference --- Full API reference.
• Database Guide --- Production patterns and performance tips.
• data-table-sqlite --- SQLite adapter details.
• data-table-postgres --- PostgreSQL adapter details.