y58pg

TypeScript library and Node CLI for PostgreSQL tasks.

Setup

npm install
npm run build

Global install

After publishing, install the CLI globally and run it with the y58pg command:

npm install --global y58-pg
y58pg create-database -h localhost -p 5432 -u postgres --database example_db -s core --owner example_db_owner

For local packaging checks, build a tarball with:

npm pack --dry-run

Passing Parameters

Options can be passed with command-line flags, environment variables, or an env file.

Command-line flags have the highest precedence:

y58pg create-database -h localhost -p 5432 -u postgres --database example_db -s core --owner example_db_owner --writer-role example_db_writer_role --reader-role example_db_reader_role

Environment variables are read when a command-line flag is not set:

PG_HOST=localhost
PG_PORT=5432
PG_USER=postgres
PG_PASSWORD=secret
PG_DATABASE=example_db
PG_SCHEMA=core
PG_OWNER=example_db_owner
PG_OWNER_PASSWORD=app-secret
PG_WRITER_ROLE=example_db_writer_role
PG_READER_ROLE=example_db_reader_role
PG_NEW_USER=example_app_user
PG_NEW_USER_PASSWORD=user-secret
PG_NEW_USER_ROLE=example_db_writer_role
PG_DROPPED_USER=example_app_user

y58pg create-database

The same NAME=value lines can be placed in an env file and loaded with -e / --env:

y58pg create-database -e .env

Blank lines and comment lines beginning with # are ignored. Env file values are not shell-expanded or unquoted, so write values without surrounding quotes unless the quote characters are part of the intended value. The env file may contain variables unrelated to this CLI.

The precedence order is:

1. Command-line flags
2. Actual environment variables
3. Values from the env file

Command-line option values can reference an environment variable or a value from the env file by using an exact env:NAME value. The name must be a valid environment variable name, and the whole option value must be the reference:

y58pg migrate-database -e .env --database env:PG_DATABASE -u env:PG_USER -i env:PG_INPUT

When an env:NAME reference is resolved, actual environment variables take precedence over values from the env file. The -e / --env option itself may also use env:NAME, but that reference can only be resolved from an actual environment variable because the env file has not been loaded yet.

By default, commands run in interactive mode. The CLI reviews resolved values before connecting, using command-line flags, environment variables, env file values, and command defaults where available. Press Enter to keep the value shown in brackets, or type a replacement. If a required value is still missing, the CLI prompts for it. Password prompts hide existing password values with stars. create-database asks for the owner password twice and both entries must match. drop-database asks for the database name as confirmation before dropping it. drop-user asks for the user name as confirmation before dropping it.

Use -y or --yes for non-interactive mode. In this mode, no prompts are shown; any missing required value causes the command to fail. Required values must come from a flag, an actual environment variable, an env file, or a command default. Passwords can be passed with -w / --password, but prefer an indirect value such as -w env:NAME or PG_PASSWORD instead of a literal password.

Host values must be valid IP addresses or DNS names. PostgreSQL user, database, schema, owner, writer role, reader role, new user, new user role, and dropped user values must be simple PostgreSQL identifiers: at most 63 bytes, starting with a letter or underscore, and containing only letters, digits, underscores, or dollar signs.

Options

Common Options

These options are accepted by every command except where noted.

• -y, --yes: Skip interactive review. For drop-database and drop-user, also skip the drop confirmation prompt.

• -e, --env PATH: Load env file values from PATH. The file contains NAME=value lines.

• -h, --host HOST: PostgreSQL server host. Defaults to PG_HOST, then localhost.

• -p, --port PORT: PostgreSQL server port. Defaults to PG_PORT, then 5432.

• -u, --user NAME: PostgreSQL user to connect as. Defaults to PG_USER. Required in --yes mode.

• -w, --password PASSWORD: PostgreSQL connection password. Defaults to PG_PASSWORD. Required in --yes mode. Avoid passing a literal password on the command line because shell history and process listings may expose it; prefer -w env:NAME or PG_PASSWORD.

• -d, --database NAME: PostgreSQL database name. Defaults to PG_DATABASE. Required in --yes mode for commands that operate on a database. Optional for create-user when used with --schema to set the new user's search path. Not accepted by drop-user.

create-database

Create a PostgreSQL database:

y58pg create-database -u postgres -d example_db --owner example_db_owner

• -s, --schema NAME: Schema name to create. Defaults to PG_SCHEMA, then core.

• --owner NAME: PostgreSQL login role that will own the database. Defaults to PG_OWNER, then <database-name>_owner.

• --owner-password PASSWORD: Password for the database owner role. Defaults to PG_OWNER_PASSWORD. Required in --yes mode. Avoid passing a literal password on the command line because shell history and process listings may expose it; prefer --owner-password env:NAME or PG_OWNER_PASSWORD.

• --writer-role NAME: NOLOGIN role with write privileges on the schema. Defaults to PG_WRITER_ROLE, then <database-name>_writer_role in interactive mode. Required in --yes mode.

• --reader-role NAME: NOLOGIN role with read privileges on the schema. Defaults to PG_READER_ROLE, then <database-name>_reader_role in interactive mode. Required in --yes mode.

When creating a database, the CLI also creates the owner as a login role when needed, creates the writer and reader as NOLOGIN roles when needed, revokes default database privileges from PUBLIC, grants CONNECT on the database to the writer and reader roles, changes the new database's public schema owner to the requested owner role, revokes all privileges on that schema from PUBLIC, creates the requested schema owned by the database owner, revokes all privileges on that schema from PUBLIC, sets the owner's search path in the new database to the requested schema, and grants current and future schema privileges to the writer and reader roles.

If the owner role already exists and can login, the command reuses it. If the owner role already exists but cannot login, the command fails. The owner password is only used when the owner role needs to be created.

If the writer or reader role already exists and cannot login, the command reuses it. If either role already exists and can login, the command fails. Missing writer and reader roles are created as NOLOGIN roles.

The writer role receives CONNECT on the database, USAGE on the schema, SELECT, INSERT, UPDATE, and DELETE on current and future tables, and USAGE, SELECT, and UPDATE on current and future sequences. The reader role receives CONNECT on the database, USAGE on the schema, SELECT on current and future tables, and USAGE and SELECT on current and future sequences.

create-user

Create a PostgreSQL login role and grant it one existing role:

y58pg create-user -u postgres --new-user example_app_user --new-user-role example_db_writer_role

• -d, --database NAME: Database name for the new user's search path. Defaults to PG_DATABASE when set. Must be provided with --schema to set a search path.

• -s, --schema NAME: Schema name for the new user's search path. Defaults to PG_SCHEMA when set. Must be provided with --database to set a search path.

• --new-user NAME: PostgreSQL login role to create. Defaults to PG_NEW_USER. Required in --yes mode.

• --new-user-password PASSWORD: Password for the new user. Defaults to PG_NEW_USER_PASSWORD. Required in --yes mode. Avoid passing a literal password on the command line because shell history and process listings may expose it; prefer --new-user-password env:NAME or PG_NEW_USER_PASSWORD.

• --new-user-role NAME: Existing role to grant to the new user. Defaults to PG_NEW_USER_ROLE. Required in --yes mode.

The command connects to PostgreSQL using the common connection options, verifies that the requested new user role already exists, creates the new login role, and grants that one role to the new user. If both --database and --schema are provided, the command also sets the new user's search_path to that schema for that database. If only one of --database or --schema is provided, the command fails. If the role named by --new-user-role does not exist, the command fails before creating the new user.

drop-user

Drop an existing PostgreSQL user:

y58pg drop-user -u postgres --dropped-user example_app_user

• --dropped-user NAME: PostgreSQL user to drop. Defaults to PG_DROPPED_USER. Required in --yes mode.

The command connects to PostgreSQL using the common connection options and runs DROP ROLE for the requested user. PostgreSQL will reject the drop if the user owns objects, has dependent privileges, or does not exist.

migrate-database

