Skip to content
Tutorials Center
My learning

Topics

Lesson 15 of 39 ยท Loaders and Data Loading

Database Access Patterns in Loaders

Organize database access for React Router v7 loaders with .server modules, a model layer, parallel queries, DTO mapping and N+1 avoidance, shown with a runnable in-memory stand-in.

Loaders run on the server, so they can talk to a database directly. There is no separate REST API to build first. That is a big productivity win, but it also invites messy code: SQL scattered through route files, whole rows returned to the browser, and loops that run one query per row. This lesson shows a clean structure and the mistakes to avoid.

No database runs on this site, so the real code (Prisma and plain SQL style) is read-only. The stand-ins use an in-memory fake with the same shape so you can run the patterns.

Put queries in a model layer

Keep route modules about HTTP and UI. Put queries in .server modules, one per resource:

Text
app/
  db.server.ts            // creates the client once
  models/
    post.server.ts        // getPosts, getPost, createPost
    user.server.ts
  routes/
    posts.tsx
    posts.$slug.tsx
TypeScript
// app/db.server.ts
import { PrismaClient } from "@prisma/client";

declare global { var __db: PrismaClient | undefined; }

// reuse one client across dev-server reloads
export const db = (globalThis.__db ??= new PrismaClient());
TypeScript
// app/models/post.server.ts
import { db } from "~/db.server";

export function getPostSummaries(limit = 20) {
  return db.post.findMany({
    select: { slug: true, title: true, publishedAt: true, author: { select: { name: true } } },
    orderBy: { publishedAt: "desc" },
    take: limit,
  });
}

Two benefits: the model functions are easy to test (see Testing Loaders and Actions), and the select returns only the fields the page uses, which also keeps secrets and bulk text out of the response.

Map rows to the data the page needs

A loader should return a small, purpose-built object. Convert rows to that shape in the model layer:

TypeScript
export async function getPost(slug: string) {
  const row = await db.post.findUnique({ where: { slug }, include: { author: true } });
  if (!row) return null;
  return {
    slug: row.slug,
    title: row.title,
    html: row.html,
    author: { name: row.author.name },   // never author.email or passwordHash
  };
}
JS Stand-in: select only the fields a page needs

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Run independent queries in parallel

A page often needs several unrelated pieces of data. Awaiting them one by one adds the latencies together; Promise.all overlaps them.

TypeScript
export async function loader({ params }: Route.LoaderArgs) {
  const [post, related, commentCount] = await Promise.all([
    getPost(params.slug),
    getRelatedPosts(params.slug),
    countComments(params.slug),
  ]);
  if (!post) throw new Response("Not found", { status: 404 });
  return { post, related, commentCount };
}
JS Stand-in: sequential versus parallel queries

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Avoid the N+1 query problem

N+1 happens when you load a list (1 query) and then run one query per item (N queries) for related data.

TypeScript
// Bad: 1 + N queries
const posts = await db.post.findMany();
for (const post of posts) {
  post.author = await db.user.findUnique({ where: { id: post.authorId } });
}

// Good: one query with a join (include/select)
const posts = await db.post.findMany({ include: { author: { select: { name: true } } } });

If you cannot join, batch: collect the ids, fetch them with a single WHERE id IN (...), and look rows up in a Map.

JS Stand-in: count queries for N+1 versus a batch

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Safety rules

  • Never build SQL by string concatenation with params or search params. Use parameterized queries or your ORM, which prevents SQL injection.
  • Paginate. Always cap take and clamp user-provided page sizes.
  • Check ownership. A query like getNote(id) must also filter by the current user (where: { id, userId }), or anyone can read anyone's note by changing the URL.
  • One client per process. Creating a new database client per request exhausts connections.
flowchart LR
  R["route loader"] --> M["model function (.server)"]
  M --> D["db client"]
  D --> DB[("database")]
  M -->|"plain DTO"| R
  R -->|"loaderData"| C["component"]

Routes call models; models own queries and shape the data.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why use .server model modules instead of queries inside the component file?

    The .server suffix keeps database code from the browser, and plain functions are easy to unit test.
  2. 2. Two independent queries each take 50 ms. Awaiting them with Promise.all takes about

    They overlap, so the total is about the slowest one.
  3. 3. Which is the N+1 problem?

    One list query plus N related queries; fix with a join or a batch.
  4. 4. What must getNote(id) also check in a multi-user app?

    Without an ownership filter, changing the id in the URL exposes other users' data.

Summary

  • Keep queries in .server model modules and shape results into small DTOs.
  • Use Promise.all for independent queries; fix N+1 with joins or batching.
  • Parameterize queries, paginate, and always filter by ownership.
  • Share one database client per server process.

Next: Actions Basics