Chapter 30: Artifacts, Release Patterns and Quality Gates

30.7 Quality gates and database migrations

By Ravindra Bagale · Last updated: 25 October 2026

Why: the problem

Suppose we are at Razorpay. A developer's change passes all tests, but it has no tests for the new refund code and it has a SQL injection risk. Tests alone did not catch it. A quality gate is a fixed rule set the build must pass, like: coverage on new code at least 80 percent, no new critical bugs, no new security hotspots left open. If the gate fails, Jenkins stops the release. The second risk is the database. New code often needs a new column. If someone adds it by hand, staging and production drift apart. Migration tools like Flyway keep database changes as numbered files in Git and apply them in order, the same way everywhere.

What we will build

You will add a SonarQube quality gate to a Jenkins pipeline and see it fail and pass. Then you will run database migrations three ways: Flyway in Docker for a Java app, Alembic for a Python app, and the expand and contract pattern so blue-green and rollback stay safe.

What: every term in plain English

• Quality gate — A set of pass or fail rules on code quality, checked on every build.
• SonarQube — A server that scans code for bugs, code smells, security issues and coverage.
• waitForQualityGate — A Jenkins step that waits for SonarQube to say the gate passed or failed.
• Coverage on new code — The percent of new or changed lines that tests run.
• Migration — A versioned script that changes the database, like adding a column.
• Flyway — A tool that runs SQL migrations named V1__, V2__ in order and records them in a history table.
• Expand and contract — Change the schema in safe steps: first add new things, later remove old things, so old and new code both work.

How: step by step

1. Start a lab SonarQube

   docker run -d --name sonar -p 9000:9000 sonarqube:10-community
   

   What it does: Lab only. Log in at localhost:9000 and change the admin password at once.

   Output

   3e8a1c...
   

2. Create a token in SonarQube

   My Account > Security > Generate Token, name jenkins
   

   What it does: The token lets Jenkins send scans. Store it, do not paste it in files.

   Output

   Token generated: squ_****
   

3. Save the token in Jenkins

   Manage Jenkins > Credentials > Secret text, ID sonar-token
   

   What it does: The pipeline uses only the ID.

   Output

   Credential sonar-token created
   

4. Connect Jenkins to SonarQube

   Manage Jenkins > System > SonarQube servers: name sonar-lab, URL http://sonar:9000, token sonar-token
   

   What it does: withSonarQubeEnv uses this name.

   Output

   Saved
   

5. Add a webhook in SonarQube

   Administration > Configuration > Webhooks > Create: http://jenkins:8080/sonarqube-webhook/
   

   What it does: SonarQube tells Jenkins when analysis is done. Without it, waitForQualityGate waits forever.

   Output

   Webhook jenkins created
   

6. Set the gate rules

   Quality Gates > Create "Shop Gate": coverage on new code at least 80, new bugs 0, new vulnerabilities 0
   

   What it does: Clear numbers, agreed with the team.

   Output

   Quality gate "Shop Gate" set as default
   

7. Run a build and watch the gate

   Build Now
   

   What it does: The build waits a few seconds for the result.

   Output

   Checking status of SonarQube task 'AYx9...' on server 'sonar-lab'
   SonarQube task 'AYx9...' status is 'SUCCESS'
   SonarQube task 'AYx9...' completed. Quality gate is 'OK'
   

Example 1: Jenkins + SonarQube: scan, then stop the pipeline if the gate fails

Stack. Java 17 Maven, JaCoCo, SonarQube Community

Goal. Jenkins + SonarQube: scan, then stop the pipeline if the gate fails.

Jenkinsfile

pipeline {
  agent { label 'linux-docker' }
  stages {
    stage('Test') { steps { sh './mvnw -B -ntp verify' } }
    stage('Scan') {
      steps {
        withSonarQubeEnv('sonar-lab') {
          sh './mvnw -B -ntp sonar:sonar -Dsonar.projectKey=refund-service -Dsonar.coverage.jacoco.xmlReportPaths=target/site/jacoco/jacoco.xml'
        }
      }
    }
    stage('Quality gate') {
      steps {
        timeout(time: 5, unit: 'MINUTES') {
          waitForQualityGate abortPipeline: true
        }
      }
    }
    stage('Deploy staging') { steps { sh './deploy.sh staging' } }
  }
}

What each line does

• withSonarQubeEnv('sonar-lab') — Gives the scanner the server URL and token from Jenkins settings.
• sonar:sonar -Dsonar.projectKey=refund-service — Runs the scan and names the project.
• jacoco.xml — The coverage report from tests, so SonarQube knows covered lines.
• timeout(time: 5, unit: 'MINUTES') — Do not wait forever if the webhook is broken.
• waitForQualityGate abortPipeline: true — If the gate is red, the build stops here and Deploy never runs.

