Express / Hono

Express and Hono connect to DB9 through any standard PostgreSQL driver over pgwire. DB9 is PostgreSQL-compatible, so no special adapter is needed.

Prerequisites

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

Create a DB9 Database

Terminal

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

Express + Prisma

Terminal

mkdir express-db9 && cd express-db9
npm init -y
npm install express @prisma/client@6 dotenv
npm install -D prisma@6 typescript @types/express @types/node tsx
npx prisma init
npx tsc --init

Set DATABASE_URL in .env (see above), then 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
  createdAt DateTime @default(now()) @map("created_at")

  @@map("users")
}

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.

Express + pg

Terminal

mkdir express-db9 && cd express-db9
npm init -y
npm install express pg dotenv
npm install -D typescript @types/express @types/pg @types/node tsx
npx tsc --init

Create the connection pool:

src/db.ts

import { Pool } from 'pg';
import 'dotenv/config';

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

Create the table:

src/setup.ts

import { pool } from './db';

async function setup() {
  await pool.query(`
    CREATE TABLE IF NOT EXISTS users (
      id SERIAL PRIMARY KEY,
      email VARCHAR(255) UNIQUE NOT NULL,
      name VARCHAR(100) NOT NULL,
      created_at TIMESTAMPTZ DEFAULT NOW()
    )
  `);
  console.log('Table created');
  await pool.end();
}

setup();

Terminal

npx tsx src/setup.ts

Hono + Drizzle

Terminal

mkdir hono-db9 && cd hono-db9
npm init -y
npm install hono @hono/node-server drizzle-orm pg dotenv
npm install -D drizzle-kit @types/pg typescript @types/node tsx
npx tsc --init

Define the schema:

src/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(),
});

Create the DB client:

src/db.ts

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

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

export const db = drizzle(pool);

Create the table with raw SQL (drizzle-kit push fails on every run after the first against DB9):

src/setup.ts

import { Pool } from 'pg';
import 'dotenv/config';

const pool = new Pool({ connectionString: process.env.DATABASE_URL });

async function setup() {
  await pool.query(`
    CREATE TABLE IF NOT EXISTS users (
      id SERIAL PRIMARY KEY,
      email VARCHAR(255) UNIQUE NOT NULL,
      name VARCHAR(100) NOT NULL,
      created_at TIMESTAMPTZ DEFAULT NOW()
    )
  `);
  console.log('Table created');
  await pool.end();
}

setup();

Terminal

npx tsx src/setup.ts

CRUD Routes

Express + Prisma

src/index.ts

import express from 'express';
import { PrismaClient } from '@prisma/client';
import 'dotenv/config';

const app = express();
const prisma = new PrismaClient();

app.use(express.json());

app.get('/users', async (_req, res) => {
  const users = await prisma.user.findMany({
    orderBy: { createdAt: 'desc' },
  });
  res.json(users);
});

app.post('/users', async (req, res) => {
  const user = await prisma.user.create({
    data: { email: req.body.email, name: req.body.name },
  });
  res.status(201).json(user);
});

app.get('/users/:id', async (req, res) => {
  const user = await prisma.user.findUnique({
    where: { id: Number(req.params.id) },
  });
  if (!user) return res.status(404).json({ error: 'Not found' });
  res.json(user);
});

app.delete('/users/:id', async (req, res) => {
  await prisma.user.delete({ where: { id: Number(req.params.id) } });
  res.status(204).end();
});

app.listen(3000, () => console.log('Listening on http://localhost:3000'));

Express + pg

src/index.ts

import express from 'express';
import { pool } from './db';
import 'dotenv/config';

const app = express();
app.use(express.json());

app.get('/users', async (_req, res) => {
  const { rows } = await pool.query(
    'SELECT * FROM users ORDER BY created_at DESC'
  );
  res.json(rows);
});

app.post('/users', async (req, res) => {
  const { email, name } = req.body;
  const { rows } = await pool.query(
    'INSERT INTO users (email, name) VALUES ($1, $2) RETURNING *',
    [email, name]
  );
  res.status(201).json(rows[0]);
});

app.get('/users/:id', async (req, res) => {
  const { rows } = await pool.query('SELECT * FROM users WHERE id = $1', [
    req.params.id,
  ]);
  if (!rows.length) return res.status(404).json({ error: 'Not found' });
  res.json(rows[0]);
});

