【数据库迁移】node-pg-migrate/pg

安装
```
npm i -S node-pg-migrate pg
```

添加快捷脚本到package.json

"migrate": "./node_modules/.bin/node-pg-migrate"

创建一份迁移(以account表为例)

npm run migrate create account table create

会在根目录下的 migrations文件夹 内，生成1641280352090_account-table-create.js文件，内容为:

/* eslint-disable camelcase */

exports.shorthands = undefined;

exports.up = pgm => {};

exports.down = pgm => {};

改写up函数的内容

exports.up = pgm => {
    pgm.createTable('account', {
        uuid: {
            type: 'uuid',
            primaryKey: true,
        },
        nation_code: {
            type: 'varchar(10)',
            default: '86',
        },
        mobile: {
            type: 'varchar(50)',
            unique: true,
        },
        email: {
            type: 'varchar(50)',
            unique: true,
        },
        password: {
            type: 'varchar(50)',
            notNull: true
        },
        display_name: {
            type: 'varchar(50)',
        },
        avatar: {
            type: 'text',
        },
        region: {
            type: 'text',
        },
        info_json: {
            type: 'jsonb',
            default: '{}',
        },
        balance: {
            type: 'numeric',
        },
        vip_last_day: {
            type: 'date',
        },
        ctime: {
            type: 'timestamp',
            notNull: true,
            default: pgm.func('current_timestamp'),
        },
        mtime: {
            type: 'timestamp',
            notNull: true,
            default: pgm.func('current_timestamp'),
        }
    })
    pgm.createIndex('account', 'mobile', {
        name: 'account_mobile_idx',
        method: 'btree'
    })

    pgm.createIndex('account', 'email', {
        name: 'account_email_idx',
        method: 'btree'
    })
};

改写up函数的内容

exports.down = pgm => {
    pgm.dropIndex('account', 'email', {
        name: 'account_email_idx'
    })
    pgm.dropIndex('account', 'mobile', {
        name: 'account_mobile_idx'
    })
    pgm.dropTable('account');
};

对于node16，需要把文件名后缀改为.cjs
运行npm run migrate up，会自动迁移，并对pgmigrations表插入一条迁移记录
- 记得要在.env上面添加
```
DATABASE_URL=postgresql://root:密码@192.168.180.228:5432/数据库名字
```

第二次运行

npm run migrate create word table create

报错

no such file or directory, 
open '/Users/mangou/project/backend/koa_demo
/node_modules/node-pg-migrate/templates
/migration-template.cjs'

原因
- 上面文件后缀改成cjs后，包自己生成的新文件就是cjs

办法

cd /Users/mangou/project/backend/koa_demo/node_modules/node-pg-migrate/templates

mv migration-template.js migration-template.cjs

Keyboard shortcuts

【数据库迁移】node-pg-migrate/pg