data-table

The data-table package is a typed relational query toolkit that works with PostgreSQL, MySQL, and SQLite. Define tables with typed columns, run queries with a fluent builder, declare relations, and manage migrations --- all with full TypeScript inference.

Installation

npm install remix

Or install the standalone package:

npm install @remix-run/data-table

You also need a database adapter. See data-table-sqlite, data-table-postgres, or data-table-mysql.

Imports

Main Entry

import {
  // Table definition
  table,
  column,
  timestamps,

  // Database
  createDatabase,
  Database,

  // Relations
  belongsTo,
  hasOne,
  hasMany,
  hasManyThrough,

  // Query builder
  query,

  // SQL
  sql,
  rawSql,

  // Operators
  eq, ne, gt, gte, lt, lte,
  like, ilike,
  inList, notInList,
  between, isNull, notNull,
  and, or,
} from 'remix/data-table'

Sub-Exports

// Operators only
import { eq, ne, gt, gte, lt, lte, like, ilike, inList, notInList, between, isNull, notNull, and, or } from 'remix/data-table/operators'

// Migrations
import { createMigration, createMigrationRegistry, createMigrationRunner, column } from 'remix/data-table/migrations'

// Node.js migration loader
import { loadMigrations } from 'remix/data-table/migrations/node'

// SQL helpers (for adapter authors)
import { collectColumns, quoteLiteral, quotePath, quoteTableRef } from 'remix/data-table/sql-helpers'

---

Defining Tables

table(options)

Creates a typed table definition. Returns a Table object used throughout the data-table API.

import { table, column as c } from 'remix/data-table'

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'),
    bio: c.text().nullable(),
    createdAt: c.timestamp().notNull().defaultNow(),
    updatedAt: c.timestamp().notNull().defaultNow(),
  },
  primaryKey: 'id',
  timestamps: true,
})

Table Options

Option	Type	Description
name	string	The SQL table name.
columns	Record<string, ColumnBuilder>	Column definitions using the column builder API.
primaryKey	string | string[]	Primary key column(s). Defaults to 'id' if the column exists.
timestamps	boolean | { createdAt?: string; updatedAt?: string }	Auto-managed timestamp columns. true uses created_at and updated_at.
validate	(context) => result	Validation hook that runs before writes.
beforeWrite	(context) => result	Hook invoked before a row write executes.
afterWrite	(context) => void	Hook invoked after a row write completes.
beforeDelete	(context) => result	Hook invoked before a delete operation.
afterDelete	(context) => void	Hook invoked after a delete operation.
afterRead	(context) => result	Hook invoked after a row is read.

Column References

Table objects expose typed column references as properties. These can be used for type-safe column lookups:

users.id       // ColumnReference<'users', 'id'>
users.email    // ColumnReference<'users', 'email'>

---

Column Types

The column namespace (commonly aliased as c) provides chainable column builders.

Type Constructors

Constructor	SQL Type	TypeScript Type
c.integer()	INTEGER	number
c.bigint()	BIGINT	unknown
c.text()	TEXT	string
c.varchar(length)	VARCHAR(n)	string
c.boolean()	BOOLEAN	boolean
c.decimal(precision, scale)	DECIMAL(p, s)	number
c.uuid()	UUID	string
c.date()	DATE	unknown
c.time(options?)	TIME	unknown
c.timestamp(options?)	TIMESTAMP	unknown
c.json()	JSON	unknown
c.binary(length?)	BINARY	unknown
c.enum(values)	ENUM	Union of values

Column Modifiers

All column builders support these chainable modifiers:

Modifier	Description
.nullable()	Marks the column as nullable.
.notNull()	Marks the column as non-nullable.
.default(value)	Sets a literal default value.
.defaultNow()	Sets the default to the current timestamp.
.defaultSql(expression)	Sets a raw SQL expression as the default.
.primaryKey()	Marks the column as part of the primary key.
.unique(name?)	Adds a unique constraint.
.references(table, name)	Adds a foreign key reference.
.references(table, columns, name)	Adds a foreign key reference with explicit columns.
.onDelete(action)	Foreign key delete action ('cascade', 'set null', 'restrict', etc.).
.onUpdate(action)	Foreign key update action.
.check(expression, name)	Adds a check constraint.
.comment(text)	Adds a database comment.
.computed(expression, options?)	Marks the column as computed.
.unsigned()	Marks the column as unsigned.
.autoIncrement()	Marks the column as auto-incrementing.
.identity(options?)	Configures an identity column.
.collate(name)	Sets the collation.
.charset(name)	Sets the character set.
.length(value)	Sets the column length.
.precision(value, scale?)	Sets numeric precision and optional scale.
.scale(value)	Sets numeric scale.
.timezone(enabled?)	Enables or disables timezone support.

