Loading Data

Web applications are built around data. This guide covers everything about loading data in Remix --- from basic queries to advanced patterns like relations, raw SQL, and transactions.

If you are new to databases in Remix, start with Part 4 of the tutorial. This guide assumes you understand the basics and want to go deeper.

The Data Loading Pattern

In Remix, data loading follows a clear pattern:

1. Load data in the route handler
2. Pass data to a component as props
3. Return a Response with the rendered HTML

import { Database } from 'remix/data-table'
import { books } from '../data/schema.ts'
import { renderToString } from 'remix/component/server'

handler({ get }) {
  // 1. Load data
  let db = get(Database)
  let allBooks = await db.findMany(books)

  // 2. Pass to component
  let html = renderToString(<BookList books={allBooks} />)

  // 3. Return Response
  return new Response(html, {
    headers: { 'Content-Type': 'text/html; charset=UTF-8' },
  })
}

This is a deliberate design choice. Data loading happens in the handler (a plain function), not inside the component. Components receive data through props and are purely responsible for rendering.

Why load data in handlers, not components?

Loading data in handlers keeps your components pure rendering functions. This makes them easy to test (just pass mock data), easy to reason about (no side effects), and compatible with both renderToString and renderToStream.

The data-table Query API

Remix's data-table package provides a typed query builder that works with PostgreSQL, MySQL, and SQLite. You define your tables once in a schema and get type-safe queries throughout your application.

Defining Tables

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

export const books = table({
  name: 'books',
  columns: {
    id: c.integer(),
    slug: c.text(),
    title: c.text(),
    author: c.text(),
    description: c.text(),
    price: c.decimal(10, 2),
    genre: c.text(),
    cover_url: c.text(),
    isbn: c.text(),
    published_year: c.integer(),
    in_stock: c.boolean(),
  },
})

export type Book = TableRow<typeof books>

TableRow<typeof books> generates a TypeScript type from the table definition, so every query result is automatically typed.

Column Types

Method	SQL Type	TypeScript Type	Description
c.integer()	INTEGER	number	Whole numbers
c.text()	TEXT	string	Text strings
c.decimal(p, s)	DECIMAL(p,s)	number	Fixed-precision numbers
c.boolean()	BOOLEAN	boolean	True or false
c.enum([...])	TEXT + CHECK	Union of literals	Restricted set of values

findMany() --- Querying Multiple Rows

let allBooks = await db.findMany(books)

Returns an array of all rows in the table. The result type is Book[].

where --- Filtering

Filter with an exact-match object:

let fictionBooks = await db.findMany(books, {
  where: { genre: 'fiction' },
})

Or use query helpers for more complex conditions:

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

// Case-insensitive match
let results = await db.findMany(books, {
  where: ilike('genre', 'fiction'),
})

// Greater than
let expensive = await db.findMany(books, {
  where: gt('price', 20),
})

// Less than
let cheap = await db.findMany(books, {
  where: lt('price', 10),
})

// Combine conditions with AND
let cheapFiction = await db.findMany(books, {
  where: and(
    { genre: 'fiction' },
    lt('price', 15),
  ),
})

// Combine conditions with OR
let featured = await db.findMany(books, {
  where: or(
    { genre: 'fiction' },
    { genre: 'science' },
  ),
})

// IN list
let selected = await db.findMany(books, {
  where: inList('slug', ['bbq', 'heavy-metal', 'three-ways']),
})

// NOT
let notFiction = await db.findMany(books, {
  where: not({ genre: 'fiction' }),
})

orderBy --- Sorting

// Ascending
let booksByTitle = await db.findMany(books, {
  orderBy: ['title', 'asc'],
})

// Descending
let newest = await db.findMany(books, {
  orderBy: ['published_year', 'desc'],
})

limit and offset --- Pagination

let page = 2
let pageSize = 10

let pagedBooks = await db.findMany(books, {
  orderBy: ['id', 'asc'],
  limit: pageSize,
  offset: (page - 1) * pageSize,
})

Combining Options

All options can be used together:

let results = await db.findMany(books, {
  where: { genre: 'fiction', in_stock: true },
  orderBy: ['price', 'asc'],
  limit: 20,
  offset: 0,
})

findOne() --- Querying a Single Row

let book = await db.findOne(books, {
  where: { slug: 'great-gatsby' },
})

Returns a single row or null if no match is found. The result type is Book | null.

Always handle the null case:

let book = await db.findOne(books, { where: { slug: params.slug } })

if (!book) {
  return new Response('Book not found', { status: 404 })
}

// TypeScript now knows book is Book, not Book | null
return render(<BookPage book={book} />)

Finding by Primary Key

let book = await db.findOne(books, {
  where: { id: 42 },
})

count() --- Counting Rows

