data-table - Remix

Overview

data-table provides a type-safe relational query builder and CRUD helpers for working with databases in Remix applications. It supports PostgreSQL, MySQL, and SQLite through database-specific adapters. Key Features:

One API across databases (PostgreSQL, MySQL, SQLite)
Chainable query builder and high-level CRUD helpers
Type-safe reads with typed select, relations, and predicates
Optional runtime validation at the table level
Relation-first queries (hasMany, hasOne, belongsTo, hasManyThrough)
Safe scoped writes with automatic transaction handling
First-class migrations with up/down support
Raw SQL escape hatch

Installation

npm i remix
# Choose your database driver:
npm i pg                 # PostgreSQL
npm i mysql2             # MySQL
npm i better-sqlite3     # SQLite

Core Types

Database

High-level database runtime for building and executing data manipulation operations.

type Database = {
  adapter: DatabaseAdapter
  now(): unknown
  query: QueryMethod
  
  // CRUD operations
  create<table extends AnyTable>(
    table: table,
    values: Partial<TableRow<table>>,
    options?: CreateResultOptions,
  ): Promise<WriteResult>
  
  create<table extends AnyTable, relations>(
    table: table,
    values: Partial<TableRow<table>>,
    options: CreateRowOptions<table, relations>,
  ): Promise<TableRowWith<table, LoadedRelationMap<relations>>>
  
  createMany<table extends AnyTable>(
    table: table,
    values: Array<Partial<TableRow<table>>>,
    options?: CreateManyResultOptions,
  ): Promise<WriteResult>
  
  createMany<table extends AnyTable>(
    table: table,
    values: Array<Partial<TableRow<table>>>,
    options: CreateManyRowsOptions,
  ): Promise<TableRow<table>[]>
  
  find<table extends AnyTable, relations>(
    table: table,
    value: PrimaryKeyInput<table>,
    options?: { with?: relations },
  ): Promise<TableRowWith<table, LoadedRelationMap<relations>> | null>
  
  findOne<table extends AnyTable, relations>(
    table: table,
    options: FindOneOptions<table, relations>,
  ): Promise<TableRowWith<table, LoadedRelationMap<relations>> | null>
  
  findMany<table extends AnyTable, relations>(
    table: table,
    options?: FindManyOptions<table, relations>,
  ): Promise<Array<TableRowWith<table, LoadedRelationMap<relations>>>>
  
  count<table extends AnyTable>(
    table: table,
    options?: CountOptions<table>
  ): Promise<number>
  
  update<table extends AnyTable, relations>(
    table: table,
    value: PrimaryKeyInput<table>,
    changes: Partial<TableRow<table>>,
    options?: UpdateOptions<table, relations>,
  ): Promise<TableRowWith<table, LoadedRelationMap<relations>>>
  
  updateMany<table extends AnyTable>(
    table: table,
    changes: Partial<TableRow<table>>,
    options: UpdateManyOptions<table>,
  ): Promise<WriteResult>
  
  delete<table extends AnyTable>(
    table: table,
    value: PrimaryKeyInput<table>
  ): Promise<boolean>
  
  deleteMany<table extends AnyTable>(
    table: table,
    options: DeleteManyOptions<table>,
  ): Promise<WriteResult>
  
  exec(
    statement: string | SqlStatement,
    values?: unknown[]
  ): Promise<DataManipulationResult>
  
  transaction<result>(
    callback: (database: Database) => Promise<result>,
    options?: TransactionOptions,
  ): Promise<result>
}

QueryBuilder

Immutable query builder returned by db.query(table). All methods return a new builder instance.

class QueryBuilder<columnTypes, row, loaded, tableName, primaryKey> {
  // Selection
  select<selection extends (keyof row & string)[]>(
    ...columns: selection
  ): QueryBuilder<columnTypes, Pick<row, selection[number]>, loaded, tableName, primaryKey>
  
