前端工程師邁向後端之路 5 – PostgreSQL migration：淺談 database migration ( 資料庫遷移 )？

在上一篇：前端工程師邁向後端之路 4 – 用 Node.js 架設 http server 簡單用 Node.js 架設 http server 後，接著會在 Node.js 寫 API 來操作 database，在這之前我們需要先了解一下 database migration( 資料庫遷移 )，以及為什麼需要 database migration。

1. 為什麼需要 database migration？

試想一個情況：當你開始寫一個 side project 的時候，會開始對 database 做一些操作：create tables，insert 很多 data 等等，剛開始一定都在本機做開發，這邊假設是你的 macbook pro。

結果有一天，你在喝茶的時候（正常的茶），一個手滑直接把茶打翻在筆電上面，整組歹料料，不過是富二代的你並不擔心，馬上到信義區買了最新的 M1 iMac，並且心想：沒關係，我的 side project 都有 push 到 github repo，可以用錢解決的事都是小事 😆

正當你興高采烈的開箱完 iMac，並且把 side project git clone 到你的新電腦時你想到了一件事：阿我的 database 怎麼辦？我之前都是直接對 database 做操作啊！這下子你必須要重新在本機的 database create 一堆 tables，可想而知：再開發的時候直接對 database 做操作並不是一個好的選擇，因為你並沒有將對 database 的操作給記錄下來，這樣一旦你換了地方開發，或是在多人協作專案時別人 git checkout 到你的分支時，並不知道你對 database 做了什麼操作，就有可能造成開發上的錯誤以及不變。

database migration 就是用來解決這個問題⏤記錄開發中對 database 的操作，可以把它想成 database 的版本控制。

一般來說，在開發中如果需要直接對資料庫操作的話，會新增一個 migration 檔案，並將 SQL 寫在這個檔案裡，再到 terminal 執行 migrate up 執行這個檔案來對資料庫做操作，這樣一來即便你換了一台電腦，只要在新電腦的 terminal（在 project 的路徑底下）執行 migration up，就會直接執行所有 migration 的檔案，完成對 database 的所有操作。

2. PostgreSQL migration

本專案會用 Postgres.app 在本機上架設 PostgreSQL server，可以參考之前寫的文章：前端工程師邁向後端之路 3 – PostgreSQL 教學：架設 database server。

2.1 確定 PostgreSQL server 是開機的狀態

因為執行 migration 時是針對 PostgreSQL server 的 database 做操作，因此要確定要連上的 PostgreSQL server 是開機的狀態才可以操作 database，這邊我選擇用 Postgres.app 做開發，因此只需要在本機上執行 Postgres.app 就相當於將 PostgreSQL server 開機了。

2.2 在 pgAdmin 新增 database

pgAdmin 是 PostgreSQL 的 GUI，操作方法一樣可以參考：前端工程師邁向後端之路 3 – PostgreSQL 教學：架設 database server。

打開 pgAdmin 後，在 localhost server 新增一個屬於這個專案的 database：

Owner 的部分是等等會填到的參數，可以先把它記下來。

點擊 Save 後就成功建立一個新的 database 了！

2.3 安裝 node-pg-migrate

npm 中有一個 dependency：node-pg-migrate，就是在 Node.js 中實現 PostgreSQL 的 migration 功能，接下來就來安裝這個 dependency 吧！

$ yarn add node-pg-migrate

因為 node-pg-migrate 預設新增的是 JavaScript 檔案，但我們的專案是用 TypeScript 開發，因此根據官方文件，還需要安裝一個 config dependency 來輔助開發：

$ yarn add config

接著新增一個 default.json 在 config 資料夾底下：

{
  "db": {
    "user": "jimmy2952",
    "password": "",
    "host": "localhost",
    "port": 5432,
    "database": "restful-tutorial",
    "tsconfig": "./tsconfig.json",
    "migration-filename-format": "utc"
  }
}

database 的設定都寫在這裡面，tsconfig 屬性對應到的則是專案中的 tsconfig.json 檔，migration-filename-format 屬性則是指定產生檔案時的時間格式，utc 為：20200605075829074，timestamp 則是：1591343909074。

2.4 修改 package.json

新增一個 script 讓我們方便透過 node-pg-migrate 來執行 migration 檔案：

{
  "scripts": {
    ...
    "migrate": "node-pg-migrate"
  }
}

3. 執行 database migration

相關的設定都處理好之後就可以開始執行 database migration 了。

3.1 新增 migration 檔案

用 node-pg-migrate 新增 migration 檔案的指令是：

$ yarn migrate create [migration_name]

[migration_name] 通常會取和資料庫操作相關的名字，例如：我要新增一個 users table：

$ yarn migrate create user-table

node-pg-migrate 就會自動幫你新增一個 migration 檔案在 migrations 資料夾底下：

./migrations/
└── 20210817161036553_user-table.ts

打開這個檔案會長這樣：

/* eslint-disable @typescript-eslint/camelcase */
import { MigrationBuilder, ColumnDefinitions } from 'node-pg-migrate';

