postgres-fill
Simple Node.js utility to help maintaining compatibility between Postgres databases.
This little program helps you to maintain the compatibility between Postgres databases by comparing their structure with one specified in a JSON file: if it finds a table, column or constraint (primary key, foreign key, unique and index) that is not described in the JSON file, it gets added to the database.
This program does not perform alterations or deletions - it will only "expand" the databases, in order to avoid conflicts with current versions.
Obs: conflict with 'unique' constraints may occur, if they are specified in the JSON structure but the tables are holding duplicate values.
To install it, use npm:
$ npm install postgres-fill --save-dev
To run it, include the module and pass 4 arguments (the last one is optional):
require('postgres-fill')(log, connectionOptions, structure, verbose);
// log = Function that takes a string as a parameter - you can pass a log stream here
// connectionOptions = described below
// structure = described below
// verbose = defaults to 'false'. 'true' will print very detailed messages, while 'false' will only print messages
// informing about modifications in the database
connectionOptions
// Describes how to connect to the database host: string // Host user: string // Your username password: string // Your password database: string // Name of the database port: number // Recommended: 5432
Example:
"host": "192.168.0.1" "user": "root" "password": "password" "database": "myDatabase" "port": 5432
structure
// Describes the structure of the database // Represents a table name: string // Name for the table description?: string // Description (optional, not used for queries) disabled?: boolean // If true, the table will be ignored columns: // List of columns name: string // Name of the column type: string // Type (use the same name as in Postgres: int, varchar, etc) isNull?: boolean // Is the column nullable? disabled?: boolean // If true, the column will be ignored size?: number // Size of the data type (like varchar(20)) primaryKey?: boolean // Is this field a primary key? defaultValue?: any // Default value foreignKeys?: // Foreign keys in this table column: string // Name of the local column referenceTable: string // Name of the table to be referenced referenceColumn: string // Name of the column to be referenced (in the referenced table) onDelete?: string // ON DELETE action (setNull, setDefault, restrict, cascade, or noAction) onUpdate?: string // ON UPDATE actin (setNull, setDefault, restrict, cascade, or noAction) unique?: string // List of unique columns (by name) index?: string // List of index columns (by name)
!!! Note: The 'defaultValue' field will take your input literally, so if your default value is a string, wrap it around single quotes.
Example:
"name": "User" "description": "This table has the users" "disabled": false "columns": "name": "id" "type": "serial" "isNull": false "primaryKey": true "name": "name" "type": "varchar" "size": 30 "isNull": true "name": "userType" "type": "varchar" "size": 30 "defaultValue": "'regular'" "unique": "name" "index": "id" "name": "Company" "description": "This table has the companies" "disabled": false "columns": "name": "id" "type": "serial" "isNull": false "primaryKey": true "name": "name" "type": "varchar" "size": 30 "isNull": true "name": "owner" "type": "int" "isNull": false "name": "dateCreated" "type": "timestamp" "defaultValue": "now()" "foreignKeys": "column": "owner" "referenceTable": "User" "referenceColumn": "id" "onDelete": "restrict" "onUpdate": "noAction" "unique": "name" "index": "id" "name"