Skip to content

scripts

Directory actions

More options

Directory actions

More options

Latest commit

 

History

History

scripts

Folders and files

NameName
Last commit message
Last commit date

parent directory

..
__tests__
__tests__
 
 
src
src
 
 
CHANGELOG.md
CHANGELOG.md
 
 
README.md
README.md
 
 
jest.config.js
jest.config.js
 
 
package.json
package.json
 
 
tsconfig.esm.json
tsconfig.esm.json
 
 
tsconfig.json
tsconfig.json
 
 

README.md

@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.