5 NodeJS MySQL ORMs

ORMs help to abstract data mappings between your code and the database, easing data querying and manipulation. It can also help to easily change the underlying database engine without (mostly) changing any code. The following are a few ORMs you can use with nodejs.

Sequelize

Sequelize is a promise-based ORM for Node.js and io.js. It supports PostgreSQL, MySQL, MariaDB, SQLite and MSSQL and features transaction support, relations, read replication and more. Starting from 4.0.0 Sequelize will only support Node v4 and above to use ES6 features.

Example:

var Sequelize = require('sequelize');
var sequelize = new Sequelize('database', 'username', 'password');
 
var User = sequelize.define('user', {
  username: Sequelize.STRING,
  birthday: Sequelize.DATE
});
 
sequelize.sync().then(function() {
  return User.create({
    username: 'janedoe',
    birthday: new Date(1980, 6, 20)
  });
}).then(function(jane) {
  console.log(jane.get({
    plain: true
  }));
});

node-orm2

node-orm2 is another orm tool that supports MySQL, PostgreSQL, Amazon Redshift and SQLite. Features include:

– 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)

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

Easier if you are 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) {
        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(...);
});

Bookshelf

Bookshelf is a JavaScript ORM for Node.js, built on the Knex SQL query builder. Featuring both promise based and traditional callback interfaces, providing transaction support, eager/nested-eager relation loading, polymorphic associations, and support for one-to-one, one-to-many, and many-to-many relations. It is designed to work well with PostgreSQL, MySQL, and SQLite3.

While Bookshelf primarily targets Node.js, all dependencies are browser compatible, and it could be adapted to work with other javascript environments supporting a sqlite3 database, by providing a custom Knex adapter.

var knex = require('knex')({
  client: 'mysql',
  connection: {
    host     : '127.0.0.1',
    user     : 'your_database_user',
    password : 'your_database_password',
    database : 'myapp_test',
    charset  : 'utf8'
  }
});
 
var bookshelf = require('bookshelf')(knex);
 
var User = bookshelf.Model.extend({
  tableName: 'users'
});

Objection.js

Objection.js is an ORM for Node.js that aims to stay out of your way and make it as easy as possible to use the full power of SQL and the underlying database engine.

Objection.js, like Bookshelf, is built on the wonderful SQL query builder knex. All databases supported by knex are supported by objection.js. SQLite3, Postgres and MySQL are thoroughly tested.

CaminteJS

CaminteJS is cross-db ORM for nodejs, providing common interface to access most popular database formats. Caminte supports the most wide variety of databases and comes with the following adapters: mysql, sqlite3, riak, postgres, couchdb, mongodb, redis, neo4j, firebird, rethinkdb, tingodb.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>