如何解决PostgreSQL中node-pg-migrate的前置迁移冲突报错

迁移文件内容 #

exports.shorthands = undefined;

exports.up = pgm => {
    pgm.sql(`
        CREATE TABLE comments (
            id SERIAL PRIMARY KEY,
            created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
            contents VARCHAR(240) NOT NULL
            );
        `);
};

exports.down = pgm => {
    pgm.sql(`
        DROP TABLE comments; 
    `);
};

执行迁移时的错误 #

执行命令：

DATABASE_url=postgres://hrushi@localhost:5432/socialnetwork npm run migrate up

报错信息：

> ig@1.0.0 migrate
> node-pg-migrate up

Error: Not run migration 1679060101968_table-comments is preceding already run migration 1678898668879_table-comments
    at checkOrder (/Users/hrushi/Desktop/ig/node_modules/node-pg-migrate/dist/runner.js:110:19)
    at exports.default (/Users/hrushi/Desktop/ig/node_modules/node-pg-migrate/dist/runner.js:156:13)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)

推测原因：之前尝试创建过同名迁移文件，导致数据库的迁移记录表中残留旧记录。

已尝试的无效操作 #

• 删除comments表（无法删除整个数据库，因存在其他表）
• 删除迁移文件目录后重新编写，错误依旧
• 终止pgadmin中所有进程
• 登录psql执行\d命令，未发现comments表
• 尝试使用knex，因缺少配置文件报错：

npx knex migrate:latest
No configuration file found and no commandline connection parameters passed
Error: No configuration file found and no commandline connection parameters passed
    at checkConfigurationOptions (/Users/hrushi/Desktop/ig/node_modules/knex/bin/utils/cli-config-utils.js:104:11)
    at initKnex (/Users/hrushi/Desktop/ig/node_modules/knex/bin/cli.js:57:5)
    at Command.<anonymous> (/Users/hrushi/Desktop/ig/node_modules/knex/bin/cli.js:247:32)
    at Command.listener [as _actionHandler] (/Users/hrushi/Desktop/ig/node_modules/commander/lib/command.js:482:17)
    at /Users/hrushi/Desktop/ig/node_modules/commander/lib/command.js:1283:65
    at Command._chainOrCall (/Users/hrushi/Desktop/ig/node_modules/commander/lib/command.js:1177:12)
    at Command._parseCommand (/Users/hrushi/Desktop/ig/node_modules/commander/lib/command.js:1283:27)
    at /Users/hrushi/Desktop/ig/node_modules/commander/lib/command.js:1081:27
    at Command._chainOrCall (/Users/hrushi/Desktop/ig/node_modules/commander/lib/command.js:1177:12)
    at Command._dispatchSubcommand (/Users/hrushi/Desktop/ig/node_modules/commander/lib/command.js:1077:23)

解决方案 #

1. 清理数据库中的旧迁移记录 #

node-pg-migrate会在数据库中创建pgmigrations表，记录已执行的迁移。操作步骤：

• 登录psql连接目标数据库：

  psql postgres://hrushi@localhost:5432/socialnetwork
  

• 查询迁移记录表，找到旧记录：

  SELECT * FROM pgmigrations;
  

• 删除旧的1678898668879_table-comments记录：

  DELETE FROM pgmigrations WHERE name = '1678898668879_table-comments';
  

2. 重新执行迁移 #

完成清理后，再次运行迁移命令：

DATABASE_url=postgres://hrushi@localhost:5432/socialnetwork npm run migrate up

3. 后续避坑建议 #

• 不要手动删除迁移文件后重新创建同名（仅时间戳不同）的迁移文件
• 需要回滚迁移时，使用npm run migrate down命令，而非手动删除表或文件

内容的提问来源于stack exchange，提问作者Hrushi