Examples

import { column as c } from 'remix/data-table'

// Auto-incrementing primary key
c.integer().primaryKey().autoIncrement()

// Required email with uniqueness
c.varchar(255).notNull().unique('users_email_uq')

// Enum with default
c.enum(['draft', 'published', 'archived']).notNull().default('draft')

// Foreign key with cascade delete
c.integer().notNull()
  .references('users', 'fk_posts_user_id')
  .onDelete('cascade')

// Timestamp with timezone
c.timestamp({ withTimezone: true }).notNull().defaultNow()

// Decimal for currency
c.decimal(10, 2).notNull().default(0)

---

Database

createDatabase(adapter, options?)

Creates a Database instance from an adapter.

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

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

new Database(adapter, options?)

Equivalent to createDatabase().

Options

Option	Type	Default	Description
now	() => unknown	() => new Date()	Clock function used for auto-managed timestamps.

---

Database Methods

db.find(table, primaryKey, options?)

Finds a single row by primary key. Returns null if not found.

let user = await db.find(users, 1)
let user = await db.find(users, 1, { with: { posts: hasMany(users, posts) } })

db.findOne(table, options)

Finds a single row matching a where clause. Returns null if not found.

let user = await db.findOne(users, {
  where: { email: 'alice@example.com' },
  orderBy: ['createdAt', 'desc'],
  with: { posts: hasMany(users, posts) },
})

db.findMany(table, options?)

Finds all rows matching the options. Returns an array.

let activeUsers = await db.findMany(users, {
  where: { role: 'admin' },
  orderBy: [['name', 'asc']],
  limit: 10,
  offset: 0,
  with: { posts: hasMany(users, posts) },
})

FindManyOptions

Option	Type	Description
where	WhereInput	Filter conditions.
orderBy	[column, direction?] | [column, direction?][]	Sort order. Direction is 'asc' or 'desc'.
limit	number	Maximum number of rows.
offset	number	Number of rows to skip.
with	Record<string, Relation>	Relations to eagerly load.

db.create(table, values, options?)

Inserts a single row. Returns a WriteResult by default, or the full row if returnRow: true.

// Returns WriteResult { affectedRows, insertId? }
let result = await db.create(users, { email: 'alice@example.com', name: 'Alice' })

// Returns the full inserted row
let user = await db.create(users, { email: 'alice@example.com', name: 'Alice' }, { returnRow: true })

db.createMany(table, values, options?)

Inserts multiple rows. Returns a WriteResult by default, or an array of rows if returnRows: true.

let result = await db.createMany(users, [
  { email: 'alice@example.com', name: 'Alice' },
  { email: 'bob@example.com', name: 'Bob' },
])

// Returns the inserted rows (requires adapter support for RETURNING)
let rows = await db.createMany(users, [...], { returnRows: true })

db.update(table, primaryKey, changes, options?)

Updates a single row by primary key. Returns the updated row.

let updated = await db.update(users, 1, { name: 'Alice Smith' })
let updated = await db.update(users, 1, { name: 'Alice Smith' }, {
  touch: false,  // Skip updating the updatedAt timestamp
  with: { posts: hasMany(users, posts) },
})

db.updateMany(table, changes, options)

Updates multiple rows matching a where clause. Returns a WriteResult.

let result = await db.updateMany(users, { role: 'user' }, {
  where: { role: 'guest' },
  orderBy: ['createdAt', 'asc'],
  limit: 100,
})

db.delete(table, primaryKey)

Deletes a single row by primary key. Returns true if a row was deleted.

let deleted = await db.delete(users, 1)  // true or false

db.deleteMany(table, options)

Deletes multiple rows matching a where clause. Returns a WriteResult.

let result = await db.deleteMany(users, {
  where: { role: 'guest' },
})

db.count(table, options?)

Counts rows matching an optional where clause.

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

db.exec(statement, values?)

Executes a raw SQL statement. Also accepts SqlStatement values from the sql or rawSql template tags.

let result = await db.exec('DELETE FROM sessions WHERE expires_at < NOW()')
let result = await db.exec(sql`DELETE FROM sessions WHERE expires_at < ${cutoff}`)

db.query(table)

Returns a bound Query builder for the given table. See Query Builder below.

let activeUsers = await db.query(users)
  .where({ role: 'admin' })
  .orderBy('name', 'asc')
  .limit(10)
  .all()

db.transaction(callback, options?)

Executes a callback within a database transaction. If the callback throws, the transaction is rolled back. Supports nested transactions via savepoints.

await db.transaction(async (tx) => {
  let user = await tx.create(users, { email: 'alice@example.com', name: 'Alice' }, { returnRow: true })
  await tx.create(posts, { userId: user.id, title: 'First Post' })
})

---

Query Builder

