remix/data-table

Typed relational query toolkit for JavaScript runtimes.

Features

One API Across Databases: Same query and relation APIs across PostgreSQL, MySQL, and SQLite
One Query API: Build reusable Query objects with query(table) and execute them with db.exec(...), or use db.query(table) as shorthand
Type-Safe Reads: Typed select, relation loading, and predicate keys
Optional Runtime Validation: Add validate(context) at the table level for create/update validation and coercion
Relation-First Queries: hasMany, hasOne, belongsTo, hasManyThrough, and nested eager loading
Safe Scoped Writes: update/delete with orderBy/limit run safely in a transaction
First-Class Migrations: Plain SQL up.sql/down.sql files with a journaling runner and dry-run planning
Database Lifecycle Commands: Wipe, migrate, rollback, inspect, seed, and reset through remix db
Raw SQL Escape Hatch: Execute SQL directly with db.exec(sql\...`)`

data-table gives you two complementary APIs:

Query Objects for expressive joins, aggregates, eager loading, and scoped writes
CRUD Helpers for common create/read/update/delete flows (find, create, update, delete)

Both APIs are type-safe. Runtime validation is opt-in with table-level validate(context).

Installation

npm i remix
npm i pg
# or
npm i mysql2
# or
# use the SQLite client built into your runtime

Setup

Define tables once, then create a database for your SQL dialect.

import { column as c, hasMany, query, table } from 'remix/data-table'
import { createPostgresDatabase } from 'remix/data-table/postgres'

let users = table({
  name: 'users',
  columns: {
    id: c.uuid(),
    email: c.varchar(255),
    role: c.enum(['customer', 'admin']),
    created_at: c.integer(),
  },
})

let orders = table({
  name: 'orders',
  columns: {
    id: c.uuid(),
    user_id: c.uuid(),
    status: c.enum(['pending', 'processing', 'shipped', 'delivered']),
    total: c.decimal(10, 2),
    created_at: c.integer(),
  },
})

let userOrders = hasMany(users, orders)

let db = createPostgresDatabase({ connectionString: process.env.DATABASE_URL })

Use query(table) when you want to build a standalone reusable query object. Execute it later with db.exec(query). Use db.query(table) when you want the same chainable Query already bound to a database instance.

Standalone Query Builder

query(table) is the primary query-builder API. It gives you an unbound Query value that can be composed, stored, reused, and executed against any compatible database instance.

import { eq, ilike, query } from 'remix/data-table'

let pendingOrdersForExampleUsers = query(orders)
  .join(users, eq(orders.user_id, users.id))
  .where({ status: 'pending' })
  .where(ilike(users.email, '%@example.com'))
  .select({
    orderId: orders.id,
    customerEmail: users.email,
    total: orders.total,
    placedAt: orders.created_at,
  })
  .orderBy(orders.created_at, 'desc')
  .limit(20)

let recentPendingOrders = await db.exec(pendingOrdersForExampleUsers)

Unbound queries stay lazy until you pass them to db.exec(...):

let shippedCustomerQuery = query(users)
  .where({ role: 'customer' })
  .with({
    recentOrders: userOrders.where({ status: 'shipped' }).orderBy('created_at', 'desc').limit(3),
  })

let customers = await db.exec(shippedCustomerQuery)

// customers[0].recentOrders is fully typed

The same standalone query builder also handles terminal read and write operations:

let nextPendingOrder = await db.exec(
  query(orders).where({ status: 'pending' }).orderBy('created_at', 'asc').first(),
)

await db.exec(
  query(orders)
    .where({ status: 'pending' })
    .orderBy('created_at', 'asc')
    .limit(100)
    .update({ status: 'processing' }),
)

Bound Query Shorthand

If you already have a db instance in hand and do not need a standalone query value, db.query(table) returns the same query builder already bound to that database:

let recentPendingOrders = await db
  .query(orders)
  .where({ status: 'pending' })
  .orderBy('created_at', 'desc')
  .limit(20)
  .all()

CRUD Helpers

data-table provides helpers for common create/read/update/delete operations. Use these helpers for common operations without building a full query chain.

Read operations

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

let user = await db.find(users, 'u_001')

let firstPending = await db.findOne(orders, {
  where: { status: 'pending' },
  orderBy: ['created_at', 'asc'],
})

let page = await db.findMany(orders, {
  where: or({ status: 'pending' }, { status: 'processing' }),
  orderBy: [
    ['status', 'asc'],
    ['created_at', 'desc'],
  ],
  limit: 50,
  offset: 0,
})

where accepts the same single-table object/predicate inputs as query().where(...), and orderBy uses tuple form:

['column', 'asc' | 'desc']
[['columnA', 'asc'], ['columnB', 'desc']]

Create helpers

// Default: metadata (affectedRows/insertId)
let createResult = await db.create(users, {
  id: 'u_002',
  email: '[email protected]',
  role: 'customer',
  created_at: Date.now(),
})

// Return a typed row (with optional relations)
let createdUser = await db.create(
  users,
  {
    id: 'u_003',
    email: '[email protected]',
    role: 'customer',
    created_at: Date.now(),
  },
  {
    returnRow: true,
    with: { recentOrders: userOrders.orderBy('created_at', 'desc').limit(1) },
  },
)

// Bulk insert metadata
let createManyResult = await db.createMany(orders, [
  { id: 'o_101', user_id: 'u_002', status: 'pending', total: 24.99, created_at: Date.now() },
  { id: 'o_102', user_id: 'u_003', status: 'pending', total: 48.5, created_at: Date.now() },
])

// Return inserted rows (requires database RETURNING support)
let insertedRows = await db.createMany(
  orders,
  [{ id: 'o_103', user_id: 'u_003', status: 'pending', total: 12, created_at: Date.now() }],
  { returnRows: true },
)

createMany/insertMany throw when every row in the batch is empty (no explicit values).

Update and delete helpers

let updatedUser = await db.update(users, 'u_003', { role: 'admin' })

let updateManyResult = await db.updateMany(
  orders,
  { status: 'processing' },
  {
    where: { status: 'pending' },
    orderBy: ['created_at', 'asc'],
    limit: 25,
  },
)

let deletedUser = await db.delete(users, 'u_002')

let deleteManyResult = await db.deleteMany(orders, {
  where: { status: 'delivered' },
  orderBy: [['created_at', 'asc']],
  limit: 200,
})

db.update(...) throws when the target row cannot be found.

Return behavior:

find/findOne -> row or null
findMany -> rows
create -> WriteResult by default, row when returnRow: true
createMany -> WriteResult by default, rows when returnRows: true (not supported in MySQL because it doesn't support RETURNING)
update -> updated row (throws when target row is missing)
updateMany/deleteMany -> WriteResult
delete -> boolean

Validation and Lifecycle

Validation is optional and table-scoped. Define validate(context) to validate/coerce write payloads, and add lifecycle callbacks when you need custom read/write/delete behavior.

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

let payments = table({
  name: 'payments',
  columns: {
    id: c.uuid(),
    amount: c.decimal(10, 2),
  },
  beforeWrite({ value }) {
    return {
      value: {
        ...value,
        amount: typeof value.amount === 'string' ? value.amount.trim() : value.amount,
      },
    }
  },
  validate({ operation, value }) {
    if (operation === 'create' && typeof value.amount === 'string') {
      let amount = Number(value.amount)

      if (!Number.isFinite(amount)) {
        return fail('Expected a numeric amount', ['amount'])
      }

      return { value: { ...value, amount } }
    }

    return { value }
  },
  beforeDelete({ where }) {
    if (where.length === 0) {
      return fail('Refusing unscoped delete')
    }
  },
  afterRead({ value }) {
    if (!('amount' in value)) {
      return { value }
    }

    return {
      value: {
        ...value,
        // Example read-time shaping
        amount:
          typeof value.amount === 'number' ? Math.round(value.amount * 100) / 100 : value.amount,
      },
    }
  },
})

Use fail(...) in hooks when you want to return issues without manually building { issues: [...] }.

Validation and lifecycle semantics:

Write order is beforeWrite -> validate -> timestamp/default touch -> execute -> afterWrite
validate runs for writes (create, createMany, insert, insertMany, update, updateMany, upsert)
Hook context includes { operation: 'create' | 'update', tableName, value }
Write payloads are partial objects
Unknown columns fail validation before and after hook processing
beforeDelete can veto deletes by returning { issues }
afterDelete runs after successful deletes with affectedRows
afterRead runs for each loaded row (root rows, eager-loaded relation rows, and write-returning rows)
afterRead receives the current read shape, which may be partial/projection rows; guard field access accordingly
Predicate values (where, having, join predicates) are not runtime-validated
Lifecycle callbacks are synchronous; returning a Promise throws a validation error
Callback validation errors include metadata.source (beforeWrite, validate, beforeDelete, afterRead, etc.) for easier debugging
Callbacks do not introduce implicit transactions (use db.transaction(...) when you need rollback guarantees)

Transactions

await db.transaction(async (tx) => {
  let user = await tx.create(
    users,
    { id: 'u_010', email: '[email protected]', role: 'customer', created_at: Date.now() },
    { returnRow: true },
  )

  await tx.create(orders, {
    id: 'o_500',
    user_id: user.id,
    status: 'pending',
    total: 79,
    created_at: Date.now(),
  })
})

Migrations

data-table ships a SQL-first migration system under remix/data-table/migrations. Each migration is a directory containing hand-written up.sql and (optionally) down.sql. The runner journals applied migrations, detects checksum drift and missing applied migrations, and wraps each migration in a transaction when the database supports transactional DDL.

Example Setup

app/
  db/
    migrations/
      20260228090000_create_users/
        up.sql
        down.sql
      20260301113000_add_user_status/
        up.sql
        down.sql
  db.ts

Keep migration directories in one parent directory (for example app/db/migrations).
Each directory is named YYYYMMDDHHmmss_<slug>.
up.sql is required. down.sql is optional (omit for irreversible migrations).
Scripts may contain multiple statements. id and name are inferred from the directory name.

Migration File Example

20260228090000_create_users/up.sql:

create table users (
  id serial primary key,
  email varchar(255) not null unique,
  created_at timestamptz not null default now()
);

create unique index users_email_idx on users (email);

20260228090000_create_users/down.sql:

drop index if exists users_email_idx;
drop table if exists users;

Multi-Statement Driver Configuration

The runner sends each migration to the database as a single multi-statement script. Make sure the underlying driver accepts multiple statements:

better-sqlite3: works out of the box (db.exec).
pg: works out of the box when no parameter array is passed.
mysql2: requires multipleStatements: true on the connection/pool.

import { createMysqlDatabase } from 'remix/data-table/mysql'

let db = createMysqlDatabase({
  uri: process.env.DATABASE_URL,
  multipleStatements: true,
})

Database Command Configuration

Configure remix db statically in remix.json. Connection secrets are read from the named environment variable at command runtime, and paths are resolved relative to remix.json:

{
  "$schema": "https://remix.run/schemas/remix.json",
  "db": {
    "adapter": {
      "type": "postgres",
      "connectionString": { "env": "DATABASE_URL" },
    },
    "migrations": {
      "directory": "./db/migrations",
      "journalTable": "app_migrations",
    },
    "seed": "./db/seed.sql",
  },
}

Application runtime setup remains application-owned. It does not need to expose any special exports for the CLI.

Run lifecycle commands through the Remix CLI:

remix db status
remix db migrate
remix db migrate --to 20260301113000_add_user_status
remix db rollback
remix db rollback --step 2
remix db rollback --to 20260301113000_add_user_status
remix db rollback --dry-run
remix db seed
remix db reset --force
remix db wipe --force

rollback reverts the most recently applied migration by default. Bound it with either --step <count> or --to <migration>, which reverts through the target migration inclusively. Migration targets accept a bare migration id (20260301113000) or the full directory name (20260301113000_add_user_status). Use --dry-run to report what would be reverted without changing the database.

remix db status reports applied migrations whose files are no longer present as missing. If the journal table does not exist, it reports every migration as pending without creating the table. Forward migration runs stop before executing SQL when an applied journal entry is missing from the current migration set. Rollbacks skip those orphaned journal entries so migrations that are still present can be reverted.

wipe and reset are destructive. They require a config-backed database so it can close, recreate, and reconnect to the configured database.

Programmatic Migrations

Load migrations and pass the resolved collection directly to the database:

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

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

Database.migrate() supports forward and backward directions, a target or step bound, dry runs, and a custom journal table:

await db.migrate(migrations)
await db.migrate(migrations, { to: '20260301113000_add_user_status' })
await db.migrate(migrations, { step: 1 })
await db.migrate(migrations, { direction: 'down' })
await db.migrate(migrations, { direction: 'down', to: '20260301113000' })
await db.migrate(migrations, { direction: 'down', step: 1 })
await db.migrate(migrations, { journalTable: 'app_migrations' })

let plan = await db.migrate(migrations, { dryRun: true })
for (let script of plan.sql) console.log(script)

to and step are mutually exclusive. Omit journalTable to use data_table_migrations.

Hosts that embed the database CLI can invoke the same rollback behavior through runRemixDb():

import { runRemixDb } from 'remix/data-table/cli'

await runRemixDb({ command: 'rollback', db, migrations })
await runRemixDb({ command: 'rollback', db, migrations, step: 2, dryRun: true })

Database drivers with migration locking run the complete migration and journal lifecycle through the connection that owns the lock. This keeps advisory locks correctly paired when the driver uses a connection pool, including pools configured with a single connection.

Read status separately, or rebuild a database with migrations and an optional seed. A seed is a function that receives the database; loadSeed() builds one from a SQL file:

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

let seed = await loadSeed('./db/seed.sql')

let status = await db.migrationStatus(migrations, { journalTable: 'app_migrations' })
await db.reset({ migrations })
await db.reset({ migrations, seed })
await db.reset({ migrations, seed, journalTable: 'app_migrations' })

When a lifecycle command is the last thing a process does, close database-owned connections so the process can exit:

await db.close()

Transaction Modes

By default each migration is wrapped in a transaction when the database supports transactional DDL. Override per migration with a directive on the first non-blank line of up.sql:

-- data-table/transaction: none
create index concurrently users_email_active_idx on users (email) where status = 'active';

Supported modes:

auto (default): wrap when the database supports transactional DDL.
required: wrap; the runner throws if the database cannot support it.
none: never wrap. Use this for statements like postgres CREATE INDEX CONCURRENTLY that cannot run inside a transaction.

You can also set transaction directly on a MigrationDescriptor when registering migrations programmatically.

Programmatic Registration

For non-filesystem runtimes, register migrations directly:

import { createMigrationRegistry } from 'remix/data-table/migrations'

let registry = createMigrationRegistry()
registry.register({
  id: '20260228090000',
  name: 'create_users',
  up: 'create table users (id serial primary key, email text not null);',
  down: 'drop table users;',
})

await db.migrate(registry)

Raw SQL Escape Hatch

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

await db.exec(sql`select * from users where id = ${'u_001'}`)
await db.exec(rawSql('update users set role = ? where id = ?', ['admin', 'u_001']))

Use sql when you need raw SQL plus safe value interpolation:

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

let email = input.email
let minCreatedAt = input.minCreatedAt

let result = await db.exec(sql`
  select id, email
  from users
  where email = ${email}
    and created_at >= ${minCreatedAt}
`)

sql keeps values parameterized for the database dialect, so you can avoid manual string concatenation.

Custom Database Drivers

Applications normally use one of the concrete SQLite, PostgreSQL, or MySQL factories. Integration packages can add another dialect by implementing DatabaseDriver and extending Database. This complete skeleton assumes the underlying client exposes the operations needed by the driver:

import {
  Database,
  type DatabaseDriver,
  type DataManipulationRequest,
  type DataManipulationResult,
  type DatabaseOptions,
  type TableRef,
  type TransactionOptions,
  type TransactionToken,
} from 'remix/data-table'
import type { AcmeClient } from 'acme-database'

class AcmeDriver implements DatabaseDriver<'acme'> {
  readonly dialect = 'acme'
  readonly capabilities = {
    returning: true,
    savepoints: true,
    upsert: true,
    transactionalDdl: true,
    migrationLock: false,
  } as const

  #client: AcmeClient

  constructor(client: AcmeClient) {
    this.#client = client
  }

  execute(request: DataManipulationRequest): Promise<DataManipulationResult> {
    return this.#client.execute(request)
  }

  executeScript(sql: string, transaction?: TransactionToken): Promise<void> {
    return this.#client.executeScript(sql, transaction)
  }

  beginTransaction(options?: TransactionOptions): Promise<TransactionToken> {
    return this.#client.beginTransaction(options)
  }

  commitTransaction(transaction: TransactionToken): Promise<void> {
    return this.#client.commitTransaction(transaction)
  }

  rollbackTransaction(transaction: TransactionToken): Promise<void> {
    return this.#client.rollbackTransaction(transaction)
  }

  hasTable(table: TableRef, transaction?: TransactionToken): Promise<boolean> {
    return this.#client.hasTable(table, transaction)
  }

  hasColumn(table: TableRef, column: string, transaction?: TransactionToken): Promise<boolean> {
    return this.#client.hasColumn(table, column, transaction)
  }

  createSavepoint(transaction: TransactionToken, name: string): Promise<void> {
    return this.#client.createSavepoint(transaction, name)
  }

  rollbackToSavepoint(transaction: TransactionToken, name: string): Promise<void> {
    return this.#client.rollbackToSavepoint(transaction, name)
  }

  releaseSavepoint(transaction: TransactionToken, name: string): Promise<void> {
    return this.#client.releaseSavepoint(transaction, name)
  }

  wipe(): Promise<void> {
    return this.#client.wipe()
  }

  close(): void | Promise<void> {
    return this.#client.close()
  }
}

export class AcmeDatabase extends Database<'acme'> {
  constructor(client: AcmeClient, options?: DatabaseOptions) {
    super(new AcmeDriver(client), options)
  }
}

Database supplies queries, CRUD helpers, relations, transactions, and migrations. The private driver owns SQL execution and connection lifecycle. A driver provides:

dialect and an immutable capabilities object
execute() and executeScript()
hasTable(), hasColumn(), wipe(), and idempotent close()
transaction and savepoint lifecycle methods using opaque TransactionToken values

Drivers whose capabilities report migrationLock: true also implement withMigrationLock() and run its callback with a DatabaseDriver bound to the connection that owns the lock.

Database, DatabaseDriver, and the supporting driver protocol types are all exported directly from remix/data-table. Transaction callbacks receive a transaction-scoped Database, so custom subclass methods are intentionally unavailable inside the callback.

data-schema - Optional schema parsing you can use inside table-level validate(...) hooks
data-table-postgres - PostgreSQL database integration
data-table-mysql - MySQL database integration
data-table-sqlite - SQLite database integration

License

See LICENSE