• Knex.js: SQL Query Builder
• Sequelize
• Bookshelf
• Waterline
• Objection.js
• Mongoose
• Typegoose
• TypeORM
• MikroORM
• Prisma

• customer discounts
• loan approvals
• sales commissions
• shipping and tax calculations

• query builders
• migration scripts
• a CLI tool for generating boilerplate code
• a seeding feature for pre-populating tables with test data

• initial setup and configuration
• basic CRUD operations
• advance query operations

Knex.js: SQL Query Builder

• Launch: Dec, 2012
• Website
• GitHub: Used by 158.6k
• Databases: Postgres, MSSQL, MySQL, MariaDB, SQLite3, Oracle, and Amazon Redshift

knex({ a: 'table', b: 'table' })
  .select({
    aTitle: 'a.title',
    bTitle: 'b.title'
  })
  .whereRaw('?? = ??', ['a.column_1', 'b.column_2'])

SQL Output:
select `a`.`title` as `aTitle`, `b`.`title` as `bTitle` from `table`
as `a`, `table` as `b` where `a`.`column_1` = `b`.`column_2`

• It helps you abstract your code from your database’s SQL dialect, making switching easier.
• It eliminates, or greatly reduces, the chances of SQL injection attacks on your application.
• It allows easy building of queries with dynamic conditions.
• It comes with additional features and CLI tools for performing database development operations.

• connection pooling
• callback and Promise interfaces
• stream interface
• transaction support
• schema support
• migration
• seeding

$ npm install knex --save

# Then add one of the following (adding a --save) flag:
$ npm install pg
$ npm install sqlite3
$ npm install mysql
$ npm install mysql2
$ npm install oracledb
$ npm install mssql

const knex = require('knex')({
  client: 'mysql',
  connection: {
    host : '127.0.0.1',
    user : 'your_database_user',
    password : 'your_database_password',
    database : 'myapp_test'
  }
});

knex.schema.createTable('users', function (table) {
  table.increments();
  table.string('name');
  table.timestamps();
})

Outputs:
create table `users` (`id` int unsigned not null auto_increment primary key, `name` varchar(255),
`created_at` datetime, `updated_at` datetime)

knex('users').where({
  first_name: 'Test',
  last_name:  'User'
}).select('id')

Outputs:
select `id` from `users` where `first_name` = 'Test' and `last_name` = 'User'

const subcolumn = knex.raw('select avg(salary) from employee where dept_no = e.dept_no')
.wrap('(', ') avg_sal_dept');

knex.select('e.lastname', 'e.salary', subcolumn)
.from('employee as e')
.whereRaw('dept_no = e.dept_no')

Outputs:
select `e`.`lastname`, `e`.`salary`, (select avg(salary) from employee where dept_no = e.dept_no)
avg_sal_dept from `employee` as `e` where dept_no = e.dept_no

import { Knex, knex } from 'knex'

interface User {
  id: number;
  age: number;
  name: string;
  active: boolean;
  departmentId: number;
}

const config: Knex.Config = {
  client: 'sqlite3',
  connection: {
    filename: './data.db',
  },
});

const knexInstance = knex(config);

try {
  const users = await knex<User>('users').select('id', 'age');
} catch (err) {
  // error handling
}

• Bookshelf
• Objection.js
• MikroORM

• Data Mapper
• Active Record

const repository = connection.getRepository(User);.

const user = new User();
user.firstName = "Timber";
await repository.save(user);

const allUsers = await repository.find();

const user = new User();
user.firstName = "Timber";
await user.save();

const allUsers = await User.find();

Sequelize

• Launch: July 2010
• Website
• GitHub: used by 271k
• Slack
• Databases: Postgres, MySQL, MariaDB, SQLite and Microsoft SQL Server

$ npm i sequelize # This will install v6

# And one of the following:
$ npm i pg pg-hstore # Postgres
$ npm i mysql2
$ npm i mariadb
$ npm i sqlite3
$ npm i tedious # Microsoft SQL Server

const { Sequelize } = require('sequelize');

// Connect to database
const sequelize = new Sequelize('database', 'username', 'password', {
  host: 'localhost',
  dialect: /* one of 'mysql' | 'mariadb' | 'postgres' | 'mssql' */
});

// Create Model
const User = sequelize.define('User', {
  // Model attributes are defined here
  firstName: {
    type: DataTypes.STRING,
    allowNull: false
  },
  lastName: {
    type: DataTypes.STRING
    // allowNull defaults to true
  }
}, {
  // Other model options go here
});

// Create instance
const jane = User.build({ firstName: "Jane", lastName: "Doe" });
await jane.save(); // save to database