Green output

[INFO] ANALYSIS SUCCESSFUL, you can find the results at: http://sonar:9000/dashboard?id=refund-service
[Pipeline] waitForQualityGate
Checking status of SonarQube task 'AYx9k2' on server 'sonar-lab'
SonarQube task 'AYx9k2' status is 'SUCCESS'
SonarQube task 'AYx9k2' completed. Quality gate is 'ERROR'
ERROR: Pipeline aborted due to quality gate failure: ERROR
Finished: FAILURE

Expected result. The gate failed because coverage on new code was 61 percent. Deploy staging did not run. After adding tests, the gate shows OK.

Example 2: Flyway in Docker: versioned SQL files applied in order before the app deploys

Stack. PostgreSQL 16, Flyway 10 in Docker, Jenkins

Goal. Flyway in Docker: versioned SQL files applied in order before the app deploys.

db/migration/V7__add_refund_reason.sql

-- V7: expand step - add a nullable column, old code ignores it
ALTER TABLE refunds ADD COLUMN reason VARCHAR(200);
CREATE INDEX idx_refunds_reason ON refunds (reason);

Jenkinsfile (migrate stage)

stage('Migrate staging DB') {
  steps {
    withCredentials([usernamePassword(credentialsId: 'staging-db', usernameVariable: 'DB_USER', passwordVariable: 'DB_PASS')]) {
      sh '''
        docker run --rm -v "$PWD/db/migration":/flyway/sql flyway/flyway:10 \
          -url=jdbc:postgresql://staging-db.internal:5432/payments \
          -user="$DB_USER" -password="$DB_PASS" info migrate
      '''
    }
  }
}

What each line does

• V7__add_refund_reason.sql — V7 is the version. Two underscores, then a description. Flyway runs files in version order.
• ADD COLUMN reason ... (nullable) — Adding a nullable column is safe: old code keeps working.
• -v "$PWD/db/migration":/flyway/sql — Mount the migration files into the Flyway container.
• credentialsId: 'staging-db' — Database user and password from Jenkins credentials.
• info migrate — First print the status, then apply pending migrations.

Green output

+-----------+---------+---------------------+---------+
| Category  | Version | Description         | State   |
+-----------+---------+---------------------+---------+
| Versioned | 6       | create refunds      | Success |
| Versioned | 7       | add refund reason   | Pending |
+-----------+---------+---------------------+---------+
Successfully validated 7 migrations (execution time 00:00.031s)
Migrating schema "public" to version "7 - add refund reason"
Successfully applied 1 migration to schema "public", now at version v7

Expected result. The database is at v7 on staging. Running it again applies nothing, because Flyway records V7 in flyway_schema_history.

Example 3: Python Alembic plus expand and contract: rename a column with zero downtime in three releases

Stack. Python 3.12, SQLAlchemy, Alembic, PostgreSQL

Goal. Python Alembic plus expand and contract: rename a column with zero downtime in three releases.

alembic/versions/0012_expand_add_full_name.py

"""expand: add full_name, keep old name column"""
from alembic import op
import sqlalchemy as sa

revision = "0012"
down_revision = "0011"

def upgrade():
    op.add_column("customers", sa.Column("full_name", sa.String(200), nullable=True))
    op.execute("UPDATE customers SET full_name = name WHERE full_name IS NULL")

def downgrade():
    op.drop_column("customers", "full_name")

release plan

Release 1 (expand):   migration 0012 adds full_name and copies data.
                      App writes BOTH name and full_name, reads name.
Release 2 (switch):   App reads full_name, still writes both.
                      Rollback to release 1 is safe: name is still filled.
Release 3 (contract): migration 0013 drops name. App uses only full_name.

Jenkinsfile (migrate stage)

stage('Migrate') {
  steps {
    withCredentials([string(credentialsId: 'crm-db-url', variable: 'DATABASE_URL')]) {
      sh 'alembic current && alembic upgrade head && alembic current'
    }
  }
}

What each line does

• revision / down_revision — Each migration points to the one before it, like a chain.
• add_column ... nullable=True — Expand: add new, never remove in the same release.
• UPDATE customers SET full_name = name — Copy old data into the new column.
• downgrade() — How to undo this one step, if needed.
• Release 3 drops name — Contract happens only after no running version reads name.
• alembic upgrade head — Apply all pending migrations up to the newest.

Green output

+ alembic current
0011
+ alembic upgrade head
INFO  [alembic.runtime.migration] Running upgrade 0011 -> 0012, expand: add full_name, keep old name column
+ alembic current
0012 (head)

