Which Query Builder/ORM should you use for Nodejs

Which Query Builder/ORM should you use for Nodejs

<strong>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.</strong>

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.


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


  • **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


$ 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 INTEGERSequelize.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 : '',
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:

// 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:
// description AS INTEGER,
// deadline as DATE_TIME,
// done as BOOLEAN,
// PRIMARY KEY ON ('id')
// );
// ALTER TABLE Item ADD INDEX idxDeadLine(Item.deadline DESC);
addColumn('id', lf.Type.INTEGER).
addColumn('description', lf.Type.STRING).
addColumn('deadline', lf.Type.DATE_TIME).
addColumn('done', lf.Type.BOOLEAN).
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

// 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

Learn more

☞ MERN Stack Front To Back: Full Stack React, Redux & Node.js

☞ The Complete Node.js Developer Course (3rd Edition)

☞ Learn and Understand NodeJS

☞ NodeJS - The Complete Guide (incl. MVC, REST APIs, GraphQL)

☞ React Node FullStack - Social Network from Scratch to Deploy

☞ Node.js Absolute Beginners Guide - Learn Node From Scratch

Quick Intro to Vuex ORM

Quick Intro to Vuex ORM

If you're looking to make a scalable Vue or Nuxt app, you might consider using Vuex ORM. I've recently used it in a project, and in this article, I'll share with you how it works and why I think you'll like it too.

If you're looking to make a scalable Vue or Nuxt app, you might consider using Vuex ORM. I've recently used it in a project, and in this article, I'll share with you how it works and why I think you'll like it too.

What is Vuex ORM

Vuex introduces some powerful concepts for managing your application state including the store, mutations, actions, and so on.

Vuex ORM is an abstraction of Vuex that allows you to think about your application state in terms of models e.g. Posts, Users, Orders, etc, and CRUD operations, e.g. create, update, delete, etc.

ORM tools (object-relational mapping) transform data between incompatible system using objects. ORMs are very popular for databases.
This allows for a signficant simplification of your code. For example, rather than this.$store.state.commit("UPDATE_USER", { ... }), you can use User.update({ ... }), making your Vue code much easier to reason about.

The other advantages of Vuex ORM are that it reduces boilerplate code by setting up the mutations and getter you'll need automatically, and also makes it easy to work with nested data structures in your application state.

From Vuex to Vuex ORM

As a way of demonstrating the advantages, let's refactor some raw Vuex code using Vuex ORM.

We'll use a classic to-do list example where we can mark a to-do as "done". Here's the store which will represent that:


