Sequelize: The Taming of the SQL

According to its source documentation, Sequelize is a 'promise-based ORM for Postgres, MySQL, MariaDB, SQLite and Microsoft SQL Server." ORM is short for object-relational mapping, which Wikipedia calls "a programming technique for converting data between incompatible type systems using object-oriented programming languages." In short, an ORM allows the user to package data in objects for database interaction, (mostly) circumventing the need for structured query language (SQL) commands. ORM's like Sequelize are essentially a translation tool between two likely very different computer programming languages. The "promise-based" part of Sequelize's description refers to javascript's Promise object, which allows for sequential, or "asynchronous" operations to be performed by returning a placeholder for a value that will eventually be determined.

Sequelize use begins with the creation of a new Seqeulize instance:

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

...and a connection to a database, which can take one of two forms:
Passing parameters to the Sequelize constructor:

const sequelize = new Sequelize('database', 'username', 'password', {
  host: 'localhost',
  dialect: 'mysql'
});

...or passing a connection URI:

const sequelize = new Sequelize('postgres://user:pass@example.com:5432/dbname');

Once the plumbing is hooked up, models can be created for tables and their entries. The documentation defines a model as "a class that extends Sequelize.Model," and takes attributes and options. Models, like connections, can be one of two forms:
Using sequelize.define:

const Order = sequelize.define('order', {
  firstName: {                    // * attributes *
    Sequelize.STRING
  },
  lastName: {
    Sequelize.STRING
  }
  orderNumber: {
    type: Sequelize.INTEGER,
    primaryKey: true
  }
}, {
  language: 'en',                 // * options *
});

...or by creating a class that extends Model and calling init:

const Model = Sequelize.Model;
class Order extends Model { }
Order.init({
  firstName: {
    type: Sequelize.STRING,       // * attributes *
  },
  lastName: {
    type: Sequelize.STRING
  },
  orderNumber: {
    type: Sequelize.INTEGER,
    primaryKey: true
  }
}, {
  sequelize,                      // * options *
  modelName: 'order',
  language: 'en'
});

Once the initial work of creating a model is complete, insertions can be relatively simple:

const order17 = Order.create({ firstName: 'Bob', lastName: 'Johnson', orderNumber: 17 });

Notice the use of an arguments object as the parameter.

Meanwhile, a simple lookup query to retrieve an entire table can be as simple as:

const orders = Order.findAll();

...or as complex as any query written in SQL. Let's take a look at a few more examples:

To select only certain columns, attributes can be defined in an array:

Order.findAll({
  attributes: ['firstName', 'lastName']
});

And a similar pattern is available for excluding specified attributes:

Order.findAll({
  attributes: { exclude: ['lastName'] }
});

And among the most fundamental of database techniques is the where statement, used for filtering returns:

Order.findAll({
  where: {
    orderNumber: 51
  }
});

A critical component of the concept of applying where is the operator. In SQL, many operators are mathematical symbols–i.e. =, >, +=, etc., while others are strings–all, any, in, or, and more. Since Sequelize is object-based, operators are defined as properties of the Op object and are all strings. In fact, the example above actually had an implicit operator, assumed to be "equals", as such:

Order.findAll({
  where: {
    orderNumber: {
      [Op.eq]: 51
    }
  }
});

Here are some common operator examples in context:

[Op.and]: {a: 3}           // AND (a = 3)
[Op.or]: [{a: 7}, {a: 7}]  // (a = 7 OR a = 8)
[Op.gt]: 4,                // > 4
[Op.gte]: 4,               // >= 4
[Op.lt]: 10,               // < 10
[Op.lte]: 10,              // <= 10
[Op.ne]: 5,               // != 5
[Op.eq]: 3,                // = 3
[Op.not]: true,            // IS NOT TRUE
[Op.between]: [4, 8],     // BETWEEN 4 AND 8
[Op.notBetween]: [8, 10], // NOT BETWEEN 8 AND 10
[Op.in]: [1, 2],           // IN [1, 2]
[Op.notIn]: [1, 2],        // NOT IN [1, 2]

While this brief introduction leaves much to be learned, I hope your interest in database management has been piqued–as was mine! Arming yourself with a basic understanding of Sequelize's functionality such as this could go a long way to broadening your understanding of database management techniques.