// Count all rows
let totalBooks = await db.count(books)

// Count with a filter
let fictionCount = await db.count(books, {
  where: { genre: 'fiction' },
})

Relations

Relations describe how tables connect to each other. Remix supports two types:

hasMany --- One-to-Many

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

export const orders = table({
  name: 'orders',
  columns: {
    id: c.integer(),
    user_id: c.integer(),
    total: c.decimal(10, 2),
    status: c.text(),
  },
})

export const orderItems = table({
  name: 'order_items',
  columns: {
    id: c.integer(),
    order_id: c.integer(),
    book_id: c.integer(),
    quantity: c.integer(),
    unit_price: c.decimal(10, 2),
  },
})

export const itemsByOrder = hasMany(orders, orderItems)

hasMany(orders, orderItems) says "one order has many order items." The foreign key is inferred from the table names (order_id on order_items references id on orders).

belongsTo --- Many-to-One

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

export const bookForOrderItem = belongsTo(orderItems, books)

belongsTo(orderItems, books) says "each order item belongs to one book."

Loading Relations with with()

Use with() to eagerly load related data:

import { with as withRelation } from 'remix/data-table'

let ordersWithItems = await db.findMany(orders, {
  where: { user_id: currentUser.id },
  orderBy: ['id', 'desc'],
  ...withRelation(itemsByOrder),
})

// Each order now has an `orderItems` property
for (let order of ordersWithItems) {
  console.log(`Order #${order.id} has ${order.orderItems.length} items`)
}

You can nest with() to load deeply related data:

let ordersWithBooks = await db.findMany(orders, {
  where: { user_id: currentUser.id },
  ...withRelation(itemsByOrder, {
    ...withRelation(bookForOrderItem),
  }),
})

// Access: order.orderItems[0].book.title

Type-Safe Queries and Results

Every query in data-table is type-safe:

// TypeScript knows the result type is Book[]
let allBooks = await db.findMany(books)

// TypeScript knows price is a number
allBooks[0].price.toFixed(2)

// TypeScript error: Property 'nonexistent' does not exist
allBooks[0].nonexistent

// TypeScript error: Argument 'invalid_column' is not a column
await db.findMany(books, { where: { invalid_column: 'test' } })

This catches mistakes at compile time, long before they reach production.

The Database Context Key Pattern

Rather than importing the db instance directly, Remix uses a middleware + context key pattern:

// app/middleware/database.ts
import type { Middleware } from 'remix/fetch-router'
import { Database } from 'remix/data-table'
import { db } from '../data/setup.ts'

export function loadDatabase(): Middleware {
  return (context, next) => {
    context.set(Database, db)
    return next()
  }
}

// app/router.ts
import { createRouter } from 'remix/fetch-router'
import { loadDatabase } from './middleware/database.ts'

let router = createRouter({
  middleware: [loadDatabase()],
})

// In any handler
handler({ get }) {
  let db = get(Database)
  let books = await db.findMany(booksTable)
}

This pattern provides:

1. Testability --- In tests, provide a different database instance through a test middleware.
2. Separation of concerns --- Handlers do not know how the database is created.
3. Type safety --- get(Database) is typed as Database, so TypeScript catches mistakes.

Creating a Database Middleware

Here is a complete database middleware setup:

import BetterSqlite3 from 'better-sqlite3'
import { createDatabase } from 'remix/data-table'
import { createSqliteDatabaseAdapter } from 'remix/data-table-sqlite'

let sqlite = new BetterSqlite3('./db/app.sqlite')
sqlite.pragma('foreign_keys = ON')

let adapter = createSqliteDatabaseAdapter(sqlite)
export let db = createDatabase(adapter)

import type { Middleware } from 'remix/fetch-router'
import { Database } from 'remix/data-table'
import { db } from '../data/setup.ts'

type SetDatabaseContextTransform = readonly [
  readonly [typeof Database, Database],
]

export function loadDatabase(): Middleware<
  'ANY',
  {},
  SetDatabaseContextTransform
> {
  return (context, next) => {
    context.set(Database, db)
    return next()
  }
}

Switching Databases

Because the database is accessed through a context key, switching from SQLite to PostgreSQL only requires changing the adapter:

// SQLite
import { createSqliteDatabaseAdapter } from 'remix/data-table-sqlite'
let adapter = createSqliteDatabaseAdapter(sqlite)

// PostgreSQL
import { createPostgresDatabaseAdapter } from 'remix/data-table-postgres'
let adapter = createPostgresDatabaseAdapter(pool)

// MySQL
import { createMysqlDatabaseAdapter } from 'remix/data-table-mysql'
let adapter = createMysqlDatabaseAdapter(connection)

Your queries stay exactly the same.

