Based on featurist's Sworm ORM library
A very lightweight write only Node.js ORM, with support for:
- Microsoft SQL Server (MSSQL)
- PostgreSQL
- MySQL
npm install sworm
Then install a database driver, one of:
npm install mssql
npm install pg
npm install mysql
See sworm in NPM.
The features in this module are mostly for writing graphs of related entities. Querying, on the other hand, is done with raw SQL so you can do it fast. See the query API for details.
This ORM avoids some of the largest issues experienced in other ORMs:
- query performance is too opaque
- N+1 queries are frequently the default
- configuring eager and lazy loading is tricky
- one-to-many, many-to-one, many-to-many relationships are notoriously difficult to get right
- lifecycle management of sessions and identity maps is rarely pleasant
- check out the massive generated SQL statements!
Just write SQL, you know how.
var person = db.model({table: 'people'});
var address = db.model({table: 'addresses'});
var bob = person({
name: 'bob',
address: address({
address: 'Fremantle'
})
});
bob.save()
Produces:
-------- people ----------
| id | name | address_id |
--------------------------
| 11 | bob | 22 |
--------------------------
------- addresses ------
| id | address |
------------------------
| 22 | Fremantle |
------------------------
Connect:
var sworm = require('sworm');
sworm.db({
driver: 'mssql',
config: {
user: 'user',
password: 'password',
server: 'localhost',
database: 'databasename'
}
}).then(function (db) {
var person = db.model({table: 'people'});
...
});
Or define models then connect:
var sworm = require('sworm');
var db = sworm.db();
var person = db.model({table: 'people'});
db.connect({
driver: 'mssql',
config: {
user: 'user',
password: 'password',
server: 'localhost',
database: 'databasename'
}
}).then(function () {
...
});
Connection options:
-
driver
, one of'mssql'
,'mysql'
or'pg'
. -
config
connection options passed to the database driver of choice. See configuration options for: SQL Server, MySQL, PostgreSQL. -
url
a connection URL passed to the postgres driver. See thepg
url format. -
log
: eithertrue
to log SQL statements withconsole.log()
Can also be a function for custom logging:
function (sql, params) { // sql == 'select * from people where name = @name' // params == {name: 'bob'} }
Defaults to
false
, no logging.
Close the connection after use:
db.close()
var createEntity = db.model(options);
options
can contain the following:
-
table
(undefined
) the name of the table to save entities to -
id
('id'
) the name of the identity column. This can be an array of id columns for compound keys. -
foreignKeyFor
a function that returns a foreign key field name for a member (see Relationships), defaults to:function foreignKeyFor(fieldName) { return fieldName + '_id'; }
createEntity
is a function that can be used to create entities from the model.
Any other properties or functions on the options
object are accessible by entities.
var address = db.model({
table: 'addresses',
function: addPerson(person) {
this.people = this.people || [];
person.address = this;
this.people.push(person);
}
});
var fremantle = address({address: 'Fremantle'});
fremantle.addPerson(person({name: 'bob'}));
The entity constructor takes an object with fields to be saved to the database.
var person = db.model({...});
var bob = person({
name: 'bob'
}, [options]);
Where options can have:
saved
: iftrue
willupdate
the entity (if modified) on the nextsave()
, iffalse
willinsert
the entity on the nextsave()
. Defaultfalse
.modified
: iftrue
(and ifsaved
is true), willupdate
the entity on the nextsave()
regardless if it has been modified.
var promise = entity.save([options]);
Inserts or updates the entity into the table. If the entity already has a value for its identity column, then it is updated, if not, it is inserted.
Objects know when they've been modified since their last insert or update, so they won't update unless a field is modified. You can force an update by passing {force: true}
.
save()
returns a promise.
entity.identity()
Returns the ID of the entity, based on the identity column specified in the model.
entity.changed()
Returns true if the object has been modified since the last save()
.
Entities can contain fields that are other entities. This way you can build up graphs of entities and save them all in one go.
When entity A contains a field that is entity B, then B will be saved first and B's ID will be set and saved with A.
The foreign key of the member will be saved on the field name member_id
. So address
will have a foreign key of address_id
. See the foreignKeyFor
option in Models.
var person = db.model({table: 'people'});
var address = db.model({table: 'addresses'});
var bob = person({
name: 'bob',
address: address({
address: "15 Rue d'Essert"
})
});
bob.save().then(function () {
assert(bob.address_id == address.id);
});
In SQL:
-------- people ----------
| id | name | address_id |
--------------------------
| 11 | bob | 22 |
--------------------------
------- addresses ------
| id | address |
------------------------
| 22 | 15 Rue d'Essert |
------------------------
When entity A contains a field that is an array that contains entities B and C. Then entity A will be saved first, followed by all entities B and C.
This allows entities B and C to refer to entity A, as they would in their tables.
var person = db.model({ table: 'people' });
var address = db.model({ table: 'addresses' });
var bob = person({name: 'bob'});
var jane = person({name: 'jane'});
var essert = address({
address: "15 Rue d'Essert",
people: [bob, jane]
});
bob.address = essert;
jane.address = essert;
essert.save().then(function () {
// all objects saved.
});
Alternatively, we can return the people in the address using a function. When the address is saved, the people
function will be called with the owner address as this
, then we can set the foreign key for the people.
var person = db.model({ table: 'people' });
var address = db.model({ table: 'addresses' });
var essert = address({
address: "15 Rue d'Essert",
people: function() {
return [
person({ name: 'bob', address: this }),
person({ name: 'jane', address: this })
];
}
});
essert.save().then(function () {
// all objects saved.
});
Notice that whether we use an array or a function, the field itself is never saved to the database, only the entities inside the array.
In SQL:
-------- people ----------
| id | name | address_id |
--------------------------
| 11 | bob | 22 |
| 12 | jane | 22 |
--------------------------
------- addresses ------
| id | address |
------------------------
| 22 | 15 Rue d'Essert |
------------------------
Many-to-many is just a combination of one-to-many and many-to-one:
var person = db.model({ table: 'people' });
var personAddress = db.model({ table: 'people_addresses', id: ['address_id', 'person_id'] });
var address = db.model({ table: 'addresses' });
function personLivesInAddress(person, address) {
pa = personAddress({person: person, address: address});
person.addresses = person.addresses || [];
person.addresses.push(pa);
address.people = address.people || [];
address.people.push(pa);
}
var bob = person({name: 'bob'});
var jane = person({name: 'jane'});
var fremantle = address({
address: "Fremantle"
});
var essert = address({
address: "15 Rue d'Essert"
});
personLivesInAddress(bob, fremantle);
personLivesInAddress(jane, fremantle);
personLivesInAddress(jane, essert);
Promise.all([essert.save(), fremantle.save()]);
In SQL:
-- people ---
| id | name |
-------------
| 11 | bob |
| 12 | jane |
-------------
------- addresses ------
| id | address |
------------------------
| 22 | 15 Rue d'Essert |
| 23 | Fremantle |
------------------------
---- people_addresses ----
| address_id | person_id |
--------------------------
| 22 | 12 |
| 23 | 12 |
| 23 | 11 |
--------------------------
var records = db.query(sql, [parameters]);
Where:
sql
is the SQL query, can be a query (i.e. select), or a command (update, insert, stored proc)parameters
. Ifsql
contains parameters in the form of@paramName
the corresponding property on theparameters
object will be substituted into the SQL, doing any escaping necessary.
For select queries, returns an array of objects, containing the fields of each record.
var entities = model.query(sql, [parameters]);
Same as db.query()
but records returned are turned into entities of the model that can subsequently be modified and saved.