postgres-fill

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