What is an ORM ? ORM solutions are useful to facilitate data-driven API development. Users have concrete needs which drive the data model of an application. In legacy development, this data architecture is typically implemented and version controlled using database scripts such as SQL scripts. A separate library is then used for the server application to execute CRUD actions on the database.

Why is it so Important?

ORMs work as a high-level API to execute CRUD, and these days quality ORMs also allow us to initialize the data through code. Complex data manipulation, cleaning and so on, is often easier in code. While dedicated Extract, Transform and Load (ETL) tools exist, the same ETL tasks can be easily implemented in ORM.

Implementing extract, transform, and load with code allows a system to more easily integrate data from very different sources. SQL databases of multiple flavors, NoSQL data, file system data, and third party data can all be integrated under a single language with a JavaScript ORM.

Finally, code-oriented data control also allows a system to implement data usage at run time or in the build process, and flexibly adapt usage during the development process as needed.

To restate, 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.

Should I use ORM like sequelize for PostgreSQL/Node.js?

To begin with it depends on the scale of your project and team working on that project. If you have a small scaled project which may need less customizations later on you can simply go for query based execution. But if you have a large project with customizations coming at a later stage try to develop yourself or your team to use an ORM like sequelize.

So the overall process needed to move to ORM is:

1. You should have great understanding of the queries and relations of the database schema.

2. You should understand the language your ORM supports. So for Node.js one should have a skilled understanding of basic Javascript, Callbacks and Promises.

3. Now if you know the queries well and can work with ORM too. You will speed your development process manyfolds.

Pros:

Standardization – ORMs usually have a single schema definition in the code. This makes it very clear what the schema is, and very simple to change it.
No need to learn SQL – queries are written in plain JavaScript.
Portable – with an ORM, it is easy to migrate between databases (we actually did that already, moving some older PostgreSQL databases to Amazon Aurora with the MySQL standardization). Because all your code uses the ORM, you only need to change / reimplement the ORM to replace the DB. Many ORMs (like Sequalize) support multiple popular databases out of the box.

Cons:

**It is hard to implement complex queries **– while ORMs simplify querying – especially if you lack experience with SQL – they can prove more difficult and non-flexible when writing complex queries (with aggregations, sub-queries, joins, etc…). (I know it’s supported, but it often feels very roundabout compared to SQL).
Can be inefficient – due to the previous point, ORMs can lead to inefficient querying (you’re writing a query that fits the ORM, not a simple / efficient query). Because you’re not using SQL, the query execution also becomes obscure (can’t run SQL explain and immediately understand what the query plan is).
New language – if you’re familiar with SQL, an ORM can feel like re-learning a new query language.

_Bottom line _– for smaller projects / projects that involve very complex queries, I’d recommend sticking with SQL. For bigger projects with bigger teams touching the codebase and making schema changes, a good ORM can be a life saver.

Following are some popular ORM :

1. Sequelize

Sequelize is another ORM for Node.js and io.js (which are finally merging together). It supports PostgreSQL, MySQL, MariaDB, SQLite and MSSQL and features solid transaction support, relations, read replication, and

more. You can install it by running the following commands:

# Install Sequelize $ npm install --save sequelize

Install the DB driver

$ npm install --save pg pg-hstore

For both mysql and mariadb dialects

$ npm install --save mysql
$ npm install --save sqlite3

MSSQL

$ npm install --save tedious

Now you are ready to use it as shown in the example below:

var Sequelize = require(‘sequelize’);

var sequelize = new Sequelize(‘database’, ‘username’, ‘password’, {

host: ‘localhost’,

dialect: ‘mysql’|‘mariadb’|‘sqlite’|‘postgres’|‘mssql’, // use one of these
pool: {

max: 5,

min: 0,

idle: 10000

},
// SQLite only

storage: ‘path/to/database.sqlite’

});
// Or you can simply use a connection uri

var sequelize = new Sequelize(‘postgres://user:[email protected]:5432/dbname’);

Just like with Bookshelf.js, you need only one connection to the database. After that, you can create a model like:

var User = sequelize.define(‘user’, {

firstName: {

type: Sequelize.STRING,

field: ‘first_name’ // first_name column matches User.firstName

},

lastName: {

type: Sequelize.STRING

}

}, {

freezeTableName: true // Model tableName (user) will be the same as the model name

});

The above Sequelize.STRING matches a VARCHAR in SQL. Other data types are Sequelize.INTEGER for INTEGER, Sequelize.BLOB for BLOB (or bytea in Postgres). You can read the full list here.

Sequelize allows you to write relations between the tables. For example, if you have a model called Project and another one called Developer and want to assign more than one developer to one project, you can do it like this:

Project.hasMany(Developer, {as: ‘devs’})

This will make sure to add the necessary fields in each model (project_id to the Developer model in this case). Or if you feel you can’t profit from the Sequelize API, you can run raw SQL queries.

Sequelize can be found on GitHub as well

2. 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’

});

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

