Creating An AngularJS Application With Sequelize - Part 1

Introduction

Object Relational Mapping (ORM) is popular in most Object-Oriented Programming languages as a way of designing and querying relational databases by representing relational data as objects.

Some of the widely used ORMs are:

1. Eloquent - A PHP/Laravel ORM that provides a simple ActiveRecord implementation of your database.
2. Sequelize A promise-based ORM for Node.js and io.js
3. Hibernate - An open-source persistent Java ORM framework created by Gavin King in 2001

While most javascript frameworks have a bias for the NoSQL JavaScript-based MongoDB, other databases such as PostgreSQL and MySQL are still very popular.

Prerequisites

Beginner knowledge and inital setup on your local environment will be required using the following:

1. Node.js - Event-driven I/O server-side JavaScript environment based on V8.
2. npm - Node package manager.
3. AngularJS 1.5.x - Our front-end javascript framework.
4. Angular Material - AngularJS UI Component framework.
5. Bower - A package manager for our libraries.
6. PostgreSQL - Our database engine of choice.
7. Sequelize CLI - The Sequelize Command Line Interface (CLI)

Pros And Cons Of Sequelize

Sequelize supports a variety of database engines such as PostgreSQL, MySQL, Microsoft SQL Server, SQLite, and MariaDB. Being an ORM, Sequelize does not support NoSQL databases such as MongoDB and CouchDB.

Sequelize enables you to access your data from your business logic with a uniform API for performing CRUD operations as opposed to dealing with the length SQL statements. This is obviously bound to make the development of your applications faster and painless. You can, however, still run your own custom SQL statements on Sequelize if it does not meet your needs.

Sequelize also supports migrations, which is like a version control for your database

Sequelize also automatically escapes user input and therefore helps to avoid SQL injection on your behalf.

Despite all the good things that can be said about Sequelize, there is a learning curve that comes along with adopting it and the documentation can be quite overwhelming. We will be looking at a very neat and very concise way of getting things up and running with Sequelize.

A Look at Our Application - Bookmark

Bookmark is an author management application that stores the details of an author as well as the details of all the books that they have written.

Bookmark should allow us to perform actions such as:

1. Creating a new author.
2. Viewing a list of added authors.
3. Adding published books to an author’s profile.
4. Updating author and published book details.
5. Deleting an Author details (and subsequently their published books).
6. Deleting a published book.

This simple application should be enough to get our hands dirty with CRUD operations using Sequelize and work with relationships between PostgreSQL tables.

Directory Structure

The bookmark application directory structure will look like this.

├── index.js
├── gulpfile.js
├── package.json
├── bower.json
├── .bowerrc
├── .sequelizerc
├── app/
    ├── images/
    ├── js/
    ├── styles/
    ├── views/
    ├── app.js
    ├── index.jade
├── server/
        ├── config/
            ├──config.js
        ├── controllers/
            ├── authors.js
            ├── books.js
        ├── migrations/
        ├── models/
            ├── index.js
            ├── book.js
            ├── author.js
        ├── seeders/

Project Setup

To get started, we will need to add the PostgresSQL client for JavaScript to our dependencies.

npm install --save pg pg-hstore sequelize

Next, we will add a .sequelizerc file that contains our sequelize default configurations.

var path = require('path');

module.exports = {
  'config': path.resolve('./', 'config/config.js'),
  'migrations-path': path.resolve('./', 'server/migrations'),
  'seeders-path': path.resolve('./', 'server/seeders'),
  'models-path': path.resolve('./', 'server/models')
};

Run the sequelize initialization command to get our sequelize bootstrap started. If it does not work, refer to the sequelize CLI module documentation and make sure it is properly set up for your environment.

sequelize init

This should create a few directories in our server directory:

• Config - Contains a config.json file from which the database details will be read from. To use the environment variables which are also supported, we will be using config.js instead.
• Migrations - Contains our database migrations.
• Models - Contains our models. Sequelize creates an index.js file inside models that loads all the models that we add to this directory. Be sure to update models/index.js to read config variables from the config/config.js file as opposed to reading from the default config/config.json.
• Seeders - Contains seed data for the development environment

We mentioned that we will replace the config.json file with config.js file and populate it with the database credentials as follows:

