node-pg-procedures v1.0.0

node-pg-procedures

Declare procedures once and have them created for you in both Postgres and Javascript

Example Usage

I am using CSON (cursive script) for more convenient multi-line strings, but you could just use JSON if you prefer.

const CSON = require('cursive')
const schema = CSON.parse(fs.readFileSync('./db-schema.cson')) //See below
const pg = require('pg')
const pgproc = require('node-pg-procedures')
const db = new pg.Pool(cfg.db)
db.ready = pgproc(schema,winston).auto(db).catch(e=>{console.error(e);process.exit(1)})
// ^ Auto adds methods to db
// , starts running the associated SQL to create these procedures
// , and returns a promise
db.ready
.then(()=>db.getPeopleByLastName({last_name:"Smith"})
.then(results => console.log(results) )

Alternate usage:

//Or, use the SQL and methods manually:
const db = new pg.Pool(cfg.db)
const pgproc = pgproc(schema,winston)
console.log(pgproc)
//{
//  methods: {name:function(...){...}, ... },
//  sql: ["...",...]
//}
//Note that the methods will call this.query, so they should somehow
//get bound to or inherit from your database object

Example db-schema.cson

[
{sql:
		|CREATE TABLE IF NOT EXISTS people(
		|	id SERIAL PRIMARY KEY,
		|	first_name TEXT,
		|	last_name TEXT
		|)
	}
{procedure: "getPeopleByLastName"
		parameters: [
				{name:"last_name", type:"varchar(100)"}
			]
		sql:
			|RETURNS TABLE (id INT,name TEXT)
			|AS $$ BEGIN
			|	RETURN QUERY SELECT
			|		id,
			|		first_name || ' ' || last_name
			|	FROM people
			|	WHERE last_name = arg_last_name
			|	LIMIT 10;
			|END $$ LANGUAGE plpgsql;
	}
]

Contributing

Clearly, I know very little about structuring a package for others' use, so feel free to suggest all those conventions...