Astro

Astro connects to DB9 through any standard PostgreSQL driver when running in SSR mode. DB9 is PostgreSQL-compatible, so no special adapter or driver is needed.

Prerequisites

• A DB9 database (create one)
• Node.js 18+
• Astro 4+

Create a DB9 Database

Terminal

db9 create --name astro-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 astro-app shows the connection string without the password; if you lose it, db9 db reset-password astro-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"

Enable SSR

Astro defaults to static output, which cannot connect to a database at request time. Switch to server-rendered mode and add the Node adapter:

Terminal

npx astro add node

Then set output: 'server' in your config:

astro.config.mjs

import { defineConfig } from 'astro/config';
import node from '@astrojs/node';

export default defineConfig({
  output: 'server',
  adapter: node({
    mode: 'standalone',
  }),
});

Setup

Prisma

Install

Terminal

npm install prisma@6 @prisma/client@6
npx prisma init

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
  createdAt DateTime @default(now())
}

Push the schema and generate the client:

Terminal

npx prisma db push
npx prisma generate

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

Singleton client

src/lib/prisma.ts

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

const globalForPrisma = globalThis as unknown as { prisma: PrismaClient };

export const prisma = globalForPrisma.prisma ?? new PrismaClient();

if (import.meta.env.DEV) {
  globalForPrisma.prisma = prisma;
}

Drizzle

Install

Terminal

npm install drizzle-orm pg
npm install -D drizzle-kit @types/pg

Schema

src/lib/schema.ts

import { pgTable, serial, varchar, 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(),
});

Client

src/lib/db.ts

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

const pool = new Pool({
  connectionString: import.meta.env.DATABASE_URL,
});

export const db = drizzle(pool);

Server Endpoint

Create an API route that handles GET and POST requests:

Prisma

src/pages/api/users.ts

import type { APIRoute } from 'astro';
import { prisma } from '../../lib/prisma';

export const GET: APIRoute = async () => {
  const users = await prisma.user.findMany({
    orderBy: { createdAt: 'desc' },
  });
  return new Response(JSON.stringify(users), {
    headers: { 'Content-Type': 'application/json' },
  });
};

export const POST: APIRoute = async ({ request }) => {
  const body = await request.json();
  const user = await prisma.user.create({
    data: { email: body.email, name: body.name },
  });
  return new Response(JSON.stringify(user), {
    status: 201,
    headers: { 'Content-Type': 'application/json' },
  });
};

Drizzle

src/pages/api/users.ts

import type { APIRoute } from 'astro';
import { db } from '../../lib/db';
import { users } from '../../lib/schema';

export const GET: APIRoute = async () => {
  const allUsers = await db.select().from(users).orderBy(users.createdAt);
  return new Response(JSON.stringify(allUsers), {
    headers: { 'Content-Type': 'application/json' },
  });
};

export const POST: APIRoute = async ({ request }) => {
  const body = await request.json();
  const [user] = await db.insert(users).values({
    email: body.email,
    name: body.name,
  }).returning();
  return new Response(JSON.stringify(user), {
    status: 201,
    headers: { 'Content-Type': 'application/json' },
  });
};

SSR Page

Fetch data server-side in the frontmatter of an .astro page:

Prisma

src/pages/users.astro

---
import { prisma } from '../lib/prisma';

const users = await prisma.user.findMany({
  orderBy: { createdAt: 'desc' },
});
---

<html>
  <body>
    <h1>Users</h1>
    <ul>
      {users.map((user) => (
        <li>{user.name} ({user.email})</li>
      ))}
    </ul>
  </body>
</html>

Drizzle

src/pages/users.astro

---
import { db } from '../lib/db';
import { users } from '../lib/schema';

const allUsers = await db.select().from(users).orderBy(users.createdAt);
---

<html>
  <body>
    <h1>Users</h1>
    <ul>
      {allUsers.map((user) => (
        <li>{user.name} ({user.email})</li>
      ))}
    </ul>
  </body>
</html>

Production Notes

• SSR required: DB9 connections happen at request time. You must use output: 'server' (or output: 'hybrid' for mixed pages). Static builds cannot query a database.
• Node adapter: Use @astrojs/node in standalone or middleware mode. Edge adapters (Cloudflare, Vercel Edge) do not support raw TCP connections required by PostgreSQL.
• 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 your connection string.
• Connection pooling: Start with a small pool size (5–10). DB9 handles per-tenant connection management server-side.

Troubleshooting

Pages return empty data or build errors

Astro’s default output: 'static' mode pre-renders pages at build time. Database queries in .astro frontmatter or API routes require output: 'server'. Update astro.config.mjs and add the Node adapter.

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.

Adapter errors on deploy

DB9 requires a TCP connection (pgwire protocol). If you deploy to Cloudflare Pages, Deno Deploy, or another edge platform, the PostgreSQL driver will fail. Use @astrojs/node with a Node.js-compatible host (Docker, Railway, Fly.io, traditional VPS).

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