Verificando autenticación…

Skip to main content

Liquibase Template

Purpose and Scope​

This project provides a standardized template and a set of tools for managing and applying schema changes in PostgreSQL databases using Liquibase. It is designed to facilitate database versioning and controlled migration execution in both local environments and CI/CD pipelines.

Main Features:

  • Definition of database changes (DDL and DML) through changelog files in YAML format.
  • Execution of migrations in local development environments against a PostgreSQL instance.
  • Integration with CI/CD pipelines (Jenkins and GitLab CI) to automate migration application.
  • Deployment of migrations in Docker containers, facilitating execution in environments such as Google Kubernetes Engine (GKE) and Google Cloud Run.
  • Secure connection to Google Cloud SQL instances using Cloud SQL Proxy.

The scope covers everything from creating and modifying tables to loading initial or configuration data.

Architecture​

The workflow and main components of this Liquibase template are:

  1. Change Development: Developers define schema or data changes in *.yaml files following Liquibase syntax. These files are organized in a directory structure (database/changelog/tables/, database/changelog/data/).
  2. Local Testing: Changes can be tested locally by running the Liquibase CLI against a PostgreSQL instance configured for development.
  3. Version Control: Changelog files are versioned in a Git repository.
  4. Docker Image Build: A Dockerfile is provided to build an image that contains:
    • Liquibase.
    • The project's changelog scripts.
    • Cloud SQL Proxy (to connect to Google Cloud SQL instances).
  5. Continuous Integration / Continuous Deployment (CI/CD):
    • Jenkins: A Jenkinsfile defines a pipeline (liquibase-migration) that presumably builds the Docker image and runs it to apply migrations in the target environment.
  • GitLab CI: A .gitlab-ci.yml file integrates with LATAM pipelines to deploy changes, specifically for Cloud Run ($LATAM_PIPELINE_CLOUD_RUN_DEPLOY).
  1. Target Environment Execution (GKE/Cloud Run):
    • The Docker image runs as a Pod in GKE (defined in deploy/gke/deployment.yaml.erb) or as a service in Cloud Run (defined in deploy/run/*.yaml.erb).
    • Inside the container, Cloud SQL Proxy establishes a secure connection to the database.
    • Liquibase runs the validate, releaseLocks, and update commands against the target database, applying pending changes.

This approach ensures that database changes are consistent, versioned, and applied in an automated and secure manner across different environments.

Technologies and Dependencies​

  • Liquibase: Main tool for database migration management.
  • PostgreSQL: Relational database management system for which the changelogs are designed.
  • Docker: For containerizing the Liquibase application and its dependencies.
  • YAML: Format used to write Liquibase changelogs.
  • Google Cloud SQL Proxy: For secure connections to PostgreSQL instances in Google Cloud.
  • Jenkins: CI/CD system used to orchestrate deployments (via Jenkinsfile).
  • GitLab CI: CI/CD system used to orchestrate deployments to Cloud Run (via .gitlab-ci.yml).
  • Google Kubernetes Engine (GKE): Container orchestration platform, one of the possible deployment environments.
  • Google Cloud Run: Serverless platform for running containers, another possible deployment environment.
  • Bash/Shell: Used for execution scripts in the Dockerfile and in local instructions.
  • Red Hat Universal Base Image (UBI) 8: Base image for the Dockerfile (redhat_ubi_8_liquidbase_4).

Local Setup​

Follow these steps to run Liquibase migrations in your local environment.

Prerequisites​

  • Liquibase CLI: Must be installed and accessible in your PATH. You can find installation instructions here.
  • PostgreSQL JDBC Driver: Download the JDBC driver for PostgreSQL (a .jar file) and place it in a location accessible to Liquibase (e.g., in a lib/ folder within the project, or in the lib directory of your Liquibase installation). The original readme.md suggests a path such as ../lib/postgresql.jar relative to the database folder.
  • PostgreSQL Instance: You need a running and accessible PostgreSQL instance.

Setup Steps​

  1. Create project:
    • Go to: https://app.getport.io/self-serve
    • Select Liquibase scaffolding
    • Select GKE deployment
    • Select Sample_data
    • If you want to push the project to GitLab, select Push to GitLab and register the GitLab group id

🔖 Note​

The component name MUST ALWAYS start with liquibase, for example: liquibase-migration-my-component-name
  1. Local execution

Install Liquibase​

Mac​

brew install liquibase

Linux​

Installation instructions here

Windows​

Installation instructions here

Configure database​

#Create a role
CREATE ROLE newuser WITH LOGIN PASSWORD 'password';
ALTER ROLE newuser CREATEDB;

#with the newuser create a new database
create database template;

#Connect to the database
\connect template

#Create the schmea
CREATE SCHEMA "template" AUTHORIZATION newuser;

#Set default schemma to template
SET search_path TO template;
SHOW search_path;

#list tables
\dt

Create table​

#Export variables

export DATABASE_USERNAME=newuser
export DATABASE_PASSWORD=password
export DATABASE_URL=jdbc:postgresql://127.0.0.1:5432/template?currentSchema=template

Run Liquibase​

cd liquibase-migration/database
liquibase --classpath=../lib/postgresql.jar --changeLogFile=changelog-master.yaml --url=${DATABASE_URL} \
                  --username=${DATABASE_USERNAME} --password=${DATABASE_PASSWORD} --logLevel=${LOG_LEVEL:-info} update

Unlock Database​

liquibase --classpath=../lib/postgresql.jar --changeLogFile=changelog-master.yaml --url=${DATABASE_URL} \
                  --username=${DATABASE_USERNAME} --password=${DATABASE_PASSWORD} --logLevel=${LOG_LEVEL:-info} releaseLocks