使用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)
沒有留言:
張貼留言