Learn Remix - Database & Persistence

#Introduction

Up to episode 9, all your data lived in arrays and objects that vanished when the server restarted. Serious applications need persistence: data stored in a database that survives beyond the process. This is where Remix shows its strength, because database queries are written directly in loaders and actions — on the server.

This approach removes the extra API layer. You don't need to build dedicated REST endpoints for internal data; a loader can simply call a repository function, and an action saves the changes. The result is less code, stronger types, and an easier-to-follow flow.

Episode 10 uses Prisma as the example ORM, then covers connections and pooling, querying in loaders and actions, and transaction handling.

#Prisma Integration

#Prisma Setup

Prisma is the most popular TypeScript ORM in the Remix ecosystem. Install it and initialize with a PostgreSQL or SQLite driver to learn:

npm install prisma @prisma/client
npx prisma init --datasource-provider postgresql

The prisma init command creates a prisma folder and a .env file for DATABASE_URL. The database schema is written in the Prisma Schema Language, not in manual SQL.

#Defining Models

Models are defined in prisma/schema.prisma, then synced to the database:

model Post {
  id        String   @id @default(cuid())
  judul     String
  konten    String
  createdAt DateTime @default(now())
}

After the model is written, run prisma migrate dev to create the table. Prisma generates TypeScript types you can use directly in loaders and actions.

#Connections and Connection Pooling

#The PrismaClient Singleton Pattern

During development, hot reload creates many PrismaClient instances if this isn't handled. The standard solution is a global singleton:

PrismaClient singleton

import { PrismaClient } from "@prisma/client";
 
const globalForPrisma = globalThis;
export const prisma = globalForPrisma.prisma ?? new PrismaClient();
 
if (process.env.NODE_ENV !== "production") {
  globalForPrisma.prisma = prisma;
}

This pattern ensures a single PrismaClient instance is reused for the lifetime of the process. It prevents connection leaks in development and speeds up startup.

#Connection Pooling

For serverless environments that constantly start new processes, each process creates a new connection — risking exhaustion of the database connection limit. Use a connection pooler such as PgBouncer or Supabase Pooler. Set the pool size per environment with an env var like DATABASE_POOL_SIZE so development and production use different values.

#Querying in Loaders and Actions

#Reading Data in a Loader

The loader is the place for read queries. Data is returned directly to the component:

Prisma query in a loader

import { prisma } from "~/lib/prisma.server";
import { useLoaderData } from "@remix-run/react";
 
export async function loader() {
  const posts = await prisma.post.findMany({
    orderBy: { createdAt: "desc" },
    take: 10,
  });
  return { posts };
}
 
export default function Daftar() {
  const { posts } = useLoaderData();
  return (
    <ul>
      {posts.map((p) => <li key={p.id}>{p.judul}</li>)}
    </ul>
  );
}

The loader calls prisma.post.findMany directly on the server. There is no intermediate API endpoint — querying and rendering live in one route file.

#Writing Data in an Action

Actions write changes. Validate first, then write:

Saving data from an action

export async function action({ request }) {
  const formData = await request.formData();
  const judul = String(formData.get("judul"));
 
  const post = await prisma.post.create({
    data: { judul },
  });
 
  return redirect(`/posts/${post.id}`);
}

Prisma.create returns the newly created data, then redirect takes the user to the detail page. Because the route's loaders are refreshed automatically after an action, the post list always stays in sync with no extra code.

#Transactions and Persistence

#Atomic Operations with $transaction

When a single action must change several tables at once, use a transaction so that everything succeeds or everything fails. Prisma.$transaction runs several operations atomically.

Transaction with $transaction

export async function action({ request }) {
  const formData = await request.formData();
  const postId = String(formData.get("postId"));
 
  await prisma.$transaction([
    prisma.viewLog.create({ data: { postId } }),
    prisma.post.update({ where: { id: postId }, data: { dilihat: { increment: 1 } } }),
  ]);
 
  return redirect(`/posts/${postId}`);
}

The array of operations in $transaction executes together; if one fails, all of them are rolled back. This matters for data that must stay consistent, such as counters and logs.

#Limit What You Send to the Client

A loader returns all model fields by default. Don't send sensitive data to the client. Pick fields with select in Prisma, or write a serializer function in a dedicated module. This practice comes up again in episode 13 on security and episode 18 on architecture.

#Conclusion

Episode 10 connects your application to a database: Prisma for the ORM and schema, a singleton PrismaClient with connection pooling, direct querying in loaders and actions, and transactions for data consistency. Persistence is no longer a foreign word.

The key takeaways:

• Database queries are written directly in loaders and actions, with no internal API layer.
• Prisma defines the schema and generates TypeScript types.
• Use the singleton pattern for PrismaClient in development.
• A connection pooler matters for serverless workloads.
• Actions write with validation; redirect after the data is saved.
• $transaction makes several operations run atomically.

In the next episode, episode 11, we'll discuss authentication and session — authentication patterns in Remix, session handling with cookies, route protection and user context, and OAuth and social login with secure sessions. The database already stores data; now it's time to make sure only the right people can access it.