Database Queries

Query builder and CRUD helpers for type-safe database operations

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, '[e-mail]'))
  .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, '[e-mail]')).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: '[e-mail]',
  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: '[e-mail]',
    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: '[e-mail]', 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 }