In the process of version iteration, there is the probability of database changes, such as adding a certain table field, deleting a certain field, etc.

Lack of a change history, or time spent finding database change records and executing scripts when upgrading multiple release environments;

This scheme provides a database change record method;

Use the open source library: Migrate, which currently supports different types of databases (visualization is currently using mongodb, supported);

use

Migrate Provides two methods to perform changes: 1. Command line 2. SDK (use Go)

The command line is used in this solution (official documentation).

The installation

Download the latest binary file from GitHub: github.com/golang-migr…

Direct decompression can be used;

Simple to use

Each database change is classified into two types: up (upgraded version) and Down (degraded version);

So each change log requires two files,

{version}_{title}.up.{extension}
{version}_{title}.down.{extension}
Copy the code

Version indicates the current version number. The sequence number or timestamp can be used to indicate the version number. The program takes the character before the first ‘_’ as the version number.

Title is used to record the current change of the subject, mainly for the user has a good prompt role;

Extension: indicates the file extension.

For example, the mongodb database:

0001_init_database.down.json  
0001_init_database.up.json
Copy the code

When creating migrated files, you can use the Migrate create command, as shown in the following example

# migrate create [-ext E] [-dir D] [-seq] [-digits N] [-format] NAME
$ migrate create -ext json -dir migrations -seq init_database
~/migrations/000001_init_database.up.json
~/migrations/000001_init_database.down.json
Copy the code

Parameter Description:

  • Ext: file extension;

  • Dir: created directory.

  • Seq: Whether to create in serial number mode.

  • Digits: Ordinal length (default: 6);

  • Format: Indicates the time format.

Use this file to initialize the test set in the mirations database.

$ cat migrations/000001_init_database.up.json
[
    {
       "insert": "test",
       "documents": [{"name": "aaa"}]
    }
]
$migrate -verbose -source file://migrations --database mongodb://root:pwd@< database IP>:27017/migrations? authSource=admin up2021/08/31 14:26:06 Start buffering 1/u init_database 2021/08/31 14:26:08 Read and execute 1/u init_database 2021/08/31 14:26:08 Finished 1/u init_database (read 1.516025172s, Ran 75.143261 MS) 2021/08/31 14:26:08 Finished after 1.654028624s 2021/08/31 14:26:08 Closing Source and DatabaseCopy the code

Parameter Description:

  • Verbose: Prints the current change log
  • Source: Change the directory for storing files. It is better to go to migrations directory of this project and use ls to view all current databases
  • Database: Connect uri for monogDB
  • Up: to upgrade (using down to degrade)

SQL > insert data into database

mongos> use migrations;
switched to db migrations
mongos> show collections;
migrate_advisory_lock
schema_migrations
test
mongos> db.test.find()
{ "_id" : ObjectId("612e3f5febb6de55cdeec1de"), "name" : "aaa" }
#Two more tables are generated, where schemA_migrations is the migration data record;
mongos> db.schema_migrations.find();
{ "_id" : ObjectId("612dcb8023fbb5b85368b874"), "version" : 1, "dirty" : false }
Copy the code

Modify version to control the upgraded version of Migrate. If the database version is later than the latest version, run the force command to change the current database migration version.

$ migrate -verbose -source file://migrations --database mongodb://root:pwd@< database IP>:27017/migrations? authSource=admin force 0000042021/08/31 14:34:52 Finished after 89.470244 MS 2021/08/31 14:34:52 Closing Source and database#The database version has been changed to version 4. If the database is up again, the system will tell you no change.
mongos> db.schema_migrations.find();
{ "_id" : ObjectId("612dcd8c1e88c95afcb426fe"), "version" : 4, "dirty" : false }
Copy the code

subsequent

Maintain a database/configuration file change history in the project so that all changes can be made through the change history when the project is redeployed. No need to spend manpower to find relevant development;