module.exports = {
    development: {
      url: 'postgres://postgres:password@localhost:5432/bookmark',
      dialect: 'postgres'
  },
    production: {
      url: process.env.DATABASE_URL,
      dialect: 'postgres'
  },
    staging: {
      url: process.env.DATABASE_URL,
      dialect: 'postgres'
  },
    test: {
      url: process.env.DATABASE_URL || 'postgres://postgres:password@localhost:5432/bookmark_test',
      dialect: 'postgres'
  }
};

Notice that we can use the username, password format, or URL format to create a connection to the database. I prefer the URL as it is shorter. The dialect specifies the database driver we will be using. In this case, it will be Postgres.

Be sure to update server/models/index.js to create the connection using the URL.

'use strict';

var fs        = require('fs');
var path      = require('path');
var Sequelize = require('sequelize');
var basename  = path.basename(module.filename);
var env       = process.env.NODE_ENV || 'development';
var config    = require(__dirname + '/../config/config')[env];
var db        = {};

//Create a Sequelize connection to the database using the URL in config/config.js
if (config.use_env_variable) {
  var sequelize = new Sequelize(process.env[config.use_env_variable]);
} else {
  var sequelize = new Sequelize(config.url, config);
}

//Load all the models
fs
  .readdirSync(__dirname)
  .filter(function(file) {
    return (file.indexOf('.') !== 0) && (file !== basename) && (file.slice(-3) === '.js');
  })
  .forEach(function(file) {
    var model = sequelize['import'](path.join(__dirname, file));
    db[model.name] = model;
  });

Object.keys(db).forEach(function(modelName) {
  if (db[modelName].associate) {
    db[modelName].associate(db);
  }
});

//Export the db Object
db.sequelize = sequelize;
db.Sequelize = Sequelize;

module.exports = db;

Testing The Database Connection

Once we have the project set up, proceed to create a PostgreSQL database called bookmark.

We can then test to see if the connection to the database was successful using the authenticate() method.

Simply add the following code in index.js.

var models = require('./server/models/');
models.sequelize
  .authenticate()
  .then(function () {
    console.log('Connection successful');
  })
  .catch(function(error) {
    console.log("Error creating connection:", error);
  });

Creating Our First Model And Migration

Once we have our project set up and have verified our connection to the database, let’s proceed to create our first model and migration in the server directory.

A map between the model and the database table is done using the define method which takes in the table name as a parameter, an attributes object, and an options attribute.

sequelize.define('name', {attributes}, {options})

Fortunately, the Sequelize CLI tool provides us with a very handy command-line tool to create a model and migration all in one command. Simply run:

sequelize model:create --name=Author --attributes name:string,bio:text --underscored

This will add the following files to our application:

• Model - server/models/author.js
• Migration file - server/migrations/2016..-create-author.js

The --underscored parameter specifies that we would like our timestamp fields (created_at and updated_at) to appear in the underscore format. Notice that the author model does not have the underscored attribute option so it still expects our timestamps to be createdAt and updatedAt. Let’s fix that.

'use strict';
module.exports = function(sequelize, DataTypes) {
  //Define the Author model
  var Author = sequelize.define('Author', {
    //Define the data types of the Author fields
    name: DataTypes.STRING,
    bio: DataTypes.TEXT
  }, {
    //set the timestamps to be underscored: (created_at, updated_at)
    underscored: true,
    classMethods: {
      associate: function(models) {
        // associations can be defined here
      }
    }
  });
  return Author;
};

The author model will then give us access to methods such as findAll(), create(), update() and destroy().

Running Migrations.

In the previous section, we were able to create a connection to the bookmark database and create a model and migrations for the Authors table.

This means that our table does not exist yet. Running the migrations is just as easy. Simply run sequelize db:migrate

This will add an empty Authors table and another SequelizeMeta to keep track of the migrations.

Adding Author CRUD Operations

Now that we have a model and a table to query data from, we will proceed to create the CRUD operations from which our front end will be able to query.

Create a server/controllers directory and add an authors.js file to hold our query methods. We will create methods to perform the following actions:

• index() - List of all authors using the findAll() method which takes optional parameters such as attributes, where filters, Pagination/Limits, data ordering, and Relations/Associations. Read more about querying options here.
• show() - Details about one author using the findById() method takes in an id to get a record by.
• create() - Add a new author using the create() method which takes in an array of the record to create.
• update() - Modify existing author details using the update() method which takes an array of the data to update, and a where object to filter records to update.
• delete() - Delete an author from the platform using the destroy() method which takes an object of parameters inside a where object.

