Remix

Remix connects to DB9 through any standard PostgreSQL driver over pgwire. Loaders and actions run server-side, so your database credentials stay off the client. No special adapter or driver is needed.

Prerequisites

• A DB9 database (create one)
• Node.js 18+
• Remix 2+

Create a DB9 Database

Terminal

db9 create --name remix-app

db9 create prints the connection string, with the admin password, once — copy it from the Connection String: line. It has this shape:

Output

postgresql://YOUR_TENANT_ID.admin:YOUR_PASSWORD@pg.d0000.db9.io:5432/postgres

YOUR_TENANT_ID is the tenant ID prefix in the generated connection string. Copy the username from the Connection String: line printed by db9 create or from db9 db connect. The examples below use pg.db9.io:5433, which reaches the same server; either host and port works. db9 db status remix-app shows the connection string without the password; if you lose it, db9 db reset-password remix-app sets a new password and prints a new connection string.

Set the connection string as an environment variable:

.env

DATABASE_URL="postgresql://YOUR_TENANT_ID.admin:YOUR_PASSWORD@pg.db9.io:5433/postgres?sslmode=require"

Setup

Prisma

Terminal

npx create-remix@latest remix-db9
cd remix-db9
npm install prisma@6 @prisma/client@6
npx prisma init

Define your schema:

prisma/schema.prisma

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String
  posts     Post[]
  createdAt DateTime @default(now())
}

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String?
  published Boolean  @default(false)
  author    User     @relation(fields: [authorId], references: [id])
  authorId  Int
  createdAt DateTime @default(now())
}

Push the schema to DB9:

Terminal

npx prisma db push
npx prisma generate

This first push works against an empty database. Later schema changes need raw SQL — see Troubleshooting.

Create a singleton client to avoid connection exhaustion during development:

app/db.server.ts

import { PrismaClient } from '@prisma/client';

let prisma: PrismaClient;

declare global {
  var __prisma: PrismaClient | undefined;
}

if (process.env.NODE_ENV === 'production') {
  prisma = new PrismaClient();
} else {
  if (!global.__prisma) {
    global.__prisma = new PrismaClient();
  }
  prisma = global.__prisma;
}

export { prisma };

Drizzle

Terminal

npx create-remix@latest remix-db9
cd remix-db9
npm install drizzle-orm pg
npm install -D drizzle-kit @types/pg

Define your schema:

app/schema.server.ts

import { pgTable, serial, varchar, text, boolean, integer, timestamp } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: varchar('email', { length: 255 }).unique().notNull(),
  name: varchar('name', { length: 100 }).notNull(),
  createdAt: timestamp('created_at', { withTimezone: true }).defaultNow(),
});

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  title: varchar('title', { length: 500 }).notNull(),
  content: text('content'),
  published: boolean('published').default(false),
  authorId: integer('author_id').notNull().references(() => users.id),
  createdAt: timestamp('created_at', { withTimezone: true }).defaultNow(),
});

Create a singleton client:

app/db.server.ts

import { drizzle } from 'drizzle-orm/node-postgres';
import { Pool } from 'pg';

let pool: Pool;

declare global {
  var __pool: Pool | undefined;
}

if (process.env.NODE_ENV === 'production') {
  pool = new Pool({ connectionString: process.env.DATABASE_URL });
} else {
  if (!global.__pool) {
    global.__pool = new Pool({ connectionString: process.env.DATABASE_URL });
  }
  pool = global.__pool;
}

export const db = drizzle(pool);

Note

The .server.ts suffix tells Remix to exclude the file from client bundles. Always use it for database modules.

Loader (Read Data)

Loaders run on the server before a route renders. Use them to fetch data from DB9.

Prisma

app/routes/users.tsx

import type { LoaderFunctionArgs } from '@remix-run/node';
import { json } from '@remix-run/node';
import { useLoaderData } from '@remix-run/react';
import { prisma } from '~/db.server';

export async function loader({ request }: LoaderFunctionArgs) {
  const users = await prisma.user.findMany({
    include: { posts: true },
    orderBy: { createdAt: 'desc' },
  });
  return json({ users });
}

export default function UsersPage() {
  const { users } = useLoaderData<typeof loader>();
  return (
    <ul>
      {users.map((user) => (
        <li key={user.id}>
          {user.name} — {user.posts.length} posts
        </li>
      ))}
    </ul>
  );
}

Drizzle

app/routes/users.tsx

