🤾🏼 📤 👖 Configurar scripts de CI / CD para migração de banco de dados do zero usando GitLab e Liquibase 🧕🏻 👽 🍼

Prólogo

Boa tarde, queridos leitores. Recentemente, tive que dominar uma nova área de CI / CD, configurando do zero a entrega de scripts de migração de banco de dados em um dos projetos. Ao mesmo tempo, foi difícil superar o primeiro estágio de "os olhos estão com medo", quando a tarefa parece clara, mas você não sabe por onde começar. No entanto, a questão acabou por ser muito mais simples do que parecia inicialmente, ao mesmo tempo que apresentava vantagens inegáveis ao custo de várias horas de trabalho e não exigia quaisquer fundos adicionais, exceto os indicados no título.

, , , , .

, . 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 .

, , :

.
, .
, .
, Liquibase.
Liquibase /.
...
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. . , , . :

, .
, , .

, // - , . .

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

. , , . ( ):

GitLab. .
. , .

, . , , , . , , .

sudo gitlab-runner register

Enter the GitLab instance URL

GitLab
Enter the registration token
Enter a description for the runner

, , my-awesome-runner
Enter tags for the runner

. : liquibase,dev

GitLab CI/CD
Enter an executor

. .

shell

shell – . bash.

sudo gitlab-runner list

GitLab CI/CD:

meu-incrível-corredor — 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, , , . , , .

(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

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

only

, . except

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

when: manual

. , , . ~~, -~~ .

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.

Se alguns momentos ficarem para trás e houver desejo de aprender mais sobre eles, escreva nos comentários. Vou manter em mente, mas não vou prometer nada, tk. este artigo, por exemplo, levou mais de 20 horas em três semanas, o que é duas a três vezes o tempo médio de escrita do artigo.

Como de costume, se você gostou do artigo, dê uma olhada nos outros:

Artigo de leitura obrigatória interessante: analisador inteligente de um número escrito em palavras
Atualização automática de scripts após a implantação
Serialização de classe JavaScript com referências cíclicas

Configurar scripts de CI / CD para migração de banco de dados do zero usando GitLab e Liquibase

Prólogo

GitLab CI/CD

Liquibase

Liquibase

changelog

changeset

DATABASECHANGELOG

runAlways

SQL-

Liquibase

Java

Liquibase

Git

GitLab Runner

GitLab Runner

CI/CD

(stages)

(jobs)

More articles: