Onion Relational Mapping

OnionRM is an ORM for node.js targeting Postgres. OnionRM owes it's existence to node-orm2 from which OnionRM forked.

Only Postgres?

Aren't ORMs supposed to be database agnostic?

Many, and perhaps most, are. But, at the end of the day you've chosen Postgres for a reason. Postgres has many awesome features, and shouldn't your application make use of them?

Features like JSON, Arrays are natively supported.

Getting Started

To get started, initialize a connection to Postgres;

var orm = require('onionrm');

orm.connect("postgres://user@localhost/database", function(err, db){
	//define models

	//perform database operations here

	db.close() //close connection
};

Models

Like all other ORMs, OnionRM lets your define models. Models can be defined after connecting to Postgres and acquiring a database object;

var Customer = db.define("customers", {
	created_at      : Date,
	updated_at      : Date,
	username        : String,
	password_hash   : String,
	name            : String
});

//class level methods can be defined like this;
Customer.withUsernameAndPassword = function(username, password, callback){
	var hash = calculateAHash(password);
	var filter = {
		username: username,
		password_hash: hash
	};
	this.find(filter, callback);
};

var Order = db.define("orders", {
	created_at      : Date,
	updated_at      : Date,
	number          : String,
	status_code     : Number,
	customer_id     : String
}, {
	methods: {
		//define instance methods here
		statusDescription: function () {
			switch(this.status_code){
					case 1:
						return "Processing";
					case 2:
						return "Shipped";
					case 3:
						return "Cancelled";
					default:
						return "Pending";
			}
		}
	}
});

Order.hasOne "customer", Customer

Creating Models

To create/save an instance of a method, pass a hash into the create method;

var details = {
	number: "123-abc",
	customer_id: "435cb549-8d10-4137-8e60-67c6f6204c7e"
};

Order.create(details, function(err, order){
	//other tasks
});

Upon successful persistance of the created instance, a model instance will be returned.

Updating Models

With a reference to an OnionRM model, you can then perform an update. To update an model, mutate it's state, and then call save;

order.status_code = 2;
order.save(function(err){

});

Models expose an isDirty method for checking if anything has actually changed since the last time it was saved.

order.isDirty(); //false
order.status_code = 2;
order.isDirty(); //true (assuming status_code previously was not 2, false otherwise)
order.save(function(err){

});

Deleting Models

Deletes can be performed in a similar fashion. Simply call remove on an OnionRM model instance;

order.remove(function(err){

});

Chain Find

With the basics of how to create, update, or delete models out of the way, let's look at how to fetch existing models.

Fetching data starts with ChainFind, a chainable querying builder. The simplest query is find;

Order.find({number: "456-zyx"}, function(err, orders){

};

The above is similar to writing the following SQL:

select id, created_at, updated_at, number, status_code, customer_id
from orders
where number='456-zyx';

Note: Any SQL shown in this document is simply for illustration purposes. The actual SQL generated by OnionRM is slightly different, and can even vary based upon which ChainFind operators are performed (e.g. to help resolve ambiguous columns, etc).

The first parameters to find is a hash of properties to "filter" by. As illustrated above they're simple equalities, as as we'll see later many more operators are available.

Note: It's worth pointing out that if only one item is expected, the one method can be used instead. It behaves exactly like find, and is chainable, but only returns the first matching record.

Additional keys/values in the filter hash are ANDed together. So, this;

Order.find({customer_id: "435cb549-8d10-4137-8e60-67c6f6204c7e", status_code: 3}, function(err, orders){

});

Is similar to;

select id, created_at, updated_at, number, status_code, customer_id
from orders
where customer_id='435cb549-8d10-4137-8e60-67c6f6204c7e'
	and status_code=3;

Since this is a chainable query builder, we don't have to stop here. Omitting the callback parameter to find allows for almost limitless queries. Since all* chainable actions are valid for find as well as one, for the remainder of this section we'll just used the variable chain to represent either. Similar to;

var chain = Order.find();
//or
var chain = Order.one();

Limit/Offset

When working against large datasets, it's useful to paginate through records;

Order.find().limit(100).offset(1000).run(function(err, orders){

});

select id, created_at, updated_at, number, status_code, customer_id
from orders
limit 100
offset 1000;

Of course, offset is required to use limit. If you only cared about the first 500 orders in a particular status, you could;

Order.find({status_code: 1}).limit(500).fun(function(err, orders){

});

select id, created_at, updated_at, number, status_code, customer_id
from orders
where status_code=1
limit 500;

Order

Continuing with the above example, let's say you wanted to find the 500 most recent orders in a status.

Order.find({status_code: 1}).limit(500).order("-created_at").run(function(err, orders){

});

select id, created_at, updated_at, number, status_code, customer_id
from orders
where status_code=1
order by created_at desc
limit 500;

As in you can see in the above example, the order of limit and order is irrelevant. The generated SQL will be syntactically correct, since the SQL isn't generated (or ran) until the fun function is called.

To find the 500 oldest orders in a specific status, remove the negative sign in front of the sort property;

Order.find({status_code: 1}).limit(500).order("created_at").run(function(err, orders){

});

select id, created_at, updated_at, number, status_code, customer_id
from orders
where status_code=1
order by created_at
limit 500;

Join/With

Filtering on the values of a specific model can only get you so far, however many times we want to find items based on their relationship to other items. This is where join and with come in.

By itself, join simply joins one model to another;

LineItem.find({product_code: 'ZZZ123'}).join("orders", "id", "order_id").run(function(err, lineItems){

});

select --properties in line item model
from line_items
join orders on order_id=orders.id
where line_items.product_code = 'ZZZ123';

To specify filters on a joined model, use with;

LineItem.find({product_code: 'ZZZ123'}).join("orders", "id", "order_id").with({status_code: 3}).run(function(err, lineItems){

});

select --properties in line item model
from line_items
join orders on order_id=orders.id
where line_items.product_code = 'ZZZ123'
	and orders.status_code = 3;

The with operator is slightly different from other ChainFind methods in that it's applied only to the immediately preceding join. This permits multiple models to be joined, without ambiguity of model property names. For example;

LineItem.find({product_code: 'ZZZ123'}).join("orders", "id", "order_id").with({created_at: orm.gt(thisYear)}).join("customers", "id", "customer_id").with({created_at: orm.lt(thisYear)}).run(function(err, lineItems){

});

select --properties in line item model
from line_items
join orders on order_id=orders.id
join customers on customer_id=customers.id
where line_items.product_code = 'ZZZ123'
	and orders.created_at > '2015-01-01T00:00:00Z'
	and customers.created_at < '2015-01-01T00:00:00Z';

The orm.gt and orm.lt operators will be discussed in detail in the section below.

Only/Omit

By default OnionRM retrieves all (and only) the properties specified in a model's definition when querying Postgres. In the example "order" model, all our SQL has explicitly selected specific fields;

select id, created_at, updated_at, number, status_code, customer_id
from orders
--etc

This allows for database columns to exist, that aren't mapped into a model (and thusly aren't fetched). But, it also allows for fields to be blacklisted/whitelisted on demand using only and omit.

Perhaps you're exposing orders via an API, and you'd rather not expose status_code. You could blacklist it using omit;

Order.find(filter).omit("status_code").run//....

Alternatively you could whitelist which properties you do want expose using only;

Order.find(filter).only(["id", "created_at", "number", "customer_id"]).run//....

As JSON

All the examples so far have executed the ChainFind by invoking run. The run method instantiates models, and returns an array of OnionRM models.

In scenarios where you're just fetching models to turn right around and serialize them to JSON, it might make sense to have Postgres directly handle the serialization for you;

Order.find().join("customers", "customer_id", "id").with({name: "Fred"}).omit("status_code").limit(100).asJson(function(err, json){

});

select array_to_json(array_agg(t))::text as json from (
	select
		orders.id,
		orders.created_at,
		orders.updated_at,
		orders.number, orders.
		customer_id
	from orders
	join customers on orders.customer_id=customers.id
	where customers.name='Fred'
	limit 100
) t;

The asJson method can be used anywhere run would have been used, and it's second parameters (called json in the above example) is string containing JSON - useful for directly handing off to Express (or other).

Comparators

So far we've seen filters that query on equality. However, many more operators are available.

Not Equals

var filter = {
	status_code: orm.ne(1)
};
Order.find(filter, function(err, orders){

});

Is similar to;

select id, created_at, updated_at, number, status_code, customer_id
from orders
where status_code <> 1;

To keep the remainder of these comparators concise, only the relevant section of code will be illustrated.

Greater Than

created_at: orm.gt(aDate)

created_at > '2015-01-01T06:00:00Z'

Greater Than Or Equal

created_at: orm.gte(aDate)

where created_at >= '2015-01-01T06:00:00Z'

Less Than

created_at: orm.lt(aDate)

where created_at < '2015-01-01T06:00:00Z'

Less Than Or Equal

created_at: orm.lte(aDate)

where created_at <= '2015-01-01T06:00:00Z'

In

status_code: [1, 2, 3]

where status_code in (1, 2, 3)

Not In

status_code: orm.not_in([1, 2, 3])

where status_code not in (1, 2, 3)

Between

created_at: orm.between(aDate, anotherDate)

where created_at between '2015-01-01T06:00:00Z' and '2015-02-01T06:00:00Z'

Any

The ANY operator compares a literal value for equality to any element in an array.

favorite_foods: orm.any('cookies')

where favorite_foods =ANY('cookies')

Modulo

age: orm.mod(4)

where age % 4

Model Reconstitution

You can reconstitute an OnionRM model by calling the reconstitute class method with a hash of the instance's properties;

Customer.reconstitute({name: "Sue"}, function(err, model){

});

It's like soup!

Anything Else?

Sure, there are tons of awesome features OnionRM inherited from node-orm2. However, unless it's outlined in this readme, it's subject to removal as existing code is refactored and Postgres specific functionality is added.

Get in and explore! If there's anything super awesome, submit a pull request, even if it's just to document said feature.

:squirrel: