Drizzle Pg Proxy

Simple implementation of Drizzle HTTP Proxy for postgres as a standalone service. It uses hono as the http server, jose for JWT, and implements the /query endpoint to execute queries on a postgres database as show in the drizzle docs. It expects all queries to be signed with a JWT token to ensure that only authorized sources can execute queries.

I've found that running the proxy in a separate service is useful in avoiding connection limits on the database.

Note: For use with migrations, I haven't been able to get the drizzle migration script to run well through the proxy, so you still need to expose the TCP connection to the database for migrations.

Usage

You need an APP_SECRET key that is used in both your drizzle app code as well as the proxy service. This key is used to sign the JWT that is sent to the proxy service and the proxy service uses the same key to verify the JWT before executing the queries.

Generating an APP_SECRET

You can generate an APP_SECRET by running the following command:

openssl rand -hex 64

# Example output: 5af18615c9762d848ec19241a705c6816cfc0392dd80cae2f54ec2f9b0f2fd36db37ae88fdb752ed6b991e12f65214ada08528de6a85712639586c7cc3c31808

Then you can use the db object to query your database as you would with drizzle.

Deploying the docker container

The docker container requires 2 variables to be set:

• APP_SECRET: The secret key used to sign the JWT
• DATABASE_URL: The postgres connection string

The service listens on port 3030 and exposes the /query endpoint. Drizzle needs to be linked specifically to this /query endpoint.

Deploying on Railway

This template can easily be deployed with a linked postgresql database on Railway:

The template will automatically link the proxy service with the postgres database and set the DATABASE_URL environment variable. You will need to set the APP_SECRET environment variable manually.

After deploying on Railway you can use the public HTTPS endpoint that Railway generates and set it to the DATABASE_PROXY for the drizzle code example below.

Usage in Drizzle

In your app using drizzle, you need to follow the postgres http proxy connection setup as described in the drizzle docs.

Here is a modified setup with the JWT signing as well as some bonus date parsing:

import { drizzle } from "drizzle-orm/pg-proxy";
import * as schema from "./schemas";

import { JWTPayload, SignJWT } from "jose";

const APP_SECRET = process.env.APP_SECRET
const DATABASE_PROXY = process.env.DATABASE_PROXY // The URL of the proxy service

const key = new TextEncoder().encode();
const alg = "HS256";

export async function signJwt<TPayload extends JWTPayload>(
	payload: TPayload,
	options?: { expires?: Date | string },
) {
	return await new SignJWT(payload)
		.setProtectedHeader({ alg })
		.setIssuedAt()
		.setExpirationTime(options?.expires ?? "7 days")
		.sign(key);
}

export function isStringISODate(str: string): boolean {
	// Regular expression to match ISO 8601 date format
	const iso8601Regex = /^\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}(\.\d+)?Z$/;
	return iso8601Regex.test(str);
}

export const db = drizzle(
	async (sql, params, method) => {
		try {
			const response = await fetch(env.DATABASE_PROXY + "/query", {
				method: "POST",
				headers: {
					"Content-Type": "application/jwt",
				},
				body: await signJwt({ sql, params, method }),
			});
			const rows = await response.json();

			if (rows.length > 0) {
				const keys = Object.keys(rows[0]);
				for (const key of keys) {
					if (
						typeof rows[0][key] === "string" &&
						isStringISODate(rows[0][key])
					) {
						for (const row of rows) {
							row[key] = new Date(row[key]);
						}
					}
				}
			}

			return { rows };
		} catch (e: any) {
			console.error("Error from pg proxy server: ", e.message);
			return { rows: [] };
		}
	},
	{ schema },
);