@wmfs/pg-diff-sync

pg-diff-sync

[

Takes two objects that describe the structure of a database and produces the PostgreSQL statements required to get from one to the other.

Install

$ npm install pg-diff-sync --save

Usage

  const pgDiffSync = require('pg-diff-sync')
  const currentDbStructure = {...} 
  const expectedDbStructure = {...} 

  const statements = pgDiffSync(
    currentDbStructure,
    expectedDbStructure
  )  
  // Returns an array of DDL statements to apply on the PostgreSQL database

API

pgDiffSync(baseDbStructure, targetDbStructure)

Arguments:

Arg	Type	Notes
baseDbStructure	object	An object representing the original starting position of a database - most likely the output conjured from the pg-info package
targetDbStructure	object	And an object representing how the database needs to be - in the same form as baseDbStructure (again see the pg-info package for more details)

Output

The output from pg-diff-sync is a simple array of strings.

• Each string is a separate DDL statement, that should be run on the PostgreSQL database that produced the baseDbStructure object.
• With some caveats, running these statements on the base database will turn it into the target database

Example output

[
  "CREATE SCHEMA pg_diff_sync_test;",
  "COMMENT ON SCHEMA pg_diff_sync_test IS 'Schema auto-generated by Relationize.js!';",
  "CREATE TABLE pg_diff_sync_test.people();",
  "COMMENT ON TABLE pg_diff_sync_test.people IS 'Just a simple list of people!';",
  "ALTER TABLE pg_diff_sync_test.people ADD COLUMN employee_no text NOT NULL;",
  "ALTER TABLE pg_diff_sync_test.people ADD COLUMN first_name text NOT NULL;",
  "ALTER TABLE pg_diff_sync_test.people ADD COLUMN last_name text NOT NULL;",
  "ALTER TABLE pg_diff_sync_test.people ADD COLUMN age integer;",
  "ALTER TABLE pg_diff_sync_test.people ADD COLUMN _created timestamp with time zone NOT NULL DEFAULT now();",
  "ALTER TABLE pg_diff_sync_test.people ADD COLUMN _created_by text;",
  "ALTER TABLE pg_diff_sync_test.people ADD COLUMN _modified timestamp with time zone NOT NULL DEFAULT now();",
  "ALTER TABLE pg_diff_sync_test.people ADD COLUMN _modified_by text;",
  "COMMENT ON COLUMN pg_diff_sync_test.people.first_name IS 'Person''s first name';",
  "COMMENT ON COLUMN pg_diff_sync_test.people.age IS 'Age in years';",
  "COMMENT ON COLUMN pg_diff_sync_test.people._created IS 'Timestamp for when this record was created';",
  "COMMENT ON COLUMN pg_diff_sync_test.people._created_by IS 'UserID that created this record (if known)';",
  "COMMENT ON COLUMN pg_diff_sync_test.people._modified IS 'Timestamp for when this record was last updated';",
  "COMMENT ON COLUMN pg_diff_sync_test.people._modified_by IS 'UserID that last modified this record (if known)';",
  "ALTER TABLE pg_diff_sync_test.people ADD PRIMARY KEY (employee_no);",
  "CREATE UNIQUE INDEX people_first_name_last_name_idx ON pg_diff_sync_test.people (first_name,last_name);"
]

Testing

$ npm test

License

MIT

Changelog

1.27.0 (2022-11-02)

🛠 Builds

• deps-dev: update dependency mocha to v10.1.0 (c88abc6)
• deps-dev: update dependency semantic-release to v19.0.3 (4138b9c)
• deps-dev: update dependency semantic-release to v19.0.5 (e03c8c0)
• deps: update dependency ejs to v3.1.8 (7d19732)