Auto-generate models for Sequelize using sequelize-auto

Writing database queries without the model like below

SELECT * FROM your_table_name WHERE column_name = 'some_value';

has many issues like adding proper data type validation etc. to get data from the same database table with different conditions, you have to write another SQL query. this leads to code duplicates and also makes our code lengthy, hard to debug, and reduces code readability.

Well, the solution to this problem is Object-Relational Mapping (ORM) libraries. They provide a mapping layer that allows developers to work with relational databases using object-oriented paradigms, without directly writing SQL queries.

Introduction:

Sequelize is a powerful Object-Relational Mapping (ORM) library for Node.js, widely used for working with databases like PostgreSQL, MySQL, and SQLite. It simplifies database operations by mapping JavaScript objects to database tables.

However, manually creating Sequelize models for each table can be time-consuming and error-prone. Fortunately, Sequelize-Auto comes to the rescue, offering a seamless solution for auto-generating models based on existing database schemas.

In this blog post, we will explore how to leverage Sequelize-Auto to automate the model creation process.

Step 1: Installation and Setup

To get started, ensure that Sequelize (prerequisite), Sequelize-Auto, and the appropriate database driver are installed in your Node.js project. Use the following command to install Sequelize-Auto via npm:

npm i sequelize-auto

Step 2: Configuring Sequelize-Auto

Next, we need to configure Sequelize-Auto to connect to our database and generate models. Create a file named sequelize-auto-config.json in the config folder of your node project. below I am giving an example with a Postgres database.

{
  "dialect": "postgres",
  "host": <DB_host>, // this must be in string
  "port": <DB_PORT>, // this is an integer
  "additional": {
    "timestamps": false, // does not include postgress default timestamp for createdAt and updatedAt
  },
  "skipTables": ["cities", "clients" ], // does not create model file for cities and clients table
  "caseProp": "l",
  "caseFile": "p",
  "singularize": false, //keep the model file name plural like users
  "caseModel": "p",
  "lang": "js"
}

Step 3: Command to generate Models

Create a "models" folder in your project to store generated model files. and include its path in the below command.

Create a Makefile in the project at the same directory level as the package.json and write the below command to generate model

Note: Don't forget to change the <database>, <username>, <password>, <path_of_sequelize-auto-config.json> based on your requirement.

generate-models:
    node node_modules/sequelize-auto/bin/sequelize-auto -o "./models" -d <database> -u <username> -x <password> --config <path_of_sequelize-auto-config.json>

Step 4: Run the command

Open the terminal in the same project directory and Run the make command

make generate-models

By running this command, the Sequelize-Auto library will connect to the specified database, analyze its structure, and automatically generate Sequelize models based on the tables and columns in the database. The generated models will then be saved in the specified output directory ("./models" in this example).

Explanations:

It will generate a file/files such as ./models/Users.js which looks like this:

module.exports = function(sequelize, DataTypes) {
  return sequelize.define('User', {
    id: {
      type: DataTypes.INTEGER,
      allowNull: false,
      primaryKey: true,
      autoIncrement: true
    },
    username: {
      type: DataTypes.STRING(20),
      allowNull: true
    },
    aNumber: {
      type: DataTypes.SMALLINT,
      allowNull: true
    },
    dateAllowNullTrue: {
      type: DataTypes.DATE,
      allowNull: true
    },
    defaultValueBoolean: {
      type: DataTypes.BOOLEAN,
      allowNull: true,
      defaultValue: true
    }
  }, {
    tableName: 'User',
    timestamps: false,
  });
};

Sequelize-auto also generates an initialization file, ./models/init-models.js, which contains the code to load each model definition into Sequelize:

var DataTypes = require("sequelize").DataTypes;
var _User = require("./User");
var _Product = require("./Product");

function initModels(sequelize) {
  var User = _User(sequelize, DataTypes);
  var Product = _Product(sequelize, DataTypes);

  return {
    User,
    Product,
  };
}
module.exports = { initModels };

This makes it easy to import all your models into Sequelize by calling initModels(sequelize). Now you can write your database queries based on models.

var initModels = require("./models/init-models");
...
var models = initModels(sequelize);

models.User.findAll({ where: { username: "tony" }}).then(...);

Explanation of the above generate-models command:

Let's break down the different components of the command and understand their meaning:

node: This is the command to execute a Node.js script or application.
node_modules/sequelize-auto/bin/sequelize-auto: This specifies the path to the sequelize-auto script that is part of the Sequelize-Auto library. This script is responsible for generating the models.
-o "./models": This flag specifies the output directory where the generated models will be saved. In this case, the models will be saved in the "./models" directory.
-d <database>: This flag specifies the name of the database from which the models will be generated. In this example, the database is named "master".
-u <username>: This flag specifies the username to be used when connecting to the database. In this case, the username is "postgres".
-x <password>: This flag specifies the password for the specified username. In this example, the password is "theimm0rtaL".
--config <path_of_sequelize-auto-config.json>: This flag specifies the path to a configuration file that provides additional settings for the Sequelize-Auto library. The configuration file, in this case, is located at "config/database/sequelize-auto-config.json".

auto-generate models for sequelize