This content originally appeared on Level Up Coding - Medium and was authored by Tara Prasad Routray
Learn the basics of Sequelize and how you can integrate it with Node.js or Express.
Sequelize is a popular ORM for Node.js which allows managing the SQL databases easily. It supports various databases like — Postgres, MySQL, MariaDB, SQLite, and Microsoft SQL Server. It utilizes Node.js and JavaScript object syntax to accomplish the mapping with databases. It provides reliable transaction support, relations, eager and lazy loading, read replication and much more.
In this article, I will demonstrate how you can integrate the Sequelize ORM in a few easy steps. We’ll be using the MySQL database throughout the process.
Benefits of Using Sequelize
The following are the benefits of using Sequelize:
- Avoid writing raw SQL queries.
- Avoid SQL injection vulnerabilities.
- Write more consistent code.
- Get powerful tooling for migration.
Install Sequelize Through NPM
To add the Sequelize ORM to your Node.js or Express app, you need to execute the following command in the application root directory.
npm install --save sequelize
This will install Sequelize and add it as a dependency in the composer.json file.
Install Database Driver
Since we will be working with the MySQL database, we’ll install the driver for MySQL only. You need to run the following command in the application root directory to enable the support for database connectivity.
npm install --save mysql2
For other databases, you can run the appropriate command from the following list.
npm install --save pg pg-hstore # Postgres
npm install --save mariadb # MariaDB
npm install --save sqlite3 # Sqlite
npm install --save tedious # Tedious
Establish Database Connection
To connect to a database, you need to open a connection stream between Sequelize and the running MySQL Daemon. So, create a directory named config in the application root, and create a file named database.js inside the config directory. Refer to the following code snippet to understand how you can import Sequelize, create an instance with authentication details, and export it so that it can be used to establish a database connection.
After you add the above code to the database.js file, you have to import it into the app.js file, which is the primary file used for starting and managing the node server. Refer to the following code snippet to understand how you can import the database.js file and establish the database connection.
Start your node server by running the following command.
node app.js
After executing the above command, your database connection will be established, and the node server will be up and running. A similar message will show in the terminal.
Testing the database connection..
Executing (default): SELECT 1+1 AS result
Connection has been established successfully.
Server is up and running at: http://localhost:3000
Close Database Connection
By default, Sequelize will always keep the connection open, and use the same connection for all queries. If you need to close the connection, you can run the following command.
db.close();
Here, db is the name of the constant (which holds the Sequelize instance) created in the app.js file.
Create a Model and Schema
Model is the core of Sequelize. It is an abstraction that represents a table in the database. A model provides various details about a table, such as the name of the table, the columns it has, and the data types of the columns.
You can give a custom name to a model. It doesn't need to be the same as of table’s name. It is recommended to keep a singular name for a model (such as Post), while a table can have a pluralized name (such as app_posts).
A model can be represented in two ways:
- By calling sequelize.define()
- By extending the Model class and calling init()
Now, create a directory named models, and create a file named Post.js inside the models directory.
Option 1: Define the Post Model By Calling Sequelize.define()
Refer to the following code snippet to understand how you can define a model by calling the sequeize.define() function.
Option 2: Define the Post Model By Extending the Model Class and Calling Init()
Refer to the following code snippet to understand how you can define a model by extending the Model class and calling the init() function.
Model Schema Data Types
The data types must be mentioned for each column you have added to a model’s schema. Refer to the following code snippet to see all the data types available for the MySQL database.
String Data Types
DataTypes.STRING // Type: VARCHAR(255)
DataTypes.STRING(1024) // Type: VARCHAR(1024)
DataTypes.STRING.BINARY // Type: BINARY
DataTypes.TEXT('tiny') // Type: TINYTEXT
DataTypes.TEXT // Type: TEXT
Number Data Types
DataTypes.INTEGER // Type: INTEGER
DataTypes.BIGINT // Type: BIGINT
DataTypes.BIGINT(20) // Type: BIGINT(20)
DataTypes.FLOAT // Type: FLOAT
DataTypes.FLOAT(11) // Type: FLOAT(11)
DataTypes.FLOAT(11, 10) // Type: FLOAT(11,10)
DataTypes.DOUBLE // Type: DOUBLE
DataTypes.DOUBLE(11) // Type: DOUBLE(11)
DataTypes.DOUBLE(11, 10) // Type: DOUBLE(11,10)
DataTypes.DECIMAL // Type: DECIMAL
DataTypes.DECIMAL(10, 2) // Type: DECIMAL(10,2)
Date Data Types
DataTypes.DATE // Type: DATETIME
DataTypes.DATE(6) // Type: DATETIME(6) for MySQL 5.6.4+. Fractional seconds support with up to 6 digits of precision
DataTypes.DATEONLY // Type: DATE
Boolean Data Type
DataTypes.BOOLEAN // Type: TINYINT(1)
UUID Data Type
DataTypes.UUID // Type: CHAR(36)
To specify a column as type UUID, you need to provide an additional defaultvalue parameter, so that Sequelize can auto-generate the UUID for that column.
{
type: DataTypes.UUID,
defaultValue: DataTypes.UUIDV4
}Synchronize a Model
Model synchronization is the process to tell Sequelize to automatically perform an SQL query to the database and check that the columns mentioned in the model’s schema must match with the target table. A model can be synchronized with the database by calling the .sync() function.
Following are the three ways to sync a Model. Here we will be syncing the Post model.
- Post.sync()
- Post.sync({force: true})
- Post.sync({alter: true})
Syncing a Model Using the Post.sync() Function
This will create the table if it doesn’t exist. If it exists, then no action is performed.
Syncing a Model Using the Post.sync({force:true}) Function
This will delete the table if it exists, then it will recreate the table.
Syncing a Model Using the Post.sync({alter:true}) Function
This will first check the table in the database (which columns are available, what are their data types, etc), and then perform the required changes in the database table to make sure that the table matches the model’s schema.
You can use this in the app.js file right after the database connection gets established. Refer to the following code snippet to understand how you can use model syncing.
When you restart the node server, the table app_posts will be automatically created. Here is a screenshot of the table.
You will notice that two additional columns have been added — createdAt, and updatedAt. These columns are auto generated by Sequelize for the table when the sync() operation is performed.
You can use db.sync() in the app.js file to sync all models at one go.
WARNING: The sync({ force: true }) and sync({ alter: true }) can be destructive if used in production environment. Therefore, they are not recommended to be used in production.
Drop a Table
To drop a table, you can use the .drop() function on a model instance.
Post.drop();
To drop all tables, you can use the .drop() function on the db instance.
db.drop();
Create a Record Using Model
To create/insert a record in a table, you can use the .create() function on a model instance. Refer to the following code snippet to understand how you can create a new record in a table.
Read Records Using Model
To read/fetch a record from a table, you can use the .findOne() function on a model instance. Refer to the following code snippet to understand how you can read a record from a table.
To read all records from a table, you can use the .findAll() function on a model instance. Refer to the following code snippet to understand how you can read all records from a table.
Update a Record Using Model
To update a record in a table, you can use the .update() function on a model instance. Refer to the following code snippet to understand how you can update a record in a table.
Delete a Record Using Model
To delete a record in a table, you can use the destroy() function on a model instance. Refer to the following code snippet to understand how you can delete a record in a table.
Here is the link to the GitHub repository that I have created, which includes the above-listed features of Sequelize ORM.
GitHub - tararoutray/sequelize-demo: A project to demonstrate the basics of Sequelize
Kudos! You have completed learning the basics of Sequelize ORM. Now you can go ahead and start integrating them into your current or upcoming projects.
To read the complete set of features offered by Sequelize ORM, you can refer to its official documentation.
If you enjoyed reading this post and have learnt something new, then please give a clap, share it with your friends, and follow me to get updates for my upcoming posts. You can connect with me on LinkedIn.
The Ultimate Guide To Get Started With Sequelize ORM was originally published in Level Up Coding on Medium, where people are continuing the conversation by highlighting and responding to this story.
This content originally appeared on Level Up Coding - Medium and was authored by Tara Prasad Routray
Tara Prasad Routray | Sciencx (2021-12-14T05:26:17+00:00) The Ultimate Guide To Get Started With Sequelize ORM. Retrieved from https://www.scien.cx/2021/12/14/the-ultimate-guide-to-get-started-with-sequelize-orm/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.