4. Lovefiled

Lovefield is not a real ORM. It’s actually a relational database for web apps, built upon IndexedDB, developed by Google and written entirely in JavaScript. It doesn’t support raw SQL queries, but it comes with an API that tries to mimic the SQL syntax.

Besides downloading directly from GitHub repository, Lovefield supports npm and bower package management systems and can be found using

npm info lovefield

bower info lovefield

Adding Lovefield as the dependency and executing npm update or bower update will automatically pull down the designated release.

Defining Schema

The concept of Lovefield is to define a database schema, then operate on the instance implementing that schema. In the example, schema definition is carried out through a set of synchronous APIs:

// SQL equivalent: CREATE DATABASE IF NOT EXISTS todo

// This schema definition (or data definition commands in SQL, DDL) is not

// executed immediately. Lovefield uses builder pattern to build the schema

// first, then performs necessary database open/creation later.

var schemaBuilder = lf.schema.create(‘todo’, 1);
// SQL equivalent:

// CREATE TABLE IF NOT EXISTS Item (

//   id AS INTEGER,

//   description AS INTEGER,

//   deadline as DATE_TIME,

//   done as BOOLEAN,

//   PRIMARY KEY ON (‘id’)

// );

// ALTER TABLE Item ADD INDEX idxDeadLine(Item.deadline DESC);

schemaBuilder.createTable(‘Item’).

addColumn(‘id’, lf.Type.INTEGER).

addColumn(‘description’, lf.Type.STRING).

addColumn(‘deadline’, lf.Type.DATE_TIME).

addColumn(‘done’, lf.Type.BOOLEAN).

addPrimaryKey([‘id’]).

addIndex(‘idxDeadline’, [‘deadline’], false, lf.Order.DESC);

The code above has pseudo SQL commands to demonstrate their equivalent concept in SQL. Once the schema is defined, Lovefield needs to be instructed to create or connect to the corresponding instance:

// Promise-based API to get the instance.

schemaBuilder.connect().then(function(db) {

// …

});

From this point on, the schema cannot be altered. Both the connect() and Lovefield offered query APIs are asynchronous Promise-based APIs. This design is to prevent Lovefield from blocking main thread since the queries can be long running and demanding quite some CPU and I/O cycles.

If the database is brand new, Lovefield will create it using the schema. If the database already exists, Lovefield will attempt to identify the instance using database name specified in the schema, and connect to it.

Lovefield also uses Promise chaining pattern extensively:

// Start of the Promise chaining

schemaBuilder.connect().then(function(db) {

// Asynchronous call connect() returned object: db

todoDb = db;
// Get the schema representation of table Item.

// All schema-related APIs are synchronous.

item = db.getSchema().table(‘Item’);
// Creates a row. Lovefield does not accept plain objects as row.

// Use the createRow() API provided in table schema to create a row.

var row = item.createRow({

‘id’: 1,

‘description’: ‘Get a cup of coffee’,

‘deadline’: new Date(),

‘done’: false

});
// INSERT OR REPLACE INTO Item VALUES row;

// The exec() method returns a Promise.

return db.insertOrReplace().into(item).values([row]).exec();
}).then(function() {

// When reached here, Lovefield guarantees previous INSERT OR REPLACE

// has been committed with its implicit transaction.
// SELECT * FROM Item WHERE Item.done = false;

// Return another Promise by calling this SELECT query’s exec() method.

return todoDb.select().from(item).where(item.done.eq(false)).exec();
}).then(function(results) {

// The SELECT query’s Promise will return array of rows selected.

// If there were no rows, the array will be empty.
results.forEach(function(row) {

// Use column name to directly dereference the columns from a row.

console.log(row[‘description’], ‘before’, row[‘deadline’]);

});

});

5. waterline

Waterline is a new kind of storage and retrieval engine.

It provides a uniform API for accessing stuff from different kinds of databases, protocols, and 3rd party APIs. That means you write the same code to get and store things like users, whether they live in Redis, mySQL, LDAP, MongoDB, or Postgres.

Waterline strives to inherit the best parts of ORMs like ActiveRecord, Hibernate, and Mongoose, but with a fresh perspective and emphasis on modularity, testability, and consistency across adapters.

Originally published at socialdribbler.com