Apply SQL migrations from a directory:

y58pg migrate-database -u example_db_owner -d example_db -s core -i ./migrations

• -s, --schema NAME: Schema name to migrate. Defaults to PG_SCHEMA, then core.

• -i, --input PATH: Input directory containing .sql files. Defaults to PG_INPUT. Required in --yes mode.

The command applies *.sql files in alphabetical order. It sets the session search path to the target schema before running migrations, then creates and reads an _migrations table in that schema to track applied migrations by filename. The migration history must match the beginning of the sorted file list by exact filename, not by number prefix: for example, a recorded 002_bar.sql does not match an available 002_foo.sql. History entries without an exact matching file cause the command to fail, and files are only applied after the last recorded migration. A PostgreSQL advisory lock is held while migrations run to prevent concurrent migration processes from conflicting.

load-data

Bulk load CSV files from a directory:

y58pg load-data -u example_db_owner -d example_db -s core -i ./csv

• -s, --schema NAME: Schema name to load data into. Defaults to PG_SCHEMA, then core.

• -i, --input PATH: Input directory containing .csv files. Defaults to PG_INPUT. Required in --yes mode.

The command loads *.csv files in foreign-key dependency order, using alphabetical order as the tie-breaker for unrelated tables. Each filename must match an existing table in the target schema, for example users.csv loads into core.users. Each CSV file must include a header row; the header columns are used as the COPY column list, so the CSV column order does not have to match the physical table order. If a target table does not exist, the command prints an error and exits.

save-data

Bulk save table data to CSV files:

y58pg save-data -u example_db_owner -d example_db -s core -o ./csv

• -s, --schema NAME: Schema name to save data from. Defaults to PG_SCHEMA, then core.

• -o, --output PATH: Output directory for .csv files. Defaults to PG_OUTPUT. Required in --yes mode.

• -f, --force: Overwrite existing .csv files.

The command saves every base table in the target schema as a CSV file with a header row. Each output filename matches the table name, for example core.users is saved to users.csv. If any output file already exists, the command prints an error and exits before writing files unless --force is set.

drop-database

Drop an existing database:

y58pg drop-database -u example_db_owner -d example_db 

• -f, --force: Terminate other connections before dropping the database.

Programmatic usage

import { y58pg } from '@yard58/y58pg';

await y58pg.createDatabase({
    user: 'postgres',
    password: 'secret',
    database: 'example_db',
    schema: 'core',
    owner: 'example_db_owner',
    ownerPassword: 'app-secret'
});

await y58pg.createUser({
    user: 'postgres',
    password: 'secret',
    database: 'example_db',
    schema: 'core',
    newUser: 'example_app_user',
    newUserPassword: 'user-secret',
    newUserRole: 'example_db_writer_role'
});

await y58pg.dropUser({
    user: 'postgres',
    password: 'secret',
    droppedUser: 'example_app_user'
});

await y58pg.migrateDatabase({
    user: 'example_db_owner',
    password: 'app-secret',
    database: 'example_db',
    schema: 'core',
    input: './migrations'
});

await y58pg.loadData({
    user: 'example_db_owner',
    password: 'app-secret',
    database: 'example_db',
    schema: 'core',
    input: './csv'
});

await y58pg.saveData({
    user: 'example_db_owner',
    password: 'app-secret',
    database: 'example_db',
    schema: 'core',
    output: './csv'
});

await y58pg.dropDatabase({
    user: 'example_db_owner',
    password: 'app-secret',
    database: 'example_db',
    force: true
});

When called from code, option names match the CLI long option names in camel case. user and password are the connection credentials. For createDatabase, owner and ownerPassword are the login role that will own the new database and that role's password. For createUser, newUser, newUserPassword, and newUserRole are the login role to create, its password, and the existing role to grant; database and schema may also be provided together to set the new user's search path for that database. For dropUser, droppedUser is the login role to drop. createDatabase defaults missing host, port, schema, owner, writerRole, and readerRole the same way as the CLI. createUser and dropUser default missing host and port the same way as the CLI.