Clever Geek Handbook

Configurar scripts de CI / CD para la migración de bases de datos desde cero usando GitLab y Liquibase

Prólogo

Buenas tardes queridos lectores. Recientemente, tuve que dominar una nueva área de CI / CD, configurando desde cero la entrega de scripts de migración de bases de datos en uno de los proyectos. Al mismo tiempo, fue difícil superar la primera etapa de "los ojos tienen miedo", cuando la tarea parece estar clara, pero no sabes por dónde empezar. Sin embargo, la pregunta resultó ser mucho más simple de lo que parecía inicialmente, al tiempo que brindaba ventajas innegables a costa de varias horas de trabajo y no requería fondos adicionales, salvo los indicados en el título.





, , , , .













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





  • , .





  • , , .





, // - , . .





Estructura del proyecto

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:





mi-corredor-impresionante
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



.





, :





Variables CI / CD
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



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





:





Ambientes
Environments

only



, . except



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





when: manual



. , , . , - .





Combinar en prod, lanzamiento 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.





Si quedan algunos momentos detrás de escena y hay ganas de aprender más sobre ellos, escribe en los comentarios. Lo tendré en cuenta, pero no prometeré nada, tk. este artículo, por ejemplo, me llevó más de 20 horas durante tres semanas, lo que equivale a dos o tres veces el tiempo medio de redacción de un artículo.





Como de costumbre, si te gustó el artículo, mira los demás:







More articles:


All Articles