@pgsql/scripts

@pgsql/scripts

Migration script generation for PostgreSQL: derive revert and verify scripts from the classified statement facts of a deploy script (classifyStatements in @pgsql/transform).

import { classifyStatements } from '@pgsql/transform';
import { revertFor, verifyFor } from '@pgsql/scripts';
import { loadModule } from 'plpgsql-parser';

await loadModule();
const facts = classifyStatements(deploySql);

const { sql: revertSql, warnings } = revertFor(facts);
const { sql: verifySql } = verifyFor(facts);

• revertFor(facts) — mechanical inverses in reverse topological order of the statement dependency graph, so dependents are dropped before their dependencies and no CASCADE is ever needed. Inverses are built as AST nodes and deparsed (pgsql-deparser), never string-templated.
• verifyFor(facts) — one existence check per created object, each raising on failure via SELECT 1/(CASE WHEN <exists> THEN 1 ELSE 0 END);.

Nothing outside the supported vocabulary is ever guessed at: revertFor emits a -- revert not derivable: <reason> comment plus a warning; verifyFor emits nothing plus a warning. The list is exported as SUPPORTED_STATEMENTS (and SUPPORTED_NODE_TAGS).

Node-level API

For consumers that compose inverses at the AST level (semantic diffing, migration generation) without round-tripping through deparsed text:

import { invertStatement, existenceCheck } from '@pgsql/scripts';

const inverse = invertStatement(facts[0]);  // AST statement nodes, [] = nothing to revert, null = not derivable
const checks = existenceCheck(facts[0]);    // SelectStmt check nodes, [] = nothing to check, null = not derivable

invertStatement returns the per-statement inverse as wrapped AST nodes (e.g. { DropStmt: {...} }); existenceCheck returns the raise-on-failure checks as SelectStmt nodes. Both return null instead of guessing when derivation is not possible — including partially underivable multi-command statements.

Supported statements

Statement	Revert	Verify
CREATE SCHEMA	DROP SCHEMA	information_schema.schemata
CREATE TABLE	DROP TABLE	to_regclass
CREATE VIEW	DROP VIEW	to_regclass
CREATE INDEX	DROP INDEX	to_regclass
CREATE SEQUENCE	DROP SEQUENCE	to_regclass
CREATE TYPE (composite, enum, range)	DROP TYPE	to_regtype
CREATE DOMAIN	DROP DOMAIN	to_regtype
CREATE FUNCTION / PROCEDURE	DROP FUNCTION / PROCEDURE with input signature (overload safe)	to_regprocedure
CREATE TRIGGER	DROP TRIGGER ... ON table	pg_trigger
CREATE POLICY	DROP POLICY ... ON table	pg_policies
CREATE EXTENSION	DROP EXTENSION	pg_extension
CREATE ROLE	DROP ROLE	pg_roles
ALTER TABLE ... ADD COLUMN	DROP COLUMN	information_schema.columns
ALTER TABLE ... ADD CONSTRAINT (named)	DROP CONSTRAINT	information_schema.table_constraints
ALTER TABLE ... ENABLE / FORCE ROW LEVEL SECURITY	DISABLE / NO FORCE	pg_class.relrowsecurity / relforcerowsecurity
GRANT privileges (tables, sequences, functions, schemas)	REVOKE same privileges	has_table_privilege / has_function_privilege / has_schema_privilege
GRANT role TO role	REVOKE role FROM role	pg_auth_members
COMMENT ON	COMMENT ON ... IS NULL	—
CREATE MATERIALIZED VIEW / CREATE TABLE AS	DROP MATERIALIZED VIEW / DROP TABLE	to_regclass
CREATE SERVER	DROP SERVER	pg_foreign_server
CREATE FOREIGN TABLE	DROP FOREIGN TABLE	to_regclass
CREATE USER MAPPING	DROP USER MAPPING	pg_user_mappings
CREATE COLLATION	DROP COLLATION	pg_collation
CREATE AGGREGATE	DROP AGGREGATE with input signature	to_regprocedure
CREATE OPERATOR (binary)	DROP OPERATOR (left, right)	to_regoperator
CREATE CAST	DROP CAST (source AS target)	pg_cast
CREATE PUBLICATION	DROP PUBLICATION	pg_publication
CREATE SUBSCRIPTION	DROP SUBSCRIPTION	pg_subscription
CREATE STATISTICS	DROP STATISTICS	pg_statistic_ext
CREATE EVENT TRIGGER	DROP EVENT TRIGGER	pg_event_trigger
CREATE RULE	DROP RULE ... ON table	pg_rules
ALTER TYPE ... ADD VALUE	— (Postgres has no DROP VALUE; warns)	pg_enum
ALTER TABLE ... ATTACH PARTITION	DETACH PARTITION	pg_inherits
ALTER DEFAULT PRIVILEGES ... GRANT	ALTER DEFAULT PRIVILEGES ... REVOKE	pg_default_acl + aclexplode
SECURITY LABEL	SECURITY LABEL ... IS NULL	—
CREATE FOREIGN DATA WRAPPER	DROP FOREIGN DATA WRAPPER	pg_foreign_data_wrapper
CREATE CONVERSION	DROP CONVERSION	pg_conversion
CREATE ACCESS METHOD	DROP ACCESS METHOD	pg_am
CREATE TRANSFORM	DROP TRANSFORM FOR type LANGUAGE lang	pg_transform
CREATE OPERATOR CLASS / FAMILY	DROP ... USING am	pg_opclass / pg_opfamily
CREATE TEXT SEARCH CONFIGURATION / DICTIONARY / PARSER / TEMPLATE	matching DROP	pg_ts_config / pg_ts_dict / pg_ts_parser / pg_ts_template
CREATE TABLESPACE	DROP TABLESPACE	pg_tablespace
ALTER ... RENAME TO	rename back (both names are in the statement)	object exists under new name
ALTER ... SET SCHEMA (qualified source)	move back (both schemas are in the statement)	object exists in new schema
GRANT ALL	REVOKE ALL	expands to the object type's concrete privilege list

