Clever Geek Handbook

Setting up CI / CD scripts for database migration from scratch using GitLab and Liquibase

Prologue

Good afternoon, dear readers. Quite recently, I had to master a new area of ​​CI / CD, setting up from scratch the delivery of database migration scripts in one of the projects. At the same time, it was difficult to overcome the very first stage of "the eyes are afraid", when the task seems to be clear, but you do not know where to start. However, the question turned out to be much simpler than it seemed initially, while giving undeniable advantages at the cost of several hours of work and not requiring any additional funds, except for those indicated in the title.





, , , , .













  • Liquibase









  • GitLab Runner





  • CI/CD









, . C#/.NET, Vue.js Postgres.





CI/CD " " CI/CD, .





, CI/CD, , , , CI/CD, .





GitLab + Liquibase , , , . GitLab CI/CD & Liquibase .





, , . , .





Liquibase. , .





:





  • GitLab Community Edition – Git





  • GitLab Runner – CI/CD





  • Liquibase, 4.3.4





  • Liquibase





  • Liquibase GitLab Runner GitLab . .





  • 3 . .





, , , Premium-.





( ) : https://github.com/Doomer3D/Gliquibase.





GitLab CI/CD

CI/CD , , , ( /) ( ) , . GitLab pipelines, GitLab Runner, GitLab , .





GitLab Community Edition 13.x.





Liquibase

Liquibase («») – c , . , Liquibase (changeset). SQL-, - , . : https://www.liquibase.org/get-started/databases.





Liquibase Java, JVM.





Oracle 19 , . , , .





, , , .





, CI/CD, (pipeline), (stage), (job).





– (deploy), .. . , . , – . , – (deploy-dev) (deploy-prod), . , , , .





(GitLab Runner) – , , . , , / , CI/CD, , , , ..





! GitLab http, , GitLab, GitLab , .





– , , , , .





Liquibase . Liquibase , Liquibase .





, , , . :





failed:





, , . , VPN - GitLab , pending, .. , , . hosts .





, , :





  1. .





  2. , .





  3. , .





  4. , Liquibase.





  5. Liquibase /.





  6. ...





  7. Profit!





.





Liquibase

Liquibase. , , Liquibase.





Liquibase: https://docs.liquibase.com/concepts/basic/home.html





, , , , Liquibase, «», , , .





Liquibase – . (changeset) – . , , , /, . (changelog), , , .





changelog

Liquibase . – , . / . , : SQL, XML, JSON YAML. SQL XML .





, .. . master.xml. . , , . :





  • , .





  • , , .





, // - , . .





Project structure

Liquibase db/changelog, - master.xml





, 156 157, . = . common , -, . -, .





master.xml:





<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
                   xmlns:pro="http://www.liquibase.org/xml/ns/pro"
                   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                   xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.2.xsd     http://www.liquibase.org/xml/ns/pro http://www.liquibase.org/xml/ns/pro/liquibase-pro-4.2.xsd ">

  <preConditions>
    <dbms type="oracle" />
  </preConditions>

  <!--   -->
  <include file="/common/pre_migration.xml" />

  <!--  -->
  <includeAll path="/v156" relativeToChangelogFile="true" />
  <includeAll path="/v157" relativeToChangelogFile="true" />

</databaseChangeLog>

XML- , . XML- Liquibase.





preConditions , , oracle, , . preConditions .





. include ( ) includeAll ( ).





includeAll - , , .. . + , , , .





includeAll - ( ), , .





changeset

. 2021-05-01 TASK-001 CREATE TEST TABLE.xml:





<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
                   xmlns:pro="http://www.liquibase.org/xml/ns/pro"
                   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                   xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.2.xsd     http://www.liquibase.org/xml/ns/pro http://www.liquibase.org/xml/ns/pro/liquibase-pro-4.2.xsd ">

  <changeSet author="Doomer" id="20210501-01">
    <preConditions onFail="MARK_RAN">
      <not>
        <tableExists tableName="TEST"/>
      </not>
    </preConditions>
    <createTable tableName="TEST" remarks=" ">
      <column name="ID" type="NUMBER(28,0)" remarks="">
        <constraints nullable="false" primaryKey="true" primaryKeyName="TEST_PK" />
      </column>
      <column name="CODE" type="VARCHAR2(64)" remarks="">
        <constraints nullable="false" />
      </column>
      <column name="NAME" type="VARCHAR2(256)" remarks="">
        <constraints nullable="false" />
      </column>
    </createTable>
    <rollback>
      <dropTable tableName="TEST" />
    </rollback>
  </changeSet>

</databaseChangeLog>

, TEST . , .





preConditions , , .





rollback , , . , , , .





.





id

, , . .

author

, , . .

dbms

, .

contexts

, . , , , .





