真实项目中如何手动管理数据库Schema？求创建与版本管控流程

一、团队常用的数据库管理方式 #

真实项目里不会只靠.sql文件加Git管控——这种方式存在明显缺陷：没法跟踪哪些脚本已经执行过，多人协作时容易出现执行顺序混乱、重复执行或漏执行的问题，回滚操作也全靠手动，风险很高。

主流的方案分两种：一种是像你用的Prisma这类ORM自带的Schema管理+自动生成迁移脚本；另一种是手动编写迁移脚本（Migration）+ 元数据表跟踪版本，后者也是不依赖框架时的核心思路，而且团队里通常会把所有迁移文件和Schema文件都纳入Git管控，确保协作一致性。

二、不依赖框架手动实现Migration的步骤 #

以NodeJS+MySQL为例，手动搭建迁移体系的核心是用一个元数据表记录已执行的迁移版本，再配合脚本执行/回滚迁移：

1. 初始化目录与元数据表 #

• 创建migrations文件夹，每个迁移版本对应一个子文件夹，命名规则用「时间戳+描述」（比如20240520120000_initial_schema），每个子文件夹里必须包含up.sql（升级脚本）和down.sql（回滚脚本）。
• 先创建元数据表，用来记录已执行的迁移：

CREATE TABLE IF NOT EXISTS migrations_metadata (
    id INT AUTO_INCREMENT PRIMARY KEY,
    migration_version VARCHAR(255) NOT NULL UNIQUE,
    executed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

2. 编写迁移脚本 #

比如第一个初始化Schema的版本，up.sql写创建用户表的逻辑：

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

对应的down.sql写回滚逻辑：

DROP TABLE IF EXISTS users;

3. 编写执行迁移的NodeJS脚本 #

写一个migrate.js，核心逻辑是连接数据库、读取已执行版本、执行未执行的迁移并记录：

const fs = require('fs');
const path = require('path');
const mysql = require('mysql2/promise');

async function runMigrations() {
    const connection = await mysql.createConnection({
        host: 'localhost',
        user: 'root',
        password: 'your_db_password',
        database: 'your_target_db'
    });

    // 确保元数据表存在
    await connection.execute(`
        CREATE TABLE IF NOT EXISTS migrations_metadata (
            id INT AUTO_INCREMENT PRIMARY KEY,
            migration_version VARCHAR(255) NOT NULL UNIQUE,
            executed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        );
    `);

    // 获取已执行的迁移版本
    const [executedMigrations] = await connection.execute('SELECT migration_version FROM migrations_metadata');
    const executedVersions = new Set(executedMigrations.map(item => item.migration_version));

    // 按顺序读取所有迁移版本
    const migrationDir = path.join(__dirname, 'migrations');
    const migrationVersions = fs.readdirSync(migrationDir).sort();

    for (const version of migrationVersions) {
        if (!executedVersions.has(version)) {
            console.log(`开始执行迁移: ${version}`);
            const upSql = fs.readFileSync(path.join(migrationDir, version, 'up.sql'), 'utf8');
            
            // 用事务确保迁移原子性
            await connection.beginTransaction();
            try {
                await connection.execute(upSql);
                await connection.execute('INSERT INTO migrations_metadata (migration_version) VALUES (?)', [version]);
                await connection.commit();
                console.log(`迁移 ${version} 执行成功`);
            } catch (err) {
                await connection.rollback();
                console.error(`迁移 ${version} 失败:`, err);
                throw err;
            }
        }
    }

    await connection.end();
}

runMigrations().catch(err => console.error('迁移流程失败:', err));

4. 实现回滚功能 #

写一个rollback.js，逻辑是找到最后执行的迁移版本，执行对应的down.sql并删除元数据记录：

const fs = require('fs');
const path = require('path');
const mysql = require('mysql2/promise');

async function rollbackMigration() {
    const connection = await mysql.createConnection({
        host: 'localhost',
        user: 'root',
        password: 'your_db_password',
        database: 'your_target_db'
    });

    // 获取最后执行的迁移版本
    const [lastMigration] = await connection.execute(`
        SELECT migration_version FROM migrations_metadata 
        ORDER BY executed_at DESC LIMIT 1
    `);

    if (lastMigration.length === 0) {
        console.log('没有可回滚的迁移');
        await connection.end();
        return;
    }

    const version = lastMigration[0].migration_version;
    console.log(`开始回滚迁移: ${version}`);
    const downSql = fs.readFileSync(path.join(__dirname, 'migrations', version, 'down.sql'), 'utf8');

    await connection.beginTransaction();
    try {
        await connection.execute(downSql);
        await connection.execute('DELETE FROM migrations_metadata WHERE migration_version = ?', [version]);
        await connection.commit();
        console.log(`回滚 ${version} 成功`);
    } catch (err) {
        await connection.rollback();
        console.error(`回滚 ${version} 失败:`, err);
        throw err;
    }

    await connection.end();
}

rollbackMigration().catch(err => console.error('回滚流程失败:', err));

三、数据库创建与版本管理的完整流程 #

1. 项目初始化阶段 #

• 确定数据库类型（MySQL/PostgreSQL等），手动或用脚本创建基础数据库；
• 初始化迁移目录和元数据表；
• 编写第一个初始化Schema的迁移脚本，提交到Git；
• 团队成员拉取代码后，执行node migrate.js同步数据库。

2. 日常开发阶段 #

• 当需要修改Schema（新增表、调整字段等），创建新的迁移版本文件夹，编写对应的up.sql和down.sql；
• 本地执行迁移脚本验证效果，确认无误后提交到Git；
• 其他团队成员拉取代码后，执行迁移脚本同步自己的数据库。

3. 部署阶段 #

• 测试环境：CI/CD流程中加入node migrate.js命令，部署代码后自动执行迁移；
• 生产环境：先在预生产环境验证迁移脚本，执行前务必备份数据库，然后手动或通过CI/CD执行迁移。

4. 回滚场景 #

• 若迁移执行失败或发现Schema问题，立即执行node rollback.js回滚到上一个版本；
• 修复迁移脚本的问题后，重新执行迁移。

内容的提问来源于stack exchange，提问作者Thuận Phạm