Raw SQL with sql Template Tag

For queries that are too complex for the query builder, use the sql template tag:

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

// Simple raw query
let results = await db.query(sql`
  SELECT books.title, COUNT(order_items.id) as order_count
  FROM books
  LEFT JOIN order_items ON order_items.book_id = books.id
  GROUP BY books.id
  ORDER BY order_count DESC
  LIMIT 10
`)

Parameterized Queries

The sql template tag automatically parameterizes interpolated values to prevent SQL injection:

let genre = 'fiction'
let minPrice = 10

let results = await db.query(sql`
  SELECT * FROM books
  WHERE genre = ${genre}
  AND price >= ${minPrice}
`)

The values genre and minPrice are sent as parameters, not interpolated into the SQL string. This prevents SQL injection attacks.

Never use string concatenation for SQL

Always use the sql template tag for raw queries. Never build SQL strings with concatenation or template literals:

// DANGEROUS - SQL injection vulnerability
let query = `SELECT * FROM books WHERE genre = '${userInput}'`

// SAFE - parameterized query
let query = sql`SELECT * FROM books WHERE genre = ${userInput}`

Transactions

A transaction groups multiple database operations so they either all succeed or all fail. This prevents partial updates that leave your data in an inconsistent state.

await db.transaction(async (tx) => {
  // Create an order
  let order = await tx.create(orders, {
    user_id: userId,
    total: cartTotal,
    status: 'pending',
    created_at: Date.now(),
  })

  // Create order items
  for (let item of cartItems) {
    await tx.create(orderItems, {
      order_id: order.id,
      book_id: item.bookId,
      quantity: item.quantity,
      unit_price: item.price,
      title: item.title,
    })
  }

  // Deduct stock
  for (let item of cartItems) {
    await tx.update(books, item.bookId, {
      in_stock: false,  // simplified example
    })
  }
})

If any operation inside the transaction throws an error, all changes are rolled back --- the order, the items, and the stock changes all disappear as if they never happened.

When to use transactions

Use transactions whenever you need to make multiple related changes that must succeed or fail together:

• Creating an order with its items
• Transferring money between accounts
• Updating a user's profile and their related settings
• Any operation where partial completion would leave data inconsistent

Write Operations

db.create() --- Insert One Row

let newBook = await db.create(books, {
  slug: 'new-book',
  title: 'A New Book',
  author: 'Author Name',
  price: 19.99,
  genre: 'fiction',
  in_stock: true,
})

// newBook has the auto-generated id
console.log(newBook.id) // 4

db.createMany() --- Insert Multiple Rows

let newBooks = await db.createMany(books, [
  { slug: 'book-1', title: 'Book One', /* ... */ },
  { slug: 'book-2', title: 'Book Two', /* ... */ },
])

db.update() --- Update One Row

let updated = await db.update(books, bookId, {
  title: 'Updated Title',
  price: 24.99,
})

The second argument is the primary key value. Only the specified columns are updated; others remain unchanged.

db.destroy() --- Delete One Row

await db.destroy(books, bookId)

Connection Pooling Considerations

For production applications, especially with PostgreSQL or MySQL, connection pooling is important:

• SQLite --- Uses a single connection. No pooling needed because SQLite serializes writes internally.
• PostgreSQL --- Use a connection pool (like pg.Pool) to reuse database connections across requests. Creating a new connection for every request is slow and can exhaust the database server's connection limit.
• MySQL --- Similar to PostgreSQL, use a connection pool.

// PostgreSQL with connection pooling
import { Pool } from 'pg'
import { createPostgresDatabaseAdapter } from 'remix/data-table-postgres'

let pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 20,          // Maximum connections in the pool
  idleTimeoutMillis: 30000,
})

let adapter = createPostgresDatabaseAdapter(pool)
let db = createDatabase(adapter)

Close connections gracefully

When your application shuts down, close the connection pool to release database connections:

process.on('SIGTERM', async () => {
  await pool.end()
  process.exit(0)
})

Query Method Reference

Method	Description	Returns
db.findMany(table, options?)	Find all matching rows	Row[]
db.findOne(table, options)	Find one row	Row | null
db.count(table, options?)	Count matching rows	number
db.create(table, data)	Insert one row	The inserted Row
db.createMany(table, data[])	Insert multiple rows	Row[]
db.update(table, id, data)	Update one row by ID	The updated Row
db.destroy(table, id)	Delete one row by ID	void
db.query(sql)	Run a raw SQL query	any[]
db.transaction(fn)	Run operations in a transaction	Return value of fn

Related

• Tutorial: Database --- Step-by-step database setup.
• Forms & Mutations --- Writing data from form submissions.
• Middleware --- The database middleware pattern.
• Routing In Depth --- How handlers receive the context.