Database Queries

data-table provides two complementary APIs for database operations:

Query Builder - Expressive joins, aggregates, eager loading, and scoped writes
CRUD Helpers - Simplified create/read/update/delete flows

Both APIs are fully type-safe and support runtime validation.

Query Builder

Use db.query(table) for complex queries with joins, aggregates, and custom selection:

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

let recentPendingOrders = await db
  .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)
  .all()

Where Predicates

The query builder accepts object syntax or explicit predicates:

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

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

// Comparison operators
await db.query(orders).where(gt(orders.total, 100)).all()
await db.query(orders).where(gte(orders.total, 50)).all()
await db.query(orders).where(lt(orders.created_at, Date.now())).all()

// IN/NOT IN
await db.query(users).where(inList(users.id, ['u_1', 'u_2', 'u_3'])).all()
await db.query(orders).where(notInList(orders.status, ['cancelled', 'refunded'])).all()

// Pattern matching
await db.query(users).where(like(users.email, '%@example.com')).all()
await db.query(users).where(ilike(users.username, 'john%')).all() // case-insensitive

// Null checks
await db.query(users).where(isNull(users.deleted_at)).all()
await db.query(orders).where(notNull(orders.shipped_at)).all()

// Between
await db.query(orders).where(between(orders.total, 50, 500)).all()

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

await db
  .query(users)
  .where(
    or(
      eq(users.role, 'admin'),
      eq(users.role, 'moderator')
    )
  )
  .all()

Joins

The query builder supports join, leftJoin, and rightJoin:

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

// Inner join
let ordersWithUsers = await db
  .query(orders)
  .join(users, eq(orders.user_id, users.id))
  .select({
    orderId: orders.id,
    userEmail: users.email,
    total: orders.total,
  })
  .all()

// Left join
let allOrdersWithOptionalUsers = await db
  .query(orders)
  .leftJoin(users, eq(orders.user_id, users.id))
  .all()

// Multiple joins
let orderDetails = await db
  .query(orders)
  .join(users, eq(orders.user_id, users.id))
  .join(addresses, eq(orders.shipping_address_id, addresses.id))
  .select({
    orderId: orders.id,
    customerName: users.name,
    shippingCity: addresses.city,
  })
  .all()

Selection

Narrow selected columns or create aliases:

// Select specific columns
let userEmails = await db
  .query(users)
  .select('email', 'created_at')
  .all()

// Create aliases
let summaries = await db
  .query(orders)
  .select({
    id: orders.id,
    amount: orders.total,
    date: orders.created_at,
  })
  .all()
// summaries: { id: string; amount: number; date: number }[]

Distinct

Eliminate duplicate rows:

let uniqueStatuses = await db
  .query(orders)
  .select('status')
  .distinct()
  .all()

Ordering

Sort results by one or more columns:

// Single order
await db.query(users).orderBy(users.created_at, 'desc').all()

// Multiple orders
await db
  .query(orders)
  .orderBy(orders.status, 'asc')
  .orderBy(orders.created_at, 'desc')
  .all()

Grouping and Aggregates

Group rows and filter with having:

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

let stats = await db
  .query(orders)
  .select({
    userId: orders.user_id,
    orderCount: db.count(),
    totalSpent: db.sum(orders.total),
  })
  .groupBy(orders.user_id)
  .having(gte(db.count(), 5))
  .all()

Pagination

Limit and offset results:

// First page
let page1 = await db.query(orders).limit(20).offset(0).all()

// Second page
let page2 = await db.query(orders).limit(20).offset(20).all()

Counting

Count matching rows:

let pendingCount = await db
  .query(orders)
  .where({ status: 'pending' })
  .count()

Existence Checks

Check if any rows match:

let hasAdmin = await db
  .query(users)
  .where({ role: 'admin' })
  .exists()

Scoped Updates

Update matching rows:

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

Scoped Deletes

Delete matching rows:

// Delete with where clause
await db
  .query(orders)
  .where({ status: 'cancelled' })
  .delete()

CRUD Helpers