The query() function creates a fluent query builder. When obtained via db.query(table), it is bound to the database and can execute queries directly.

import { query } from 'remix/data-table'

// Unbound query (must be passed to db.exec() to run)
let q = query(users).where({ role: 'admin' }).orderBy('name', 'asc')

// Bound query (from db.query())
let results = await db.query(users)
  .where({ role: 'admin' })
  .select(['id', 'name', 'email'])
  .orderBy('name', 'asc')
  .limit(10)
  .offset(20)
  .all()

Query Methods

Filtering

Method	Description
.where(input)	Adds a WHERE clause. Accepts an object { column: value } or operators.
.distinct()	Returns only distinct rows.

Selection

Method	Description
.select(columns)	Selects specific columns.

Sorting & Pagination

Method	Description
.orderBy(column, direction?)	Sorts by a column. Direction is 'asc' or 'desc'.
.limit(n)	Limits the number of rows returned.
.offset(n)	Skips the first n rows.

Joins

Method	Description
.join(table, on, type?)	Adds a JOIN clause. Type is 'inner', 'left', 'right', or 'full'.

Relations

Method	Description
.with(relations)	Eagerly loads relations on the result rows.

Execution (bound queries only)

Method	Returns	Description
.all()	Promise<row[]>	Executes the query and returns all matching rows.
.first()	Promise<row | null>	Returns the first matching row or null.
.find(primaryKey)	Promise<row | null>	Finds a row by primary key.
.count()	Promise<number>	Returns the count of matching rows.
.exists()	Promise<boolean>	Returns whether any matching rows exist.
.insert(values, options?)	Promise<WriteResult>	Inserts a row via the query.
.insertMany(values, options?)	Promise<WriteResult>	Inserts multiple rows.
.update(changes, options?)	Promise<WriteResult>	Updates matching rows.
.delete(options?)	Promise<WriteResult>	Deletes matching rows.
.upsert(values, options?)	Promise<WriteResult>	Inserts or updates on conflict.

---

Relations

Relations define how tables are connected and enable eager loading with .with().

belongsTo(source, target, options?)

Defines a relation where the source table has a foreign key pointing to the target table. Returns a Relation with cardinality 'one'.

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

let postAuthor = belongsTo(posts, users, {
  foreignKey: 'userId',   // Column on posts (default: inferred from target table name)
  targetKey: 'id',         // Column on users (default: primary key)
})

let post = await db.findOne(posts, {
  where: { id: 1 },
  with: { author: postAuthor },
})
// post.author is a User row

hasOne(source, target, options?)

Defines a relation where the target table has a foreign key pointing to the source. Returns a Relation with cardinality 'one'.

import { hasOne } from 'remix/data-table'

let userProfile = hasOne(users, profiles, {
  foreignKey: 'userId',   // Column on profiles
  targetKey: 'id',         // Column on users (default: primary key)
})

hasMany(source, target, options?)

Defines a relation where the target table has a foreign key pointing to the source. Returns a Relation with cardinality 'many'.

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

let userPosts = hasMany(users, posts, {
  foreignKey: 'userId',   // Column on posts
  targetKey: 'id',         // Column on users (default: primary key)
})

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

hasManyThrough(source, target, options)

Defines a many-to-many relation through a join table.

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

let userRoles = hasMany(users, userRolesJoin)

let roles = hasManyThrough(users, roles, {
  through: userRoles,
  throughTargetKey: 'roleId',
  throughForeignKey: 'id',
})

Relation Modifiers

All relation objects support chainable modifiers:

let recentPosts = hasMany(users, posts)
  .where({ status: 'published' })
  .orderBy('createdAt', 'desc')
  .limit(5)

let user = await db.find(users, 1, {
  with: {
    recentPosts,
    profile: hasOne(users, profiles),
  },
})

Modifier	Description
.where(input)	Filters related rows.
.orderBy(column, direction?)	Sorts related rows.
.limit(n)	Limits the number of related rows.
.offset(n)	Skips related rows.
.with(relations)	Nested eager loading on related rows.

---

Operators

Import from remix/data-table or remix/data-table/operators:

import { eq, ne, gt, gte, lt, lte, like, ilike, inList, notInList, between, isNull, notNull, and, or } from 'remix/data-table/operators'

Where Input

The where option accepts an object of column-value pairs (implicit eq) or operator functions:

// Simple equality
await db.findMany(users, { where: { role: 'admin' } })

// Using operators
await db.findMany(users, {
  where: and(
    eq('role', 'admin'),
    gte('createdAt', new Date('2024-01-01')),
    like('email', '%@example.com'),
  ),
})

Operator Reference

