Vaishnavi Rawool

Posted on Jun 27

Introduction to Sequelize: Simplifying Database Operations in Node.js

#node #database #sql #javascript

What is a ORM ?

ORM (Object-Relational Mapping) bridges databases and object-oriented programming by mapping database tables to objects in code. It allows developers to interact with databases using familiar programming constructs, abstracting away SQL queries for easier development and maintenance of applications.

Advantages of ORMs

ORMs abstract away the need to write raw SQL queries directly in your application code, reducing the complexity
Complex database operations, such as JOINs and GROUP BYs, can be expressed in a simpler syntax using ORM methods and APIs.
Supports transactions and rollback mechanisms
ORMs can significantly reduce development time and effort
ORMs includes features for optimizing query performance (e.g., lazy loading).
Same application logic can be used with different database systems (e.g., MySQL, PostgreSQL, SQLite) without significant changes
ORMs offer built-in validation mechanisms (e.g., unique, not null). You can also write custom validations (e.g., email regex validation, phone number validation)

Disadvantages of ORMs

ORMs introduce a learning curve as developers need to familiarize themselves with the ORM's API and its querying methods
Understanding how the ORM translates application code into SQL queries often requires digging into ORM logs or debugging mechanisms.
Despite the ORM's capabilities, there are situations where developers may need to resort to writing raw SQL queries for certain operations. Sequelize, for example, provides mechanisms for executing raw SQL queries when needed.

Getting started with Sequelize

1] Installation

npm install sequelize

or

yarn add sequelize

2]You'll also have to manually install the driver for your database of choice:

One of the following:

$ npm install --save pg pg-hstore # Postgres

$ npm install --save mysql2

$ npm install --save mariadb

$ npm install --save sqlite3

$ npm install --save tedious # Microsoft SQL Server

$ npm install --save oracledb # Oracle Database

Connecting to a Database

1] Create a config.json and store development db details as follows:

{

    "development": {

      "username": "postgres",

      "password": "root",

      "database": "travel",

      "host": "localhost",

      "dialect": "postgres",

      "port":"5432"

    }

  }

Similarly you can add details of other environments e.g., staging/production

2] Create a dbConfig.js file and paste the following code :

const path = require('path');

const { Sequelize } = require('sequelize');

// Load Sequelize configurations from config.json file
const env = process.env.NODE_ENV || 'development'; // default environment is development if not set explicitly

const config = require("./config.json")[env];

// You will have to pass database name, username, password, host, port and dialect (in this case postgres) inorder to create a instance.

const sequelize = new Sequelize(config.database, config.username, config.password, {

  host: config.host,

  dialect: "postgres",

  port: config.port

});

// export the newly created instance from this file
  module.exports.sequelize = sequelize;

Sequelize provides authenticate() function to check whether the database connection was successful

try {

  await sequelize.authenticate();

  console.log('Connection has been established successfully.');

} catch (error) {

  console.error('Unable to connect to the database:', error);

}

Let us explore the basics of model creation in sequelize.

In Sequelize, a model represents a table in a database.
You define a table structure i.e columns, relationships (one-to-one, one-to-many etc), indexes, hooks etc using a model
Columns in a Sequelize model are defined using attributes.
Attributes can specify various parameters, including:

type: Specifies the data type of the column (e.g., STRING, INTEGER, DATE).
allowNull: Indicates whether the column can be null (true or false).
defaultValue: Sets a default value for the column if not provided.
primaryKey: Marks the column as the primary key (true or false).
unique: Ensures the column values are unique (true or false).
Other constraints like autoIncrement, references, onDelete, etc., can also be defined depending on the database relationship requirements.

Create a user.model.js file inside models folder that has a simple User model configuration code.

const { Sequelize, DataTypes, Model } = require('sequelize');

const sequelize = require(‘./dbConfig.js’);

class User extends Model {}

User.init(
  {

// id attribute which is a primary key

   id: {
     type: DataTypes.INTEGER,
     primaryKey: true,
     autoIncrement: true
    },

    firstName: {
      type: DataTypes.STRING,
      allowNull: false,
    },

    lastName: {
      type: DataTypes.STRING,
      // allowNull defaults to true
    },
  },
  {
    // Other model options go here
    sequelize, // We need to pass the connection instance that we have created earlier
    modelName: 'User', // We need to choose the model name
  },
);

You can define relationships between tables using sequelize. Consider the example below, where we create a one-to-many association between the User and Address tables using hasMany:

User.Addresses = User.hasMany(Address);

Similarly, you can create one-to-one and many-to-many relations as well. Read in detail about relationships
Model Associations

Model Synchronization

When you define a model, you're telling Sequelize a few things about its table in the database. However, what if the table actually doesn't even exist in the database? What if it exists, but it has different columns, less columns, or any other difference?

This is where model synchronization comes in. A model can be synchronized with the database by calling model.sync(options), an asynchronous function (that returns a Promise). With this call, Sequelize will automatically perform an SQL query to the database. Note that this changes only the table in the database, not the model in the JavaScript side.

User.sync() - This creates the table if it doesn't exist (and does nothing if it already exists)
User.sync({ force: true }) - This creates the table, dropping it first if it already existed
User.sync({ alter: true }) - This checks what is the current state of the table in the database (which columns it has, what are their data types, etc), and then performs the necessary changes in the table to make it match the model.

Let us explore some basics CRUD operations with the help of sequelize:

1] Create a new record

create() method is asynchronous hence we have to put await before it
Import User from user.model.js file

const User = require(‘../model/user.model.js’);

const jane = await User.create({ firstName: 'Jane', lastName: 'Doe' });


// Jane exists in the database now!
console.log(jane instanceof User); // true
console.log(jane.name); // "Jane"

2] Simple SELECT queries (all of the querying methods are asychronous)

// fetch all users from user table

const users = await User.findAll();

// fetch certain fields from user table

await User.findAll({  attributes: [firstName,’lastName’]});

//apply where clause and fetch user with id = 1

await User.findAll({ where: {  id: 1, }});

Read more about model querying here: Model Querying

3] Update query

await User.update( { lastName: 'Doe' },{
    where: {
      lastName: null,
    },
  },
);

4] Delete query

// Delete everyone named "Jane"
await User.destroy({
  where: {
    firstName: 'Jane',
  },
});

CONCLUSION

Sequelize offers a comprehensive documentation covering various SQL methods along with advanced topics such as Joins, Aggregate functions, Transactions, and Lazy Loading. Its user-friendly navigation makes it accessible even for beginners, helping in a thorough understanding of the ORM. I highly recommend exploring Sequelize's documentation to delve deeper into its capabilities. Sequelize

DEV Community