For common operations, use the high-level CRUD helpers:

Read Operations

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

// Find by primary key
let user = await db.find(users, 'u_001')
// user: User | null

// Find one matching row
let firstPending = await db.findOne(orders, {
  where: { status: 'pending' },
  orderBy: ['created_at', 'asc'],
})
// firstPending: Order | null

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

// Count matching rows
let totalOrders = await db.count(orders, {
  where: { status: 'delivered' },
})
// totalOrders: number

Create Operations

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

// Create and return row
let createdUser = await db.create(
  users,
  {
    id: 'u_003',
    email: '[email protected]',
    role: 'customer',
    created_at: Date.now(),
  },
  { returnRow: true }
)
// createdUser: User

// Bulk insert with 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() },
])
// createManyResult: { affectedRows: number; insertId?: unknown }

// Bulk insert and return rows (requires adapter 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 }
)
// insertedRows: Order[]

Update Operations

// Update by primary key (throws if not found)
let updatedUser = await db.update(users, 'u_003', { role: 'admin' })
// updatedUser: User

// Update many matching rows
let updateManyResult = await db.updateMany(
  orders,
  { status: 'processing' },
  {
    where: { status: 'pending' },
    orderBy: ['created_at', 'asc'],
    limit: 25,
  }
)
// updateManyResult: { affectedRows: number }

Delete Operations

// Delete by primary key
let deleted = await db.delete(users, 'u_002')
// deleted: boolean

// Delete many matching rows
let deleteManyResult = await db.deleteMany(orders, {
  where: { status: 'delivered' },
  orderBy: [['created_at', 'asc']],
  limit: 200,
})
// deleteManyResult: { affectedRows: number }

Return Behavior Summary

Method	Default Return	With Options
`find`	`row \| null`
`findOne`	`row \| null`
`findMany`	`row[]`
`count`	`number`
`create`	`WriteResult`	`row` (with `returnRow: true`)
`createMany`	`WriteResult`	`row[]` (with `returnRows: true`)
`update`	`row`	Throws when not found
`updateMany`	`WriteResult`
`delete`	`boolean`
`deleteMany`	`WriteResult`

Transactions

Wrap multiple operations in a transaction:

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(),
  })

  // Automatically commits on success, rolls back on error
})

Raw SQL

Execute raw SQL when needed:

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

// With template literals (safe interpolation)
await db.exec(sql`select * from users where id = ${'u_001'}`)

// With positional parameters
await db.exec(rawSql('update users set role = ? where id = ?', ['admin', 'u_001']))

Type Exports

import type {
  Database,
  WriteResult,
  TableRow,
  QueryForTable,
} from 'remix/data-table'

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

Getting Started

Core Concepts

Routing

UI Components

Data Management

Server

Utilities

Database Queries

Query Builder

Where Predicates

Joins

Selection

Distinct

Ordering

Grouping and Aggregates

Counting

Existence Checks

Scoped Updates

Scoped Deletes

CRUD Helpers

Read Operations

Create Operations

Update Operations

Delete Operations

Return Behavior Summary

Transactions

Raw SQL

Type Exports

Build docs developers (and LLMs) love

Getting Started

Core Concepts

Routing

UI Components

Data Management

Server

Utilities

​Query Builder

​Where Predicates

​Joins

​Selection

​Distinct

​Ordering

​Grouping and Aggregates

​Pagination

​Counting

​Existence Checks

​Scoped Updates

​Scoped Deletes

​CRUD Helpers

​Read Operations

​Create Operations

​Update Operations

​Delete Operations

​Return Behavior Summary

​Transactions

​Raw SQL

​Type Exports

Build docs developers (and LLMs) love

Query Builder

Where Predicates

Joins

Selection

Distinct

Ordering

Grouping and Aggregates

Pagination

Counting

Existence Checks

Scoped Updates

Scoped Deletes

CRUD Helpers

Read Operations

Create Operations

Update Operations

Delete Operations

Return Behavior Summary

Transactions

Raw SQL

Type Exports