Not derivable (warned, never guessed): REVOKE, unnamed constraints, ALTER ... SET with unknown prior value, ALTER ... OWNER TO (prior owner unknown), SET SCHEMA on unqualified names, arbitrary DML, dynamic SQL, prefix operators.

🛠 Built by the Constructive team — creators of modular Postgres tooling for secure, composable backends. If you like our work, contribute on GitHub.

Related

• pgpm: A Postgres Package Manager that brings modular development to PostgreSQL with reusable packages, deterministic migrations, recursive dependency resolution, and tag-aware versioning.
• pgsql-test: Instant, isolated PostgreSQL databases for each test with automatic transaction rollbacks, context switching, and clean seeding for fast, reliable database testing.
• pgsql-seed: PostgreSQL seeding utilities for CSV, JSON, SQL data loading, and pgpm deployment.
• pgsql-parser: The real PostgreSQL parser for Node.js, providing symmetric parsing and deparsing of SQL statements with actual PostgreSQL parser integration.
• pgsql-deparser: A streamlined tool designed for converting PostgreSQL ASTs back into SQL queries, focusing solely on deparser functionality to complement pgsql-parser.
• @pgsql/parser: Multi-version PostgreSQL parser with dynamic version selection at runtime, supporting PostgreSQL 15, 16, and 17 in a single package.
• @pgsql/types: Offers TypeScript type definitions for PostgreSQL AST nodes, facilitating type-safe construction, analysis, and manipulation of ASTs.
• @pgsql/enums: Provides TypeScript enum definitions for PostgreSQL constants, enabling type-safe usage of PostgreSQL enums and constants in your applications.
• @pgsql/utils: A comprehensive utility library for PostgreSQL, offering type-safe AST node creation and enum value conversions, simplifying the construction and manipulation of PostgreSQL ASTs.
• @pgsql/traverse: PostgreSQL AST traversal utilities for pgsql-parser, providing a visitor pattern for traversing PostgreSQL Abstract Syntax Tree nodes, similar to Babel's traverse functionality but specifically designed for PostgreSQL AST structures.
• pg-proto-parser: A TypeScript tool that parses PostgreSQL Protocol Buffers definitions to generate TypeScript interfaces, utility functions, and JSON mappings for enums.
• libpg-query: The real PostgreSQL parser exposed for Node.js, used primarily in pgsql-parser for parsing and deparsing SQL queries.

Disclaimer

AS DESCRIBED IN THE LICENSES, THE SOFTWARE IS PROVIDED "AS IS", AT YOUR OWN RISK, AND WITHOUT WARRANTIES OF ANY KIND.

No developer or entity involved in creating Software will be liable for any claims or damages whatsoever associated with your use, inability to use, or your interaction with other users of the Software code or Software CLI, including any direct, indirect, incidental, special, exemplary, punitive or consequential damages, or loss of profits, cryptocurrencies, tokens, or anything else of value.