How to Use PostgreSQL with Remix: Simple Setup Guide

To use PostgreSQL with Remix, set up a PostgreSQL database and connect it using an ORM like Prisma. Configure your database URL in environment variables, define your schema with Prisma, generate the client, and use it in Remix loaders or actions to query or modify data.

Syntax

Using PostgreSQL with Remix typically involves these parts:

• Database URL: Connection string stored in .env file.
• Prisma Schema: Defines your database tables and relations.
• Prisma Client: Auto-generated code to query the database.
• Remix Loaders/Actions: Functions where you use Prisma Client to fetch or update data.

typescript

DATABASE_URL="postgresql://user:password@localhost:5432/mydb?schema=public"

// prisma/schema.prisma
model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String?
  published Boolean  @default(false)
  createdAt DateTime @default(now())
}

// In Remix loader or action
import { PrismaClient } from '@prisma/client';
const prisma = new PrismaClient();

export async function loader() {
  const posts = await prisma.post.findMany();
  return { posts };
}

Example

This example shows a simple Remix loader fetching posts from PostgreSQL using Prisma.

typescript

// .env
DATABASE_URL="postgresql://user:password@localhost:5432/mydb?schema=public"

// prisma/schema.prisma
model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String?
  published Boolean  @default(false)
  createdAt DateTime @default(now())
}

// Run these commands in terminal:
// npx prisma generate
// npx prisma migrate dev --name init

// app/routes/index.tsx
import { LoaderFunction, json } from '@remix-run/node';
import { useLoaderData } from '@remix-run/react';
import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient();

export const loader: LoaderFunction = async () => {
  const posts = await prisma.post.findMany({
    where: { published: true },
    orderBy: { createdAt: 'desc' },
  });
  return json({ posts });
};

export default function Index() {
  const { posts } = useLoaderData<typeof loader>();
  return (
    <main>
      <h1>Published Posts</h1>
      <ul>
        {posts.map(post => (
          <li key={post.id}>
            <strong>{post.title}</strong><br />
            {post.content}
          </li>
        ))}
      </ul>
    </main>
  );
}

Output

<main>
  <h1>Published Posts</h1>
  <ul>
    <li><strong>Post Title 1</strong><br />Post content here</li>
    <li><strong>Post Title 2</strong><br />Another post content</li>
  </ul>
</main>

Common Pitfalls

Common mistakes when using PostgreSQL with Remix include:

• Not setting DATABASE_URL correctly in .env, causing connection errors.
• Forgetting to run npx prisma generate after changing the schema.
• Using Prisma Client outside Remix loaders or actions, which can cause connection leaks.
• Not handling async properly in loaders/actions, leading to runtime errors.

typescript

// Wrong: Using PrismaClient inside component (causes multiple instances)
import { PrismaClient } from '@prisma/client';
const prisma = new PrismaClient();

export default function Posts() {
  const posts = prisma.post.findMany(); // ❌ This is async and not allowed here
  return <div>{/* ... */}</div>;
}

// Right: Use PrismaClient inside loader
import { PrismaClient } from '@prisma/client';
const prisma = new PrismaClient();

export async function loader() {
  const posts = await prisma.post.findMany(); // ✅ Async allowed here
  return { posts };
}

Quick Reference

• Set DATABASE_URL in .env for PostgreSQL connection.
• Define schema in prisma/schema.prisma and run migrations.
• Generate Prisma Client with npx prisma generate.
• Use Prisma Client inside Remix loaders or actions only.
• Close Prisma Client if needed to avoid connection leaks in serverless environments.

Key Takeaways

Always store your PostgreSQL connection string in a .env file as DATABASE_URL.

Use Prisma ORM with Remix for easy and type-safe database access.

Run prisma generate and migrations after schema changes to keep client updated.

Use Prisma Client only inside Remix loaders or actions to avoid async issues.

Handle database connections carefully to prevent leaks, especially in serverless.