@waukeshamakerspace/db-kit

MySQL plumbing for WMI services: the house DATABASE_URL / DATABASE_NAME convention, a drizzle-free pool and migration connection, and the ensure/migrate/baseline commands each repo was copying by hand into packages/database/scripts/.

Install

npm install @waukeshamakerspace/db-kit mysql2

drizzle-orm is a peer dependency only for createDbHandle and runMigrations; the core below never imports it.

Use

Drizzle-free core

db-kit's own drizzle-orm is a devDependency at whatever version the kit was last built against. The WMI apps run older ones (0.29 and 0.36 at the time of writing), and a handle from one drizzle wrapping schema objects from another is not a supported configuration. So the parts that are not drizzle live on their own and the app keeps its one drizzle() line, on its own version:

// packages/database/src/index.ts
import { createPool, databaseConfigFromEnv, pingPool, closePool } from '@waukeshamakerspace/db-kit';
import { drizzle } from 'drizzle-orm/mysql2';
import * as schema from './schema/index.js';

const pool = createPool(databaseConfigFromEnv(), { connectionLimit: 10 });
export const db = drizzle(pool, { schema, mode: 'default' });
export const ping = () => pingPool(pool);
export const closeDb = () => closePool(pool);

createPool(config, tuning?) returns a lazily-connected mysql2/promise pool with the service database in the URI, verified TLS when the host is RDS, and the config's pool size. tuning is any other PoolOptions (keymaster passes waitForConnections, queueLimit, enableKeepAlive, keepAliveInitialDelay, connectTimeout and idleTimeout), and may override connectionLimit: the kit default is 5 and most WMI Lambdas run 10, so pass it explicitly. pingPool runs SELECT 1 and throws; closePool ends the pool.

Migrations are the same shape. The kit opens the connection (service database, multipleStatements, TLS) and the app runs its own migrator over it:

// packages/database/scripts/migrate.ts
import { databaseConfigFromEnv, loadNearestEnv, openMigrationConnection } from '@waukeshamakerspace/db-kit';
import { drizzle } from 'drizzle-orm/mysql2';
import { migrate } from 'drizzle-orm/mysql2/migrator';

loadNearestEnv();
const connection = await openMigrationConnection(databaseConfigFromEnv());
try {
  await migrate(drizzle(connection), { migrationsFolder: 'migrations' });
} finally {
  await connection.end();
}

createDbHandle(schema) and runMigrations(config, folder) still exist and do exactly this with db-kit's own drizzle. Use them only when the app's drizzle-orm version matches the kit's; otherwise the Lambda bundle carries two copies. When the versions are reconciled they become a one-line swap.

The three environment shapes

Every WMI app ends up at the same DatabaseConfig (url for the server, name for the database, connectionLimit), reached from one of three places:

DATABASE_URL + DATABASE_NAME (roster, journeyman):

const config = databaseConfigFromEnv();
// DATABASE_URL=mysql://roster_app:[email protected]:3306  DATABASE_NAME=roster

Discrete DB_* variables (gatehouse, atlas): DB_HOST, DB_PORT, DB_USER, DB_PASS, DB_NAME, with DB_MIGRATOR_USER / DB_MIGRATOR_PASS for scripts:

const config = databaseConfigFromDiscreteEnv(process.env, { defaultName: 'gatehouse' });
// scripts run as the DDL-capable user when one is set:
const migrator = databaseConfigFromDiscreteEnv(process.env, { defaultName: 'gatehouse', preferMigrator: true });

The user and password are percent-encoded into the URL, so an RDS password carrying @, :, /, ?, # or % arrives at MySQL intact. Host defaults to localhost and port to 3306; the user never defaults to root.

Secrets Manager (keymaster): the Lambda copies the secret's JSON into the same DB_* variables before the database module is imported, then uses the discrete adapter:

// lambda.ts, before `await import('./app.js')`
Object.assign(process.env, JSON.parse(secret.SecretString));
// database/src/config.ts
export const getDbConfig = () => databaseConfigFromDiscreteEnv(process.env, { defaultName: 'keymaster' });

Scripts

The kit ships these as library functions, not binaries: each app keeps its own short scripts/{migrate,baseline,ensure-db}.ts and calls them, because the five apps span drizzle-orm 0.29 to 0.36 and run migrations over their OWN drizzle. A service's migrate script is a handful of lines:

// packages/database/scripts/migrate.ts
import { databaseConfigFromDiscreteEnv, openMigrationConnection } from '@waukeshamakerspace/db-kit';
import { drizzle } from 'drizzle-orm/mysql2';
import { migrate } from 'drizzle-orm/mysql2/migrator';

const connection = await openMigrationConnection(
  databaseConfigFromDiscreteEnv(process.env, { defaultName: 'atlas', preferMigrator: true }),
);
await migrate(drizzle(connection), { migrationsFolder: './drizzle' });
await connection.end();

openMigrationConnection(config) hands back a multipleStatements, RDS-TLS connection the app wraps in its own drizzle migrate(). An app whose drizzle version matches the kit's can skip the wrapper and call runMigrations(config, migrationsFolder, logger?) directly (it uses db-kit's own drizzle). baselineMigrations(config, folder, { requiredTables }) marks a pre-Drizzle schema's migrations already-applied (see below); ensureDatabase and provisionDatabase are documented under Provisioning. The config comes from databaseConfigFromEnv (DATABASE_URL / DATABASE_NAME) or databaseConfigFromDiscreteEnv (the discrete DB_* convention).

