Introduction
Objection.js is a Node.js ORM built around the wonderful SQL query builder knex. All databases
supported by knex are supported by objection.js. SQLite3, Postgres and MySQL are thoroughly tested.
What objection.js gives you:
What objection.js doesn't give you:
- A custom query DSL. SQL is used as a query language.
- Automatic database schema creation and migration.
It is useful for the simple things, but usually just gets in your way when doing anything non-trivial.
Objection.js leaves the schema related things to you. knex has a great migration tool
that we recommend for this job.
Objection.js uses Promises and coding practices that make it ready for the future. You can already use things like ES7
async/await and ES6 classes using a transpiler such as
Babel. Check out our ES7 example project.
Topics
Installation
npm install knex objection
You also need to install one of the following depending on the database you want to use:
npm install pg
npm install sqlite3
npm install mysql
npm install mysql2
npm install mariasql
Getting started
To use objection.js all you need to do is initialize knex and give the
connection to objection.js using Model.knex(knex)
:
var Knex = require('knex');
var Model = require('objection').Model;
var knex = Knex({
client: 'postgres',
connection: {
host: '127.0.0.1',
database: 'your_database'
}
});
Model.knex(knex);
The next step is to create some migrations and models and start using objection.js. The best way to get started is to
use the example project:
git clone git@github.com:Vincit/objection.js.git objection
cd objection/examples/express
npm install
npm install knex -g
knex migrate:latest
npm start
The express
example project is a simple express server. The example-requests.sh
file contains a bunch of curl
commands for you to start playing with the REST API.
cat example-requests.sh
We also have an ES7 version of the express example project. It uses Babel for the ES7 --> ES5
transpiling.
git clone git@github.com:Vincit/objection.js.git objection
cd objection/examples/express-es7
npm install
npm install knex -g
knex migrate:latest
npm start
Also check out our API documentation and recipe book.
Query examples
The Person
model used in the examples is defined here.
All queries are started with one of the Model methods query(),
$query() or $relatedQuery().
All these methods return a QueryBuilder instance that can be used just like
a knex QueryBuilder.
Insert a person to the database:
Person
.query()
.insert({firstName: 'Jennifer', lastName: 'Lawrence'})
.then(function (jennifer) {
console.log(jennifer instanceof Person);
console.log(jennifer.firstName);
console.log(jennifer.fullName());
})
.catch(function (err) {
console.log('oh noes');
});
insert into "Person" ("firstName", "lastName") values ('Jennifer', 'Lawrence')
Fetch all persons from the database:
Person
.query()
.then(function (persons) {
console.log(persons[0] instanceof Person);
console.log('there are', persons.length, 'Persons in total');
})
.catch(function (err) {
console.log('oh noes');
});
select * from "Person"
The return value of the .query()
method is an instance of QueryBuilder
that has all the methods a knex QueryBuilder has. Here is a simple example that uses some of them:
Person
.query()
.where('age', '>', 40)
.andWhere('age', '<', 60)
.andWhere('firstName', 'Jennifer')
.orderBy('lastName')
.then(function (middleAgedJennifers) {
console.log('The last name of the first middle aged Jennifer is');
console.log(middleAgedJennifers[0].lastName);
});
select * from "Person"
where "age" > 40
and "age" < 60
and "firstName" = 'Jennifer'
order by "lastName" asc
The next example shows how easy it is to build complex queries:
Person
.query()
.select('Person.*', 'Parent.firstName as parentFirstName')
.join('Person as Parent', 'Person.parentId', 'Parent.id')
.where('Person.age', '<', Person.query().avg('Person.age'))
.whereExists(Animal.query().select(1).whereRef('Person.id', 'Animal.ownerId'))
.orderBy('Person.lastName')
.then(function (persons) {
console.log(persons[0].parentFirstName);
});
select "Person".*, "Parent"."firstName" as "parentFirstName"
from "Person"
inner join "Person" as "Parent" on "Person"."parentId" = "Parent"."id"
where "Person"."age" < (select avg("Person"."age") from "Person")
and exists (select 1 from "Animal" where "Person"."id" = "Animal"."ownerId")
order by "Person"."lastName" asc
Update models:
Person
.query()
.patch({lastName: 'Dinosaur'})
.where('age', '>', 60)
.then(function (patch) {
console.log('all persons over 60 years old are now dinosaurs');
console.log(patch.lastName);
})
.catch(function (err) {
console.log(err.stack);
});
update "Person" set "lastName" = 'Dinosaur' where "age" > 60
While the static .query()
method can be used to create a query to a whole table .$relatedQuery()
method
can be used to query a single relation. .$relatedQuery()
returns an instance of QueryBuilder
just like the .query()
method.
var jennifer;
Person
.query()
.where('firstName', 'Jennifer')
.first()
.then(function (person) {
jennifer = person;
return jennifer
.$relatedQuery('pets')
.where('species', 'dog')
.orderBy('name');
})
.then(function (jennifersDogs) {
console.log(jennifersDogs[0] instanceof Animal);
console.log(jennifer.pets === jennifersDogs);
console.log('Jennifer has', jennifersDogs.length, 'dogs');
})
.catch(function (err) {
console.log(err.stack);
});
select * from "Person" where "firstName" = 'Jennifer'
select * from "Animal"
where "species" = 'dog'
and "Animal"."ownerId" = 1
order by "name" asc
Insert a related model:
Person
.query()
.where('id', 1)
.first()
.then(function (person) {
return person.$relatedQuery('pets').insert({name: 'Fluffy'});
})
.then(function (fluffy) {
console.log(fluffy.id);
})
.catch(function (err) {
console.log('something went wrong with finding the person OR inserting the pet');
console.log(err.stack);
});
select * from "Person" where "id" = 1
insert into "Animal" ("name", "ownerId") values ('Fluffy', 1)
Eager queries
Okay I said there is no custom DSL but actually we have teeny-tiny one for fetching relations eagerly, as it isn't
something that can be done easily using SQL. The following examples demonstrate how to use it:
Fetch one relation:
Person
.query()
.eager('pets')
.then(function (persons) {
console.log(persons[0].pets[0].name);
console.log(persons[0].pets[0] instanceof Animal);
});
Fetch multiple relations on multiple levels:
Person
.query()
.eager('[pets, children.[pets, children]]')
.then(function (persons) {
console.log(persons[0].pets[0].name);
console.log(persons[1].children[2].pets[1].name);
console.log(persons[1].children[2].children[0].name);
});
Fetch one relation recursively:
Person
.query()
.eager('[pets, children.^]')
.then(function (persons) {
console.log(persons[0].children[0].children[0].children[0].children[0].firstName);
});
The expressions can be arbitrarily deep. See the full description here.
Because the eager expressions are strings they can be easily passed for example as a query parameter of an HTTP
request. However, using such expressions opens the whole database through the API. This is not very secure. Therefore
the QueryBuilder has the .allowEager
method.
allowEager can be used to limit the allowed eager expression to a certain subset. Like this:
expressApp.get('/persons', function (req, res, next) {
Person
.query()
.allowEager('[pets, children.pets]')
.eager(req.query.eager)
.then(function (persons) { res.send(persons); })
.catch(next);
});
The example above allows req.query.eager
to be one of:
'pets'
'children'
'children.pets'
'[pets, children]'
'[pets, children.pets]'
.
Examples of failing eager expressions are:
'movies'
'children.children'
'[pets, children.children]'
'notEvenAnExistingRelation'
.
In addition to the .eager
method, relations can be fetched using the loadRelated
and $loadRelated
methods of
Model.
Transactions
Transactions are started by calling the objection.transaction
function. Give all the models you want to use in the transaction as parameters to the transaction
function. The model
classes are bound to a newly created transaction and passed to the callback function. Inside this callback, all queries
started through them take part in the same transaction.
The transaction is committed if the returned Promise is resolved successfully. If the returned Promise is rejected
the transaction is rolled back.
objection.transaction(Person, Animal, function (Person, Animal) {
return Person
.query()
.insert({firstName: 'Jennifer', lastName: 'Lawrence'})
.then(function () {
return Animal
.query()
.insert({name: 'Scrappy'});
});
}).then(function (scrappy) {
console.log('Jennifer and Scrappy were successfully inserted');
}).catch(function (err) {
console.log('Something went wrong. Neither Jennifer nor Scrappy were inserted');
});
You only need to give the transaction
function the model classes you use explicitly. All the related model classes
are implicitly bound to the same transaction.
objection.transaction(Person, function (Person) {
return Person
.query()
.insert({firstName: 'Jennifer', lastName: 'Lawrence'})
.then(function (jennifer) {
return jennifer
.$relatedQuery('pets')
.insert({name: 'Scrappy'});
});
}).then(function (scrappy) {
console.log('Jennifer and Scrappy were successfully inserted');
}).catch(function (err) {
console.log('Something went wrong. Neither Jennifer nor Scrappy were inserted');
});
The only way you can mess up with the transactions is if you explicitly start a query using a model class that is not
bound to the transaction:
var Person = require('./models/Person');
var Animal = require('./models/Animal');
objection.transaction(Person, function (Person) {
return Person
.query()
.insert({firstName: 'Jennifer', lastName: 'Lawrence'})
.then(function (jennifer) {
return Animal
.query()
.insert({name: 'Scrappy'});
});
});
Documents
Objection.js makes it easy to store non-flat documents as table rows. All properties of a model that are marked as
objects or arrays in the model's jsonSchema
are automatically converted to JSON strings in the database and
back to objects when read from the database. The database columns for the object properties can be normal
text columns. Postgresql has the json
and jsonb
data types that can be used instead for better performance
and possibility to make queries to the documents.
The address
property of the Person model is defined as an object in the Person.jsonSchema:
Person
.query()
.insert({
firstName: 'Jennifer',
lastName: 'Lawrence',
age: 24,
address: {
street: 'Somestreet 10',
zipCode: '123456',
city: 'Tampere'
}
})
.then(function (jennifer) {
console.log(jennifer.address.city);
return Person.query().where('id', jennifer.id);
})
.then(function (jenniferFromDb) {
console.log(jenniferFromDb.address.city);
})
.catch(function (err) {
console.log('oh noes');
});
Validation
JSON schema validation can be enabled by setting the jsonSchema property
of a model class. The validation is ran each time a Model
instance is created. For example all these will trigger
the validation:
Person.fromJson({firstName: 'jennifer', lastName: 'Lawrence'});
Person.query().insert({firstName: 'jennifer', lastName: 'Lawrence'});
Person.query().update({firstName: 'jennifer', lastName: 'Lawrence'}).where('id', 10);
Person.query().patch({age: 24}).where('age', '<', 24);
You rarely need to call $validate method explicitly, but you
can do it when needed. If validation fails a ValidationError
will be thrown. Since we use Promises, this usually means that a promise will be rejected with an instance of
ValidationError
.
Person.query().insert({firstName: 'jennifer'}).catch(function (err) {
console.log(err instanceof objection.ValidationError);
console.log(err.data);
});
See the recipe book for instructions
if you want to use some other validation library.
Models
Models are created by inheriting from the Model base class.
In objection.js the inheritance is done as transparently as possible. There is no custom Class abstraction making you
wonder what the hell is happening. Just plain old ugly javascript inheritance.
Minimal model
A working model with minimal amount of code:
var Model = require('objection').Model;
function MinimalModel() {
Model.apply(this, arguments);
}
Model.extend(MinimalModel);
MinimalModel.tableName = 'SomeTableName';
module.exports = MinimalModel;
A model with custom methods, json schema validation and relations
This is the model used in the examples:
var Model = require('objection').Model;
function Person() {
Model.apply(this, arguments);
}
Model.extend(Person);
module.exports = Person;
Person.prototype.fullName = function () {
return this.firstName + ' ' + this.lastName;
};
Person.tableName = 'Person';
Person.jsonSchema = {
type: 'object',
required: ['firstName', 'lastName'],
properties: {
id: {type: 'integer'},
parentId: {type: ['integer', 'null']},
firstName: {type: 'string', minLength: 1, maxLength: 255},
lastName: {type: 'string', minLength: 1, maxLength: 255},
age: {type: 'number'},
address: {
type: 'object',
properties: {
street: {type: 'string'},
city: {type: 'string'},
zipCode: {type: 'string'}
}
}
}
};
Person.relationMappings = {
pets: {
relation: Model.OneToManyRelation,
modelClass: __dirname + '/Animal',
join: {
from: 'Person.id',
to: 'Animal.ownerId'
}
},
movies: {
relation: Model.ManyToManyRelation,
modelClass: __dirname + '/Movie',
join: {
from: 'Person.id',
through: {
from: 'Person_Movie.personId',
to: 'Person_Movie.movieId'
},
to: 'Movie.id'
}
},
parent: {
relation: Model.OneToOneRelation,
modelClass: Person,
join: {
from: 'Person.parentId',
to: 'Person.id'
}
},
children: {
relation: Model.OneToManyRelation,
modelClass: Person,
join: {
from: 'Person.id',
to: 'Person.parentId'
}
}
};
Testing
To run the tests, all you need to do is configure the databases and run npm test
. Check out
this file for the
test database configurations. If you don't want to run the tests against all databases you can
just comment out configurations from the testDatabaseConfigs
list.
Changelog
0.2.3
- Promise constructor is now exposed through
require('objection').Promise
.
0.2.2
- $beforeUpdate, $afterUpdate, $beforeInsert etc. are now asynchronous and you can return promises from them.
- Added
Model.fn()
shortcut to knex.fn
. - Added missing
asCallback
and nodeify
methods for QueryBuilder
.
0.2.1
What's new
- bugfix: Chaining
insert
with returning
now returns all listed columns.
0.2.0
What's new
- New name
objection.js
. $beforeInsert
, $afterInsert
, $beforeUpdate
and $afterUpdate
hooks for Model
.- Postgres jsonb query methods:
whereJsonEquals
, whereJsonSupersetOf
, whereJsonSubsetOf
and friends. whereRef
query method.- Expose
knex.raw()
through Model.raw()
. - Expose
knex.client.formatter()
through Model.formatter()
. QueryBuilder
can be used to make sub queries just like knex's QueryBuilder
.- Possibility to use a custom
QueryBuilder
subclass by overriding Model.QueryBuilder
. - Filter queries/objects for relations.
- A pile of bug fixes.
Breaking changes
- Project was renamed to objection.js. Migrate simply by replacing
moron
with objection
.
0.1.0
First release.