  select<selection extends Record<string, QueryColumnInput<columnTypes>>>(
    selection: selection,
  ): QueryBuilder<columnTypes, SelectedAliasRow<columnTypes, selection>, loaded, tableName, primaryKey>
  
  distinct(value?: boolean): QueryBuilder<columnTypes, row, loaded, tableName, primaryKey>
  
  // Filtering
  where(
    input: WhereInput<QueryColumns<columnTypes>>,
  ): QueryBuilder<columnTypes, row, loaded, tableName, primaryKey>
  
  having(
    input: WhereInput<QueryColumns<columnTypes>>,
  ): QueryBuilder<columnTypes, row, loaded, tableName, primaryKey>
  
  // Joins
  join<target extends AnyTable>(
    target: target,
    on: Predicate<QueryColumns<columnTypes> | QueryColumnName<target>>,
    type?: JoinType,
  ): QueryBuilder<MergeColumnTypeMaps<columnTypes, QueryColumnTypeMap<target>>, row, loaded, tableName, primaryKey>
  
  leftJoin<target extends AnyTable>(
    target: target,
    on: Predicate<QueryColumns<columnTypes> | QueryColumnName<target>>,
  ): QueryBuilder<MergeColumnTypeMaps<columnTypes, QueryColumnTypeMap<target>>, row, loaded, tableName, primaryKey>
  
  rightJoin<target extends AnyTable>(
    target: target,
    on: Predicate<QueryColumns<columnTypes> | QueryColumnName<target>>,
  ): QueryBuilder<MergeColumnTypeMaps<columnTypes, QueryColumnTypeMap<target>>, row, loaded, tableName, primaryKey>
  
  // Ordering and grouping
  orderBy(
    column: QueryColumnInput<columnTypes>,
    direction?: OrderDirection,
  ): QueryBuilder<columnTypes, row, loaded, tableName, primaryKey>
  
  groupBy(
    ...columns: QueryColumnInput<columnTypes>[]
  ): QueryBuilder<columnTypes, row, loaded, tableName, primaryKey>
  
  // Pagination
  limit(value: number): QueryBuilder<columnTypes, row, loaded, tableName, primaryKey>
  offset(value: number): QueryBuilder<columnTypes, row, loaded, tableName, primaryKey>
  
  // Relations
  with<relations extends RelationMapForSourceName<tableName>>(
    relations: relations,
  ): QueryBuilder<columnTypes, row, loaded & LoadedRelationMap<relations>, tableName, primaryKey>
  
  // Execution - Read
  all(): Promise<Array<row & loaded>>
  first(): Promise<(row & loaded) | null>
  find(value: PrimaryKeyInputForRow<row, primaryKey>): Promise<(row & loaded) | null>
  count(): Promise<number>
  exists(): Promise<boolean>
  
  // Execution - Write
  insert(
    values: Partial<row>,
    options?: { returning?: ReturningInput<row>; touch?: boolean },
  ): Promise<WriteResult | WriteRowResult<row>>
  
  insertMany(
    values: Partial<row>[],
    options?: { returning?: ReturningInput<row>; touch?: boolean },
  ): Promise<WriteResult | WriteRowsResult<row>>
  
  update(
    changes: Partial<row>,
    options?: { returning?: ReturningInput<row>; touch?: boolean },
  ): Promise<WriteResult | WriteRowsResult<row>>
  
  delete(
    options?: { returning?: ReturningInput<row> }
  ): Promise<WriteResult | WriteRowsResult<row>>
  
  upsert(
    values: Partial<row>,
    options?: {
      returning?: ReturningInput<row>
      touch?: boolean
      conflictTarget?: (keyof row & string)[]
      update?: Partial<row>
    },
  ): Promise<WriteResult | WriteRowResult<row>>
}

API Reference

createDatabase

Creates a database runtime from an adapter.

adapter

DatabaseAdapter

required

Adapter implementation responsible for SQL execution.

options.now

() => unknown

Clock function used for auto-managed timestamps. Defaults to Date.now.

database

Database

A Database API instance.

import { createDatabase } from 'remix/data-table'
import { createPostgresDatabaseAdapter } from 'remix/data-table-postgres'
import { Pool } from 'pg'

let pool = new Pool({ connectionString: process.env.DATABASE_URL })
let db = createDatabase(createPostgresDatabaseAdapter(pool))

table

Creates a table object with symbol-backed metadata and direct column references.

options.name

string

required

SQL table name.

options.columns

Record<string, ColumnBuilder>

required

Column definitions using the column builder.

options.primaryKey

string | readonly string[]

Primary key column(s). Defaults to 'id' if an id column exists.

options.timestamps

boolean | { createdAt?: string; updatedAt?: string }

Enable automatic timestamp management.

options.validate

TableValidate<row>

Optional validation function called on create/update operations.

options.beforeWrite

TableBeforeWrite<row>

Optional lifecycle hook called before write operations.

options.afterWrite

TableAfterWrite<row>

Optional lifecycle hook called after write operations.

options.beforeDelete

TableBeforeDelete

Optional lifecycle hook called before delete operations.

options.afterDelete

TableAfterDelete

Optional lifecycle hook called after delete operations.

options.afterRead

TableAfterRead<row>

Optional lifecycle hook called after read operations.

table

Table

A frozen table object with column references.

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

let users = table({
  name: 'users',
  columns: {
    id: c.uuid().primaryKey(),
    email: c.varchar(255).notNull().unique(),
    role: c.enum(['customer', 'admin']),
    created_at: c.timestamp(),
  },
  primaryKey: 'id',
  timestamps: true,
})

column

Chainable column builder namespace for defining column types and constraints.

Type Constructors

varchar(length)

(length: number) => ColumnBuilder<string>

Variable-length string column.

text()

() => ColumnBuilder<string>

Unlimited text column.

integer()

() => ColumnBuilder<number>

Integer column.

bigint()

() => ColumnBuilder

Big integer column.

decimal(precision, scale)

(precision: number, scale: number) => ColumnBuilder<number>

Decimal/numeric column with specified precision and scale.

boolean()

() => ColumnBuilder<boolean>

Boolean column.

uuid()

() => ColumnBuilder<string>

UUID column.

date()

() => ColumnBuilder

Date column (date only, no time).

time(options?)

(options?: { precision?: number; withTimezone?: boolean }) => ColumnBuilder

Time column.

timestamp(options?)

(options?: { precision?: number; withTimezone?: boolean }) => ColumnBuilder

Timestamp column (date and time).

json()

() => ColumnBuilder

JSON column.

binary(length?)

(length?: number) => ColumnBuilder

Binary data column.

enum(values)

<values extends readonly string[]>(values: values) => ColumnBuilder<values[number]>

Enum column with specified allowed values.

Modifiers

nullable()

() => ColumnBuilder<output | null>

Allow null values.

notNull()

() => ColumnBuilder<Exclude<output, null>>

Disallow null values (default for most columns).

default(value)

(value: unknown) => ColumnBuilder<output>

Set a default literal value.

defaultNow()

() => ColumnBuilder<output>

Set default to current timestamp.

defaultSql(expression)

(expression: string) => ColumnBuilder<output>

Set default to a SQL expression.

primaryKey()

() => ColumnBuilder<output>

Mark as primary key column.

unique(name?)

(name?: string) => ColumnBuilder<output>

Add unique constraint with optional constraint name.

references(table, column, name)

(table: string, column: string | string[], name: string) => ColumnBuilder<output>

Add foreign key reference.

onDelete(action)

(action: ForeignKeyAction) => ColumnBuilder<output>

Set foreign key on delete action (cascade, set null, etc.).

onUpdate(action)

(action: ForeignKeyAction) => ColumnBuilder<output>

Set foreign key on update action.

check(expression, name)

(expression: string, name: string) => ColumnBuilder<output>

Add check constraint.

comment(text)

(text: string) => ColumnBuilder<output>

Add column comment.

computed(expression, options?)

(expression: string, options?: { stored?: boolean }) => ColumnBuilder<output>

