@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/[email protected] => ghcr.io/schemavaults/dbh/postgres-ws-proxy:0.11.1

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

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=""

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