.

runAlways

. , . , . .

runOnChange

. , . recreatable-, , , ..

.





DATABASECHANGELOG

, , DATABASECHANGELOG, DATABASECHANGELOGLOCK, Liquibase.





, – , . , <>-<>< >, 20210501-01KD. , Liquibase, , .





MD5- , , . , Liquibase. , , MD-5 . , DATABASECHANGELOG.





runAlways

, , , , , , Liquibase, - . USER_ID.





- -, . :





<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
                   xmlns:pro="http://www.liquibase.org/xml/ns/pro"
                   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                   xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.2.xsd     http://www.liquibase.org/xml/ns/pro http://www.liquibase.org/xml/ns/pro/liquibase-pro-4.2.xsd ">

  <changeSet author="SYSTEM" id="PRE_MIGRATION" runAlways="true">
    <sql splitStatements="true" stripComments="true">
      --      liquibase
      CALL DBMS_SESSION.SET_CONTEXT('CLIENTCONTEXT','USER_ID', 13);
    </sql>
  </changeSet>

</databaseChangeLog>

SQL-, USER_ID 13 – Liquibase. , runAlways .





SQL-

SQL, . 2021-05-01 TASK-002 TEST.sql, TEST:





--liquibase formatted sql


--changeset Doomer:20210501-02
--preconditions onFail:MARK_RAN
--precondition-sql-check expectedResult:1 SELECT COUNT(*) FROM ALL_TABLES WHERE TABLE_NAME = 'TEST' AND OWNER = 'STROY';
--precondition-sql-check expectedResult:0 SELECT COUNT(*) FROM TEST WHERE ID = 1;
insert into TEST (ID, CODE, NAME)
values (1, 'TEST', '- ');
--rollback not required


--changeset Doomer:20210501-03
--preconditions onFail:MARK_RAN
--precondition-sql-check expectedResult:1 SELECT COUNT(*) FROM ALL_TABLES WHERE TABLE_NAME = 'TEST' AND OWNER = 'STROY';
--precondition-sql-check expectedResult:1 SELECT COUNT(*) FROM TEST WHERE ID = 1;
update TEST
   set NAME = 'CONTEXT USER_ID=' || nvl(SYS_CONTEXT('CLIENTCONTEXT', 'USER_ID'), 'NULL')
 where ID = 1;
--rollback not required

.





TEST, ID = 1. , , DATABASECHANGELOG (MARK_RAN). preConditions.





USER_ID.





TEST:





, Liquibase , RTFM.





Liquibase

Liquibase , , . , GitLab Runner', , Liquibase .





Liquibase: https://docs.liquibase.com/commands/community/home.html





:





  • update –





  • updateSQL – SQL- ,





, :









changeLogFile

- ,





master.xml

url

,





jdbc:oracle:thin:1.2.3.4:1521:orastb

username

,





vasya

password

,





pupkin

defaultSchemaName





DATA

contexts





dev / prod

driver





oracle.jdbc.OracleDriver

classpath





/usr/share/liquibase/4.3.4/drivers/ojdbc10.jar

outputFile

, updateSQL. , .





, Liquibase c . , (. ). Liquibase , , .





Windows:





call "C:\Temp\liqui\liquibase-4.3.1\liquibase.bat" ^
--defaultSchemaName=STROY ^
--driver=oracle.jdbc.OracleDriver ^
--classpath="C:\Temp\liqui\ojdbc5.jar" ^
--url=jdbc:oracle:thin:@1.2.3.4:1521:dev ^
--username=xxx ^
--password=yyy ^
--changeLogFile=.\master.xml ^
--contexts="dev"
--logLevel=info ^
updateSQL

, , . , , , GitLab, . .





Centos 7, . , Linux.





Java

Liquibase Java 11+, . OpenJRE 11:





sudo yum install java-11-openjdk
java --version

Liquibase

: https://www.liquibase.org/get-started/quickstart





Liquibase , Java. . , /usr/share/liquibase/<version>, /usr/share/liquibase/4.3.4





, Liquibase, drivers . ojdbc10.jar





, Liquibase :





cd /usr/share/liquibase/4.3.4
liquibase --version

Git

Git GitLab Runner , , Centos 7 , , . GitLab Runner , git , 1.8. , GitLab , - , , CI/CD .





, git GitLab Runner:





#    
git --version

#  ,    1.8
sudo yum remove git*

#         (2.30)
sudo yum -y install https://packages.endpoint.com/rhel/7/os/x86_64/endpoint-repo-1.7-1.x86_64.rpm
sudo yum install git

GitLab Runner

: https://docs.gitlab.com/runner/install/linux-manually.html





#  
curl -L "https://packages.gitlab.com/install/repositories/runner/gitlab-runner/script.rpm.sh" | sudo bash

