霍爾筆記

全端網站設計範例：連結資料庫

約 2,011 字 --

本篇為「全端網站架構」中的後端範例及細節。接續前兩篇：全端網站設計範例：後端登入驗證機制。

此專案的資料庫將會使用 Knex 這個框架來實做，搭配 Docker 啟動 Local 的 MySQL Server。

用 Docker, Docker-Compose 啟動 MySQL

由於我們要使用的資料庫是 MySQL，本身就是一個需要安裝且常駐的 Server，用 Docker 來配置需要設定的環境變數然後啟動，會是很方便的方式，不需要考慮你的 Local 端用的是什麼作業系統。

然而 Docker 在設定 Exposed 的 Port 和其它環境變數時，雖然可以透過指令加入 Parameters，但每次啟動都要打上長長一串指令也不是很方便易讀。這時把這些設定都寫入設定檔，用 Docker-compose 啟動就會變的更加方便。

以下為 docker-compose.yml

version: '3'
services:
  mysql:
    image: mysql:8.0
    restart: always
    command: --default-authentication-plugin=mysql_native_password --character-set-server=utf8 --collation-server=utf8_general_ci
    ports:
      - 3306:3306
    environment:
      MYSQL_ROOT_PASSWORD: root
      MYSQL_DATABASE: test
    volumes:
      - mysql:/var/lib/mysql
volumes:
  mysql:

裡面做的事，比較重要的是

  • services 下新增一個 mysql 的 Service
  • image: mysql:8.0 裡面取用 MySQL 8.0 的 Docker Image
  • command 後打的一串主要是因為儲存資料會有中文，所以把編碼設定為 utf8
  • ports 對應本機端的 Port Mapping 到 Docker Container 的 Port
  • environment 下設定預設的 Root 密碼（帳號為 root 的密碼）及 Database 名稱
  • volumes，在 services/mysql 下做的事為 Mapping 本機的檔案目錄至 Docker 內的檔案目錄，這樣做的目的是為了把 MySQL 產生的資料存在本機端我們命名為 mysql 的資料夾內（實際存放在 Docker 指定的位置），以避免把服務關掉後資料就不見了

接下來每次啟動和停止 Docker 的服務就只要在有 docker-compose.yml 的目錄下打

  • 啟動：docker-compose up
  • 停止：docker-compose down

就可以啟動和停止 MySQL 了。

透過 MySQL Client 下指令

由於剛剛啟動的是 MySQL Server，如果想要對資料庫做一些操作、下一些 SQL 指令的話，一般會透過有圖形介面的 MySQL Client 如 MySQL Workbench 或 phpMyAdmin 等等，或是 CLI 版的 MySQL Client。

如果要用有圖形介面的 MySQL Client，或本機端的其它 Client，可以直接以本機的 MySQL Server URL localhost:3306 配上剛剛輸入的帳號密碼去連線。但我們這邊打算直接用 CLI 去操作，那就只需要用 Docker Image 自帶的 Client 即可。

在 MySQL 啟動的情況下，下 docker ps 查看啟動中的 Docker Container

$ docker ps
CONTAINER ID   IMAGE       COMMAND                  CREATED         STATUS         PORTS                               NAMES
0fbb17d539f0   mysql:8.0   "docker-entrypoint.s…"   7 seconds ago   Up 4 seconds   0.0.0.0:3306->3306/tcp, 33060/tcp   verp-api_mysql_1

找到 Container ID 後，用此 ID 執行 Docker 指令

$ docker exec -it b83db8166f3f mysql -uroot -proot
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.25 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

其中 -uroot -proot 為登入的 username 及 password，成功後便可看到登入後的畫面，就可以下 SQL 去做點事了。

Knex.js

Knex.js 是一個 SQL Query Builder 的框架，抽象出一層，隔離基於一些 Node.js 所寫的 Database Client，像是 pg、sqlite 還有我們要用的 mysql、mysql2。讓我們用比較簡潔的 JavaScript 語法就能組合出 SQL Query。

也就是說 Knex.js 主要 Focus 在 Query Builder 這塊，但也搭配使用這些 Libraries 把連線的部分做完了。

安裝及使用

我們開始來安裝 Knex.js

$ yarn add knex mysql

將套件加入至專案後，在專案根目錄新增一個資料夾 db，底下新增 index.ts，我們要透過 Knex 和 MySQL 連線。

index.ts 內容如下

import { knex as Knex } from 'knex';
import log from 'npmlog';

const knexConfig = {
  client: 'mysql',
  version: '8.0',
  connection: {
    host: '127.0.0.1',
    user: 'root',
    password: 'root',
    database: 'test',
  },
};

const knex = Knex(knexConfig);

// Test if connection has been established
knex
  .select(knex.raw('1'))
  .then(() => {
    log.info('db', `Successfully connected to MySQL ${knexConfig.connection.host}`);
  })
  .catch((err) => {
    log.error('db', `Failed connecting to MySQL with error: ${err.message}`);
  });

export default {
  knex,
};

當 Knex 物件被產生時，就會自動連線至指定的 Database，為了測試有沒有成功，我們下了一個 SELECT 1 的 SQL Query，成功或失敗皆印出 Log。