Operator	Description	Example
eq(column, value)	Equal to	eq('role', 'admin')
ne(column, value)	Not equal to	ne('status', 'deleted')
gt(column, value)	Greater than	gt('age', 18)
gte(column, value)	Greater than or equal	gte('price', 10)
lt(column, value)	Less than	lt('stock', 5)
lte(column, value)	Less than or equal	lte('price', 100)
like(column, pattern)	SQL LIKE (case-sensitive)	like('name', 'A%')
ilike(column, pattern)	SQL ILIKE (case-insensitive)	ilike('email', '%@GMAIL.COM')
inList(column, values)	IN list	inList('id', [1, 2, 3])
notInList(column, values)	NOT IN list	notInList('status', ['deleted', 'banned'])
between(column, low, high)	BETWEEN range	between('price', 10, 50)
isNull(column)	IS NULL	isNull('deletedAt')
notNull(column)	IS NOT NULL	notNull('email')
and(...predicates)	Combine with AND	and(eq('a', 1), gt('b', 2))
or(...predicates)	Combine with OR	or(eq('role', 'admin'), eq('role', 'moderator'))

---

SQL Template Tags

sql

Tagged template for parameterized SQL. Values are passed as bind parameters, preventing SQL injection.

import { sql } from 'remix/data-table'

let statement = sql`SELECT * FROM users WHERE email = ${email} AND role = ${role}`
let result = await db.exec(statement)

rawSql

Tagged template for raw SQL strings. Interpolated values are inserted literally (not parameterized). Use only with trusted values.

import { rawSql } from 'remix/data-table'

let tableName = 'users'
let statement = rawSql`DROP TABLE ${tableName}`

WARNING

rawSql does not escape or parameterize values. Never use it with user input.

---

Lifecycle Hooks

Table lifecycle hooks run during database operations. They can transform data or reject operations.

validate

Runs before writes (create and update). Can transform the value or return validation issues.

let users = table({
  name: 'users',
  columns: { /* ... */ },
  validate({ operation, tableName, value }) {
    if (operation === 'create' && !value.email) {
      return { issues: [{ message: 'Email is required', path: ['email'] }] }
    }
    return { value }
  },
})

beforeWrite

Runs before a row write (after validation). Can transform the value.

let users = table({
  name: 'users',
  columns: { /* ... */ },
  beforeWrite({ operation, value }) {
    return { value: { ...value, email: value.email?.toLowerCase() } }
  },
})

afterWrite

Runs after a write completes. Receives the written values and affected row count.

let users = table({
  name: 'users',
  columns: { /* ... */ },
  afterWrite({ operation, values, affectedRows }) {
    console.log(`${operation}: ${affectedRows} rows affected`)
  },
})

beforeDelete / afterDelete

Run before and after delete operations.

afterRead

Runs after a row is read. Can transform the returned data.

let users = table({
  name: 'users',
  columns: { /* ... */ },
  afterRead({ value }) {
    return { value: { ...value, displayName: value.name?.toUpperCase() } }
  },
})

---

Migrations

createMigration(options)

Creates a migration descriptor.

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(),
      createdAt: c.timestamp().notNull().defaultNow(),
      updatedAt: c.timestamp().notNull().defaultNow(),
    })

    ctx.createIndex('users', ['email'], { name: 'idx_users_email' })
  },

  down(ctx) {
    ctx.dropIndex('idx_users_email')
    ctx.dropTable('users')
  },
})

createMigrationRegistry()

Creates a migration registry that collects migration descriptors.

createMigrationRunner(adapter, options?)

Creates a migration runner that executes migrations against a database adapter.

loadMigrations(directory)

Node.js helper that loads migration files from a directory. Import from remix/data-table/migrations/node.

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

let migrations = await loadMigrations('./migrations')

---

Type Helpers

Type	Description
TableRow<table>	The row shape produced by a table (all columns resolved to their output types).
TableRowWith<table, relations>	A table row with eagerly loaded relations included.
TableColumns<table>	The column builder map for a table.
TableName<table>	The string literal type of the table's name.
TablePrimaryKey<table>	The primary key column tuple for a table.
AnyTable	A table with erased column types.
AnyRelation	A relation with erased types.
Table<name, columns, primaryKey>	Fully typed table object.
Relation<source, target, cardinality>	Typed relation between two tables.

---

Error Types

Error Class	Description
DataTableError	Base error for all data-table errors.
DataTableQueryError	Error during query execution.
DataTableValidationError	Validation failure from lifecycle hooks.
DataTableConstraintError	Database constraint violation.
DataTableAdapterError	Error from the database adapter.

---

Related

• Database Guide --- Practical patterns for working with the data layer.
• Tutorial: Database --- Step-by-step database setup.
• data-table-sqlite --- SQLite adapter.
• data-table-postgres --- PostgreSQL adapter.
• data-table-mysql --- MySQL adapter.
• data-schema --- Schema validation for form data and JSON.