# 
export GITLAB_RUNNER_DISABLE_SKEL=true; sudo -E yum install gitlab-runner

GitLab Runner

, GitLab Runner, Liquibase.





GitLab Runner: https://docs.gitlab.com/runner/configuration/





, , , :





#   
which gitlab-runner # /usr/bin/gitlab-runner

#    
sudo chmod +x /usr/bin/gitlab-runner

, . , .





#  
sudo useradd --comment 'GitLab Runner' --create-home gitlab-runner --shell /bin/bash

#  
sudo gitlab-runner install --user=gitlab-runner --working-directory=/home/gitlab-runner

systemctl:





#  
sudo gitlab-runner status

#  
sudo gitlab-runner start

#  
sudo gitlab-runner stop

#    
sudo gitlab-runner list

GitLab Runner Liquibase. register



, GitLab.





GitLab Settings ⇨ CI/CD ⇨ Runners



. , , . ( ):





:





  1. GitLab. .





  2. . , .





, . , , , . , , .





sudo gitlab-runner register







:





  1. Enter the GitLab instance URL







    GitLab





  2. Enter the registration token











  3. Enter a description for the runner







    , , my-awesome-runner





  4. Enter tags for the runner







    . : liquibase,dev





    GitLab CI/CD





  5. Enter an executor







    . .





    shell





shell – . bash.





sudo gitlab-runner list



GitLab CI/CD:





my-awesome-runner
my-awesome-runner

CI/CD

, CI/CD . .gitlab-ci.yml . , bash, , , /ci



.





, , , , .





, .gitlab-ci.yml



, ci/lint



, : https://gitlab.example.com/gitlab-org/my-project/-/ci/lint. . , YAML.





:





variables:
    LIQUIBASE_VERSION: "4.3.4"

stages:
    - deploy

deploy-dev:
    stage: deploy
    tags:
        - liquibase
        - dev
    script:
        - 'bash ./ci/deploy-db.sh $DEV_DB $DEV_DB_USER $DEV_DB_PASS'
    environment:
        name: dev
    only:
        - dev

deploy-prod:
    stage: deploy
    tags:
        - liquibase
        - prod
    script:
        - 'bash ./ci/deploy-db.sh $DEV_DB $DEV_DB_USER $DEV_DB_PASS'
    environment:
        name: prod
    when: manual
    only:
        - prod

: https://docs.gitlab.com/ee/ci/variables/README.html





variables:
    LIQUIBASE_VERSION: "4.3.4"

, . LIQUIBASE_VERSION Liquibase, . , Liquibase .





, , , , .





Settings ⇨ CI/CD ⇨ Variables



.





, :





CI / CD Variables
CI/CD Variables

, , , CI/CD, , , . , , .





(stages)

stages:
    - deploy

, .





(jobs)

:





deploy-dev:
    stage: deploy
    tags:
        - liquibase
        - dev
    script:
        - 'bash ./ci/deploy-db.sh $DEV_DB $DEV_DB_USER $DEV_DB_PASS'
    environment:
        name: dev
    only:
        - dev

stage: deploy



, .





, .. liquibase dev. script



. , prod, , , , , . , , .





environment , Operations ⇨ Environments



. (, , ..), , , . , , -, .





:





Environments
Environments

only



, . except



, . : https://docs.gitlab.com/ee/ci/jobs/job_control.html.





when: manual



. , , . , - .





Merge in prod, manual launch
,

script



, . shell- , bash. , .. , .





Liquibase:





#!/bin/bash
echo "Environment: $CI_ENVIRONMENT_NAME"
cd db/changelog
/usr/share/liquibase/$LIQUIBASE_VERSION/liquibase \
    --classpath=/usr/share/liquibase/$LIQUIBASE_VERSION/drivers/ojdbc10.jar \
    --driver=oracle.jdbc.OracleDriver \
    --changeLogFile=master.xml \
    --contexts="$CI_ENVIRONMENT_NAME" \
    --defaultSchemaName=STROY \
    --url=jdbc:oracle:thin:@$1 \
    --username=$2 \
    --password=$3 \
    --logLevel=info \
    update

Liquibase .





DEV_DB, DEV_DB_USER, DEV_DB_PASS $1, $2 $3 . , $CI_ENVIRONMENT_NAME, , - , .





, , .





Liquibase :





, . , CI/CD . , Liquibase , , , .





General ⇨ Merge requests



.





! CI/CD, .





, . - 5 . , , - CI/CD.





If some moments are left behind the scenes, and there is a desire to learn more about them, write in the comments. I will keep in mind, but I will not promise anything, tk. this article, for example, took me over 20 hours of time over three weeks, which is two to three times the average article writing time.





As usual, if you liked the article, check out the others:







More articles:


All Articles