// Shortcut for creating instance and saving to database at once
const jane = await User.create({ firstName: "Jane", lastName: "Doe" });

// Find all users
const users = await User.findAll();
console.log(users.every(user => user instanceof User)); // true
console.log("All users:", JSON.stringify(users, null, 2));

// What if you wanted to obtain something like WHERE char_length("content") = 7?
Post.findAll({
  where: sequelize.where(sequelize.fn('char_length', sequelize.col('content')), 7)
});
// SELECT ... FROM "posts" AS "post" WHERE char_length("content") = 7

// A more complex example
Post.findAll({
  where: {
    [Op.or]: [
      sequelize.where(sequelize.fn('char_length', sequelize.col('content')), 7),
      {
        content: {
          [Op.like]: 'Hello%'
        }
      },
      {
        [Op.and]: [
          { status: 'draft' },
          sequelize.where(sequelize.fn('char_length', sequelize.col('content')), {
            [Op.gt]: 10
          })
        ]
      }
    ]
  }
});

SELECT
  ...
FROM "posts" AS "post"
WHERE (
  char_length("content") = 7
  OR
  "post"."content" LIKE 'Hello%'
  OR (
    "post"."status" = 'draft'
    AND
    char_length("content") > 10
  )
)

// Callee is the model definition. This allows you to easily map a query to a predefined model
const projects = await sequelize.query('SELECT * FROM projects', {
  model: Projects,
  mapToModel: true // pass true here if you have any mapped fields
});
// Each element of `projects` is now an instance of Project

Bookshelf

• Launch: March, 2013
• Website
• GitHub: Used by 22.4k
• Plugins
• Databases : PostgreSQL, MySQL, and SQLite3

• eager and nested-eager relation loading
• polymorphic associations
• support for one-to-one, one-to-many, and many-to-many relations.

$ npm install knex
$ npm install bookshelf

# Then add one of the following:
$ npm install pg
$ npm install mysql
$ npm install sqlite3

// Setting up the database connection
const knex = require('knex')({
  client: 'mysql',
  connection: {
    host     : '127.0.0.1',
    user     : 'your_database_user',
    password : 'your_database_password',
    database : 'myapp_test',
    charset  : 'utf8'
  }
})
const bookshelf = require('bookshelf')(knex)

// Define User model
const User = bookshelf.model('User', {
  tableName: 'users',
  posts() {
    return this.hasMany(Posts)
  }
})

// Define Post model
const Post = bookshelf.model('Post', {
  tableName: 'posts',
  tags() {
    return this.belongsToMany(Tag)
  }
})

// Define Tag model
const Tag = bookshelf.model('Tag', {
  tableName: 'tags'
})

// Unfortunate example of unreadable code
new User({id: 1}).fetch({withRelated: ['posts.tags']}).then((user) => {
  console.log(user.related('posts').toJSON())
}).catch((error) => {
  console.error(error)
})

• the documentation is not particularly helpful
• at the time of writing, the library hadn’t been updated for five months

Waterline

• Launch: May 2013
• Website
• GitHub: Used by 8.5k
• Documentation
• Databases : Local disk/memory, MySQL, MongoDB, and Postgres(official adapters)
• Community Database Adapters: Oracle, SAP, Cassandra, IBM, Apache Derby, Redis, Solr and more

$ npm install --save waterline

# Install database adapters
$ npm install --save sails-mysql
$ npm install --save-dev sails-disk

const Waterline = require('waterline');
const sailsDiskAdapter = require('sails-disk');
const waterline = new Waterline();

const userCollection = Waterline.Collection.extend({
  identity: 'user',
  datastore: 'default',
  primaryKey: 'id',

  attributes: {
    id: {
        type: 'number',
        autoMigrations: {autoIncrement: true}
    },
    firstName: {type:'string'},
    lastName: {type:'string'},

    // Add a reference to Pets
    pets: {
      collection: 'pet',
      via: 'owner'
    }
  }
});

waterline.registerModel(userCollection);

(async ()=>{
    // First we create a user
    var user = await User.create({
      firstName: 'Neil',
      lastName: 'Armstrong'
    });

    // Then we create the pet
    var pet = await Pet.create({
      breed: 'beagle',
      type: 'dog',
      name: 'Astro',
      owner: user.id
    });

    // Then we grab all users and their pets
    var users = await User.find().populate('pets');
  })()

var thirdPageOfRecentPeopleNamedMary = await Model.find({
  where: { name: 'mary' },
  skip: 20,
  limit: 10,
  sort: 'createdAt DESC'
});

• Documentation is mixed in with Sails.js documentation.
• At the time of writing, the library package hadn’t been updated in nine months.