NodeJs 使用 sequelize 建立 PostgreSQL 資料表查詢 id 預設欄位 type

Posted on by Clarence

繼上一篇PostgreSQL 在宣告 serial 遇到的雷我們來試試看 NodeJS 中使用 sequelize 它會不會幫我們把這個雷避掉，要避掉這個雷只要把上一次提到的 id 欄位 type 改成 bigint 就不管是 PostgreSQL 8 或是 PostgreSQL 9 都不會有這個問題了

使用 sequelize 建立使用 PostgreSQL 的專案

建立一個 NodeJS 專案

在這邊因為是範例我們用預設狀態把一個專案建立起來

mkdir sequelize-test
npm init -y

安裝 sequelize 與 PostgreSQL 套件

npm install sequelize pg

初始化 sequelize

npx sequelize-cli init

修改 sequelize 使用 PostgreSQL

修改 sequelize-test/config/config.json 直接複製貼上即可

{
  "development": {
    "username": "postgres",
    "password": password,
    "database": "database_development",
    "host": "127.0.0.1",
    "dialect": "postgres"
  },
  "test": {
    "username": "root",
    "password": null,
    "database": "database_test",
    "host": "127.0.0.1",
    "dialect": "postgres"
  },
  "production": {
    "username": "root",
    "password": null,
    "database": "database_production",
    "host": "127.0.0.1",
    "dialect": "postgres"
  }
}

使用 sequelize 建立資料庫

npx sequelize-cli db:create

Sequelize CLI [Node: 14.8.0, CLI: 6.2.0, ORM: 6.3.5]

Loaded configuration file "config/config.json".
Using environment "development".
Database database_development created.

查詢資料庫列表

postgres=# \l
                                      List of databases
         Name         |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges
----------------------+----------+----------+------------+------------+-----------------------
 database_development | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
 postgres             | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
 template0            | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
                      |          |          |            |            | postgres=CTc/postgres
 template1            | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
                      |          |          |            |            | postgres=CTc/postgres
(4 rows)

使用 sequelize 建立資料表

npx sequelize-cli model:generate --name user --attributes name:string

Sequelize CLI [Node: 14.8.0, CLI: 6.2.0, ORM: 6.3.5]

New model was created at /Users/clarence/Documents/node/sequelize-test/models/user.js .
New migration was created at /Users/clarence/Documents/node/sequelize-test/migrations/20200000000000-create-user.js .

使用 sequelize migrate

npx sequelize-cli db:migrate

Sequelize CLI [Node: 14.8.0, CLI: 6.2.0, ORM: 6.3.5]

Loaded configuration file "config/config.json".
Using environment "development".
== 20200000000000-create-user: migrating =======
== 20200000000000-create-user: migrated (0.035s)

查詢資料表

database_development=# \d
              List of relations
 Schema |     Name      |   Type   |  Owner
--------+---------------+----------+----------
 public | SequelizeMeta | table    | postgres
 public | users         | table    | postgres
 public | users_id_seq  | sequence | postgres
(3 rows)

這邊發現 type 一樣是 integer 那我們再來看看 users_id_seq 

database_development=# \d users
                                      Table "public.users"
  Column   |           Type           | Collation | Nullable |              Default
-----------+--------------------------+-----------+----------+-----------------------------------
 id        | integer                  |           | not null | nextval('users_id_seq'::regclass)
 name      | character varying(255)   |           |          |
 createdAt | timestamp with time zone |           | not null |
 updatedAt | timestamp with time zone |           | not null |
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)

查詢 users_id_seq 可以發現 Type 是 bigint 與 Maximum 依然是 9223372036854775807 所以 sequelize 並不會幫我們修復這個問題

database_development=# \d users_id_seq
                        Sequence "public.users_id_seq"
  Type  | Start | Minimum |       Maximum       | Increment | Cycles? | Cache
--------+-------+---------+---------------------+-----------+---------+-------
 bigint |     1 |       1 | 9223372036854775807 |         1 | no      |     1
Owned by: public.users.id

手動修復問題把 id 改成 bigint

手動修改 id 改成 bigint，要修改的檔案是 migrations/20200000000000-create-user.js，把 id 修改成如下

{
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.BIGINT
      },
      name: {
        type: Sequelize.STRING
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE
      }
}

移除剛剛建立的 table

npx sequelize-cli db:migrate:undo

Sequelize CLI [Node: 14.8.0, CLI: 6.2.0, ORM: 6.3.5]

Loaded configuration file "config/config.json".
Using environment "development".
== 20200000000000-create-user: reverting =======
== 20200000000000-create-user: reverted (0.017s)

重新 migrate，並且查看 table

database_development=# \d users
Table "public.users"
Column | Type | Collation | Nullable | Default
-----------+--------------------------+-----------+----------+-----------------------------------
id | bigint | | not null | nextval('users_id_seq'::regclass)
name | character varying(255) | | |
createdAt | timestamp with time zone | | not null |
updatedAt | timestamp with time zone | | not null |
Indexes:
"users_pkey" PRIMARY KEY, btree (id)

如此就可以避免前一篇的問題再發生了

參考資料

No comments yet