Sequelize: The Basics

      No Comments on Sequelize: The Basics

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. In addition, there is also a chance to create a SQL injection vulnerability if the queries are not properly sanitized.

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 get started with Sequelize, the first step is to make sure that you have your project folder set up. Go ahead and run npm init to do so. 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.

Database

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 database. It will be called testDB in this case.

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';

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

Now that the database exists and is configured, it’s time to start working on the code. We need to import Sequelize before we can use it.

const Sequelize = require(“sequelize”);

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'
});

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
  }
});

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.

User.sync();

Inserting Records

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

var testUser = User.create({
  username: “testUser”,
  password: “pass123” 
}).then((myUser) => {
  print("Record created!");
}); 

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. Values are read from the objects as if they were properties. So, for example:

console.log(myUser.username);

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"
});

This will result in the username field of the object being updated 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);
}) 

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

SELECT * FROM Users WHERE username = 'testUser' LIMIT 1;

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);
}) 

The main advantage to specifying fields to be pulled from the database is that it results in less data being transferred. As databases are accessed either via TCP/IP or UNIX sockets, being able to limit the data passed over this link helps increase the speed of your program. The disadvantage is that Sequelize doesn’t have a way to pull in additional fields of the model once it has been retrieved from the database. 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);
}) 

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 *