背景介绍
数据库版本的变更在我们日常开发中是一个非常常见的场景，下面我们分析下，基于Nodejs会哪些方法呢
Solution 1 use Sequelize
1 Sequelize introduce
https://sequelize.org/docs/v6/other-topics/migrations/
2 Practice in response
2.1 install
npm install sequelize sequelize-cli2.2 prepare .sequelizerc config file
// .sequelizercconst path = require('path');module.exports = {config: path.resolve('config', 'database.json'),//2.3 define database config file path'migrations-path': path.resolve('config', 'migrations') //2.4 define migrateions dir
};2.3 prepare database.json
{"development": {"username": "xxx","password": "xxx","database": "xxx","host": "xxx","dialect": "mysql"}
}2.4 write migrate code
-
define the file of migrate ,realize the up and down function
-
when you exec the “migrate: run“ cmd ,the up function will be exec
-
when you exec the “migrate: undo“ cmd ,the down function will be exec
-
before “migrate:run” exec ,it will create a table(SequelizeMeta) in the current db
-
the SequelizeMeta will record the current migrate file (version) to guarantee won't repeat execution
-
the dir /migrations all the file will be checked an execution
2.5 automation
RUN npx sequelize db:migrate --env productionadd the cmd in the docker file ,when cmd “npm install“ execution end ,it will be executed
ensure you should not login to the pod to run this cmd
Solution 2 Handwrite the above steps
// 查询config表获取当前版本,并且查看目标版本号// 新增配置,如果目标版本为空,则需要到最新的版本// 获取某文件路径下的 升级脚本和日期进行排序// 判断是版本升级还是版本回滚// 如果是升级:// 判断当前file是否在data目录里面,如果是的话，不需要执行，如果不在，则需要执行该文件// 每次执行完,更新数据库信息，将新版本写入db中// 如果是回滚:// 判断当前file是否在data目录里面，如果是的话，执行回滚，如果不在，则不需要执行该文件// 每次执行完，更新数据库信息，移除当前版本信息Step1: 创建migrations文件夹，所有升级的脚本都定义在该文件夹下，升级的脚本可以是js，也可以是sql形式
step2: 创建migrate的流程
const fs = require('fs');
const path = require('path');
const MIGRATIONS_DIR = path.join(__dirname, 'migrations');async function getCurrentVersion(connection) {const [rows] = await connection.execute(`SELECT value FROM config WHERE id = 'db_version'`);return rows.length > 0 ? rows[0].value : null;
}async function setVersion(connection, version) {await connection.execute(`INSERT INTO config (key, value) VALUES ('db_version', ?) ON DUPLICATE KEY UPDATE value = VALUES(value);`, [version])
}async function getMigrations() {return fs.readdirSync(MIGRATIONS_DIR).filter(file => file.endsWith('.js')).map(file => {const [date, name] = file.split('-');return { date, name, file };}).sort((a, b) => a.date.localeCompare(b.date));
}async function migrate(connection, targetVersion = null) {try {await connection.query('START TRANSACTION');const currentVersion = await getCurrentVersion(connection);const migrations = await getMigrations();const currentIndex = migrations.findIndex(m => m.date === currentVersion);const targetIndex = targetVersion ? migrations.findIndex(m => m.date === targetVersion) : migrations.length - 1;if (targetIndex === -1) {throw new Error(`Target version ${targetVersion} not found`);}if (currentIndex < targetIndex) {console.log('Upgrading...');for (let i = currentIndex + 1; i <= targetIndex; i++) {const migration = migrations[i];const migrationPath = path.join(MIGRATIONS_DIR, migration.file);const { up } = require(migrationPath);console.log(`Executing ${migration.file}`);await up(connection);await setVersion(connection, migration.date);}} else if (currentIndex > targetIndex) {console.log('Rolling back...');for (let i = currentIndex; i > targetIndex; i--) {const migration = migrations[i];const migrationPath = path.join(MIGRATIONS_DIR, migration.file);const { down } = require(migrationPath);console.log(`Reverting ${migration.file}`);await down(connection);await setVersion(connection, i > 0 ? migrations[i - 1].date : null);}} else {console.log('Already at target version');}await connection.commit()} catch (error) {await connection.rollback()console.error('Migration failed:', error);} finally {connection.release();}
}
module.exports = {migrate
}
step3:db初始化时候，判断是否执行
guideline
s1
data migrate
记录 migrate log
