Sequelize: The Taming of the SQL

gduple

Grant

Posted on July 20, 2020

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');
Enter fullscreen mode Exit fullscreen mode

...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'
});
Enter fullscreen mode Exit fullscreen mode

...or passing a connection URI:

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

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 *
});
Enter fullscreen mode Exit fullscreen mode

...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'
});
Enter fullscreen mode Exit fullscreen mode

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 });
Enter fullscreen mode Exit fullscreen mode

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();
Enter fullscreen mode Exit fullscreen mode

...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']
});
Enter fullscreen mode Exit fullscreen mode

And a similar pattern is available for excluding specified attributes:

Order.findAll({
  attributes: { exclude: ['lastName'] }
});
Enter fullscreen mode Exit fullscreen mode

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

Order.findAll({
  where: {
    orderNumber: 51
  }
});
Enter fullscreen mode Exit fullscreen mode

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
    }
  }
});
Enter fullscreen mode Exit fullscreen mode

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]
Enter fullscreen mode Exit fullscreen mode

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.

💖 💪 🙅 🚩
gduple
Grant

Posted on July 20, 2020

Join Our Newsletter. No Spam, Only the good stuff.

Sign up to receive the latest update from our blog.

Related