store: {
  state: { todos: [] },
  mutations: {
    MARK_DONE(state, id) {
      const todo = state.todos.find(todo => todo.id === id);
      todo.done = true;

Let's say we display our to-do items on the home page of the app. We'll use a computed property todos and a v-for to link the to-do items to the template.

When a to-do is clicked, we'll mark it as "done" by making a commit to the MARK_DONE mutation.


    v-for="todo in todos"
  export default {
    computed: {
      todos() {
        return this.$store.state.todos;
    methods: {
      markDone(id) {
        this.$store.state.commit(MARK_DONE, id);

The Vuex ORM way

As I said, Vuex ORM represents data as models. So we'll first create a Todo model and define the fields we'd need like id, title, and done.

Unlike most Vue software, Vuex ORM uses ES6 classes for configuration.

import { Model } from "@vuex-orm/core";

export default class Todo extends Model {
  static entity = "todos";

  static fields () {
    return {
      id: this.string(""),      
      title: this.string(""),
      done: this.boolean(false),

Now it's time to register the model to the Vuex ORM "database", which allows you to use the model.

While we're at it, we can register the Vuex ORM plugin with Vuex.


import VuexORM from "@vuex-orm/core";
import Todo from "./models/Todo";

const database = new VuexORM.Database();
database.register(Todo, {});

const plugin = VuexORM.install(database);

export default {
  plugins: [plugin]

With our Vuex ORM store set up, we can start using it in our components. First, import the model into a component file. Now, rather than using the "weird" syntax of Vuex, we can use standard CRUD methods to query our store:


import Todo from "../store/models/todo";
export default {
  computed: {
    // todos() {
    //   return this.$store.state.todos;
    // }
    todos: () => Todo.all();
  methods: {
    markDone(id) {
      // this.$store.state.commit(MARK_DONE, id);
        where: id,
        data: { done: true }

I don't know about you, but I find that much more readable!

Store config

But hang on, where is the store configuration for the Todo model? Unless you want to customize it, you don't need any! Vuex ORM will automatically create state, mutations, and getters that are aliased to the model API e.g. read, update, find.


Even better, you can add some really handy plugins to Vuex ORM (that's right, a plugin for a plugin for a plugin), include ones to abstract your server communication.

For example, there is an Axios plugin that is almost zero config so long as your model endpoints fit the RESTful pattern.

Let's say when our app loads, it retrieves all the to-do items from the server and pushes them to the store:

created() {
  try {
    let { data } = await this.$http.get("/todos");
    data.forEach(todo => this.$store.state.commit(ADD_TODO, todo));
  } catch (err) {
    // handle error

The Vuex ORM Axios plugin adds additional model methods like fetch which allows you to replace the above code with:

created() {

How easy is that?


There's plenty more to know about Vuex ORM so check out the docs:

Thanks for reading

Learn Sequelize ORM in Node.js with MySQL From Scratch

Learn Sequelize ORM in Node.js with MySQL From Scratch

This Nodejs and MySQL tutorial, we'll learn Sequelize ORM in Nodejs with MySQL From Scratch. Sequelize is a promise-based Node.js ORM for Postgres, MySQL, MariaDB, SQLite, and Microsoft SQL Server. It supports the dialects PostgreSQL, MySQL, SQLite, and MSSQL plus some cool features like transactions and eager-loading.

Getting Started

Sequelize is a promise-based ORM for Node.js v4 and up. It supports the dialects PostgreSQL, MySQL, SQLite, and MSSQL plus some cool features like transactions and eager-loading.

Q: Why use Sequelize instead of standard SQL Queries?

A: Basically because it supports more than one database system and it provides you an OOP Database API with all queries in form of methods and function so you don't have to write or care about queries plus its advanced Association system for putting relations between Models.

I assume you have a basic Node.js project for working with Sequelize.

Now let's start by installing Sequelize on our project.

npm install sequelize --save 

Sequelize Also requires your database system driver (depending on what dialect you choose) on our case we will be working with MySQL.

npm install mysql2 --save 

This will install MySQL adapter which allows Sequelize to connect and to the MySQL Deamon server.

We also, need to install Sequelize-CLI for managing our project and keeping track of migrations.

npm install sequelize-cli -g 

We save the CLI globally because you will be using on more than one project and you need to access directly through the command line.

Connecting to Database

First, we need to open connection steam between Sequelize and the running MySQL Deamon.

Create a database folder under src/database and add a connection.js file under it.

/* connection/js */

const Sequelize = require("sequelize");

const sequelize = new Sequelize("socialnetwork", "root", "", {
  host: "",
  dialect: "mysql",
  operatorsAliases: false

module.exports = sequelize;
global.sequelize = sequelize;

Change the config depending on your database server settings.

we export the connection instance as well as we export it globally which means we can access the Sequelize instance from any module on our environment without import (Global variables aren't good but in our case, it is the best scenario).

Now under the app.js (or main entry point module), you can require the connection module to connect to the database and expose the connection instance globally.

Models & Schemas

Models are the building blocks of your tables each model represents a database table where you define a model by putting the schema and giving it a name.
The Schema is a set of attributes (table columns) and associations (table relations).

So let's say we are going to create a social media application something like twitter clone, ao first we start by creating the database, this is better done manually from the CLI of any other MySQL client.
mysql$ CREATE DATABASE socialnetwork
The above command will create a database for us.

For a basic Social network, we would have a tweet and a user (tweet's author), the user can tweet one or more tweets but a tweet belongs to a single user.

Let's create the tweet model.

const Sequelize = require("sequelize");

module.exports = sequelize.define("Tweet", {
  id: {
    type: Sequelize.INTEGER(11),
    allowNull: false,
    autoIncrement: true,
    primaryKey: true
  content: Sequelize.STRING(300)

The Sequelize define method registers a new model using the provided name and schema.
Attributes can have the type directly, all are defined in the main namespace of Sequelize (STRING, DATE...).
Or you can define attributes using options with type and allowNull, unique.

for type sizes, you simply use the type as a function and pass the length (number) as an argument.

the id attributes are the primary key that uniquely identifies each tweet.

We export the defined model so later we will be able to use it for inserting, fetching, updating or deleting model's data.

We also need to define and register the user model the same way we did the tweet but with different attributes.

const Sequelize = require("sequelize");

module.exports = sequelize.define("User", {
  id: {
    type: Sequelize.INTEGER(11),
    allowNull: false,
    autoIncrement: true,
    primaryKey: true
  username: {
    type: Sequelize.STRING(35),
    allowNull: false,
    unique: true
  passwd: {
    type: Sequelize.STRING(20),
    allowNull: false

The username has to be unique so we make it unique (only one persistence can be saved with a certain username string), we set unique to true.

You can create default attributes on Sequelize which gets added to any model (like the id).

Sequelize Migrations

Sequelize other than the ORM comes with a Database Builder which allow you to create and manage the structure of your database tables over time without manually interacting with the CLI or typing silly commands.

All Migrations features are manipulated through Sequelize-CLI that we have installed earlier.

First, for migrations to work we need to initialize our project with the CLI and add some database configuration.

sequelize init 

This, in fact, it is used to initialize a whole Sequelize project to work properly with models, migrations, and seeds and since we already created the connection stream and add some models we will remove the models folder.

This what will be created under the root of our project.

  • config: database connection configuration for (development, testing, and production). So make sure to put your settings in the development object.
  • migrations: All migrations will be stored in here with their timestamp of creation.
  • seeders: A seed is a data that gets added to the database by default.
  • models: All models should go here where there is also a connection stream to Sequelize (in our case we will delete this).

Make sure to delete the newly added models folder by the CLI.

Let's add our tweets and users tables migrations, we will use the CLI to generate a new migration which will be placed by default under the migrations/ folder with some pre-defined code.

sequelize migration:generate --name create_tweets_table

This will generate a new migration named (create_tweets_table) and place it under migrations/ for the naming convention of migrations you always want it to be like a verb or a sentence tells what the migration does exactly.

Open the tweets migration file (Sequelize will add a timestamp represents when the migration has been generated to the filename), and add your column types which to be created on up method and dropTable command to drop and remove the table from the database if down is executed.

/* 20190214224339-create_tweets_table.js */
"use strict";

module.exports = {
  up: (queryInterface, Sequelize) => {
    //You must return a promise 
    return queryInterface.createTable("tweets", {
      id: {
        type: Sequelize.INTEGER(11),
        allowNull: false,
        autoIncrement: true,
        primaryKey: true
      content: Sequelize.STRING(300),
      userId: Sequelize.INTEGER(11),
      //Those are added by default on insertion (make sure to create the their columns)
      createdAt: Sequelize.DATE,
      updatedAt: Sequelize.DATE

  down: (queryInterface, Sequelize) => {
    //Return a promise that drops a table in case of (migration:undo)
    return queryInterface.dropTable("tweets");

If we run migrate command the up method will be called and the table will be created, but Sequelize is smart enough to only run the up command once (it keeps track of migrated tables).

the userId attribute we will use it later to associate a user with one or more tweets.

And in case you run migration:undo or migration:undo:all it will rollback either the last submitted migration or simply or migrations in this case whatever you put inside the down method will be executed.

Add the user's migration too.

sequelize migration:generate --name create_users_table

And add the schema of the table on up and drop table on down.

/* 20190214225010-create_users_table.js */

"use strict";

module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.createTable("users", {
      id: {
        type: Sequelize.INTEGER(11),
        allowNull: false,
        autoIncrement: true,
        primaryKey: true
      username: {
        type: Sequelize.STRING(35),
        allowNull: false,
        unique: true
      passwd: {
        type: Sequelize.STRING(20),
        allowNull: false
      createdAt: Sequelize.DATE,
      updatedAt: Sequelize.DATE

  down: (queryInterface, Sequelize) => {
    return queryInterface.dropTable("users");

Now our migrations are created now we need to migrate them to the database.

sequelize db:migrate

It will output the successfully migrated migration and errors in case of any.

You can use the CLI to take a look at the users and tweets tables structure using the describe command on your MySQL CLI.

use socialnetwork;
describe tweets;
describe users;
Inserting to Database

Let's create a bootstrap.js module that will do the fake data insertion on the database for our social network.

/* src/bootstrap.js */
//Export a default function (we use Async/Await)
module.exports = async () => {
  //Require models
  const Tweet = require("./models/Tweet");
  const User = require("./models/User");
  //Create Relations 
  User.hasMany(Tweet, { as: "Tweets", foreignKey: "userId" });
  Tweet.belongsTo(User, { as: "User", foreignKey: "userId" });

For our Models Association (Relation) we have two constraints:

  • User: can have one or more Tweets (one-to-many).
  • Tweet: can have one user only as an author (one-to-one)

Each Sequelize model has a built-in methods used to describe associations among other models, the first parameter it takes is the target and an options object as the second parameter.

  • hasMany: for a one-to-many association, will associate User with many tweets with configuration (foreignKey will be added to the tweets table).
  • as option: used to set setters in the User Model so you can call userInstance.setTweets(tweets).
  • belongsTo: for a one-to-one association to associate each tweet with one and only one user.

Check the Association Docs for more detailed info.

Let's insert some User to the database.

//Generic Error Handler 
const errHandler = err => {
  //Catch and log any error.
  console.error("Error: ", err);
//create returns a promise which gets resolved to the user instance 
//We also use await, you can use standard then callback.
const user = await User.create({
  username: "alexdmc",
  passwd: "1234567890"
}).catch(errHandler); ///< Catch any errors that gets thrown
//You must provide the userId to get each tweet linked to a single user.
const tweet = await Tweet.create({
  content: "This is actually the tweet content Tweeted from Iphone",
  userId: user.id

A model.create method is used to create a user instance using the provided data and then save it and finally returns a promise holding the new user instance.

We use await which gets resolved if the creation and saving succeed otherwise the catch callback (errHandler) will get called.

To associate a tweet with its author (user) we have to put the userId attribute under the tweet to point to the author's unique id.

Fetching from Database (Eager Loading)

For fetching specific data related to a specific model we have to use the Model class providing it with a filter (condition) to fetch a specific record(s) from the database.

Let's try to fetch users data plus all its tweets (using eager-loading).

eager-loading is the process of fetching a models data that is associated with another model in one fetch call (also known as Populating).

//Find All Users with Thier Tweets
const users = await User.findAll({
  where: { username: "alexdmc" },
  include: [{ model: Tweet, as: "Tweets" }] ///< include used to eager-load associated model 
//log users & tweets
console.log("AlexDMC Tweets: ", JSON.stringify(users));

We findAll the users with their associated tweets the where attributes take the filter object and include is an array on which you specify the associated model you want to eager-load alongside the current model (User).

the include takes an array of object we need to specify associated model's model class and the as attribute (which you used to define the association between the two models).

The output should have all the registered users with an array of their tweets.

Read More on Eager-Loading Docs.

What's Next

This tutorial is just a quick introduction to Sequelize and how to use its main features, if you want to master and work with Sequelize you must read the http://docs.sequelizejs.comDocs for more detailed pieces of information and instructions.