Expected result. Both old and new code work with the database during the release. Blue-green and rollback stay safe.

Verify: did it work?

1. Check gate status by API

   curl -s -u "$SONAR_TOKEN:" "http://localhost:9000/api/qualitygates/project_status?projectKey=refund-service" | jq -r .projectStatus.status
   

   What it does: OK or ERROR.

   Output

   OK
   

2. Check database version

   docker run --rm flyway/flyway:10 -url=jdbc:postgresql://staging-db.internal:5432/payments -user="$DB_USER" -password="$DB_PASS" info | tail -3
   

   What it does: Last line should say Success for the newest version.

   Output

   | Versioned | 7 | add refund reason | Success |
   

3. Check the history table

   psql "$DATABASE_URL" -c "select version, success from flyway_schema_history order by installed_rank desc limit 2"
   

   What it does: Flyway stores what ran and when.

   Output

    version | success
   ---------+---------
    7       | t
    6       | t
   

When it is red 1

ERROR: Pipeline aborted due to quality gate failure: ERROR

Cause. The code broke a gate rule. This is the gate working.

Fix, step by step

1. Open the SonarQube link in the log.
2. Read which condition failed, like coverage on new code.
3. Add tests or fix the issue. Do not lower the gate to pass.

When it is red 2

waitForQualityGate: Timeout has been exceeded

Cause. SonarQube never called Jenkins back. The webhook is missing or wrong.

Fix, step by step

1. Create the webhook to /sonarqube-webhook/ with a trailing slash.
2. Check SonarQube can reach the Jenkins URL.
3. Look at Administration > Webhooks > Recent deliveries.

When it is red 3

ERROR: Validate failed: Migrations have failed validation
Detected applied migration not resolved locally: 6

Cause. Someone changed or deleted an already applied migration file.

Fix, step by step

1. Never edit an applied migration.
2. Restore the original file from Git.
3. Put the change in a new V8 file.

When it is red 4

ERROR: Migration V7__add_refund_reason.sql failed
SQL State: 42P07 Message: relation "idx_refunds_reason" already exists

Cause. The index was created by hand earlier, so staging drifted.

Fix, step by step

1. Remove the manual index in a lab, or use CREATE INDEX IF NOT EXISTS.
2. Make the rule: no manual schema changes.
3. Run flyway repair only after you understand the state.

Cleanup

1. Stop the lab SonarQube

   docker rm -f sonar
   

   What it does: Frees about 2 GB of memory.

   Output

   sonar
   

2. Reset a lab database

   docker rm -f pg-lab && docker run -d --name pg-lab -e POSTGRES_PASSWORD=lab -p 5432:5432 postgres:16-alpine
   

   What it does: Start migrations again from V1 in the lab only.

   Output

   f0e1d2...
   

Practice tasks

Homework: try at home

Recap

• A quality gate is a fixed pass or fail rule set on every build.
• SonarQube plus waitForQualityGate stops bad code before deploy.
• Database changes are versioned files in Git, applied by Flyway, Liquibase or Alembic.
• Never edit applied migrations; add new ones.
• Expand and contract keeps blue-green and rollback safe.

FAQ

Is SonarQube free?

The Community edition is free and enough to learn. Paid editions add branch and PR analysis.

Can I use only coverage as a gate?

You can, but add new bugs and vulnerabilities too. Coverage alone can be faked.

Should migrations run in the app at startup?

For small apps it is fine. For big apps, run them as a separate pipeline step, so many Pods do not race.

Liquibase or Flyway?

Both work. Flyway uses plain SQL files; Liquibase also supports YAML and XML change sets.

Can I roll back a migration?

Sometimes, with a downgrade script. Safer is a new forward migration.

Interview questions

1. What is a quality gate and how do you use it in Jenkins?

   A set of rules like coverage on new code and zero new critical issues. withSonarQubeEnv scans, then waitForQualityGate abortPipeline true stops the pipeline on failure.

2. How do you manage database changes in CI/CD?

   Versioned migration files in Git, applied by Flyway, Liquibase or Alembic as a pipeline stage before deploy, same files in every environment.

3. What is expand and contract?

   A zero-downtime schema change: add new structures, migrate data, switch code, then drop old structures in a later release.

4. Why should you never edit an applied migration?

   The checksum changes, validation fails, and environments drift.

5. What if waitForQualityGate hangs?

   Check the SonarQube webhook to Jenkins and always wrap it in a timeout.

Samajla ka?

Ravi Sir's Cards for this lesson

Revision cardInterview card

मराठी cards