Each of these methods from the Author model returns a promise which we then use to return a 200 status code message and the queried data. If an error occurs, we catch the error and return a 500 status code to the front end.

Author = require('../models/').Author;
Book = require('../models/').Book;

module.exports= {
  // Get a list of all authors using model.findAll()
  index(req, res) {
    Author.findAll({
      include: Book
    })
      .then(function (authors) {
        res.status(200).json(authors);
      })
      .catch(function (error) {
        res.status(500).json(error);
      });
  },

  // Get an author by the unique ID using model.findById()
  show(req, res) {
    Author.findById(req.params.id, {
      include: Book
    })
    .then(function (author) {
      res.status(200).json(author);
    })
    .catch(function (error){
      res.status(500).json(error);
    });
  },

  // Create a new author using model.create()
  create(req, res) {
    Author.create(req.body)
      .then(function (newAuthor) {
        res.status(200).json(newAuthor);
      })
      .catch(function (error){
        res.status(500).json(error);
      });
  },

  // Edit an existing author details using model.update()
  update(req, res) {
    Author.update(req.body, {
      where: {
        id: req.params.id
      }
    })
    .then(function (updatedRecords) {
      res.status(200).json(updatedRecords);
    })
    .catch(function (error){
      res.status(500).json(error);
    });
  },

  // Delete an existing author by the unique ID using model.destroy()
  delete(req, res) {
    Author.destroy({
      where: {
        id: req.params.id
      }
    })
    .then(function (deletedRecords) {
      res.status(200).json(deletedRecords);
    })
    .catch(function (error){
      res.status(500).json(error);
    });
  }
};

Seqeulize Relations / Assocations

Database tables often relate to data in other tables. In our case, an author can have many books. But each book can only belong to one user. This presents a One to Many relationship. Let’s take a minute to look at how Sequelize implements database relationships.

One-To-One Association (1:1)

One-To-One associations are associations between exactly two models connected by a single foreign key.

This is the most basic form of relationship where an entity, say a User may only have one relationship to another entity, say Phone and the reverse holds true that a Phone belongs to only one user.

One-To-One association is achieved using belongsTo() and hasOne() methods. Both can be used in different cases by setting one as the source and the other as the target.

In the example below, the Phone model acts as the source and the User model acts as the target.

var User  = sequelize.define('user', {/* attributes */});
var Phone  = sequelize.define('phone', {/* attributes */});
Phone.belongsTo(User);
User.hasOne(Phone);

One-To-Many Association (1:M)

One-To-Many comes into play when one source, in our case Author, has multiple targets, say Books. This is achieved using the hasMany() method which we will be looking at as we set up the Books model.

var Author  = sequelize.define('Author', {/* attributes */});
var Book  = sequelize.define('Book', {/* attributes */});
Author.hasMany(Book);

This will add a author_id foreign key field in our case or authorId if underscored is set to false.

Many-To-Many Association (M:M)

One-To-Many association is used when multiple sources are linked to multiple targets. In our application, this could be many books can have many genres and many genres can also belong to many books.

Book.belongsToMany(Genre, {through: 'BookGenre'});
Genre.belongsToMany(Book, {through: 'BookGenre'});

This will add a new pivot table BookGenre that has book_id and genre_id foreign keys. You can also rename your Model and define the foreign key names in the many-to-many relationship.

This adds additional methods to both the Book and Genre models that make it easy to query data.

• The Book model will have methods such as getGenres and addGenre
• The Genre model will have methods such as setBooks and getBooks

Our bookmark application does not have a many-to-many relation between Books and Genres. Can you challenge yourself to add this relation?

Creating A One-To-Many Relation Between Authors and Books.

To create this relation, we will need to create the books model and migration first. We have already gone through how to create both for Authors. Can you challenge yourself to create the Books model?

Here is the sequelize CLI command to create the model and migration. Our Book will have the book name, ISBN number, publication date, description id, and the author id.

sequelize model:create --name=Book --attributes name:string,isbn:integer,publication_date:date,description:text,author_id:integer --underscored

Update the new Book model to use snake_case for the timestamps using the underscored attribute.

