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

Get the connection string:

Terminal

db9 db status astro-app

Set the connection string as an environment variable:

.env

DATABASE_URL="postgresql://astro-app.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 @prisma/client
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

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.
• Port 5433: DB9 uses port 5433, not the default PostgreSQL port 5432.
• TLS required: 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 uses port 5433. Check that your DATABASE_URL contains :5433/ and not :5432/.

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).

prisma db push fails

DB9 has limited information_schema support. See the Prisma guide for workarounds.

Next Pages

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