export const shorthands: ColumnDefinitions | undefined = undefined;

export async function up(pgm: MigrationBuilder): Promise<void> {
}

export async function down(pgm: MigrationBuilder): Promise<void> {
}

接著我們就可以將對資料庫操作的 SQL 指令寫在這個檔案裡面了，其中 up function 代表的是要新增的操作，down function 代表的則是逆向操作，因此如果要新增一個 users table 的話，這個檔案就會變成這樣：

import { MigrationBuilder, ColumnDefinitions } from 'node-pg-migrate';

export const shorthands: ColumnDefinitions | undefined = undefined;

export async function up(pgm: MigrationBuilder): Promise<void> {
  pgm.sql(`
    CREATE TABLE users (
      id SERIAL PRIMARY KEY,
      username VARCHAR,
      email VARCHAR
    )
  `);
}

export async function down(pgm: MigrationBuilder): Promise<void> {
  pgm.sql(`
    DROP TABLE users;
  `);
}

3.2 執行 migration up

接著打開 terminal 執行：

$ yarn migrate up

如此一來，node-pg-migrate 會由舊到新執行所有比目前 migration 檔案還新的 up function。，執行成功後我們可以用 pgAdmin 檢查是否有新增 table：

可以看到確實新增了 users table，除此之外 node-pg-migrate 還自動新增了一個 pgmigrations table 來記錄執行的 migration 檔案和時間：

我們可以再新增一個 migration 檔案在 users table 新增幾筆資料：

$ yarn migrate create init-user-data

接著修改檔案：

import { MigrationBuilder, ColumnDefinitions } from 'node-pg-migrate';

export const shorthands: ColumnDefinitions | undefined = undefined;

export async function up(pgm: MigrationBuilder): Promise<void> {
  pgm.sql(`
    INSERT INTO users (username, email)
    VALUES
      ('Jimmy', '[email protected]'),
      ('Hello', '[email protected]'),
      ('World', '[email protected]');
  `);
}

export async function down(pgm: MigrationBuilder): Promise<void> {
  pgm.sql(`
    DELETE FROM users WHERE username = 'Jimmy';
    DELETE FROM users WHERE username = 'Hello';
    DELETE FROM users WHERE username = 'World';
  `);
}

最後再執行一次：

$ yarn migrate up

可以在 pgAdmin 看到確實有新增了三筆資料：

3.3 執行 migrate down

有時候可能只是為了測試別人的 branch 才會 migration up，當測試完成後你會希望 database 回到測試前的狀態，這時候就需要 migration down 來回朔 database 的狀態，和 migration up 最大的差別是：migration down 一次只執行一個 migration 檔案的 down function。

執行 migration down：

$ yarn migrate down
yarn run v1.22.10
$ node-pg-migrate down
> Migrating files:
> - 20210817163347564_init-user-data
### MIGRATION 20210817163347564_init-user-data (DOWN) ###

    DELETE FROM users WHERE username = 'Jimmy';
    DELETE FROM users WHERE username = 'Hello';
    DELETE FROM users WHERE username = 'World';
  ;
DELETE FROM "public"."pgmigrations" WHERE name='20210817163347564_init-user-data';

Migrations complete!
✨  Done in 2.51s.

可以看到只有執行最後 migration 檔案的 down function。那我們為了測試 migration down 和 migration up 的差別，就再執行一次 migration down，讓 database 回到最原始的狀態：

$ yarn migrate down

再執行 migrate up：

$ yarn migrate up  
yarn run v1.22.10
$ node-pg-migrate up
> Migrating files:
> - 20210817161036553_user-table
> - 20210817163347564_init-user-data
### MIGRATION 20210817161036553_user-table (UP) ###

    CREATE TABLE users (
      id SERIAL PRIMARY KEY,
      username VARCHAR,
      email VARCHAR
    )
  ;
INSERT INTO "public"."pgmigrations" (name, run_on) VALUES ('20210817161036553_user-table', NOW());


### MIGRATION 20210817163347564_init-user-data (UP) ###

    INSERT INTO users (username, email)
    VALUES
      ('Jimmy', '[email protected]'),
      ('Hello', '[email protected]'),
      ('World', '[email protected]');
  ;
INSERT INTO "public"."pgmigrations" (name, run_on) VALUES ('20210817163347564_init-user-data', NOW());


Migrations complete!
✨  Done in 1.81s.

可以看到如剛剛所說的，migrate up 會執行所有比目前 migration 檔案還新的 up function。

今天簡單介紹了如何在 Node.js 中用 migration 來記錄對 PostgreSQL database 的操作，希望這篇文章對你有幫助！

了解完 database migration 後，接著就要來用 RESTful 設計 API：前端工程師邁向後端之路 6 – 設計 RESTful API

4. 參考資料

SQL and PostgreSQL: The Complete Developer’s Guide
node-pg-migrate official document

如果覺得我的文章有幫助的話，歡迎幫我的粉專按讚哦~謝謝你！