Data Table Overview

The data-table package is a typed relational query toolkit that works with PostgreSQL, MySQL, and SQLite. It lets you define tables, run queries, declare relations, manage migrations, and add lifecycle hooks --- all with full TypeScript inference.

What is a query toolkit?

Unlike a full ORM, data-table does not manage entity instances or track changes behind the scenes. You define tables, write queries, and get back plain objects. The toolkit generates SQL for you and infers TypeScript types from your table definitions.

Key Features

Feature	What It Does
Table definitions	Define columns, primary keys, defaults, constraints, and enums with a chainable builder API.
Query builder	Fluent API for SELECT, INSERT, UPDATE, DELETE with type-safe column references.
Relations	belongsTo, hasOne, hasMany, and hasManyThrough with eager loading via .with().
Operators	eq, gt, lt, like, inList, between, and, or, and more for WHERE clauses.
Transactions	db.transaction() with automatic rollback on error and nested savepoints.
Lifecycle hooks	validate, beforeWrite, afterWrite, beforeDelete, afterDelete, afterRead.
Migrations	createMigration with up/down functions for schema changes.
Multi-database	One API, three adapters: SQLite, PostgreSQL, MySQL.

Quick Example

Define a table and query it:

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

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

// Connect to SQLite
let adapter = createSqliteDatabaseAdapter(new SQLite('app.db'))
let db = createDatabase(adapter)

// Create a row
await db.create(tasks, { title: 'Write docs' })

// Query rows
let pending = await db.findMany(tasks, {
  where: { done: false },
  orderBy: ['createdAt', 'desc'],
})

How Tables Work

A table definition is a schema declaration. It does not create the table in the database --- that is what migrations are for. The definition tells data-table the column names, types, and constraints so it can generate correct SQL and infer TypeScript types.

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', 'user']).notNull().default('user'),
  },
  timestamps: true,
})

What does timestamps: true do?

It auto-manages createdAt and updatedAt columns. On create, both are set to the current time. On update, updatedAt is refreshed automatically.

How Queries Work

There are two ways to query: direct methods and the query builder.

Direct methods are concise for common operations:

let user = await db.find(users, 1)
let admins = await db.findMany(users, { where: { role: 'admin' } })
let count = await db.count(users)

The query builder is a fluent API for complex queries:

import { gt } from 'remix/data-table/operators'

let results = await db.query(users)
  .where(gt('createdAt', new Date('2024-01-01')))
  .orderBy('name', 'asc')
  .limit(10)
  .all()

How Relations Work

Relations define how tables connect and enable eager loading:

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

let userPosts = hasMany(users, posts)
let postAuthor = belongsTo(posts, users)

let user = await db.find(users, 1, {
  with: { posts: userPosts },
})
// user.posts is an array of Post rows

Database Adapters

data-table does not talk to databases directly. You plug in an adapter:

Adapter	Package	Driver
SQLite	remix/data-table-sqlite	better-sqlite3
PostgreSQL	remix/data-table-postgres	pg
MySQL	remix/data-table-mysql	mysql2

Related

• Data Table Tutorial --- Build a task manager database step by step.
• Data Table Reference --- Full API reference for every export.
• Database Guide --- Practical patterns for production use.
• data-table-sqlite --- SQLite adapter overview.
• data-table-postgres --- PostgreSQL adapter overview.
• data-table-mysql --- MySQL adapter overview.