PostgreSQL and NpgsqlRest Development Toolchain.

A CLI tool with a TUI experience for managing PostgreSQL client tools and NpgsqlRest in your development environment.

Note: This project is under active development. Commands, options, and behavior are subject to change.

• Bun runtime

bun install -g pgdev

pgdev update

bun uninstall -g pgdev

Interactive TUI dashboard for configuring your development environment. This is the main entry point for all configuration. Aliases: pgdev init, pgdev setup.

Sections:

• NpgsqlRest — detect or install NpgsqlRest (npm, bun, standalone binary, or Docker)
• PostgreSQL Tools — detect or install psql, pg_dump, pg_restore (Homebrew, apt, apk, dnf)
• NpgsqlRest Config Files — create and manage NpgsqlRest JSON config files (production, development, local) with a TUI editor for connection strings, config settings, and more
• pgdev Environment — configure env file, database connection, and project settings (project directory, tests, schemas)

Extract database schema and routines into project files (DB → Files):

• Dumps the full schema to project_dir/V000_schema.sql (DDL for tables, types, etc.) — prompted separately in interactive mode
• Extracts each routine (function, procedure, or other configured types) into individual .sql files in project_dir
• Applies configurable formatting to new files (see [format] options)
• Organizes files into subdirectories based on group_order (by API type, schema, name segment, or object kind)
• New files include a pgdev TOML header with migration metadata (type, version, run_before, rerun_with)
• Supports VIEW extraction when added to routine_types
• Interactive by default — shows each change and prompts before writing:
  • Y (default) — apply this change
  • n — skip this file
  • N — skip and remember (adds to sync_skip in config, skipped in future syncs)
  • a — apply all remaining changes without prompting
  • q — quit immediately
• Functional comparison — existing files are only updated when there's a real difference (definition, comments, grants), not just formatting. User formatting is preserved for unchanged files.
• Handles multi-routine files — multiple routines in one file are synced together

Note: --format cannot be combined with --comments/--grants/--definitions.

Compare project SQL files against the live database (read-only):

• Parses all .sql files in project_dir and fetches routine metadata from pg_catalog
• Reports routines that need creating, updating, or dropping
• Compares definition (parameters, return type, body, attributes), comments, and optionally grants
• Supports ignore_body_whitespace for whitespace-insensitive body comparison
• Supports routines inside DO $$ ... $$ blocks and files with multiple routines

pgdev includes a file-based migration system that tracks three types of SQL files in project_dir:

Files are classified by priority:

1. TOML header — explicit type or version in the pgdev header overrides everything
2. Naming convention — filename prefix (V/R + separator) determines type
3. Parser detection — files containing valid routine definitions are classified as routines
4. Unrecognized — warning issued, file skipped

SQL files can include a pgdev metadata header at the top of the file. Two formats are supported:

Format 1 — line comments:

-- [pgdev]
-- type = "routine"
-- run_before = "R__create_views"

Format 2 — block comment with --- delimiters:

/*
---
[pgdev]
type = "routine"
# version = ""
# run_before = ""
# rerun_with = []
---
*/

File references in run_before and rerun_with support flexible formats:

• Bare name: schema (stripped of prefix and extension)
• Filename: V000_schema.sql or V000_schema
• Relative path: test/V000_schema.sql, ./test/V000_schema.sql, /test/V000_schema.sql
• Version number: 000 (for versioned migrations)

Migration history is tracked in one of two modes:

• Comment mode (default) — stores JSON in COMMENT ON DATABASE, scoped by project_name. Zero database footprint.
• Table mode — stores history in a dedicated table per project.

Execute a SQL command via psql using the configured connection.

pgdev exec "SELECT version()"

Open an interactive psql session using the configured connection.

Update pgdev to the latest published version.

Run custom NpgsqlRest commands defined in pgdev.toml under [npgsqlrest.commands]. For example, if your config has:

[npgsqlrest.commands]
dev = "./config/production.json --optional ./config/development.json"

Then pgdev dev will launch NpgsqlRest with those arguments.

pgdev uses two TOML config files in your project root:

Layering: defaults → pgdev.toml → pgdev.local.toml

Running pgdev for the first time creates a pgdev.toml with all available options. The full structure:

# Path to .env file for resolving {ENV_VAR} placeholders in config values
env_file = ""

# Show detailed output during tool detection and updates
verbose = true

# Tool paths — bare command name uses PATH, or set a full path
[tools]
npgsqlrest = "npgsqlrest"
psql = "psql"
pg_dump = "pg_dump"
pg_restore = "pg_restore"

# NpgsqlRest run commands — value is the config file args passed to npgsqlrest CLI
# Example: dev = "./config/production.json --optional ./config/development.json"
[npgsqlrest.commands]
dev = ""
validate = ""
serve = ""
validate-prod = ""

# SQL commands used by pgdev
[commands]
schemas_query = "select nspname::text from pg_namespace ..."

# Database connection for pgdev tools (psql, pg_dump, pg_restore)
# Values support {ENV_VAR} placeholders resolved via env_file above
# To share connection with NpgsqlRest instead, set config_file to a JSON config path
[connection]
host = "{PGHOST}"
port = "{PGPORT}"
database = "{PGDATABASE}"
username = "{PGUSER}"
password = "{PGPASSWORD}"
# config_file = "./config/production.json"
# connection_name = "Default"

# Project directories and settings
[project]
project_dir = ""
project_name = ""
tests_dir = ""
schemas = []
grants = false
ignore_body_whitespace = false
routine_types = ["FUNCTION", "PROCEDURE"]
api_dir = ""
internal_dir = ""
group_segment = 0
skip_prefixes = []
group_order = []
sync_skip = []
up_prefix = "V"
repeatable_prefix = "R"
separator = "__"
history_mode = "comment"
history_schema = "pgdev"
history_table = ""

# SQL formatting options (applied during sync)
[format]
lowercase = true
param_style = "multiline"
indent = "    "
simplify_defaults = true
omit_default_direction = true
attribute_style = "multiline"
strip_dump_comments = true
comment_signature_style = "types_only"
drop_before_create = true
create_or_replace = false

Paths to external tools. Use a bare command name to use PATH, or set a full path.

Custom NpgsqlRest run commands. Each key becomes a pgdev <name> subcommand. The value is the config file arguments passed to the NpgsqlRest CLI.

SQL commands used internally by pgdev.

Database connection for pgdev tools. Two modes:

Independent connection — set host, port, database, username, password directly:

Shared connection — read connection string from a NpgsqlRest JSON config file:

Project directory and sync/diff/migration settings.

Directory grouping dimensions:

• type — Split by API vs internal (uses api_dir/internal_dir names). Routines with HTTP comments go to api_dir, others to internal_dir.
• schema — Split by PostgreSQL schema name (uses the schema name as directory).
• name — Split by name segment extracted from the routine's snake_case name (e.g. get_user_data with group_segment=1 → user/).
• kind — Split by object type (uses lowercase type name: function/, procedure/, view/).

Example: group_order = ["type", "schema", "kind"] produces paths like api/myschema/function/my_func.sql.

SQL formatting options applied when extracting routines during sync.

MIT