Make column a computed/generated column.

autoIncrement()

() => ColumnBuilder<output>

Enable auto-increment (MySQL/SQLite).

identity(options?)

(options?: IdentityOptions) => ColumnBuilder<output>

Enable identity column (PostgreSQL).

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

let columns = {
  id: c.uuid().primaryKey(),
  email: c.varchar(255).notNull().unique('users_email_uq'),
  age: c.integer().nullable(),
  balance: c.decimal(10, 2).default(0),
  role: c.enum(['admin', 'user']).default('user'),
  created_at: c.timestamp().defaultNow(),
}

Relations

hasMany

Defines a one-to-many relation from source to target.

source

AnyTable

required

Source table.

target

AnyTable

required

Target table.

options.foreignKey

KeySelector<target>

Foreign key column(s) on target. Defaults to inferred foreign key.

options.targetKey

KeySelector<source>

Target key column(s) on source. Defaults to source primary key.

relation

Relation<source, target, 'many'>

A relation descriptor.

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

let userOrders = hasMany(users, orders)
let customOrders = hasMany(users, orders, {
  foreignKey: 'customer_id',
})

hasOne

Defines a one-to-one relation from source to target where the foreign key lives on target.

source

AnyTable

required

Source table.

target

AnyTable

required

Target table.

options.foreignKey

KeySelector<target>

Foreign key column(s) on target.

options.targetKey

KeySelector<source>

Target key column(s) on source.

relation

Relation<source, target, 'one'>

A relation descriptor.

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

let userProfile = hasOne(users, profiles)

belongsTo

Defines a one-to-one relation from source to target where the foreign key lives on source.

source

AnyTable

required

Source table.

target

AnyTable

required

Target table.

options.foreignKey

KeySelector<source>

Foreign key column(s) on source.

options.targetKey

KeySelector<target>

Target key column(s) on target.

relation

Relation<source, target, 'one'>

A relation descriptor.

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

let orderUser = belongsTo(orders, users)

hasManyThrough

Defines a many-to-many relation through an intermediate table.

source

AnyTable

required

Source table.

target

AnyTable

required

Target table.

options.through

Relation

required

Intermediate relation.

options.throughForeignKey

KeySelector<target>

Foreign key on target referencing intermediate table.

options.throughTargetKey

string | string[]

Primary/target key on intermediate table.

relation

Relation<source, target, 'many'>

A relation descriptor.

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

let userEnrollments = hasMany(users, enrollments)
let userCourses = hasManyThrough(users, courses, {
  through: userEnrollments,
})

Query Operators

Predicates for building type-safe where clauses.

Comparison Operators

eq(column, value)

(column, value) => Predicate

Equality comparison.

ne(column, value)

(column, value) => Predicate

Inequality comparison.

gt(column, value)

(column, value) => Predicate

Greater than comparison.

gte(column, value)

(column, value) => Predicate

Greater than or equal comparison.

lt(column, value)

(column, value) => Predicate

Less than comparison.

lte(column, value)

(column, value) => Predicate

Less than or equal comparison.

like(column, pattern)

(column, pattern: string) => Predicate

SQL LIKE pattern matching (case-sensitive).

ilike(column, pattern)

(column, pattern: string) => Predicate

SQL ILIKE pattern matching (case-insensitive).

inList(column, values)

(column, values: readonly unknown[]) => Predicate

IN list comparison.

notInList(column, values)

(column, values: readonly unknown[]) => Predicate

NOT IN list comparison.

between(column, lower, upper)

(column, lower: unknown, upper: unknown) => Predicate

BETWEEN range comparison.

isNull(column)

(column) => Predicate

IS NULL check.

notNull(column)

(column) => Predicate

IS NOT NULL check.

Logical Operators

and(...predicates)

(...predicates: Predicate[]) => Predicate

Logical AND of multiple predicates.

or(...predicates)

(...predicates: Predicate[]) => Predicate

Logical OR of multiple predicates.

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

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