Next, we will add the association in the classMethods object defined in our Author model. onDelete: 'cascade' guarantees that when an author is deleted, the attached books are deleted as well.

'use strict';
module.exports = function(sequelize, DataTypes) {
  //Define the Author model
  var Author = sequelize.define('Author', {
    //Define the data types of the Author fields
    name: DataTypes.STRING,
    bio: DataTypes.TEXT
  }, {
    //set the timestamps to be underscored: (created_at, updated_at)
    underscored: true,
    classMethods: {
      associate: function(models) {
        //An author can have many books.
        Author.hasMany(models.Book, {
          onDelete: 'cascade'
        });
      }
    }
  });
  return Author;
};

We do not need to specify the author_id foreign key because this is the default key the Author model expects. The last thing we need to do is see the relation in action! But how do we do this?

Remember the Author controller we created earlier? We can now return an author with the list of all the books under their name by adding the include option in findById for the show() method and findAll for the index method.

Author = require('../models/').Author;
Book = require('../models/').Book;

module.exports= {
  //Get a list of all authors using model.findAll()
  index(req, res) {
    Author.findAll({
      //Return all books that have a matching author_id for each Author
      include: Book
    })
      .then(function (authors) {
        res.status(200).json(authors);
      })
      .catch(function (error) {
        res.status(500).json(error);
      });
  },

  //Get an author by the unique ID using model.findById()
  show(req, res) {
    Author.findById(req.params.id, {
      //Return all books that have a matching author_id for the author
      include: Book
    })
    .then(function (author) {
      res.status(200).json(author);
    })
    .catch(function (error){
      res.status(500).json(error);
    });
  },
  ...

When our frontend makes a GET request to index or show, the returned request will contain an array of books (Empty since we do not have the books controller up and running yet).

Start a Node.js server using express on your localhost in index.js and call the index method when a GET request is made to /authors. You can then use postman to make the request since we do not have a front end to consume our controller methods.

var app = require('express')(),
  authors = require('./server/controllers/authors');

app.get('/authors', authors.index);

app.set('port', process.env.PORT || 8000);
app.listen(app.get('port'), function () {
  console.log("Magic happens on port", app.get('port'));
});

Adding Books Controller CRUD Operations

Let’s go ahead and add the CRUD operations to query data from the Books model. We will be using the exact approach we used for servers/controllers/authors.js. Are you up for the challenge?

Here is an excerpt of how servers/controllers/books.js looks like.

Book = require('../models/').Book;

module.exports= {
  //Get a list of all books using model.findAll()
  index(req, res) {
    Book.findAll()
      .then(function (books) {
        res.status(200).json(books);
      })
      .catch(function (error) {
        res.status(500).json(error);
      });
  },

  //Get a book by it's unique ID using model.findById()
  show(req, res) {
    Book.findById(req.params.id)
    .then(function (book) {
      res.status(200).json(book);
    })
    .catch(function (error){
      res.status(500).json(error);
    });
  },
  ...

Adding API Endpoints

The last part of this tutorial involves adding the API endpoints from which our front end will be able to query data from. Since our app is simple, we will simply add them in index.js:

var app = require('express')(),
  authors = require('./server/controllers/authors'),
  books = require('./server/controllers/books');;

app.get('/authors', authors.index);
app.get('/authors/:id', authors.show);
app.post('/authors', authors.create);
app.put('/authors', authors.update);
app.delete('/authors', authors.delete);

app.get('/books', books.index);
app.get('/books/:id', books.show);
app.post('/books', books.create);
app.delete('/books', books.delete);

app.set('port', process.env.PORT || 8000);
app.listen(app.get('port'), function () {
  console.log("Magic happens on port", app.get('port'));
});

Once we add a sample user and book to our bookmark database, go ahead and make a GET request to /authors. This time we should have a sample book on the author response.

Conclusion

In this article, we were able to achieve the following:

• Introduce Sequelize ORM for node.js and highlight the pros and cons
• Setup the server side of the Bookmark application.
• Create models and relations/associations for authors and nooks.
• Create and run migrations for authors and books tables.
• See the relation in action.

We also threw in a few challenges that should help you explore Sequelize further. In the second part, we will create a simple frontend using Angular and Angular Material.

While this has been a gentle introduction to Sequelize, there is a lot more to learn. Feel free to engage in the comment section.

You can also follow what we have been doing on the GitHub repo for bookmark.