Sequelize: The Taming of the SQL
Published: 2020-07-20 13:30:54

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](https://en.wikipedia.org/w/index.php?title=Object-relational_mapping&oldid=952535425) 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:
```javascript
const { Sequelize } = require('sequelize');
```
...and a connection to a database, which can take one of two forms:
Passing parameters to the Sequelize constructor:
```javascript
const sequelize = new Sequelize('database', 'username', 'password', {
  host: 'localhost',
  dialect: 'mysql'
});
```
...or passing a connection URI:
```javascript
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`:
```javascript
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`:
```javascript
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:
```javascript
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:
```javascript
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:
```javascript
Order.findAll({
  attributes: ['firstName', 'lastName']
});
```
And a similar pattern is available for excluding specified attributes:
```javascript
Order.findAll({
  attributes: { exclude: ['lastName'] }
});
```
And among the most fundamental of database techniques is the `where` statement, used for filtering returns:
```javascript
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:
```javascript
Order.findAll({
  where: {
    orderNumber: {
      [Op.eq]: 51
    }
  }
});
```
Here are some common operator examples in context: 
```javascript
[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.