Relations

Define and load table relationships with type-safe eager loading

data-table provides relation-first query APIs with support for hasMany, hasOne, belongsTo, and hasManyThrough relationships.

Defining Relations

hasMany

Defines a one-to-many relationship where the foreign key lives on the target table:

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

let users = table({
  name: 'users',
  columns: {
    id: c.uuid(),
    email: c.varchar(255),
  },
})

let orders = table({
  name: 'orders',
  columns: {
    id: c.uuid(),
    user_id: c.uuid(),
    status: c.varchar(50),
    total: c.decimal(10, 2),
  },
})

// User has many orders
let userOrders = hasMany(users, orders)

By default, hasMany infers the foreign key as user_id (singular source table name + _id).

Custom Keys

let userOrders = hasMany(users, orders, {
  targetKey: 'id', // Source key (defaults to primary key)
  foreignKey: 'customer_id', // Foreign key on target table
})

hasOne

Defines a one-to-one relationship where the foreign key lives on the target table:

let profiles = table({
  name: 'profiles',
  columns: {
    id: c.uuid(),
    user_id: c.uuid(),
    bio: c.text(),
  },
})

let userProfile = hasOne(users, profiles)

belongsTo

Defines a one-to-one relationship where the foreign key lives on the source table:

let orderUser = belongsTo(orders, users)

By default, belongsTo infers the foreign key as user_id and the target key as the primary key of the target table.

Custom Keys

let orderUser = belongsTo(orders, users, {
  foreignKey: 'customer_id', // Foreign key on source table
  targetKey: 'id', // Target key (defaults to primary key)
})

hasManyThrough

Defines a many-to-many relationship through an intermediate table:

let users = table({
  name: 'users',
  columns: {
    id: c.uuid(),
    email: c.varchar(255),
  },
})

let teams = table({
  name: 'teams',
  columns: {
    id: c.uuid(),
    name: c.varchar(100),
  },
})

let teamMembers = table({
  name: 'team_members',
  columns: {
    id: c.uuid(),
    team_id: c.uuid(),
    user_id: c.uuid(),
    role: c.varchar(50),
  },
})

// User has many team_members
let userTeamMembers = hasMany(users, teamMembers)

// User has many teams through team_members
let userTeams = hasManyThrough(users, teams, {
  through: userTeamMembers,
  throughTargetKey: 'id', // team_members.team_id references teams.id
  throughForeignKey: 'team_id', // teams.id is referenced by team_members.team_id
})

Eager Loading Relations

Load relations with the .with() method:

Query Builder

let usersWithOrders = await db
  .query(users)
  .where({ role: 'customer' })
  .with({ orders: userOrders })
  .all()

// usersWithOrders: Array<User & { orders: Order[] }>

CRUD Helpers

// find()
let user = await db.find(users, 'u_001', {
  with: { orders: userOrders },
})
// user: (User & { orders: Order[] }) | null

// findOne()
let firstCustomer = await db.findOne(users, {
  where: { role: 'customer' },
  with: { orders: userOrders },
})
// firstCustomer: (User & { orders: Order[] }) | null

// findMany()
let customers = await db.findMany(users, {
  where: { role: 'customer' },
  with: { orders: userOrders },
})
// customers: Array<User & { orders: Order[] }>

// create()
let createdUser = await db.create(
  users,
  { id: 'u_003', email: '[e-mail]', role: 'customer', created_at: Date.now() },
  {
    returnRow: true,
    with: { orders: userOrders },
  }
)
// createdUser: User & { orders: Order[] }

// update()
let updatedUser = await db.update(
  users,
  'u_003',
  { role: 'admin' },
  { with: { orders: userOrders } }
)
// updatedUser: User & { orders: Order[] }

Relation Modifiers

Filter, sort, and limit related records:

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

// Each user has at most 3 recent shipped orders

Available Modifiers

let relation = userOrders
  .where({ status: 'pending' }) // Filter related rows
  .orderBy('created_at', 'desc') // Sort related rows
  .limit(10) // Limit related rows
  .offset(5) // Skip related rows

