React Express & Mysql: Sequelize

Now, you have React project in my-app folder and Express project in prod_project folder. We are going to create back-end APIs that will be accessed by our React app. If you don't have React and Express apps set up, you go to the page Product CRUD Project

In the prod_project folder, run the following command to install dependencies that are required to create the APIs on Express server.

D:\prod_project> npm install sequelize sequelize-cli mysql2 jsonwebtoken cors body-parser bcrypt

sequelize helps us synchronize the models (defined later in our project) with Mysql database. From the models, you can create tables in the database, query, add, update, delete data in the tables.

sequelize-cli - Sequelize Command Line Interface helps us create models, migrations, and database.

mysql2  is a fast mysql driver to work with mysql database from Express.

jsonwebtoken works in user authentication by token for securely transmitting data between our back-end Express and front-end React apps as a JSON object.

cors - Cross-origin resource sharing is a Nodejs package to allow client or front-end app to access Express APIs.

body-parser will help us easily extract the entire body portion of an incoming request stream and exposes it on req.body.

bcrypt is used to hash and salt passwords securely.

Create db folder in prod_project then in the db folder execute the command below to initialaize sequelize:

D:\prod_project\db>npx sequelize-cli init

The command will generate config, models, migrations, and seeders folders in the db folder. In the config folder, there is config.json file that allows you define variables used by sequlize to access database.

config/config.json

{
  "development": {
    "username": "root",
    "password": null,
    "database": "database_development",
    "host": "127.0.0.1",
    "dialect": "mysql"
  },
  "test": {
    "username": "root",
    "password": null,
    "database": "database_test",
    "host": "127.0.0.1",
    "dialect": "mysql"
  },
  "production": {
    "username": "root",
    "password": null,
    "database": "database_production",
    "host": "127.0.0.1",
    "dialect": "mysql"
  }
}

It is enough to create database_development database on local machine. Let run command npx sequelize-cli db:create to create the database. You can change the database name or other configuration information. For this tutorial, just keep them unchanged.

Run the following command to generate User model and its migration:
npx sequelize-cli model:generate --name User --attributes username:string,email:string,password:string
It created the User model and migration with username, email, and password fields.

 
User model

'use strict';
const {
  Model
} = require('sequelize');
module.exports = (sequelize, DataTypes) => {
  class User extends Model {
    /**
     * Helper method for defining associations.
     * This method is not a part of Sequelize lifecycle.
     * The `models/index` file will call this method automatically.
     */
    static associate(models) {
      // define association here
    }
  }
  User.init({
    username: DataTypes.STRING,
    email: DataTypes.STRING,
    password: DataTypes.STRING
  }, {
    sequelize,
    modelName: 'User',
  });
  return User;
};

User migration

'use strict';
module.exports = {
  async up(queryInterface, Sequelize) {
    await queryInterface.createTable('Users', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      username: {
        type: Sequelize.STRING
      },
      email: {
        type: Sequelize.STRING
      },
      password: {
        type: Sequelize.STRING
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE
      }
    });
  },
  async down(queryInterface, Sequelize) {
    await queryInterface.dropTable('Users');
  }
};

Now create two more models and migrations for Product and Image.
Product model

'use strict';
const {
  Model
} = require('sequelize');
module.exports = (sequelize, DataTypes) => {
  class Product extends Model {
    /**
     * Helper method for defining associations.
     * This method is not a part of Sequelize lifecycle.
     * The `models/index` file will call this method automatically.
     */
    static associate(models) {
      // define association here
      Product.hasMany(models.Image, {
        foreignKey: "product_id",
      }); 
    }
  }
  Product.init({
    title: DataTypes.STRING,
    description: DataTypes.STRING,
    price: DataTypes.REAL
  }, {
    sequelize,
    modelName: 'Product',
  });
  return Product;
};

Product migration

'use strict';
module.exports = {
  async up(queryInterface, Sequelize) {
    await queryInterface.createTable('Products', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      title: {
        type: Sequelize.STRING
      },
      description: {
        type: Sequelize.STRING
      },
      price: {
        type: Sequelize.REAL
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE
      }
    });
  },
  async down(queryInterface, Sequelize) {
    await queryInterface.dropTable('Products');
  }
};

Image model

'use strict';
const {
  Model
} = require('sequelize');
module.exports = (sequelize, DataTypes) => {
  class Image extends Model {
    /**
     * Helper method for defining associations.
     * This method is not a part of Sequelize lifecycle.
     * The `models/index` file will call this method automatically.
     */
    static associate(models) {
      // define association here
      
        Image.belongsTo(models.Product, {
          foreignKey: "product_id",
          targetKey: "id",
        }); 
    }
  }
  Image.init({
    fileUrl: DataTypes.STRING,
    mineType: DataTypes.STRING,
  }, {
    sequelize,
    modelName: 'Image',
  });
  return Image;
};

Image migration

'use strict';
module.exports = {
  async up(queryInterface, Sequelize) {
    await queryInterface.createTable('Images', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      fileUrl: {
        type: Sequelize.STRING
      },
      mineType: {
        type: Sequelize.STRING
      },
     
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE,
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE,
      }
    });
  },
  async down(queryInterface, Sequelize) {
    await queryInterface.dropTable('Images');
  }
};

A product has many images or images belong to one product. Thus, we need to add product_id foreign key column to the images table.

Migration to add product_id column to the images table.

'use strict';

module.exports = {
  async up (queryInterface, Sequelize) {
    /**
     * Add altering commands here.
     *
     * Example:
     * await queryInterface.createTable('users', { id: Sequelize.INTEGER });
     */
     return queryInterface.addColumn(
      'images',
      'product_id',
      Sequelize.INTEGER
    );
  },

  async down (queryInterface, Sequelize) {
    /**
     * Add reverting commands here.
     *
     * Example:
     * await queryInterface.dropTable('users');
     */
  }
};

To create users, products, and images tables in the database, issue the following command: 

npx sequelize-cli db:migrate.

Now the tables were created. We need to add some sample data for testing. In the seeds folder, create three seed files - adduserdata.js, addproductdata.js, and addimagedata.js.

adduserdata.js

'use strict';

module.exports = {
  async up (queryInterface, Sequelize) {
    /**
     * Add seed commands here.
     *
     * Example:
     * await queryInterface.bulkInsert('People', [{
     *   name: 'John Doe',
     *   isBetaMember: false
     * }], {});
    */
      await queryInterface.bulkInsert('users', [
        {
        username: 'user1',
        email: 'user1@gmail.com',
        password:"12345_YaOne",
        },
        {
        username: 'user2',
        email: 'user2@gmail.com',
        password:"12345_YaTwo",
          }
          ,
          
    ], {});
  },

  async down (queryInterface, Sequelize) {
    /**
     * Add commands to revert seed here.
     *
     * Example:
     * await queryInterface.bulkDelete('People', null, {});
     */
  }
};

addproductdata.js

'use strict';

module.exports = {
  async up (queryInterface, Sequelize) {
    /**
     * Add seed commands here.
     *
     * Example:
     * await queryInterface.bulkInsert('People', [{
     *   name: 'John Doe',
     *   isBetaMember: false
     * }], {});
    */
      await queryInterface.bulkInsert('products', [
        {
        title: 'Jinkairui Infrared Heating Neck Shoulder',
        description: 'RELIEVE PAIN & RELEASE TENSION: Electric back massager help relax tight muscles and alleviate soreness PORTABLE & VERSATILE: Ergonomic shiatsu back massager fits perfectly behind neck and body contour',
        price: 50,
        },
        {
          title: 'suntekcam 2G 20MP 1080P',
          description: 'suntekcam 2G 20MP 1080P MMS/SMTP/SMS HC801M 2g hunting Trail Camera Wildlife photo traps 0.3S Trigger Hunter camera suntekcam 2G 20MP 1080P MMS/SMTP/SMS HC801M 2g hunting Trail Camera Wildlife photo traps 0.3S Trigger Hunter camera suntekcam 2G',
          price: 60,
          }
          ,
        {
          title: '1Pc 25mm Golf Hat Clip Metal Cap Clips',
          description: '1Pc 25mm Golf Hat Clip Metal Cap Clips with Magnet Golf Ball Markers Silver Golf Putting Green Accessories Golf Training Aids',
          price: 60,
        }
        ,
        {
          title: '2022 years The Newest Stranger Things',
          description: '2022 years The Newest Stranger Things series print summer children\'s summer 3D fashion casual shorgt sleeve T shirt tops',
          price: 20.5,
        }    
    ], {});
  },

  async down (queryInterface, Sequelize) {
    /**
     * Add commands to revert seed here.
     *
     * Example:
     * await queryInterface.bulkDelete('People', null, {});
     */
  }
};

addimagedata.js

'use strict';

module.exports = {
  async up (queryInterface, Sequelize) {
    /**
     * Add seed commands here.
     *
     * Example:
     * await queryInterface.bulkInsert('People', [{
     *   name: 'John Doe',
     *   isBetaMember: false
     * }], {});
    */
      await queryInterface.bulkInsert('images', [
        {
        fileUrl: 'image0.png',
        mineType: 'image/png',
        product_id:1,
        },
        {
          fileUrl: 'image1.png',
          mineType: 'image/png',
          product_id:1,
          }
          ,
        {
          fileUrl: 'image3.png',
          mineType: 'image/png',
          product_id:2,
        }
        ,
        {
          fileUrl: 'image3.png',
          mineType: 'image/png',
          product_id:3,
        }    
    ], {});
  },

  async down (queryInterface, Sequelize) {
    /**
     * Add commands to revert seed here.
     *
     * Example:
     * await queryInterface.bulkDelete('People', null, {});
     */
  }
};

Finally, run the commands to add data to the tables:
npx sequelize-cli db:seed --seed adduserdata.js
npx sequelize-cli db:seed --seed addproductdata.js
npx sequelize-cli db:seed --seed addimagedata.js

Now, our database is ready. The next article, we will create APIs in Express to query, insert, update, and delete data from the database.