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-devTo 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"]
}
]