Simple PostgreSQL database schema validation and creation.
#Overview
An alternative to using sql files to ensure a specific schema
Takes a yaml file as input and does a drop validate create in that order, depending on command line flags.
#Installation
TODO: NPM
#Usage
Usage: pg_validate [options]
Options:
-h, --help output usage information
-V, --version output the version number
-f, --file [file name] schema definition file
-h, --host [host name] postgresql host name or ip
-u, --user [user name] postgresql user name
-p, --password [user password] postgresql password
-z, --validate validate schema
-d, --drop drop schema tables
-c, --create create schema
#Configuration files
Configuration files should be made one per database, consider the simple.yaml example:
---
#####
#An example db specification
#####
db_name : testdb
tables :
neighbors :
columns :
id : increments
name : string
address :
type : string
unique : true
spouse_name :
type : string
defaultTo : No Spouse
primary : id
unique : name
associates :
columns :
id : increments
name : string
address :
type : string
unique : true
company_name : text
primary : id
unique : [name, company_name]
While relatively straightforward, pg-validator is a simple wrapper around the Knex module. Database columns are either simple a string, denoting the type, or an object.
Columns that are defined as objects are required to have a type. Other parameters must follow the Knex schema definition functions for columns. In case the knex function does not have any parameters, the boolean value true should be used. This is demonstrated in the example above with the column spouse_name.
All table properties other than the reserved keyword columns map to table commands. An array denotes multiple calls, while nested arrays will translate to an array being passed to the knex which is chained.
For example, to define multiple unique columns:
unique : [col1, col2, col3]
Compounded:
unique : [[col1, col2]]
Can mix and match, each element of the array maps to one chained knex.table call
unique : [col1, [col2, col3]]
#License
MIT