npm install orm
- MySQL
- PostgreSQL
- Amazon Redshift
- SQLite
- Create Models, sync, drop, bulk create, get, find, remove, count, aggregated functions
- Create Model associations, find, check, create and remove
- Define custom validations (several builtin validations, check instance properties before saving)
- Model instance caching and integrity (table rows fetched twice are the same object, changes to one change all)
- Plugins: MySQL FTS , Pagination
This is a node.js object relational mapping module.
An example:
var orm = require("orm");
orm.connect("mysql:https://username:password@host/database", function (err, db) {
if (err) throw err;
var Person = db.define("person", {
name : String,
surname : String,
age : Number,
male : Boolean,
continent : [ "Europe", "America", "Asia", "Africa", "Australia", "Antartica" ], // ENUM type
photo : Buffer, // BLOB/BINARY
data : Object // JSON encoded
}, {
methods: {
fullName: function () {
return this.name + ' ' + this.surname;
}
},
validations: {
age: orm.validators.rangeNumber(18, undefined, "under-age")
}
});
Person.find({ surname: "Doe" }, function (err, people) {
// SQL: "SELECT * FROM person WHERE surname = 'Doe'"
console.log("People found: %d", people.length);
console.log("First person: %s, age %d", people[0].fullName(), people[0].age);
people[0].age = 16;
people[0].save(function (err) {
// err.msg = "under-age";
});
});
});
If you're using Express, you might want to use the simple middleware to integrate more easily.
var express = require('express');
var orm = require('orm');
var app = express();
app.use(orm.express("mysql:https://username:password@host/database", {
define: function (db, models) {
models.person = db.define("person", { ... });
}
}));
app.listen(80);
app.get("/", function (req, res) {
// req.models is a reference to models used above in define()
req.models.person.find(...);
});
You can call orm.express
more than once to have multiple database connections. Models defined across connections
will be joined together in req.models
. Don't forget to use it before app.use(app.router)
, preferably right after your
assets public folder(s).
Settings are used to store key value pairs. A settings object is stored on the global orm object and on each database connection.
var orm = require("orm");
orm.settings.set("some.deep.value", 123);
orm.connect("....", function (err, db) {
// db.settings is a snapshot of the settings at the moment
// of orm.connect(). changes to it don't affect orm.settings
console.log(db.settings.get("some.deep.value")); // 123
console.log(db.settings.get("some.deep")); // { value: 123 }
});
First, add the correct driver to your package.json
:
driver | dependency |
---|---|
mysql | "mysql" : "2.0.0-alpha7" |
postgres redshift |
"pg": "~1.0.0", |
sqlite | "sqlite3" : "2.1.5" |
You can pass in connection options either as a string:
var orm = require("orm");
orm.connect("mysql:https://username:password@host/database?pool=true", function (err, db) {
// ...
});
Note: pool
is only supported by mysql & postgres.
Or as an object:
var opts = {
database : "dbname",
protocol : "[mysql|postgres|redshift|sqlite]",
host : "127.0.0.1",
port : 3306, // optional, defaults to database default
user : "..",
password : "..",
query : {
pool : true|false, // optional, false by default
debug : true|false // optional, false by default
}
};
orm.connect(opts, function (err, db) {
// ...
});
You can also avoid passing a callback and just listen for the connect event:
var orm = require("orm");
var db = orm.connect("mysql:https://username:password@host/database");
db.on("connect", function (err, db) {
// ...
});
A Model is an abstraction over one or more database tables. Models support associations (more below). The name of the model is assumed to match the table name.
Models support behaviours for accessing and manipulating table data.
Call define
on the database connection to setup a model. The name of the table and model is used as an identifier for the model on the database connection, so you can easily access the model later using the connection.
var Person = db.define('person', { // 'person' will be the table in the database as well as the model id
// properties
name : String, // you can use native objects to define the property type
surname : { type: "text", size: 50 } // or you can be specific and define aditional options
}, {
// options (optional)
});
Native | String | Native | String |
---|---|---|---|
String | 'text' | Date | 'date ' |
Number | 'number' | Object | 'object' |
Boolean | 'boolean' | Buffer | 'binary' |
| | --- | 'enum'
required
: true marks the column asNOT NULL
, false (default)defaultValue
: sets the default value for the field
size
: max length of the string
rational
: true (default) creates a FLOAT/REAL, false an INTEGER
time
: true (default) creates a DATETIME/TIMESTAMP, false a DATE
Note that these may vary accross drivers.
Are passed in during model definition.
var Person = db.define('person', {
name : String,
surname : String
}, {
methods: {
fullName: function () {
return this.name + ' ' + this.surname;
}
}
});
Person.get(4, function(err, person) {
console.log( person.fullName() );
})
Are defined directly on the model.
var Person = db.define('person', {
name : String,
height : { type: 'number', rational: false }
});
Person.tallerThan = function(height, callback) {
this.find({ height: orm.gt(height) }, callback);
};
Person.tallerThan( 192, function(err, tallPeople) { ... } );
Models can be in separate modules. Simply ensure that the module holding the models uses module.exports to publish a function that accepts the database connection, then load your models however you like.
Note - using this technique you can have cascading loads.
// your main file (after connecting)
db.load("./models", function (err) {
// loaded!
var Person = db.models.person;
var Pet = db.models.pet;
});
// models.js
module.exports = function (db, cb) {
db.load("./models-extra", function (err) {
if (err) {
return cb(err);
}
db.define('person', {
name : String
});
return cb();
});
};
// models-extra.js
module.exports = function (db, cb) {
db.define('pet', {
name : String
});
return cb();
};
Models can create their underlying tables in the database. You may call Model.sync() on each Model to create the underlying table or you can call db.sync() at a connection level to create all tables for all models.
// db.sync() can also be used
Person.sync(function (err) {
!err && console.log("done!");
});
If you want to drop a Model and remove all tables you can use the .drop()
method.
Person.drop(function (err) {
!err && console.log("person model no longer exists!");
});
ORM2 allows you some advanced tweaks on your Model definitions. You can configure these via settings or in the call to define
when you setup the Model.
For example, each Model instance has a unique ID in the database. This table column is by default "id" but you can change it.
var Person = db.define("person", {
name : String
}, {
id : "person_id"
});
// or just do it globally..
db.settings.set("properties.primary_key", "UID");
// ..and then define your Models
var Pet = db.define("pet", {
name : String
});
Pet model will have 2 columns, an UID
and a name
.
Other options:
cache
: (default:true
) Set it tofalse
to disable Instance cache (Singletons) or set a timeout value (in seconds);autoSave
: (default:false
) Set it totrue
to save an Instance right after changing any property;autoFetch
: (default:false
) Set it totrue
to fetch associations when fetching an instance from the database;autoFetchLimit
: (default:1
) IfautoFetch
is enabled this defines how many hoops (associations of associations) you want it to automatically fetch.
If you want to listen for a type of event than occurs in instances of a Model, you can attach a function that will be called when that event happens.
Currently the following events are supported:
afterLoad
: (no parameters) Right after loading and preparing an instance to be used;beforeSave
: (no parameters) Right before trying to save;afterSave
: (bool success) Right after saving;beforeCreate
: (no parameters) Right before trying to save a new instance;afterCreate
: (bool success) Right after saving a new instance;beforeRemove
: (no parameters) Right before trying to remove an instance.afterRemove
: (bool success) Right after removing an instance;
All hook function are called with this
as the instance so you can access anything you want related to it.
To get a specific element from the database use Model.get
.
Person.get(123, function (err, person) {
// finds person with id = 123
});
Finding one or more elements has more options, each one can be given in no specific parameter order. Only options
has to be after conditions
(even if it's an empty object).
Person.find({ name: "John", surname: "Doe" }, 3, function (err, people) {
// finds people with name='John' AND surname='Doe' and returns the first 3
});
If you need to sort the results because you're limiting or just because you want them sorted do:
Person.find({ surname: "Doe" }, "name", function (err, people) {
// finds people with surname='Doe' and returns sorted by name ascending
});
Person.find({ surname: "Doe" }, [ "name", "Z" ], function (err, people) {
// finds people with surname='Doe' and returns sorted by name descending
// ('Z' means DESC; 'A' means ASC - default)
});
There are more options that you can pass to find something. These options are passed in a second object:
Person.find({ surname: "Doe" }, { offset: 2 }, function (err, people) {
// finds people with surname='Doe', skips the first 2 and returns the others
});
If you just want to count the number of items that match a condition you can just use .count()
instead of finding all
of them and counting. This will actually tell the database server to do a count (it won't be done in the node process itself).
Person.count({ surname: "Doe" }, function (err, count) {
console.log("We have %d Does in our db", count);
});
Similar to .count()
, this method just checks if the count is greater than zero or not.
Person.exists({ surname: "Doe" }, function (err, exists) {
console.log("We %s Does in our db", exists ? "have" : "don't have");
});
If you need to get some aggregated values from a Model, you can use Model.aggregate()
. Here's an example to better
illustrate:
Person.aggregate({ surname: "Doe" }).min("age").max("age").get(function (err, min, max) {
console.log("The youngest Doe guy has %d years, while the oldest is %d", min, max);
});
Here's an example to illustrate how to use groupby:
//The same as "select avg(weight), age from person where country='someCountry' group by age;"
Person.aggregate(["age"], { country: "someCountry" }).avg("weight").groupBy("age").get(function (err, stats) {
// stats is an Array, each item should have 'age' and 'avg_weight'
});
Possible aggregating functions:
min
max
avg
sum
count
(there's a shortcut to this -Model.count
)
offset
: discards the firstN
elementslimit
: although it can be passed as a direct argument, you can use it here if you preferonly
: if you don't want all properties, you can give an array with the list of properties you want
If you prefer less complicated syntax you can chain .find()
by not giving a callback parameter.
Person.find({ surname: "Doe" }).limit(3).offset(2).only("name", "surname").run(function (err, people) {
// finds people with surname='Doe', skips first 2 and limits to 3 elements,
// returning only 'name' and 'surname' properties
});
You can also chain and just get the count in the end. In this case, offset, limit and order are ignored.
Person.find({ surname: "Doe" }).count(function (err, people) {
// people = number of people with surname="Doe"
});
Also available is the option to remove the selected items.
Person.find({ surname: "Doe" }).remove(function (err) {
// Does gone..
});
You can also make modifications to your instances using common Array traversal methods and save everything in the end.
Person.find({ surname: "Doe" }).each(function (person) {
person.surname = "Dean";
}).save(function (err) {
// done!
});
Person.find({ surname: "Doe" }).each().filter(function (person) {
return person.age >= 18;
}).sort(function (person1, person2) {
return person1.age < person2.age;
}).get(function (people) {
// get all people with at least 18 years, sorted by age
});
Of course you could do this directly on .find()
, but for some more complicated tasks this can be very usefull.
Model.find()
does not return an Array so you can't just chain directly. To start chaining you have to call
.each()
(with an optional callback if you want to traverse the list). You can then use the common functions
.filter()
, .sort()
and .forEach()
more than once.
In the end (or during the process..) you can call:
.count()
if you just want to know how many items there are;.get()
to retrieve the list;.save()
to save all item changes.
Conditions are defined as an object where every key is a property (table column). All keys are supposed
to be concatenated by the logical AND
. Values are considered to match exactly, unless you're passing
an Array
. In this case it is considered a list to compare the property with.
{ col1: 123, col2: "foo" } // `col1` = 123 AND `col2` = 'foo'
{ col1: [ 1, 3, 5 ] } // `col1` IN (1, 3, 5)
If you need other comparisons, you have to use a special object created by some helper functions. Here are a few examples to describe it:
{ col1: orm.eq(123) } // `col1` = 123 (default)
{ col1: orm.ne(123) } // `col1` <> 123
{ col1: orm.gt(123) } // `col1` > 123
{ col1: orm.gte(123) } // `col1` >= 123
{ col1: orm.lt(123) } // `col1` < 123
{ col1: orm.lte(123) } // `col1` <= 123
{ col1: orm.between(123, 456) } // `col1` BETWEEN 123 AND 456
{ col1: orm.like(12 + "%") } // `col1` like '12%'
Model instances are cached. If multiple different queries will result in the same result, you will get the same object. If you have other systems that can change your database (or you're developing and need to make some manual changes) you should remove this feature by disabling cache. This can be done when you're defining the Model.
var Person = db.define('person', {
name : String
}, {
cache : false
});
and also globally:
orm.connect('...', function(err, db) {
db.settings.set('instance.cache', false);
});
The cache can be configured to expire after a period of time by passing in a number instead of a boolean. The number will be considered the cache timeout in seconds (you can use floating point).
Note: One exception about Caching is that it won't be used if an instance is not saved. For example, if you fetch a Person and then change it, while it doesn't get saved it won't be passed from Cache.
To insert new elements to the database use Model.create
.
Person.create([
{
name: "John",
surname: "Doe",
age: 25,
male: true
},
{
name: "Liza",
surname: "Kollan",
age: 19,
male: false
}
], function (err, items) {
// err - description of the error or null
// items - array of inserted items
});
Every item returned has the properties that were defined to the Model and also a couple of methods you can use to change each item.
Person.get(1, function (err, John) {
John.name = "Joe";
John.surname = "Doe";
John.save(function (err) {
console.log("saved!");
});
});
Updating and then saving an instance can be done in a single call:
Person.get(1, function (err, John) {
John.save({ name: "Joe", surname: "Doe" }, function (err) {
console.log("saved!");
});
});
If you want to remove an instance, just do:
// you could do this without even fetching it, look at Chaining section above
Person.get(1, function (err, John) {
John.remove(function (err) {
console.log("removed!");
});
});
You can define validations for every property of a Model. You can have one or more validations for each property. You can also use the predefined validations or create your own.
var Person = db.define("person", {
name : String,
age : Number
}, {
validations : {
name : orm.validators.rangeLength(1, undefined, "missing"), // "missing" is a name given to this validation, instead of default
age : [ orm.validators.rangeNumber(0, 10), orm.validators.insideList([ 1, 3, 5, 7, 9 ]) ]
}
});
The code above defines that the name
length must be between 1 and undefined (undefined means any) and age
must be a number between 0 and 10 (inclusive) but also one of the listed values. The example might not make sense
but you get the point.
When saving an item, if it fails to validate any of the defined validations you'll get an error
object with the property
name and validation error description. This description should help you identify what happened.
var John = new Person({
name : "",
age : 20
});
John.save(function (err) {
// err.field = "name" , err.value = "" , err.msg = "missing"
});
The validation stops after the first validation error. If you want it to validate every property and return all validation errors, you can change this behavior on global or local settings:
var orm = require("orm");
orm.settings.set("instance.returnAllErrors", true); // global or..
orm.connect("....", function (err, db) {
db.settings.set("instance.returnAllErrors", true); // .. local
// ...
var John = new Person({
name : "",
age : 15
});
John.save(function (err) {
assert(Array.isArray(err));
// err[0].field = "name" , err[0].value = "" , err[0].msg = "missing"
// err[1].field = "age" , err[1].value = 15 , err[1].msg = "out-of-range-number"
// err[2].field = "age" , err[2].value = 15 , err[2].msg = "outside-list"
});
});
An association is a relation between one or more tables.
Is a many to one relationship. It's the same as belongs to.
Eg: Animal.hasOne('owner', Person)
.
Animal can only have one owner, but Person can have many animals.
Animal will have the owner_id
property automatically added.
The following functions will become available:
animal.getOwner(function..) // Gets owner
animal.setOwner(person, function..) // Sets owner_id
animal.hasOwner(function..) // Checks if owner exists
animal.removeOwner() // Sets owner_id to 0
Reverse access
Animal.hasOne('owner', Person, {reverse: 'pets'})
will add the following:
person.getPets(function..)
person.setPets(cat, function..)
Is a many to many relationship (includes join table).
Eg: Patient.hasMany('doctors', Doctor, { why: String }, { reverse: 'patients' })
.
Patient can have many different doctors. Each doctor can have many different patients.
This will create a join table patient_doctors
when you call Patient.sync()
:
column name | type |
---|---|
patient_id | Integer |
doctor_id | Integer |
why | varchar(255) |
The following functions will be available:
patient.getDoctors(function..) // List of doctors
patient.addDoctors(docs, function...) // Adds entries to join table
patient.setDoctors(docs, function...) // Removes existing entries in join table, adds new ones
patient.hasDoctors(docs, function...) // Checks if patient is associated to specified doctors
patient.removeDoctors(docs, function...) // Removes specified doctors from join table
doctor.getPatients(function..)
etc...
To associate a doctor to a patient:
patient.addDoctor(surgeon, {why: "remove appendix"}, function(err) { ... } )
which will add {patient_id: 4, doctor_id: 6, why: "remove appendix"}
to the join table.
If you have a relation of 1 to n, you should use hasOne
(belongs to) association.
var Person = db.define('person', {
name : String
});
var Animal = db.define('animal', {
name : String
});
Animal.hasOne("owner", Person); // creates column 'owner_id' in 'animal' table
// get animal with id = 123
Animal.get(123, function (err, animal) {
// animal is the animal model instance, if found
Foo.getOwner(function (err, person) {
// if animal has really an owner, person points to it
});
});
You can mark the owner_id
field as required in the database by specifying the required
option:
Animal.hasOne("owner", Person, { required: true });
If you prefer to use another name for the field (owner_id) you can change this parameter in the settings.
db.settings.set("properties.association_key", "id_{name}"); // {name} will be replaced by 'owner' in this case
Note: This has to be done before the association is specified.
The hasMany
associations can have additional properties in the association table.
var Person = db.define('person', {
name : String
});
Person.hasMany("friends", {
rate : Number
});
Person.get(123, function (err, John) {
John.getFriends(function (err, friends) {
// assumes rate is another column on table person_friends
// you can access it by going to friends[N].extra.rate
});
});
If you prefer you can activate autoFetch
.
This way associations are automatically fetched when you get or find instances of a model.
var Person = db.define('person', {
name : String
});
Person.hasMany("friends", {
rate : Number
}, {
autoFetch : true
});
Person.get(123, function (err, John) {
// no need to do John.getFriends() , John already has John.friends Array
});
You can also define this option globally instead of a per association basis.
var Person = db.define('person', {
name : String
}, {
autoFetch : true
});
Person.hasMany("friends", {
rate : Number
});
Associations can make calls to the associated Model by using the reverse
option. For example, if you have an
association from ModelA to ModelB, you can create an accessor in ModelB to get instances from ModelA.
Confusing? Look at the next example.
var Pet = db.define('pet', {
name : String
});
var Person = db.define('person', {
name : String
});
Pet.hasOne("owner", Person, {
reverse : "pets"
});
Person(4).getPets(function (err, pets) {
// although the association was made on Pet,
// Person will have an accessor (getPets)
//
// In this example, ORM will fetch all pets
// whose owner_id = 4
});
This makes even more sense when having hasMany
associations since you can manage the many to many
associations from both sides.
var Pet = db.define('pet', {
name : String
});
var Person = db.define('person', {
name : String
});
Person.hasMany("pets", Pet, {
bought : Date
}, {
reverse : "owners"
});
Person(1).getPets(...);
Pet(2).getOwners(...);