This is the 10th day of my participation in the August More text Challenge. For details, see: August More Text Challenge

preface

Usually we directly use mysql instructions to build a database, and then write code to add, delete and change the database. But this approach is not conducive to team development. In a project, the structure of the data tables is constantly evolving. In different environments (development environment, test environment, production environment), we all need the same data structure. What if we quickly change the data tables in each environment? Migrations solves this problem by helping us manage changes to data structures. Seeders is an initialization setting that automatically creates some test data after the table is created

sequelize

Sequelize is a Promise-based Node.js ORM that supports Postgres, MySQL, MariaDB, SQLite, and Microsoft SQL Server. It has strong transaction support, association relationship, prefetch and lazy load, read replication and other functions.

Sequelize complies with semantic versioning. Supports Node V10 and later versions to use THE ES6 function.

Create an empty project using the Sequelize plug-in with a series of Settings and install them separately

  • Sequelize ORM framework
  • Mysql2 mysql2 module
  • Sequelize – cli scaffold
npm install --save sequelize mysql2
npm install -g sequelize-cli
Copy the code

Migrations

The principle of

Every time we update our tables, we need to create a new Migrations file that contains the following information

  • The operation time, that’s in the file name
  • Database related operations, these are inMigrationsOf the fileup,downIn the function

With this information, you can update the tables to the latest state simply by performing the UP function database operations in all Migrations files

Note: All Migrations must be performed using the Migrations file, not directly using SQL. Otherwise, Migrations cannot be used to iterate over and roll back the database version

Initialize the

In the root directory, you need to run init

sequelize init
Copy the code

This will create the following folders

  • configContains configuration files that tell the CLI how to connect to the database
  • modelsContains all the models for your project
  • migrations, which contains all the migrated files
  • seedersWhich contains all the seed files

config

{ "development": { "username": "root", "password": "123456", "database": "database_development", "host": "127.0.0.1" and "the dialect", "mysql"}, "test" : {" username ":" root ", "password" : "123456", "database" : "Database_test", "the host" : "127.0.0.1", "the dialect" : "mysql"}, "production" : {" username ":" root ", "password" : "123456", "database": "database_production", "host": "127.0.0.1", "dialect": "mysql"}}Copy the code

Note: If your database does not already exist, you can call the db:create command. With the right access, it will create the database for you.

Create the migrations file

Execute the command

sequelize migration:generate --name=users
Copy the code

After execution, a Migrations file (< time >-init-< Operation name >.js) is generated to represent the new iteration

up

SQL > sequelize DB :migrate Update update update db:migrate

台湾国

up: (queryInterface, Sequelize) => {
    const { INTEGER, DATE, STRING } = Sequelize;
    await queryInterface.createTable('users', {
      id: { type: INTEGER, primaryKey: true, autoIncrement: true },
      name: STRING(30),
      age: INTEGER,
      created_at: DATE,
      updated_at: DATE,
    });
}
Copy the code

down

When running sequelize DB :migrate:undo to roll back a version that is rolled back, the down function will be invoked. You need to perform the reverse operation of updating a table. For example, create a table in up mode, and delete a table in DOWN mode

台湾国

down: (queryInterface, Sequelize) => {
    await queryInterface.dropTable('users');
}
Copy the code

You can run db:migrate:undo:all to revert to the initial state

seeders

Sometimes we need to write some test data to the database, so we can do this with a seed script.

This command will create a seed file in the seeders folder. The file name looks like xxxxXXXXxxxx-demo-user.js, which follows the same up/ Down semantics as migrating files.

We should now edit the file to insert the demo user into the Users table.

up

up: async (queryInterface, Sequelize) => {
    await queryInterface.bulkInsert('users', [{
      id: 1,
      name: 'iwhao',
      age: 18,
      created_at: new Date(),
      updated_at: new Date(),
    }], {});
  },
Copy the code

The sequelize db:seed:all command is executed to write data to the database

down

down: async (queryInterface, Sequelize) => {
    await queryInterface.bulkDelete('users');
}
Copy the code

Run the sequelize db:seed:undo command to undo the latest seed

If you want to undo all the seed

sequelize db:seed:undo:all

That’s all for this post, thank you very much for reading here, if this article is good or a little bit of help to you, please like, follow, share, of course, any questions can be discussed in the comments, I will actively answer, thanks again 😁