Getting Started With Sequelize

      No Comments on Getting Started With Sequelize

Sequelize greatly helps with the management of large relational databases. There are many reasons why this is necessary. For one, embedding SQL within strings is tedious. There’s a lot that can go wrong with this approach. Not only do you have to write the queries by hand, but you also have to convert the rows from the database into easier to use objects. As a result, the amount of work needed to store and retrieve data from the context of a program is overwhelming.

There are many different ways to handle this problem. However, most do little more than hide the issue. For example, separating database calls into functions can hide the queries, but this only sweeps the problem under the rug. The code within these functions are still an unmanageable mess, leading to longer development times. Additionally, improperly sanitized queries can lead to SQL injection vulnerabilities.

A much better solution is Sequelize. It is an object relational mapping library for Node.JS. In this regard, it automatically converts data contained within a database to objects easily usable in your code. However, even though Sequelize removes much of the complication associated with database development, it does not limit the programmer’s power. If necessary, you can still write raw queries and Sequelize will transparently feed them into the database, returning the result just as Mysqli would.

Installing Sequelize

To start, set up your project folder by making a new directory and running npm init. Next, run npm install sequelize or sudo npm install -g sequelize if you’d prefer to install Sequelize globally. Next, you’ll also need to install Node’s mysql2 package. This is done via npm install mysql2 or npm install -g mysql2.


Now we need a database to access. For this tutorial we’re going to create a table to store a list of user accounts. Each entry within the table will have a username, a password, and a description.

First, log in to your MySQL root account:

mysql -u root -p

Now create the testDB database.

create database testDB;

Now create a user and grant them access.

create user ‘testUser’@‘localhost’ identified by ‘password’; grant all privileges on testDB.* to 'testUser'@'localhost';
Code language: JavaScript (javascript)

At this point we have our database created and a user to access it. We do not need to create any tables as Sequelize will automatically create them for us.

Getting Started

We can start working on the code now that we have everything configured. We need to import Sequelize before we can use it.

const Sequelize = require(“sequelize”);
Code language: JavaScript (javascript)

And now that we have access to the module, use it to connect to the database with the credentials we specified earlier on the command line:

const databaseConnection = new Sequelize('testDB', 'testUser', 'password', { host: 'localhost', dialect: 'mysql' });
Code language: JavaScript (javascript)

Now that we have a connection to the database, it’s time to define our models.

User = databaseConnection.define("Users", { username: { type: Sequelize.STRING }, password: { type: Sequelize.STRING }, description: { type: Sequelize.STRING } });
Code language: JavaScript (javascript)

Now that we have defined the model, we need to synchronize it to the database. Doing this will create the Users table if it does not already exist.

Code language: CSS (css)

Inserting Records

We are now ready to insert our first record into the database.

var testUser = User.create({ username: “testUser”, password: “pass123” }).then((myUser) => { console.log("Record created!"); });
Code language: JavaScript (javascript)

At this point, myUser holds a reference to this object which is automatically stored within the database. If you did not want this behavior, you would use the same code, but with the build function rather than create.

Now that we can put new objects into the database, let’s learn how to retrieve them again.

Using Records

Now that we are able to insert records into our database, the next step is to be able to read and change their values. We can read from the objects as if they were properties. So, for example:

Code language: CSS (css)

Will print “testUser” to the screen. Updating is not automatic however. In order to actually change values within the database, you use myUser’s update method:

myUser.update({ username: "newUsername" });
Code language: CSS (css)

This code will update the username field in the database.

Retrieving Records

The next question is how to retrieve already existing records from the database. Sequelize provides an abstraction layer over retrieving objects from the database. The findOne function retrieves a single object, using the where parameter to specify how it should be found.

User.findOne({ where: { username: “testUser” } }).then(function(user){ console.log(“Username: “ + user.username); console.log(“Password: “ + user.password); })
Code language: JavaScript (javascript)

Sequelize would generate SQL code similar to the following for this code:

SELECT * FROM Users WHERE username = 'testUser' LIMIT 1;
Code language: JavaScript (javascript)

In Sequelize there are both the find and findOne functions differing, obviously, in how many values they return. findOne appends a limit to the query, and returns the one object found (provided there was one) as the whole result. On the other hand, findAll returns all of the results it finds as an array.  This means that it can potentially be much slower depending on how many results the database returns.

Parameters for Record Selection

When writing queries, developers often want to retrieve specific columns and rows from the database. Sequelize supports this through passing additional parameters to its find and findAll functions. For example, if we wanted to only retrieve the description of a user we would do so as follows:

User.findOne({ { attributes: ['description'], where: { username: “testUser” } } }).then(function(user){ console.log(“Description: “ + user.description); })
Code language: JavaScript (javascript)

Pulling individual fields from the database helps us to reduce bandwidth consumption. The less we have to work over the network, the faster our software will be. There is a major downside to this approach however. Retrieving additional fields requires pulling the entire object from the database a second time. Therefore, it is important to keep a reference to the primary key of the model with it. This way, you can retrieve a new instance of the User using its primary key in a where clause.

Manually Limiting Queries

We have already discussed the difference between findOne and findAll. With this in mind, it is still possible to apply your own limits to queries. You can retrieve a specific list of records from a table, starting from a specific point and continuing for a given number of records.

User.findOne({ { offset: 0, limit: 1, } }).then(function(user){ console.log(“Username: “ + user.username); console.log(“Password: “ + user.password); })
Code language: JavaScript (javascript)

In summary, using Sequelize you are able to greatly increase productivity by setting aside direct database querying and instead replacing it with the managed approach provided by using an ORM. Through Sequelize, you are able to treat your database as a collection of objects rather than a series of rows in a table. In doing so you are able to more quickly develop your software. In the next tutorial we will be discussing how to use Sequelize to bring in the relational aspects of the database.

Leave a Reply

Your email address will not be published. Required fields are marked *