app.delete('/users/:id', async (req, res) => {
  await pool.query('DELETE FROM users WHERE id = $1', [req.params.id]);
  res.status(204).end();
});

app.listen(3000, () => console.log('Listening on http://localhost:3000'));

Hono + Drizzle

src/index.ts

import { Hono } from 'hono';
import { serve } from '@hono/node-server';
import { db } from './db';
import { users } from './schema';
import { eq } from 'drizzle-orm';

const app = new Hono();

app.get('/users', async (c) => {
  const allUsers = await db.select().from(users).orderBy(users.createdAt);
  return c.json(allUsers);
});

app.post('/users', async (c) => {
  const body = await c.req.json();
  const [user] = await db
    .insert(users)
    .values({ email: body.email, name: body.name })
    .returning();
  return c.json(user, 201);
});

app.get('/users/:id', async (c) => {
  const id = Number(c.req.param('id'));
  const [user] = await db.select().from(users).where(eq(users.id, id));
  if (!user) return c.json({ error: 'Not found' }, 404);
  return c.json(user);
});

app.delete('/users/:id', async (c) => {
  const id = Number(c.req.param('id'));
  await db.delete(users).where(eq(users.id, id));
  return c.body(null, 204);
});

serve({ fetch: app.fetch, port: 3000 }, () => {
  console.log('Listening on http://localhost:3000');
});

Error Handling

Express + Prisma

src/index.ts

import { Prisma } from '@prisma/client';
import { Request, Response, NextFunction } from 'express';

app.use((err: Error, _req: Request, res: Response, _next: NextFunction) => {
  if (err instanceof Prisma.PrismaClientKnownRequestError) {
    if (err.code === 'P2002') {
      return res.status(409).json({ error: 'Duplicate entry' });
    }
  }
  console.error(err);
  res.status(500).json({ error: 'Internal server error' });
});

Express + pg

src/index.ts

import { Request, Response, NextFunction } from 'express';

app.use((err: Error, _req: Request, res: Response, _next: NextFunction) => {
  if ((err as any).code === '23505') {
    return res.status(409).json({ error: 'Duplicate entry' });
  }
  console.error(err);
  res.status(500).json({ error: 'Internal server error' });
});

Hono + Drizzle

src/index.ts

app.onError((err, c) => {
  if ((err as any).code === '23505') {
    return c.json({ error: 'Duplicate entry' }, 409);
  }
  console.error(err);
  return c.json({ error: 'Internal server error' }, 500);
});

Production Notes

• 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.
• Connection pooling: Start with 5-10 connections (max option in pg.Pool, or Prisma’s connection_limit URL parameter). DB9 handles per-tenant pooling server-side.
• Graceful shutdown: Close the database pool when the process exits to avoid leaked connections:

Express + Prisma

src/index.ts

process.on('SIGTERM', async () => {
  await prisma.$disconnect();
  process.exit(0);
});

Express + pg

src/index.ts

process.on('SIGTERM', async () => {
  await pool.end();
  process.exit(0);
});

Hono + Drizzle

src/index.ts

import { Pool } from 'pg';

// keep a reference to the pool (export it from db.ts)
process.on('SIGTERM', async () => {
  await pool.end();
  process.exit(0);
});

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.

Connection pool exhaustion

If you see “too many connections” errors, reduce the pool size and confirm you are not creating a new pool on every request. Reuse a single pool instance across the application.

TLS / SSL errors

DB9 supports TLS and libpq negotiates it by default, but the server does not require it — make sure your connection string includes ?sslmode=require so a stray disable cannot downgrade you. If you configure pg.Pool options directly, set ssl: true or ssl: { rejectUnauthorized: false } for development.

Schema push commands fail

prisma db push works against an empty database and then stops applying changes once tables exist: Prisma does not recognise DB9’s spelling of a serial column’s default, so every later push tries to create each autoincrement() sequence again. With lower-case table names it fails with relation "…_id_seq" already exists; with Prisma’s default upper-case names ("User") it reports success but re-points id at a new sequence, so inserts into a table with rows then fail with 23505 — see the Prisma guide. drizzle-kit push works only for the first push into a schema with no tables: after that its introspection reads pg_stat_user_indexes for each existing table, which DB9 does not implement, so every later push applies nothing. Create the initial schema with prisma db push or with raw DDL and apply later changes with raw DDL — or, for Drizzle, manage the schema from the start with drizzle-kit generate and drizzle-kit migrate. See the Prisma guide and the Drizzle guide.

Next Pages

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