Liquibase 在PostgreSQL建立資料表

使用Liquibase在PostgreSQL資料庫建立資料表。

範例環境：

• macOS BigSur version 10.15.5
• PostgreSQL 14.1
• Liquibase 4.10.0

事前要求

參考「Mac Homebrew安裝PostgreSQL」安裝PostgreSQL資料庫。

參考「PostgreSQL 建立資料庫管理使用者」建立admin。

參考「Mac 安裝Liquibase by Homebrew」安裝liquibase。

Liquibase設定

在任意目錄（e.g. liquibase-demo）新增liquibase.properties及changelog.sql。

PostgreSQL的JDBC url格式為jdbc:postgresql://<host>:<port>/<database>。所以連到本機PostgreSQL的postgres資料庫的JDBC url為jdbc:postgresql://localhost:5432/postgres。

liquibase.properties

changeLogFile=changelog.sql
liquibase.command.url=jdbc:postgresql://localhost:5432/postgres
liquibase.command.username: admin
liquibase.command.password: 12345

changelog.sql

--liquibase formatted sql

--changeset author:1
CREATE TABLE IF NOT EXISTS employee (
    id bigserial PRIMARY KEY,
    name varchar(60) UNIQUE NOT NULL,
    age integer,
    created_at timestamp NOT NULL
);

建立資料表

在liquibase.properties目錄以命令列執行liquibase update依liquibase.properties的參數讀取changelog.sql更新資料庫狀態。

~/../liquibase-demo$ liquibase update
####################################################
##   _     _             _ _                      ##
##  | |   (_)           (_) |                     ##
##  | |    _  __ _ _   _ _| |__   __ _ ___  ___   ##
##  | |   | |/ _` | | | | | '_ \ / _` / __|/ _ \  ##
##  | |___| | (_| | |_| | | |_) | (_| \__ \  __/  ##
##  \_____/_|\__, |\__,_|_|_.__/ \__,_|___/\___|  ##
##              | |                               ##
##              |_|                               ##
##                                                ## 
##  Get documentation at docs.liquibase.com       ##
##  Get certified courses at learn.liquibase.com  ## 
##  Free schema change activity reports at        ##
##      https://hub.liquibase.com                 ##
##                                                ##
####################################################
Starting Liquibase at 23:22:08 (version 4.10.0 #2501 built at 2022-05-04 14:27+0000)
Liquibase Version: 4.10.0
Liquibase Community 4.10.0 by Liquibase
Do you want to see this operation's report in Liquibase Hub, which improves team collaboration? 
If so, enter your email. If not, enter [N] to no longer be prompted, or [S] to skip for now, but ask again next time [S]: 
S
Skipping auto-registration
Running Changeset: changelog.sql::1::employee
Liquibase command 'update' was executed successfully.
~/../liquibase-demo$

以psql檢視目前的資料表可看到新增的employee資料表及Liquibase的databasechangelog、databasechangeloglock資料表。

postgres=> \d
                 List of relations
 Schema |         Name          |   Type   | Owner
--------+-----------------------+----------+-------
 public | databasechangelog     | table    | admin
 public | databasechangeloglock | table    | admin
 public | employee              | table    | admin
 public | employee_id_seq       | sequence | admin
(4 rows)

• PostgreSQL 建立資料表
• Liquibase Getting Started with SQL教學範例