@schemavaults/dbh

About

This package makes it easy to connect to a Postgres instance and run queries using the 'Kysely' type-safe query builder-- whether it's a local Postgres container or a serverless Neon-hosted Postgres container.

Highlighted Dependencies

• kysely - TypeScript SQL query builder (GitHub Repo)
• neon - Serverless Postgres (@neondatabase/serverless driver GitHub Repo)
• kysely-neon - Kysely Dialect for using @neondatabase/serverless

Usage

While @schemavaults/dbh can be used in development or production, this repository also contains tools to run your postgres database locally.

In your docker-compose.yml

Ensure that you have both postgres and a postgres-ws-proxy containers running with Docker. For an example, see the e2e test docker-compose.yml file: ./tests/docker-compose.yml

You'll likely want to replace the build: sections for the services in the e2e test example .yml file with image:. For example, use image: postgres:17.7 for the postgres service. For the proxy, you can pull the docker image from ghcr.io/schemavaults/dbh/postgres-ws-proxy; use the version number equal to your @schemavaults/dbh npm package installation:

# NPM Package: @schemavaults/dbh@0.12.0 => ghcr.io/schemavaults/dbh/postgres-ws-proxy:0.12.0

In your application server code

Set up an adapter based on your requirements (whether you need serverless/edge access to the database or whether using a standard ).

For an example, see the e2e test file: ./src/tests/e2e/ConnectToLocalDatabase.test.ts

You may need to define a custom WsProxyUrlGenerator function to determine how the postgres-ws-proxy can be reached.

From your command-line

CLI Help Command

# run migrations (and more) from the cli
npx @schemavaults/dbh --help
# or `bun run cli --help` if you have the dbh source repository as your working directory

Validate the shape of a migrations directory

# assert the migrations directory is well-formed:
#  - non-empty
#  - every file is prefixed with a 5-digit migration number (e.g. 00000-my-migration.ts)
#  - every module exports an up() and down() function
#  - there are no duplicate migration numbers (branch collisions, e.g. 00040-a.ts and 00040-b.ts)
# exits 0 when the directory is valid, non-zero otherwise.
bunx @schemavaults/dbh validate-migration-directory ./src/db/migrations

# treat duplicate migration numbers as warnings instead of errors (still exits 0)
bunx @schemavaults/dbh validate-migration-directory ./src/db/migrations --duplicates-as-warnings

Build example database migrations with the CLI

mkdir ./tests/tmp

# compile TypeScript Kysely migrations to JavaScript (Bun is used for building migrations)
bunx @schemavaults/dbh build-db-migrations ./src/tests/example-migrations \
  --outdir ./tests/tmp/example-compiled-migrations \
  --sql-module ./src/sql.ts \
  --sql-outdir ./tests/tmp/

# apply built migrations to database (NodeJS is used for applying migrations)
npx @schemavaults/dbh migrate ./tests/tmp/example-compiled-migrations --environment production --env-file ./.env.production
  
rm -rf ./tests/tmp

Choosing a database adapter

The migrate and reverse commands accept an -a, --adapter <adapter> flag to select which database adapter is used:

• postgres — direct Postgres connection via a pg connection pool
• postgres-neon-proxy — Postgres connection tunneled through a Neon-compatible WebSocket proxy

# apply migrations over a direct postgres connection
npx @schemavaults/dbh migrate ./migrations --environment development --adapter postgres

# or select the adapter via environment variable instead of the flag
SCHEMAVAULTS_DBH_ADAPTER="postgres" npx @schemavaults/dbh migrate ./migrations --environment development

The adapter is resolved in the following order of precedence:

1. the explicit --adapter CLI flag
2. the SCHEMAVAULTS_DBH_ADAPTER environment variable (also honoured when defined in an --env-file)
3. postgres-neon-proxy (the default, matching historical CLI behavior)

The createDbh() factory honours the same environment variable when an adapter type is not explicitly set:

import createDbh from "@schemavaults/dbh/create-dbh";

// e.g. SCHEMAVAULTS_DBH_ADAPTER="postgres"
await using dbh = await createDbh(undefined, { environment: "development" });

Note that createDbh() has no default adapter: if no adapter type is passed and SCHEMAVAULTS_DBH_ADAPTER is unset (or set to an unrecognized value), it throws.

Required Environment Variables

Ensure that the following environment variables are defined:

POSTGRES_USER=""
POSTGRES_PASSWORD=""
POSTGRES_URL=""
POSTGRES_URL_NON_POOLING=""
POSTGRES_HOST=""
POSTGRES_PORT="5432"
POSTGRES_DATABASE=""

Optional Environment Variables

# Which database adapter to use when one is not explicitly selected
# (via the CLI's --adapter flag or createDbh()'s adapter_type argument):
#   "postgres" | "postgres-neon-proxy"
SCHEMAVAULTS_DBH_ADAPTER=""

# Enable verbose adapter logging ("true" | "false");
# defaults to enabled outside of the production environment
SCHEMAVAULTS_DBH_DEBUG=""

Examples / Integration Tests

See the tests docker-compose.yml file for an example of:

• a postgres container (runs on port 5432).
• a postgres-ws-proxy container; that proxies HTTP traffic to the Postgres container via custom TCP protocol (runs on port 5433).
• a test container, that uses the @schemavaults/dbh package to execute test queries in this containerized database setup.

If you have docker compose installed you can use the helper script to run the tests:

cd ./tests && /bin/bash ./run_e2e_tests.sh

GitHub Repository

https://github.com/schemavaults/dbh