(Earlier versions shipped wmi-db-migrate / wmi-db-baseline / wmi-db-ensure / wmi-db-provision CLI bins over these same functions. No app used them, and the bins pointed at dist/bin/*.js that does not exist at install time, so a WARN Failed to create bin fired on every deploy. Removed 2026-09-08; the functions are the interface.)

Provisioning

provisionDatabase(planProvision(options), adminUrl) creates a database and its two scoped users. It replaces the block of manual SQL that each repo has been carrying in docs/deploy.md, so dev and production get set up the same way.

import { planProvision, provisionDatabase } from '@waukeshamakerspace/db-kit';
const adminUrl = process.env.WMI_DB_ADMIN_URL!; // server only, never the service .env
await provisionDatabase(planProvision({ adminUrl, name: 'journeyman' }), adminUrl);

It creates, idempotently:

| | | |---|---| | `journeyman` | CREATE DATABASE IF NOT EXISTS | | journeyman_migrator | ALTER, CREATE, DROP, INDEX, REFERENCES, SELECT, INSERT, UPDATE, DELETE | | journeyman_app | SELECT, INSERT, UPDATE, DELETE |

The migrator holds DML as well as DDL on purpose. Drizzle maintains its __drizzle_migrations bookkeeping table over the same connection, so a migrator granted DDL alone looks correct and then fails partway through the first migration it tries to record.

The app user never holds DDL, so a bug cannot alter its own schema. Narrow it further with the appPrivileges option, which accepts only those four DML privileges and refuses anything else:

// Journeyman's Lambda user: its append-only events table cannot be rewritten
planProvision({ adminUrl, name: 'journeyman', appPrivileges: ['SELECT', 'INSERT'] });

Both passwords are generated here, printed once, and never written to disk. The output is ready to paste: DATABASE_URL + DATABASE_NAME for the service's .env and samconfig.toml, and the migrator's URI for the MIGRATOR_DATABASE_URL GitHub secret.

planProvision builds the plan without touching a server, so plan.statements is exactly what would run: print them to hand the SQL to someone else, or diff them before provisionDatabase executes.

Two things worth knowing before re-running it:

• It rotates both passwords. CREATE USER IF NOT EXISTS is a no-op for an existing account, so the password is set with a following ALTER USER. Without that a re-run would print credentials that do not work. Existing deployments need the new values.
• It revokes before granting, so a re-run tightens an account that was over-granted by hand rather than only widening it. Narrowing an existing journeyman_app to SELECT,INSERT really does take UPDATE and DELETE away.

The admin credential is passed to planProvision / provisionDatabase explicitly and must be the server only (no database path). It is never read from the service's .env, because a stray DATABASE_URL must not be able to decide which server gets provisioned.

The generated SQL is verified against MySQL 8.4 in a container, not just asserted as strings: the grants, the privilege boundary (the app user is refused CREATE, ALTER, and DROP), password rotation, and narrowing.

Ensuring a database exists

ensureDatabase(config, { charset?, collation?, logger? }) creates the database when it is missing and returns { created }. The flag is looked up in information_schema.schemata, not inferred, so it is truthful, and no DDL runs when the database is already there. Keymaster passes charset: 'utf8mb4', collation: 'utf8mb4_unicode_ci'; omitted, the server default applies. Both are validated like the name, since they are interpolated into the DDL.

Baselining a database that predates Drizzle

Gatehouse's schema was built by an older raw-SQL runner and atlas's by db:push, so each had the real tables and no __drizzle_migrations journal. Running migrate there would try to CREATE tables that already exist. baselineMigrations records every generated migration as already applied, without running any of them, so the next db:migrate applies only what comes after:

await baselineMigrations(config, 'drizzle', { requiredTables: ['resources', 'locations', 'users', 'tags'] });

It reads meta/_journal.json and hashes each <tag>.sql with sha256 exactly as drizzle's readMigrationFiles does (the test suite cross-checks the two on the same folder), creates the journal table with drizzle's own DDL, and inserts one (hash, created_at) row per entry. Drizzle's applied-check is purely by created_at, so those rows are all it needs.

Two guards, because a wrong baseline is worse than none: it refuses unless every requiredTables entry already exists (an empty database gets db:migrate, never a journal with no schema), and it is a no-op once the journal has rows, so re-running it is safe. The journal is read before the connection is opened, so a missing or empty folder fails without touching the server. --dry-run prints the rows it would insert.

The convention

DATABASE_URL is the MySQL server, with no database in the path. DATABASE_NAME selects the per-service database on it. Keeping them apart is what lets one RDS instance host every service, and lets the migrator and the app connect to the same place as different users:

• <service>_migrator holds DDL on its own database only, never the RDS master
• <service>_app holds only the DML the service needs

Journeyman takes that further: its Lambda user has SELECT and INSERT only, so its append-only events table cannot be rewritten even by a bug. That is what planProvision({ ..., appPrivileges: ['SELECT', 'INSERT'] }) produces. The convention used to live as hand-written SQL in each repo's deploy doc; it is now executable.

Notable fixes over the copied scripts

databaseUri() parses instead of concatenating. Every copy did `${url}/${name}`, which silently produces garbage when DATABASE_URL carries query parameters: mysql://h:3306?ssl=true + /journeyman becomes mysql://h:3306?ssl=true/journeyman, burying the database name in the query string. TLS-enabled RDS URIs look exactly like that. It also now rejects a DATABASE_URL that already names a database, instead of building .../wrongdb/rightdb.

Database names are validated once, centrally. The name is interpolated into CREATE DATABASE, where no amount of parameter binding helps. Journeyman checked it; the raw-mysql2 services did not.