Case Study: Adding a New Table in a CI/CD Pipeline

The Scenario

Let’s consider a real-world scenario where a software development team needs to add a new table to their database as part of a new feature they are implementing. The team uses Jenkins for their CI/CD pipeline and has chosen Flyway for database migration.

Steps for Adding the New Table

1. Create the Migration Script

The first step is to create an SQL migration script that defines the new table. This script is then added to the project repository.

-- V1.2__Add_New_Table.sql

CREATE TABLE new_table (
    id INT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT
);Code language: CSS (css)

2. Version Control

The script is version-controlled using a version number or timestamp, as per Flyway’s conventions. In this example, V1.2 indicates that this is the second migration in the 1.x series.

3. Local Testing

Before committing the script, it’s crucial to test it locally to ensure that it behaves as expected.

4. Commit and Push

Once local testing is successful, the migration script is committed and pushed to the version control system.

---

CI/CD Pipeline Integration

Jenkins Configuration

1. Source Code Management: Jenkins pulls the latest code, including the new migration script, from the repository.
2. Flyway Migration Step: A shell script step is added to execute Flyway’s migrate command.
3. Automated Testing: Tests are run to ensure that the new table does not break existing functionality.
4. Approval Gate: An optional manual approval step can be added before the changes are deployed to the production database.
5. Deployment: If all tests pass and (if enabled) manual approval is given, the changes are deployed to production.

---

Rollback Strategy

Flyway Undo Script

An undo script for the new table is also created and version-controlled. This script will drop the new table.

-- U1.2__Undo_Add_New_Table.sql

DROP TABLE new_table;Code language: CSS (css)

CI/CD Rollback Step

In Jenkins, a conditional step can be added to execute Flyway’s undo command if any of the tests fail or if the manual approval is rejected.

---

This case study demonstrates a typical workflow for adding a new table to a database in a CI/CD pipeline using Jenkins and Flyway. From creating and testing the migration script to integrating it into the CI/CD pipeline and planning for possible rollbacks, each step is crucial for ensuring that the database changes are managed effectively and reliably.

Would you like to proceed to the next section, which will focus on a case study for rolling back changes?