假如一切狀況正常，Database 已經啟動，參數也沒輸入錯誤的話，就能看到 Successfully connected to MySQL 127.0.0.1 的訊息。

定義 MySQL Schema，Knex Migration

既然能和資料庫連接上了，要能夠讀寫資料，勢必要先設計好 Database Schemas。在設計 Schemas 時通常會面臨兩種選擇

  1. 先寫再說
  2. 使用 Migration

第一種選擇，先寫 Schema，之後 Schema 有修改的話，可以直接砍掉重練，但缺點就是正式上線之後這種做法就不太實際了。所以這邊採第二種選擇，從剛開始就把 Migration 的流程建立好，雖然較為複雜，但開發時就照著正式上線的流程走也是不錯的選擇。

我們首先在專案根目錄建立 knexfile.ts

module.exports = {
  client: 'mysql',
  connection: {
    host: '127.0.0.1',
    port: '3306',
    user: 'root',
    password: 'root',
    database: 'test',
  },
};

裡面的內容和 db/index.ts 的設定值其實是一樣的，新建 knexfile 的主要用意是使用 Knex CLI 來做 Migration 以及 Seed 的操作。

由於要使用 Knex CLI，可以全域安裝 knex（如：yarn global add knex），或直接在專案底下 npx knex ... 也是一種方法，這裡假定全域安裝 Knex CLI 完成。

再來透過 Knex CLI 建立 Migration，我們先建立 User 相關的 Schema，所以取名為 user

$ knex migrate:make user
Requiring external module ts-node/register
Created Migration: .../api/migrations/2021XXXXXXXX_user.ts

有個叫 migrations 的資料夾便會被建立，以及一個 ..._user.ts 的 Migration file，更新此檔案成

import { Knex } from 'knex';

export async function up(knex: Knex): Promise<void> {
  return knex.schema.createTable('Users', (table) => {
    table.increments('id').primary();
    table.string('username').unique().notNullable();
    table.string('password').notNullable();
    table.string('name').unique().notNullable();
  });
}

export async function down(knex: Knex): Promise<void> {
  return knex.schema.dropTable('Users');
}

裡面就是 User 這個 Table 的 Schema 定義，簡單的整數 id 做為 Primary Key、裡面有 username、password 和 name。

接著下 knex migrate:up 這個指令，就會把這個 Table 建立起來，此時到 MySQL 底下看

mysql> use test;
mysql> show tables;
+----------------------+
| Tables_in_test       |
+----------------------+
| knex_migrations      |
| knex_migrations_lock |
| Users                |
+----------------------+
3 rows in set (0.00 sec)

可以看到多了 Users 這個 Table，然而同時也多了 knex 用來儲存 Migration 資訊的兩個 Table。

如果要還原這個 Table，我們也只要下 knex migrate:down 就可以回復成上一動，Users 就會消失不見，這是由於我們在 down 這個 Function 中所做的動作是 Drop Table。

Knex Seeding

有了資料庫，在開發時會需要一些資料，操作起來比較方便，不用每次 Reset 資料庫都手動更新資料。這時就可以「播種」給資料庫，讓資料庫安裝後有初始資料，也稱做 Database Seeding。

就來用 Knex CLI 來做這個操作吧，首先用指令 knex seed:make 建立一個放 User 資料的檔案

$ knex seed:make users

便可以看到專案根目錄新建了一個叫 seeds 的資料夾，裡面多了一個 users.ts 的檔案，加入一些資料如下

import { Knex } from 'knex';
import bcrypt from 'bcryptjs';

async function seed(knex: Knex): Promise<void> {
  await knex('Users').del();
  await knex('Users').insert([
    {
      id: 1,
      username: 'admin',
      password: bcrypt.hashSync('admin'),
      name: 'Admin',
    },
    {
      id: 2,
      username: 'john',
      password: bcrypt.hashSync('john'),
      name: 'John',
    },
    {
      id: 3,
      username: 'jane',
      password: bcrypt.hashSync('jane'),
      name: 'Jane',
    },
  ]);
}

export default {
  seed,
};

其中密碼的部分先透過 bcrypt Hash 過後再存下來。

接著透過 Knex 指令把資料寫入（要稍微注意的是這段程式碼執行後會先刪除所有此 Table 的資料）

$ knex seed:run --specific=users.ts
Ran 1 seed files

此時進入資料庫看，就能發現剛剛建立的資料了。

mysql> select * from Users;
+----+----------+--------------------------------------------------------------+-------+
| id | username | password                                                     | name  |
+----+----------+--------------------------------------------------------------+-------+
|  1 | admin    | $2a$12$42C6V2G6TvbgfTIlYMneCuSnlRm9C0VnEtTtyIJ8LmSh9F7fs2NNu | Admin |
|  2 | john     | $2a$12$wn1.tixiDTrw2E/mQQw44.Bl1OOgkqiriLL3cWNcjT/xj1Rg.c9HC | John  |
|  3 | jane     | $2a$12$pF6DQhHFyLeeiHtspgz8UOpZIqY4yOTW0O6eH06FUzdOfK6s2r7k. | Jane  |
+----+----------+--------------------------------------------------------------+-------+
3 rows in set (0.00 sec)

參考資料

  1. Knex.js
全端網站設計範例