// Using operators
await db.query(orders)
  .where(gt('total', 100))
  .where(inList('status', ['pending', 'processing']))
  .all()

// Logical combinations
await db.query(users)
  .where(or(
    { role: 'admin' },
    and(eq('role', 'user'), notNull('verified_at'))
  ))
  .all()

// Column comparisons
await db.query(products)
  .where(gt('price', 'cost'))
  .all()

Raw SQL

sql

Builds a parameterized SQL statement.

template

TemplateStringsArray

required

SQL template string.

...values

unknown[]

Parameter values.

statement

SqlStatement

A SqlStatement object with text and values.

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

let statement = sql`select * from users where email = ${email}`
await db.exec(statement)

rawSql

Builds a SQL statement from a raw string and parameter array.

text

string

required

SQL query text.

values

unknown[]

Parameter values.

statement

SqlStatement

A SqlStatement object with text and values.

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

let statement = rawSql('select * from users where email = $1', [email])
await db.exec(statement)

Utilities

timestamps

Convenience helper for standard snake_case timestamp columns.

columns

Record<'created_at' | 'updated_at', ColumnBuilder>

Column builder map for created_at and updated_at.

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

let posts = table({
  name: 'posts',
  columns: {
    id: c.integer(),
    title: c.varchar(255),
    ...timestamps(),
  },
  timestamps: true,
})

fail

Creates a lifecycle/validation failure result with one or more issues.

message

string

required

Issue message.

path

Array<string | number>

Optional issue path.

failure

ValidationFailure

A result object for validate and lifecycle callbacks.

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

let users = table({
  name: 'users',
  columns: {
    id: c.integer(),
    email: c.varchar(255),
  },
  validate({ value }) {
    if (!value.email) {
      return fail('Email is required', ['email'])
    }
    return { value }
  },
})

Error Classes

DataTableError

Base error class for all data-table errors.

DataTableAdapterError

Thrown when adapter execution fails.

DataTableQueryError

Thrown when query construction or execution fails.

DataTableValidationError

Thrown when validation fails in lifecycle hooks.

DataTableConstraintError

Thrown when database constraints are violated.

Type Utilities

TableRow<table>

Extracts the row type from a table definition.

import type { TableRow } from 'remix/data-table'

type User = TableRow<typeof users>
// { id: string; email: string; role: 'customer' | 'admin'; ... }

TableRowWith<table, loaded>

Extracts the row type with loaded relations.

import type { TableRowWith } from 'remix/data-table'

type UserWithOrders = TableRowWith<typeof users, { orders: Order[] }>

InferInput<column>

Extracts the input type of a column builder.

InferOutput<column>

Extracts the output type of a column builder.

Routing

Components

Data

Server

Middleware

Utilities

​Overview

​Installation

​Core Types

​Database

​QueryBuilder

​API Reference

​createDatabase

​table

​column

​Type Constructors

​Modifiers

​Relations

​hasMany

​hasOne

​belongsTo

​hasManyThrough

​Query Operators

​Comparison Operators

​Logical Operators

​Raw SQL

​sql

​rawSql

​Utilities

​timestamps

​fail

​Error Classes

​DataTableError

​DataTableAdapterError

​DataTableQueryError

​DataTableValidationError

​DataTableConstraintError

​Type Utilities

​TableRow<table>

​TableRowWith<table, loaded>

​InferInput<column>

​InferOutput<column>

Build docs developers (and LLMs) love

Overview

Installation

Core Types

Database

QueryBuilder

API Reference

createDatabase

table

column

Type Constructors

Modifiers

Relations

hasMany

hasOne

belongsTo

hasManyThrough

Query Operators

Comparison Operators

Logical Operators

Raw SQL

sql

rawSql

Utilities

timestamps

fail

Error Classes

DataTableError

DataTableAdapterError

DataTableQueryError

DataTableValidationError

DataTableConstraintError

Type Utilities

TableRow<table>

TableRowWith<table, loaded>

InferInput<column>

InferOutput<column>