Awesome 社區模塊

Object Relational Mapping for fibjs

Build Status

Install

1
npm install fib-orm

Test

1
npm run ci

DBMS Support

  • MySQL & MariaDB
  • SQLite

Features

fib-orm add a set of synchronous version method on node-orm object.

Introduction

This is a fibjs object relational mapping module.

An example:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37
var orm = require("fib-orm"); var db = orm.connectSync("mysql://username:password@host/database"); var Person = db.define("person", { name : String, surname : String, age : Number, // FLOAT 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.enforce.ranges.number(18, undefined, "under-age") } }); // add the table to the database db.syncSync(); // add a row to the person table Person.createSync({ id: 1, name: "John", surname: "Doe", age: 27 }); // query the person table by surname var people = Person.findSync({ surname: "Doe" }); // 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].saveSync();

The node.js version like this:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49
var orm = require("orm"); orm.connect("mysql://username:password@host/database", function (err, db) { if (err) throw err; var Person = db.define("person", { name : String, surname : String, age : Number, // FLOAT 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.enforce.ranges.number(18, undefined, "under-age") } }); // add the table to the database db.sync(function(err) { if (err) throw err; // add a row to the person table Person.create({ id: 1, name: "John", surname: "Doe", age: 27 }, function(err) { if (err) throw err; // query the person table by surname Person.find({ surname: "Doe" }, function (err, people) { // SQL: "SELECT * FROM person WHERE surname = 'Doe'" if (err) throw err; 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"; }); }); }); }); });

Documentation

Fibjs did not add new functions, the development of documents can refer to node-orm, only need to change the asynchronous call to synchronous version.

Settings

See information in the

Connecting

See information in the

Models

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.

Defining Models

See information in the

Properties

See information in the

Instance Methods

Are passed in during model definition.

1 2 3 4 5 6 7 8 9 10 11 12 13
var Person = db.define('person', { name : String, surname : String }, { methods: { fullName: function () { return this.name + ' ' + this.surname; } } }); var person = Person.getSync(4); console.log( person.fullName() );

Model Methods

Are defined directly on the model.

1 2 3 4 5 6 7 8 9
var Person = db.define('person', { name : String, height : { type: 'integer' } }); Person.tallerThan = function(height) { return this.findSync({ height: orm.gt(height) }); }; var tallPeople = Person.tallerThan( 192);

Loading Models [NOT SUPPORT]

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.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
// your main file (after connecting) db.loadSync("./models"); // loaded! var Person = db.models.person; var Pet = db.models.pet; // models.js module.exports = function (db) { db.loadSync("./models-extra"); db.define('person', { name : String }); }; // models-extra.js module.exports = function (db) { db.define('pet', { name : String }); };

Synchronizing Models

See information in the

Dropping Models

See information in the

Advanced Options

ORM2 allows you some advanced tweaks on your Model definitions. You can configure these via settings or in the call todefine

For example, each Model instance has a unique ID in the database. This table column is added automatically, and called "id" by default.
key: true

1 2 3 4 5 6 7 8 9 10 11 12
var Person = db.define("person", { personId : { type: 'serial', key: true }, name : String }); // You can also change the default "id" property name globally: db.settings.set("properties.primary_key", "UID"); // ..and then define your Models var Pet = db.define("pet", { name : String });

PetUIDname

It's also possible to have composite keys:

1 2 3 4
var Person = db.define("person", { firstname : { type: 'text', key: true }, lastname : { type: 'text', key: true } });

Other options:

  • identityCache: (default:falsetrue
  • autoSave: (default:falsetrue
  • autoFetch: (default:falsetrue
  • autoFetchLimit: (default:1autoFetch

Hooks

See information in the

Finding Items

Model.getSync(id, [ options ])

To get a specific element from the database useModel.get

1 2
var person = Person.getSync(123); // finds person with id = 123

Model.findSync([ conditions ] [, options ] [, limit ] [, order ])

Finding one or more elements has more options, each one can be given in no specific parameter order. Onlyoptionsconditions

1 2
var people = Person.findSync({ name: "John", surname: "Doe" }, 3); // 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:

1 2 3 4 5 6
var people = Person.findSync({ surname: "Doe" }, "name"); // finds people with surname='Doe' and returns sorted by name ascending people = Person.findSync({ surname: "Doe" }, [ "name", "Z" ]); // 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:

1 2
var people = Person.findSync({ surname: "Doe" }, { offset: 2 }); // finds people with surname='Doe', skips the first 2 and returns the others

You can also use raw SQL when searching. It's documented in the

Model.countSync([ conditions])

If you just want to count the number of items that match a condition you can just use.count()

1 2
var count = Person.countSync({ surname: "Doe" }); console.log("We have %d Does in our db", count);

Model.existsSync([ conditions])

Similar to.count()

1 2
var exists = Person.existsSync({ surname: "Doe" }); console.log("We %s Does in our db", exists ? "have" : "don't have");

Aggregating Functions

AnArrayObject

Here's an example to illustrate how to use.groupBy()

1 2 3
//The same as "select avg(weight), age from person where country='someCountry' group by age;" var stats = Person.aggregate(["age"], { country: "someCountry" }).avg("weight").groupBy("age").getSync(); // stats is an Array, each item should have 'age' and 'avg_weight'

Base.aggregate()

  • .limit(): you can pass a number as a limit, or two numbers as offset and limit respectively
  • .order(): same as Model.find().order()

Additional.aggregate()

  • min
  • max
  • avg
  • sum
  • count(there's a shortcut to this -Model.count

There are more aggregate functions depending on the driver (Math functions for example).

Chaining

If you prefer less complicated syntax you can chain.find()

1 2 3
var people = Person.find({ surname: "Doe" }).limit(3).offset(2).only("name", "surname").runSync(); // finds people with surname='Doe', skips first 2 and limits to 3 elements, // returning only 'name' and 'surname' properties

If you want to skip just one or two properties, you can call.omit().only

Chaining allows for more complicated queries. For example, we can search by specifying custom SQL:

1
Person.find({ age: 18 }).where("LOWER(surname) LIKE ?", ['dea%']).allSync( ... );

It's bad practice to manually escape SQL parameters as it's error prone and exposes your application to SQL injection. The?where

.find,.where.all

You can alsoorderorderRaw

1 2 3
Person.find({ age: 18 }).order('-name').allSync( ... ); // see the 'Raw queries' section below for more details Person.find({ age: 18 }).orderRaw("?? DESC", ['age']).allSync( ... );

You can also chain and just get the count in the end. In this case, offset, limit and order are ignored.

1 2
var people = Person.find({ surname: "Doe" }).countSync(); // people = number of people with surname="Doe"

Also available is the option to remove the selected items. Note that a chained remove will not run any hooks.

1 2
Person.find({ surname: "Doe" }).removeSync(); // Does gone..

You can also make modifications to your instances using common Array traversal methods and save everything in the end. [NOT SUPPORT]

1 2 3 4 5 6 7 8 9 10 11 12 13
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()

Model.find()does not return an Array so you can't just chain directly. To start chaining you have to call.each().filter().sort().forEach()

In the end (or during the process..) you can call:

  • .countSync() if you just want to know how many items there are;
  • .getSync() to retrieve the list;
  • .saveSync() to save all item changes.

Conditions

Conditions are defined as an object where every key is a property (table column). All keys are supposed to be concatenated by the logicalANDArray

1 2
{ 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:

1 2 3 4 5 6 7 8 9 10 11
{ 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.not_between(123, 456) } // `col1` NOT BETWEEN 123 AND 456 { col1: orm.like(12 + "%") } // `col1` LIKE '12%' { col1: orm.not_like(12 + "%") } // `col1` NOT LIKE '12%' { col1: orm.not_in([1, 4, 8]) } // `col1` NOT IN (1, 4, 8)

Raw queries

1 2 3 4 5 6 7 8 9 10 11 12 13
var data = db.driver.execQuerySync("SELECT id, email FROM user") // You can escape identifiers and values. // For identifier substitution use: ?? // For value substitution use: ? var data = db.driver.execQuerySync( "SELECT user.??, user.?? FROM user WHERE user.?? LIKE ? AND user.?? > ?", ['id', 'name', 'name', 'john', 'id', 55]) // Identifiers don't need to be scaped most of the time var data = db.driver.execQuerySync( "SELECT user.id, user.name FROM user WHERE user.name LIKE ? AND user.id > ?", ['john', 55])

Identity pattern

You can use the identity pattern (turned off by default). If enabled, 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 need to call some manual SQL queries, you shouldn't use this feature. It is also know to cause some problems with complex autofetch relationships. Use at your own risk.

It can be enabled/disabled per model:

1 2 3 4 5
var Person = db.define('person', { name : String }, { identityCache : true });

and also globally:

1 2
var db = orm.connectSync('...'); db.settings.set('instance.identityCache', true);

The identity 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

Creating Items

Model.createSync(items)

To insert new elements to the database useModel.create

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
var items = Person.createSync([ { name: "John", surname: "Doe", age: 25, male: true }, { name: "Liza", surname: "Kollan", age: 19, male: false } ]); // items - array of inserted items

Updating 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.

1 2 3 4 5
var John = Person.getSync(1); John.name = "Joe"; John.surname = "Doe"; John.saveSync(); console.log("saved!");

Updating and then saving an instance can be done in a single call:

1 2 3
var John = Person.getSync(1); John.saveSync({ name: "Joe", surname: "Doe" }); console.log("saved!");

If you want to remove an instance, just do:

1 2 3 4
// you could do this without even fetching it, look at Chaining section above var John = Person.getSync(1); John.removeSync(); console.log("removed!");

Validations

See information in the

Associations

An association is a relation between one or more tables.

hasOne

Is a
Animal.hasOne('owner', Person)

owner_id

The following functions will become available:

1 2 3 4
animal.getOwnerSync() // Gets owner animal.setOwnerSync(person) // Sets owner_id animal.hasOwnerSync() // Checks if owner exists animal.removeOwnerSync() // Sets owner_id to 0

Chain Find

The hasOne association is also chain find compatible. Using the example above, we can do this to access a new instance of a ChainFind object:

1
Animal.findByOwner({ /* options */ })

Reverse access

1
Animal.hasOne('owner', Person, {reverse: 'pets'})

will add the following:

1 2 3 4 5 6
// Instance methods person.getPetsSync(function..) person.setPetsSync(cat, function..) // Model methods Person.findByPets({ /* options */ }) // returns ChainFind object

hasMany

Is a
Patient.hasMany('doctors', Doctor, { why: String }, { reverse: 'patients', key: true })

This will create a join tablepatient_doctorsPatient.sync()

column name type
patient_id Integer (composite key)
doctor_id Integer (composite key)
why varchar(255)

The following functions will be available:

1 2 3 4 5 6 7 8 9 10 11 12
patient.getDoctorsSync() // List of doctors patient.addDoctorsSync(docs) // Adds entries to join table patient.setDoctorsSync(docs) // Removes existing entries in join table, adds new ones patient.hasDoctorsSync(docs) // Checks if patient is associated to specified doctors patient.removeDoctorsSync(docs) // Removes specified doctors from join table doctor.getPatientsSync() etc... // You can also do: patient.doctors = [doc1, doc2]; patient.saveSync()

To associate a doctor to a patient:

1
patient.addDoctorSync(surgeon, {why: "remove appendix"})

which will add{patient_id: 4, doctor_id: 6, why: "remove appendix"}

getAccessor

This accessor in this type of association returns aChainFind

1 2
var doctors = patient.getDoctors().order("name").offset(1).runSync()); // ... all doctors, ordered by name, excluding first one

extendsTo

If you want to split maybe optional properties into different tables or collections. Every extension will be in a new table, where the unique identifier of each row is the main model instance id. For example:

1 2 3 4 5 6 7
var Person = db.define("person", { name : String }); var PersonAddress = Person.extendsTo("address", { street : String, number : Number });

This will create a tablepersonidnameperson_addressperson_idstreetnumberPersonhasOne.getAddress(cb).setAddress(Address, cb)

Note:Person.extendsTo

Examples & options

If you have a relation of 1 to n, you should usehasOne

1 2 3 4 5 6 7 8 9 10 11 12 13
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 var animal = Animal.getSync(123); // animal is the animal model instance, if found var person = animal.getOwnerSync(); // if animal has really an owner, person points to it

You can mark theowner_idrequired

1
Animal.hasOne("owner", Person, { required: true });

If a field is not required, but should be validated even if it is not present, then specify thealwaysValidate

1
Animal.hasOne("owner", Person, { required: false, alwaysValidate: true });

If you prefer to use another name for the field (owner_id) you can change this parameter in the settings.

1
db.settings.set("properties.association_key", "{field}_{name}"); // {name} will be replaced by 'owner' and {field} will be replaced by 'id' in this case

Note: This has to be done before the association is specified.

ThehasMany

1 2 3 4 5 6 7 8 9 10 11
var Person = db.define('person', { name : String }); Person.hasMany("friends", { rate : Number }, {}, { key: true }); var John = Person.getSync(123); var friends = John.getFriendsSync(); // 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 activateautoFetch

1 2 3 4 5 6 7 8 9 10 11 12
var Person = db.define('person', { name : String }); Person.hasMany("friends", { rate : Number }, { key : true, // Turns the foreign keys in the join table into a composite key autoFetch : true }); var John = Person.getSync(123); // 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.

1 2 3 4 5 6 7 8 9 10
var Person = db.define('person', { name : String }, { autoFetch : true }); Person.hasMany("friends", { rate : Number }, { key: true });

Associations can make calls to the associated Model by using thereverse

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
var Pet = db.define('pet', { name : String }); var Person = db.define('person', { name : String }); Pet.hasOne("owner", Person, { reverse : "pets" }); var pets = Person(4).getPetsSync(); // 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 havinghasMany

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
var Pet = db.define('pet', { name : String }); var Person = db.define('person', { name : String }); Person.hasMany("pets", Pet, { bought : Date }, { key : true, reverse : "owners" }); Person(1).getPetsSync(...); Pet(2).getOwnersSync(...);

Transaction support

You can use low level transaction function to process db transcation.

1 2 3 4 5 6
db.begin(); ... if(err) db.rollback(); else db.commit();

Or you can use trans to simpile process it.

1 2 3 4
var result = db.trans(() => { ... return result; });

Adding external database adapters

To add an external database adapter toormaddAdapter

1
require('orm').addAdapter('cassandra', CassandraAdapter);

See the documentation for creating adapters for more details.