StrongPG

Strongly-typed database migrations & querying, a wrapper around the pg module.

This package is currently in development, with only support for migrations.

Since the package is in development, there's not full documentation, but I can provide some examples:

Set up table schemas

import { DataType } from "strong-pg/IStrongPG";
import Schema from "strong-pg/Schema";

export const TAG_MAX_LENGTHS_V3 = {
	name: 64,
	description: 512,
};

export const TAG_CATEGORIES_SCHEMA_V3 = Schema.table({
	id: DataType.BIGSERIAL,
	PRIMARY_KEY: Schema.primaryKey("id"),
	name: DataType.VARCHAR(TAG_MAX_LENGTHS_V3.name),
	description: DataType.VARCHAR(TAG_MAX_LENGTHS_V3.description),
});

export const TAGS_SCHEMA_V3 = Schema.table({
	id: DataType.BIGSERIAL,
	PRIMARY_KEY: Schema.primaryKey("id"),
	name: DataType.VARCHAR(TAG_MAX_LENGTHS_V3.name),
	category: DataType.BIGINT,
	description: DataType.VARCHAR(TAG_MAX_LENGTHS_V3.description),
});

Create a database schema including the new table schemas

import { SCHEMA_V2 } from "../m2/m2";
import { TAGS_SCHEMA_V3, TAG_CATEGORIES_SCHEMA_V3, TAG_MAX_LENGTHS_V3 } from "./TagsV3";
import { DataType } from "strong-pg/IStrongPG";
import Migration from "strong-pg/Migration";
import Schema from "strong-pg/Schema";

export const SCHEMA_V3 = Schema.database({
	tables: {
		tags: TAGS_SCHEMA_V3,
		tag_categories: TAG_CATEGORIES_SCHEMA_V3,
	},
	indices: {
		tag_categories_unique: Schema.INDEX,
		tags_unique: Schema.INDEX,
	},
	collations: {
		ci: Schema.COLLATION,
	},
});

Set up a migration from the previous version of the schema to the version defined above

let m3: Migration<typeof SCHEMA_V2, typeof SCHEMA_V3>;

m3 = new Migration(SCHEMA_V2)

	.createCollation("ci", "icu", "und-u-ks-level2", false)

	.createTable("tag_categories", table => table
		.addColumn("id", DataType.BIGSERIAL)
		.addPrimaryKey("id")
		.addColumn("name", DataType.VARCHAR(TAG_MAX_LENGTHS_V3.name), c => c.notNull().collate("ci"))
		.addColumn("description", DataType.VARCHAR(TAG_MAX_LENGTHS_V3.description)))

	.createTable("tags", table => table
		.addColumn("id", DataType.BIGSERIAL)
		.addPrimaryKey("id")
		.addColumn("name", DataType.VARCHAR(TAG_MAX_LENGTHS_V3.name), c => c.notNull().collate("ci"))
		.addColumn("category", DataType.BIGINT)
		.foreignKey("category", "tag_categories", "id")
		.addColumn("description", DataType.VARCHAR(TAG_MAX_LENGTHS_V3.description)))

	.createIndex("tag_categories_unique", "tag_categories", index => index
		.unique()
		.column("name"))

	.createIndex("tags_unique", "tags", index => index
		.unique()
		.column("name")
		.column("category"))

	.alterTable("tags", alter => alter
		.unique("tags_unique", "tags_unique"))

	.schema(SCHEMA_V3);

export default m3;

Ensure migrated before running queries

import m1 from "./migration/m1/m1";
import m2 from "./migration/m2/m2";
import m3 from "./migration/m3/m3";
import m4 from "./migration/m4/m4";
import m5, { SCHEMA_V5 } from "./migration/m5/m5";
import { Pool } from "pg";
import Database from "strong-pg";

type Schema = typeof SCHEMA_V5;
export interface ISchema extends Schema { }

let pool: Pool | Promise<Pool> | undefined;
export default async function getPool () {
	return pool ??= (async () => {
		const pool = new Pool({ connectionString: process.env.DATABASE_URL });
		const database = new Database<ISchema>(SCHEMA_V5, pool);

		await database.dropIfShould();
		await pool.query("CREATE SCHEMA IF NOT EXISTS public");
		await pool.query(`SET search_path TO public`);

		database.setHistory(history => history
			.migration(m1)
			.migration(m2)
			.migration(m3)
			.migration(m4)
			.migration(m5));

		await database.migrate();

		return pool;
	})();
}

Tests

Run the type-level and unit suites with:

pnpm test

PostgreSQL integration tests require TEST_DATABASE_URL to point to a disposable database named exactly strong_pg_test. They also require STRONG_PG_TEST_DATABASE_RESET=1 as explicit authorization to destroy test data. The integration setup refuses to connect unless both safeguards are present because it recreates the public schema before every test. GitHub Actions provides this disposable database and opt-in automatically.

STRONG_PG_TEST_DATABASE_RESET=1 TEST_DATABASE_URL=postgres://postgres:postgres@localhost:5432/strong_pg_test pnpm test:integration

Security tests distinguish passing guarantees from known gaps. Normal tests prove that runtime values remain in PostgreSQL parameter arrays. Tests declared with Vitest's test.fails reproduce known injection-capable paths; an unexpected pass means the production behavior was hardened and the test should be converted into a normal regression test.

Table, column, function, and other schema identifiers are developer-authored SQL structure. sql.raw is an explicit unsafe escape hatch and must never receive untrusted input.