github.com/dresende/node-orm2 @v8.0.1 sqlite

Object Relational Mapping

This package is not actively maintained

If you're starting a new project, consider using one of the following instead as they have a more active community: * https://github.com/typeorm/typeorm * https://github.com/sequelize/sequelize

Install

npm install orm

Node.js Version Support

Supported: 4.0 +

If using Nodejs >= 14 & Postgres, you must use pg driver >= 8.1. v7 doesn't work correctly (tests time out).

Tests are run on Travis CI If you want you can run tests locally:

npm test

DBMS Support

• MySQL & MariaDB
• PostgreSQL
• Amazon Redshift
• SQLite
• MongoDB (beta, node 6 or older, doesn't work with node 8. Also, missing aggregation features)

Features

• 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 - see enforce for details)
• Model instance caching and integrity (table rows fetched twice are the same object, changes to one change all)
• Plugins: MySQL FTS , Pagination , Transaction, Timestamps, Migrations

Introduction

This is a node.js object relational mapping module.

An example:

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", "Antarctica" ], // 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";
        });
      });
    });
  });
});

---

Express

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://username:password@host/database", {
    define: function (db, models, next) {
        models.person = db.define("person", { ... });
        next();
    }
}));
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).

Examples

See examples/anontxt for an example express based app.

Documentation

Documentation is moving to the wiki.

Settings

See information in the wiki.

Connecting

See information in the wiki.

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

Properties

See information in the wiki.

Instance Methods

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() );
})

Model Methods

Are defined directly on the model.

var Person = db.define('person', {
    name    : String,
    height  : { type: 'integer' }
});
Person.tallerThan = function(height, callback) {
    this.find({ height: orm.gt(height) }, callback);
};

Person.tallerThan( 192, function(err, tallPeople) { ... } );

Loading Models

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();
};

Synchronizing Models

See information in the wiki.

Dropping Models

See information in the wiki.

Advanced Options

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 added automatically, and called "id" by default.

If you define your own key: true column, "id" will not be added:

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
});

Pet model will have 2 columns, an UID and a name.

It's also possible to have composite keys:

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

Other options:

• identityCache : (default: false) Set it to true to enable identity cache (Singletons) or set a timeout value (in seconds);
• autoSave : (default: false) Set it to true to save an Instance right after changing any property;
• autoFetch : (default: false) Set it to true to fetch associations when fetching an instance from the database;
• autoFetchLimit : (default: 1) If autoFetch is enabled this defines how many hoops (associations of associations) you want it to automatically fetch.

Hooks

See information in the wiki.

Finding Items

Model.get(id, [ options ], cb)

To get a specific element from the database use Model.get.

Person.get(123, function (err, person) {
    // finds person with id = 123
});

Model.find([ conditions ] [, options ] [, limit ] [, order ] [, cb ])

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
});

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

Model.count([ conditions, ] cb)

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);
});

Model.exists([ conditions, ] cb)

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");
});

Aggregating Functions

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);
});

An Array of properties can be passed to select only a few properties. An Object is also accepted to define conditions.

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'
});

Base .aggregate() methods

• .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() methods

• 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() 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
});

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

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

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

It's bad practice to manually escape SQL parameters as it's error prone and exposes your application to SQL injection. The ? syntax takes care of escaping for you, by safely substituting the question mark in the query with the parameters provided. You can also chain multiple where clauses as needed.

.find, .where & .all do the same thing; they are all interchangeable and chainable.

You can also order or orderRaw:

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

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. Note that a chained remove will not run any hooks.

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.

```js 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 >= 1