This Sequelize tutorial covers the basics of what Sequelize is, as well as what benefits it provides in software development. Traditionally, writing software to interact with a database requires manually writing SQL code which will be executed against it. However, this comes with several issues. First of all, writing SQL inline with the rest of a program’s code is messy. It’s also prone to mistakes. The syntax of SQL is radically different than that of traditional programming languages. This makes it difficult to mentally shift between the two. Much of this code is also standard boilerplate. There is little benefit in writing it manually.
This is where libraries such as Sequelize come in.
What is Sequelize?
Sequelize is an object relation manager (ORM) for Node.JS. It allows you to automatically hydrate objects from a database and use them in your software without writing any SQL code. Sequelize also simplifies the process of syncronizing changes to the objects back to the database.
Installing Sequelize
To get started, we first need to set up the project folder. Run the following commands to do so:
mkdir sequelize-tutorial cd sequelize-tutorial npm init -y npm install sequelize npm install mysql2
Installing MariaDB
We’ll also need a database. For this tutorial, we will install MariaDB directly on the system. On Debian Linux, run the following command to do so:
sudo apt install mariadb-server mariadb-client sudo systemctl start mariadb sudo systemctl enable mariadb
Now that we have the server installed and running, we need to create the database. For this tutorial we’re going to create a table to store a list of user accounts. Each entry within the will have a username, a password, and a description.
To do so, we first need to log into the root account of our MariaDB server.
mysql -u root -p
Now create the testDB database.
create database testDB;
Next, 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
Now that the database is up and running, it’s time to start working on our code. 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)Take note of the data types defined here. Sequelize.STRING gets dynamically defined as a string in MySQL. There are equivalent types for things such as numbers, dates and times.
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();Code language: CSS (css)Inserting Records
Now, let’s insert our first object into the database.
var testUser = User.create({
username: “testUser”,
password: “pass123”
}).then((myUser) => {
console.log("Record created!");
}); Code language: JavaScript (javascript)The create() function serves two purposes. First, it creates the object in the program. Second, it automatically syncs the newly created object with the database. The object returned in the promise is the same as the one created. This time however, it also includes the database ID of the row containing its data.
For performance reasons, you may not wish to automatically commit this object to MySQL. To avoid this, use build() in place of create(). In this case, the object is created and queued to be committed.
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:
console.log(myUser.username);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 a query similar to the following:
SELECT * FROM Users WHERE username = 'testUser' LIMIT 1;Code language: JavaScript (javascript)In Sequelize there are both the find and findOne functions. Each differs in how many values they return. Internally, findOne appends a limit to the query. The end result is a single object, which is returned without being wrapped in an array. On the other hand, findAll returns each of the objects it finds in an array. By default, it appears no limits to its query, meaning that it could potentially run very slowly, depending on how many fields are returned.
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 Sequelize 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.
Be First to Comment