3 JavaScript ORMs You Might Not Know

Key Takeaways

• Three JavaScript ORMs that can help manage complex data in applications include Bookshelf.js, Sequelize, and Lovefiled. Bookshelf.js and Sequelize are designed for Node.js and work well with PostgreSQL, MySQL, MariaDB, SQLite3, and MSSQL. Lovefield, on the other hand, is a relational database for web apps built upon IndexedDB, developed by Google.
• Bookshelf.js and Sequelize allow developers to create one-to-one, one-to-many, and many-to-many relations between tables. They also support transactions, relations, read replication, and more. Lovefield, while not supporting raw SQL queries, mimics the SQL syntax and supports ACID transactions, limiting and skipping, parametrized queries, and more.
• Despite the importance of SQL knowledge for developers, JavaScript ORMs can simplify the job by handling complex data and database interactions. The choice of ORM should be based on the specific needs of the project, the complexity of the database, and the features required.

When I wrote my first JavaScript lines, I never thought it would go beyond browser scripting and making interactive websites. I was wrong since JavaScript can be used literally everywhere:

• You can use it in the browser and you don’t have to be restricted to ECMAscript with DOM API. Languages like CoffeeScript and Dart, transpilable to plain old JavaScript, can help you develop faster and better code.
• You can use it on the server. Node.js and plenty of its frameworks are increasing in number and helping front-end developers become full-stack developers.
• You can use it for mobile apps. Using Cordova and frameworks like Ionic or NativeScript, you can build mobile apps faster without the need to know other languages like Java or Swift/Objective-C. Thanks to these frameworks, you can even share the source code between mobile platforms.
• You can build IoT. SitePoint has published some articles by Patrick Catanzariti which can be very helpful for this purpose.

With an increased complexity of the application you’re developing, you would need something to help you deal with all the data you’re storing in the database, whether in a DB server (like PostgreSQL), in the embedded SQLite of mobile devices, or in the browser. Here comes the need for ORMs. The Java community has Hibernate, the PHP developers can use Doctrine ORM, and the JavaScript community has its own ORMs. In this article, I’ll give you an introduction of some JavaScript ORMs which can help you deal with complex data in your next application.

Bookshelf.js

Bookshelf.js is an ORM for Node.js, designed to work well with PostgreSQL, MySQL, MariaDB and SQLite3. It is build on top of Knex SQL query builder and follows some patterns seen in Backbone.js, such as models and collections and similar naming conventions. If you have ever used Backbone, you’ll probably adapt Bookshelf really fast. To install Bookshelf, you would need to install Knex and also a DataBase driver:

# get knex
$ npm install knex --save

# get bookshelf
$ npm install bookshelf --save

# get one of these DB drivers
$ npm install pg
$ npm install mysql
$ npm install mariasql
$ npm install sqlite3

After installing it (make sure to pass the --save flag so it is added into the package.json file), you can use it in your Node.js application like this:

var knexInstance = require('knex')({
  client: 'mysql', // or what DB you're using
  connection: {
    host     : '127.0.0.1',
    user     : 'scott',
    password : 'tiger', // Scott's cat name
    database : 'db_name',
    charset  : 'utf8'
  }
});
// Initialize Bookshelf by passing the Knex instance
var bookshelf = require('bookshelf')(knexInstance); 

var User = bookshelf.Model.extend({
  tableName: 'users'
});

As you can see, a Bookshelf object is created by passing a Knex instance as a parameter. Then, you can use the Model‘s extend() method to create models in your application, just like the User model for the table users in the example above. Keep in mind that the bookshelf is the only instance of Bookshelf you should use throughout your application. So it’s better to make it accessible everywhere in your application, such as wrapping it in a singleton or put it in a different file and require it when needed. Bookshelf allows you to make one-to-one, one-to-many, and many-to-many relations. In our case, it would be something like:

var User = bookshelf.Model.extend({
  tableName: 'users',
  posts: function() {
    return this.hasMany(Post);
  }
});

var Post = bookshelf.Model.extend({
  tableName: 'posts',
  user: function() {
    return this.belongsTo(User);
  }
});

In case you want to take a look at it, you can find Bookshelf on GitHub.

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:pass@example.com: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

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. You can install it using Bower:

$ bower install lovefield --save

or npm:

$ npm install lovefield --save

After adding it in your HTML file, you can start using it as a front-end relational database. Creating databases and tables is simple:

// create the database for a `To Do list`
var todoDB = lf.schema.create('todo_db', 1);

var item = todoDB.createTable('items')
      .addColumn('id', lf.Type.INTEGER)
      .addColumn('task', lf.Type.STRING)
      .addColumn('deadline', lf.Type.DATE_TIME)
      .addColumn('done', lf.Type.BOOLEAN)
      .addPrimaryKey(['id']);

This code snippet shows how to create a database called todo_db and a table called items with the given columns (id as primary key, task, deadline and done). At this point, to get a list of all the unfinished tasks, the code would be:

todoDB.select()
  .from(item)
  .where(item.done.eq(false))
  .exec(); // exec() returns a Promise

The code above is very similar to SQL, where the same query would be:

SELECT * 
    FROM todo_db.items 
    WHERE done = FALSE;

Also you can do ordering, like this:

todoDB.select()
  .from(item)
  .where(item.done.eq(false))
  .orderBy(item.deadline, lf.Order.DESC) // order by deadline
  .exec();

Besides these simple queries, Lovefield is also able to handle more complex queries, such as joins. If I have references in two tables called project and developer (referencing the tables projects and developers respectively) and want to see all the projects of a given developer, I would write:

db.select()
  .from(developer, project)
  .where(lf.op.and(
    developer.projectId.eq(project.id),
    developer.id.eq('12345')
  ))
  .exec();

// these are both equivalent
db.select()
  .from(developer)
  .innerJoin(project, developer.projectId.eq(project.id))
  .where(developer.id.eq('12345'))
  .exec();

So you can see that Lovefield acts as a SQL layer on top of IndexedDB. It also supports ACID transactions (Atomicity, Consistency, Isolation, Durability), limiting and skipping (useful when paginating), parametrized queries, and much more. Just like the other ORMs, you can download Lovefield from GitHub.

Conclusions

The knowledge of SQL is a skill that every developer should have, but writing SQL queries is boring, especially when there are so many ORMs out there. When they make your job simpler, why not use them in the first place? In this article I’ve covered some of the most important ORMs released so far. Thanks to the examples provided for each of them, you should now be able to make a conscious decision about which of them to use and which fits you needs. How about you? Which JavaScript ORM do you use? Comment your choice below.

Frequently Asked Questions about JavaScript ORMs