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

Get the connection string:

Terminal

db9 db status express-app

Set the connection string as an environment variable:

.env

DATABASE_URL="postgresql://express-app.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 dotenv
npm install -D prisma typescript @types/express @types/node ts-node
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

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 ts-node
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 ts-node 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 ts-node
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 (recommended over drizzle-kit push for 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 ts-node 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

• Port 5433: DB9 uses port 5433, not the default PostgreSQL port 5432. Double-check your DATABASE_URL.
• TLS required: 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 uses port 5433, not 5432. Verify your DATABASE_URL includes :5433.

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 requires TLS. Make sure your connection string includes ?sslmode=require. If you configure pg.Pool options directly, set ssl: true or ssl: { rejectUnauthorized: false } for development.

prisma db push or drizzle-kit push fails

DB9 has limited information_schema support. Use prisma db push for Prisma (works in most cases) or manage tables with raw SQL for Drizzle. See the Prisma guide and Drizzle guide for details.

Next Pages

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