PostgreSQL dedicated ORM for node.js with automatic schema sync, table inheritance and much more.
This package is designed to make easy the process of applying changes to database after model definition changes, more than offer a quick and easy database access interface. Applying changes to database after releasing a new version of application is often a frustrating problem, usually solved with migration systems. Applying changes to database during development stage, often results in a complex sequence of backward and forward steps through migrations; this process is complicated more and more especially when working in team with concurrent changes to the models (or database schema). This package tries to solve these problems all in once.
- node-postgres-orm
- Installation
- Quick Start Example
- Error reporting
- Requirements
- Testing
- Bugs
- Documentation
- Changelog
With npm:
$ npm install --save pgoRunning following script:
var Pgo = require("../lib/pgo");
var db = new Pgo(process.env.PGO_TEST_DB);
db.model("foo", {
bar: db.INT4,
baz: {
type: db.JSON,
defaultValue: { a: 42, b: ["c", {}] },
},
});
db.model("bar", {
baz: { type: db.INT4, notNull: true },
foo: db.FKEY("foo"),
});
db.connect(console.log, function () {
var foo = new db.models.foo();
foo.save(console.log, function () {
console.log("foo saved");
db.load.foo({ id: 1 }, console.log, function (res) {
if (!res.length) return console.log("no records found");
console.log(res[0]);
db.end();
});
});
});we will obtain following output:
Pgo: CREATE SEQUENCE foos_id_seq
Pgo: CREATE TABLE foos ()
Pgo: ALTER TABLE foos ADD COLUMN id int8
Pgo: ALTER TABLE foos ALTER COLUMN id SET DEFAULT nextval('foos_id_seq'::regclass)
Pgo: UPDATE foos SET id = nextval('foos_id_seq'::regclass) WHERE id IS NULL
Pgo: ALTER TABLE foos ALTER COLUMN id SET NOT NULL
Pgo: ALTER TABLE foos ADD COLUMN bar int4
Pgo: ALTER TABLE foos ADD COLUMN baz json
Pgo: ALTER TABLE foos ALTER COLUMN baz SET DEFAULT '{"a":42,"b":["c",{}]}'::json
Pgo: UPDATE foos SET baz = '{"a":42,"b":["c",{}]}'::json WHERE baz IS NULL
Pgo: ALTER TABLE foos ALTER COLUMN baz SET NOT NULL
Pgo: ALTER TABLE foos ADD CONSTRAINT foo_id_unique UNIQUE(id)
Pgo: CREATE SEQUENCE bars_id_seq
Pgo: CREATE TABLE bars ()
Pgo: ALTER TABLE bars ADD COLUMN id int8
Pgo: ALTER TABLE bars ALTER COLUMN id SET DEFAULT nextval('bars_id_seq'::regclass)
Pgo: UPDATE bars SET id = nextval('bars_id_seq'::regclass) WHERE id IS NULL
Pgo: ALTER TABLE bars ALTER COLUMN id SET NOT NULL
Pgo: ALTER TABLE bars ADD COLUMN baz int4
Pgo: ALTER TABLE bars ALTER COLUMN baz SET NOT NULL
Pgo: ALTER TABLE bars ADD COLUMN foo int8
Pgo: ALTER TABLE bars ALTER COLUMN foo SET NOT NULL
Pgo: ALTER TABLE bars ADD CONSTRAINT bar_id_unique UNIQUE(id)
Pgo: ALTER TABLE bars ADD CONSTRAINT bar_foo_fkey FOREIGN KEY (foo) REFERENCES foos (id)
Pgo: INSERT INTO foos DEFAULT VALUES :: []
foo saved
Pgo: SELECT tableoid, * FROM foos WHERE id = $1 :: [1]
foo { id: '1', bar: null, baz: { a: 42, b: [ 'c', {} ] } }
Changeing the script as follows and running it:
var Pgo = require("../lib/pgo");
var db = new Pgo(process.env.PGO_TEST_DB);
db.model("foo", {
bar: db.INT4,
baz: {
type: db.JSON,
defaultValue: { a: 42, b: ["c", {}] },
},
});
db.model("bar", {
baz: db.INT4,
foo: db.VARCHAR(20),
});
db.connect(console.log, function () {
var foo = new db.models.foo();
foo.save(console.log, function () {
console.log("foo saved");
db.load.foo({ id: 1 }, console.log, function (res) {
if (!res.length) return console.log("no records found");
console.log(res[0]);
db.end();
});
});
});the diff is:
db.model('bar', {
- baz: { type: db.INT4, notNull: true },
- foo: db.FKEY('foo')
+ baz: db.INT4,
+ foo: db.VARCHAR(20)
});
we will obtain following output:
Pgo: ALTER TABLE bars DROP CONSTRAINT bar_foo_fkey
Pgo: ALTER TABLE bars ALTER COLUMN baz DROP NOT NULL
Pgo: ALTER TABLE bars ALTER COLUMN foo TYPE varchar(20)
Pgo: ALTER TABLE bars ALTER COLUMN foo DROP NOT NULL
Pgo: INSERT INTO foos DEFAULT VALUES :: []
foo saved
Pgo: SELECT tableoid, * FROM foos WHERE id = $1 :: [1]
foo { id: '1', bar: null, baz: { a: 42, b: [ 'c', {} ] } }
Pgo functions and methods have syncornous usage error reporting. Exceptions are thrown in case of wrong parameters number or types. Anyway it should not be required to call pgo functions in a try catch block, this kind of errors should be generated only at development time.
All pgo methods and function accessing data have asyncronous error reporting to check data integrity or consistency errors, database connection errors, etc...
Pgo implements the double done design pattern.
- Node.js 4.0 or higher.
- PostgreSQL 9.3 or higher.
To test this package is strongly required the acces to a PosgtreSQL database. The connection string should be specified in the evironment variable PGO_TEST_DB.
$ PGO_TEST_DB="postgres://user:password@host/database" npm test
Database timezone must be UTC.
Pgo is tested under a wide version matrix of Node.js and PostgreSQL.
Do not hesitate to report any bug or inconsistency @github.
Model inheritance is not respected in models of cloned Pgo.
var Pgo = require("pgo");
var db1 = new Pgo("postgres://username:password@localhost/database");
db1.model("foo", { a: db.INT4 });
db1.model("bar", { b: db.INT4 }, { parent: "foo" });
db1.connect(console.log, function () {
var db2 = db1.clone(console.log);
var foo1 = new db1.models.foo();
var bar1 = new db1.models.bar();
var foo2 = new db2.models.foo();
var bar2 = new db2.models.bar();
bar1 instanceof db1.models.foo; // true
bar2 instanceof db2.models.foo; // false; the effect of this bug
bar2 instanceof db1.models.foo; // true; this can be used as a workaround
});Documentation can be found at documentation index. Pay attention: this documentation needs to be completely reviewed, it has an old style and may be incomplete. Do not hesitate to report @github anything not correct, incomplete or not working as described.
- 2020-04-28 - v0.2.3
- Dependencies, Node.js & PostgreSQL versions review
- 2017-05-24 - v0.2.1
- README review
- 2017-01-22 - v0.2.0
- Added double done