ORM

ORM libraries provide an object-oriented layer of abstraction on top of database (typically SQL) interaction. In this lab we're going to look at the popular sequelize ORM for Node.js projects. It's the old fuddy-duddy choice now, but still w/ 1.6M weekly downloads, and a lot of established codebases out there.

Whether to use an ORM or not is a contentious topic, but the reality is that you're mostly going to use it or not based on whether the project you're on uses it or not. You will see it if you stick in web/mobile development. My advice is that you absolutely need to know SQL, then what you choose to use over that becomes preference not necessity.

• An article on Why you shouldn't use ORMs
• Prisma, more recent hotness
• Knex.js, a query builder halfway between ORM and raw SQL

Grab the repo containing a simple database for this lab here:

https://classroom.github.com/a/PXavaCes

Open the database in DBBrowser and look at the two tables we'll be using.

Connect

In the repo directory with the database file, create a new project and install the sqlite driver and sequelize.

npm init
npm install sqlite3 sequelize

Create a test.js file to test connecting to the database using the code below.

// import the library as a class
const Sequelize = require('sequelize');

// instantiate the library for use, specifying the type of database and (in the case of sqlite) the database file
const sequelize = new Sequelize({
  dialect: 'sqlite',
  storage: 'addresses.sqlite'
});

// need an async function to use await (could also use the .then/.catch promise syntax)
const test = async () => {

  // connect to the database to verify your installation and configuration
  // (other db drivers "authenticate" w/ username/password, sqlite does not)
  try {
    await sequelize.authenticate();
    console.log("Connected to database");
  } catch(err) {
    console.log("Failed to connect: " + err);
  }
}

test();

Run the file there at the command prompt with:

node test.js

Generate Models

ORMs use a class, called a model, to represent each table in the database. Instead of writing SQL queries, your code will call methods on the ORM models. One of the features that makes ORM popular is automatic synchronization between the database and the models. You can:

1. Automatically generate/update JavaScript models from an existing database - OR -
2. Define JavaScript models in code and automatically create/update a database schema

Since we have already been working with databases and SQL already, we'll take option one. sequelize uses a separate tool, sequelize-auto, for generating models from a database. Documentation is available on the github page here: https://github.com/sequelize/sequelize-auto.

npm install sequelize-auto

This installs it into node_modules, including a script to run at the command line. To generate our models, run this command:

      // on windows with backslashes
      node .\node_modules\sequelize-auto\bin\sequelize-auto -e sqlite -d addresses.sqlite -o "./models"

      // on mac/linux with forward slashes
      node node_modules/sequelize-auto/bin/sequelize-auto -e sqlite -d addresses.sqlite -o "./models"
    

• node_modules/sequelize-auto/bin/sequelize-auto: the path to the script
• -e sqlite: the type of db
• -d addresses.sqlite: the database to connect to (for a database server, we'd specify hostname, username, etc)
• -o "models/": where to put the generated model files

Open the newly created models/ subdirectory and you will see an init-models.js file and a model file for each table in the database.

The model files contain the sequelize specification of each table. If you chose to, you could write these files yourself and maintain them in code, and run a script to automatically create/update the database instead.

models/persons.js

const Sequelize = require('sequelize');
module.exports = function(sequelize, DataTypes) {
  return sequelize.define('Person', {
    id: {
      autoIncrement: true,
      type: DataTypes.INTEGER,
      allowNull: true,
      primaryKey: true
    },
    first_name: {
      type: DataTypes.TEXT,
      allowNull: true
    },
    last_name: {
      type: DataTypes.TEXT,
      allowNull: true
    }
  }, {
    sequelize,
    tableName: 'Person',
    timestamps: false
  });
};

Querying the Database

Now that you have model definitions, you can import them into your code and use them to create, update, or query the database in an object-oriented way. Under the hood, SQL is being generated and sent to the database.

In your test.js file, you need to import the models. After you create the Sequelize instance, add these lines to use the convenient init-models file to load the Person model.

// init-models contains a function to load all the models in that directory
const initModels = require('./models/init-models');

// call it and use destructuring bind to get the ones your want
const { Person } = initModels(sequelize);

Now, in the asynchronous test function after you connect to the database, you can perform queries against the database.

For each result that you retrieve from the database, use this code to dump it to the console in easily readable JSON.

console.log(JSON.stringify(resultName, null, 2));

Add the following queries to test and dump the results.

1. Follow the guide here to perform the three most basic SELECT queries: findAll, findByPk, and findOne
2. Following the model-querying-basics guide:
   1. INSERT a new person
   2. UPDATE that person's first name by primary key
   3. DELETE that new person

Associations (Foreign Keys)

In init-models, the two lines below are automatically included to specify the foreign key relationship between those two tables. In sequelize, this referred to as an association.

Associations allow you to use the models to query more complex data the way that you use joins in SQL. In this case, we might be interested in retrieving a person and all their phone numbers.

Lazy loading refers to waiting to retrieve data (the phone numbers) until you need it. This is the same as making on SQL query to get the person and then later making another SQL query to get the phone numbers by person_id, only it's done through the model.

For any one of the people in the database you already have, retreive their phone numbers by calling the getPhoneNumbers method and dump them to the console. This method is automatically generated by sequelize based on the as argument to Person.hasMany in init-models. You could change the naming convention if you were writing those models yourself.

Eager loading refers to retrieving all the data (the phone numbers) at once. To do this, you specify an include parameter to your query as below. Again, the naming conventions here are based on what sequelize-auto generated in init-models. For this to work, you'll also need to import PhoneNumbers (add it to the detructuring bind where you call initModels).

Show all the people in the database with their phone numbers included in the objects.

const peopleWithNumbers = await Person.findAll({include: {model: PhoneNumber, as: "PhoneNumbers"}});

Two things should strike you about the output from this call:

1. This looks a lot like the last assignment server route example where we put the sections into the courses. As we discussed, nested data is the common way to organize in code.
2. Under the hood, this could be a single join query or multiple queries. The debugging output from sequelize shows you that it is gaining the benefit of a single join query.