background

To give you some background, when developing a desktop IM application using Electron, the database is always resynchronized with each update during the release iteration. Specifically, the typeORM framework I use is set:

The synchronize: true,Copy the code

In general, when the version involves database updates, the database needs to be updated through SQL statements first. This paper mainly introduces the problems of database synchronization in the process of application update, and how I standardize the update process afterwards.

synchronize

The initial use of ‘synchronize: True ‘had no problems during QA testing. Although I have considered a more standardized database update process, I have no time to do it due to the low priority. But after reporting the errors, I found that dozens of errors were reported every week:

QueryFailedError: SQLITE_ERROR: table "" already exists
Copy the code

Why is there no user feedback? Check the related issueissue data. There are two main reasons: 1. Entity (table) is named with uppercase letters; 2. Synchronize the database each time (true, not recommended); The tests have nothing to do with the first reason. So that’s the second reason, and the point is that this problem doesn’t recur, or it doesn’t recur steadily. However, it is understandable that there is no user feedback for two reasons: 1. The reason for this error is that it occurs only after the second or higher synchronization, and our database has updated the table during the first synchronization, so even if the database is modified in the version iteration process, users will not be affected; 2. 2, This error has been tried at the time of occurrence… Catch (error logs are reported in this layer) without interrupting the program to continue execution (see my article on error catching);

In order to solve this problem and standardize database update during version iteration, I tried two solutions successively: 1. Typeorm itself migration scheme; 2. 2. Self-written migration scheme; I will explain why typeOrM is not used for migration.

typeorm migration

The flow of the typeORM Migration solution is as follows: 1. Modify the typeORM configuration.

"migrationsTableName": Migrations: migrations: migrations: migrations: migrations: migrations: migrations: migrations: migrations: migrations ["migration/*.js"] // Specify the directory to load the migration script. "cli": {"migrationsDir": "migration"} // Specify the directory to generate the migration script, but I am a dynamic configuration file, not in the root directory, so I do not need toCopy the code

2. Generate migration scripts;

NPX Typeorm Migration :create -n PostRefactoring // Perform the build using the typeOrm installed in the current directoryCopy the code

Write the migration SQL statement in the generated script (up, down is used for revert).

Name -> className public async up(queryRunner: queryRunner): Promise<void> { await queryRunner.query(`alter table "user" rename column "name" TO "classname"; `)}Copy the code

4. Execute the script;

NPX typeorm migration:run // if the ts file is not generated, you can run the ts-node-dev command, e.g. ts-node-dev./node_modules/typeorm/cli.js migration:runCopy the code

This scheme has some advantages, but it also has its disadvantages:

// The advantages themselves record whether each migration command is executed (recorded in the migrations table), so the developer does not need to determine the version to execute the corresponding migration command; // Disadvantages The overall process is too tedious and black box, which is not conducive to expansion and problem locationCopy the code

Migration scheme (self-implementation)

1. Create a synchronization record table;

// Generate ID version, // Version to be executed, // Whether executedCopy the code

2. Create version-migration script mapping table;

Const migrationCliMap = [{version: '1.0.0', CLI: 'ALTER TABLE "user" RENAME COLUMN "name" TO "classname";'},...Copy the code

3. Perform migration and add migration records;

/ / get the last record let _connectionManage = await jdbcMap. GetCurrentDBAsync () let _lastMigrationCli = await _connectionManage.getConnection(_connectionManage.db).getRepository('migration') .query('SELECT * FROM migration WHERE executed=1 ORDER BY id DESC LIMIT 1; ') let _version = _lastMigrationCli.length ? _lastMigrationCli[0]. Version: '0.0.0' // Iterate over the migration script and execute the unexecuted script for(let I =0, len= migrationClimap.length; i < len; i+=1) { if(semver.gt(migrationCliMap[i].version, _version)) { try{ await _connectionManage.getConnection(_connectionManage.db).getRepository('migration') .query(migrationCliMap[i].cli) await _connectionManage.getConnection(_connectionManage.db).getRepository('migration') .createQueryBuilder() .insert() .values({ version: migrationCliMap[i].version, executed: True}).execute()} Catch (err) {logservices. errorLog(' migrationCliMap[I].version} failed. ${err}`) } } }Copy the code

Note: When the database has not been synchronized for the first time, do not perform migration to avoid executing too many migration commands or even errors.