Nested Relations

Eager load relations of relations:

let orderItems = table({
  name: 'order_items',
  columns: {
    id: c.uuid(),
    order_id: c.uuid(),
    product_id: c.uuid(),
    quantity: c.integer(),
  },
})

let products = table({
  name: 'products',
  columns: {
    id: c.uuid(),
    name: c.varchar(255),
    price: c.decimal(10, 2),
  },
})

let orderOrderItems = hasMany(orders, orderItems)
let orderItemProduct = belongsTo(orderItems, products)

let usersWithNestedData = await db
  .query(users)
  .with({
    orders: userOrders.with({
      items: orderOrderItems.with({
        product: orderItemProduct,
      }),
    }),
  })
  .all()

// usersWithNestedData: Array<
//   User & {
//     orders: Array<
//       Order & {
//         items: Array<OrderItem & { product: Product | null }>
//       }
//     >
//   }
// >

Relation Types

Cardinality

• hasMany and hasManyThrough return arrays: Target[]
• hasOne and belongsTo return nullable objects: Target | null

let user = await db.find(users, 'u_001', {
  with: {
    orders: userOrders, // Order[]
    profile: userProfile, // Profile | null
  },
})

if (user) {
  user.orders.forEach((order) => {
    console.log(order.total)
  })

  if (user.profile) {
    console.log(user.profile.bio)
  }
}

Relation Queries

Relations are composable with all query builder methods:

// Count related records
let ordersWithItemCount = await db
  .query(orders)
  .select({
    id: orders.id,
    itemCount: db.count(),
  })
  .join(orderItems, eq(orders.id, orderItems.order_id))
  .groupBy(orders.id)
  .all()

// Filter by related records
let usersWithPendingOrders = await db
  .query(users)
  .join(orders, eq(users.id, orders.user_id))
  .where({ 'orders.status': 'pending' })
  .distinct()
  .all()

Composite Keys

Relations support composite keys:

let tenantUsers = table({
  name: 'tenant_users',
  columns: {
    tenant_id: c.uuid(),
    user_id: c.uuid(),
    email: c.varchar(255),
  },
  primaryKey: ['tenant_id', 'user_id'],
})

let tenantOrders = table({
  name: 'tenant_orders',
  columns: {
    id: c.uuid(),
    tenant_id: c.uuid(),
    user_id: c.uuid(),
    total: c.decimal(10, 2),
  },
})

let tenantUserOrders = hasMany(tenantUsers, tenantOrders, {
  targetKey: ['tenant_id', 'user_id'],
  foreignKey: ['tenant_id', 'user_id'],
})

Type Inference

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

// Infer relation result type
type UserOrders = RelationResult<typeof userOrders>
// Order[]

type UserProfile = RelationResult<typeof userProfile>
// Profile | null

// Infer row with relations
type UserWithOrders = TableRowWith<typeof users, { orders: typeof userOrders }>
// User & { orders: Order[] }

Performance Tips

1. Use relation modifiers to limit loaded data:

let users = await db
  .query(users)
  .with({
    recentOrders: userOrders.orderBy('created_at', 'desc').limit(5),
  })
  .all()

2. Avoid N+1 queries by eager loading relations instead of loading them in loops:

// Bad: N+1 query
let users = await db.findMany(users)
for (let user of users) {
  let orders = await db.findMany(orders, { where: { user_id: user.id } })
}

// Good: Single query with eager loading
let users = await db.findMany(users, {
  with: { orders: userOrders },
})

3. Use joins for filtering, eager loading for data:

// Filter with join, then eager load
let activeUsers = await db
  .query(users)
  .join(orders, eq(users.id, orders.user_id))
  .where({ 'orders.status': 'active' })
  .distinct()
  .with({ orders: userOrders })
  .all()

Type Exports

import type {
  Relation,
  RelationResult,
  LoadedRelationMap,
  RelationCardinality,
  HasManyOptions,
  HasOneOptions,
  BelongsToOptions,
  HasManyThroughOptions,
} from 'remix/data-table'