import type { LoaderFunctionArgs } from '@remix-run/node';
import { json } from '@remix-run/node';
import { useLoaderData } from '@remix-run/react';
import { db } from '~/db.server';
import { users } from '~/schema.server';

export async function loader({ request }: LoaderFunctionArgs) {
  const allUsers = await db.select().from(users).orderBy(users.createdAt);
  return json({ users: allUsers });
}

export default function UsersPage() {
  const { users: allUsers } = useLoaderData<typeof loader>();
  return (
    <ul>
      {allUsers.map((user) => (
        <li key={user.id}>
          {user.name} ({user.email})
        </li>
      ))}
    </ul>
  );
}

Action (Write Data)

Actions handle form submissions and other mutations, also server-side.

Prisma

app/routes/users.new.tsx

import type { ActionFunctionArgs } from '@remix-run/node';
import { json, redirect } from '@remix-run/node';
import { Form } from '@remix-run/react';
import { prisma } from '~/db.server';

export async function action({ request }: ActionFunctionArgs) {
  const formData = await request.formData();
  const email = formData.get('email') as string;
  const name = formData.get('name') as string;

  await prisma.user.create({ data: { email, name } });
  return redirect('/users');
}

export default function NewUserPage() {
  return (
    <Form method="post">
      <input type="text" name="name" placeholder="Name" required />
      <input type="email" name="email" placeholder="Email" required />
      <button type="submit">Create User</button>
    </Form>
  );
}

Drizzle

app/routes/users.new.tsx

import type { ActionFunctionArgs } from '@remix-run/node';
import { json, redirect } from '@remix-run/node';
import { Form } from '@remix-run/react';
import { db } from '~/db.server';
import { users } from '~/schema.server';

export async function action({ request }: ActionFunctionArgs) {
  const formData = await request.formData();
  const email = formData.get('email') as string;
  const name = formData.get('name') as string;

  await db.insert(users).values({ email, name });
  return redirect('/users');
}

export default function NewUserPage() {
  return (
    <Form method="post">
      <input type="text" name="name" placeholder="Name" required />
      <input type="email" name="email" placeholder="Email" required />
      <button type="submit">Create User</button>
    </Form>
  );
}

Production Notes

Never expose the connection string to the client

DB9 connections must happen server-side only (loaders, actions, resource routes). The connection string contains your admin credentials — never import database modules from client code. Use the .server.ts suffix to guarantee server-only bundling.

• Server-side only: Loaders and actions run on the server. Never import your db.server.ts module from a client-side file.
• Host and port: pg.db9.io:5433 and the pg.d0000.db9.io:5432 that db9 create prints reach the same server; port 5432 works on either host.
• TLS: Always include sslmode=require in the connection string for DB9’s hosted service.
• Node.js runtime required: DB9 uses the pgwire protocol over TCP. Deploy Remix on a Node.js server (Express, Hono, etc.), not on an edge runtime without TCP socket support.
• Connection pooling: Start with 5–10 connections. The singleton pattern in db.server.ts prevents pool exhaustion during development HMR cycles.

Troubleshooting

ECONNREFUSED on port 5432

DB9 accepts port 5432 as well as 5433, so the client is not reaching DB9 at all. A refusal from localhost or 127.0.0.1 means the connection string never reached the driver — check that DATABASE_URL is set in the process that opens the connection.

Edge runtime not supported

DB9 requires a TCP connection (pgwire protocol). If you deploy Remix to an edge runtime (e.g., Cloudflare Workers), database queries will fail. Use a Node.js-based server adapter instead.

Connection pool exhaustion in development

Remix’s dev server reloads modules on file changes. Without the singleton pattern in db.server.ts, each reload opens a new connection pool. If you see “too many connections” errors, verify you are using the global caching pattern shown above and restart the dev server.

Run prisma db push only once

prisma db push — used above — works for the initial schema creation against an empty database. Do not run it again once the tables exist: Prisma does not recognise DB9’s spelling of a serial column’s default and plans to recreate the sequence behind each autoincrement() id. For a table named after its model, as here, that second push reports success while pointing id at a new sequence that starts at 1, so inserts into a table that already has rows fail with 23505 duplicate key value. prisma migrate dev additionally fails at P3014, because DB9 rejects the hyphenated name Prisma gives its shadow database.

Create the initial schema with prisma db push, then apply later changes with raw SQL. See A second db push fails on autoincrement() ids for the details.

Next Pages

• Prisma — full Prisma integration guide
• Drizzle — full Drizzle integration guide
• Connect — connection strings and authentication
• Production Checklist — deployment readiness