Build a Changelog File

Learn how to build a changelog file for your database using Harness Database DevOps.

A changelog is a collection of database changes that can be applied to a database. It serves as a version-controlled record of changes, allowing teams to manage and track modifications to the database schema and data.

Harness Database DevOps offers multiple methods to generate changelogs for your database. This document lists each approach and provides step-by-step guidance.

• Generate Changelog from Command

• Pointing to SQL Files

• Mongodb Changelog Generation

Create a changelog

Using existing SQL files

You can create a changelog by pointing to SQL files in your git repository. This method is useful if you have existing SQL scripts that you want to use as changelogs. This approach allows you to leverage your existing SQL files without needing to convert them into a specific changelog format.

Steps to create a changelog with SQL files

Follow these steps to create a changelog by pointing to existing SQL files:

1. Place your SQL files it in a subfolder named sql in git repository.

2. Ensure that the SQL files are named in a way that reflects their order of execution.

For example, you can use a naming convention like V1__create_table.sql, V2__add_column.sql, etc.

1. Each SQL file should contain the SQL statements that define the changes you want to apply to your database schema.

2. Add changelog.yml to your git repository. In this file, include the following configuration to reference the SQL files:

databaseChangeLog:
  - includeAll:
      path: sql
      relativeToChangelogFile: true

Advanced usage options

If you need an order other than alphabetical, or want to leverage advanced features of changes, or a mixture of YAML/SQL based changes, you can use the sqlFile change type in a YAML changeset:

databaseChangeLog:
  - changeSet:
      id: execute-specific-sql-file
      author: sonichigo
      changes:
        - sqlFile:
            path: sql/specific_script.sql
            relativeToChangelogFile: true

For customers with an existing directory of scripts to run in alphabetical order, you can leverage includeAll and then follow it with additional YAML changesets to run after the referenced scripts:

databaseChangeLog:
  # First run all SQL files in the directory alphabetically
  - includeAll:
      path: sql/existing-scripts
      relativeToChangelogFile: true
      
  # Then run additional changesets after all the SQL files
  - changeSet:
      id: additional-changes-after-sql-files
      author: sonichigo
      changes:
        - createTable:
            tableName: new_feature_table
            columns:
              - column:
                  name: id
                  type: INT
                  constraints:
                    primaryKey: true

Generate Changelog SQL Command

You can create a changelog by using the generate-changelog command. This method is useful if you want to generate a changelog based on the current state of your database. This approach allows you to create a changelog file that reflects the current state of your database schema. For MongoDB, the generate-changelog command is not supported. Go to MongoDB changelog generation to generate a MongoDB changelog.

Steps to create a changelog with the generate-changelog command

Follow these steps to generate a changelog from the current state of your database:

1. Under DBOps in the Harness UI, navigate to DB Schema.

2. Click Add DB Schema.

3. Click Add DB Instance.

4. Go to Pipeline, then click Create a Pipeline.

5. Click Add Stage, then select custom stage.

6. In the Stage section, create Add Step Group as the stage type.

Enable container-based execution

Enable container-based execution.

1. In the Step Group section, select Add Step as the step type. Under "DB DevOps", select Liquibase Command as the step type.

2. By default the name is "LiquibaseCommand_1".

• Select DB Schema: The DB Schema we created on Step 2.

• Select DB Instance: The Instance we created on Step 3.

• Command: The command to be executed. In this case, we will use generate-changelog to generate a changelog file.

1. Click Apply Changes and Save the Pipeline.

2. Click Run to run the pipeline.

3. Once the pipeline is executed successfully, you will find the changelog file in the specified path.

Visual Overview

Commit to Git

YAML Overview

pipeline:
  name: changelog
  identifier: changelog
  projectIdentifier: default_project
  orgIdentifier: default
  tags: {}
  stages:
    - stage:
        name: generate-changelog
        identifier: generatechangelog
        description: "Generate Changelog using Liquibase Command"
        type: Custom
        spec:
          execution:
            steps:
              - stepGroup:
                  name: changelog
                  identifier: changelog
                  steps:
                    - step:
                        type: LiquibaseCommand
                        name: LiquibaseCommand_1
                        identifier: LiquibaseCommand_1
                        spec:
                          connectorRef: account.harnessImage
                          command: generate-changelog
                          dbSchema: generatechangelog
                          dbInstance: instance
                          excludeChangeLogFile: false
                        timeout: 10m
                    - step:
                        type: Run
                        name: Commit to Git
                        identifier: Commit_to_Git
                        spec:
                          connectorRef: dockerHarness
                          image: alpine/git
                          shell: Sh
                          command: |-
                            ls -la
                            git init

                            # Configure Git user
                            git config --global user.email "[e-mail]"
                            git config --global user.name "John Doe"
                            git config --global user.password "<+secrets.getValue("johndoe")>"

                            echo "adding"
                            git add generated.yml
                            echo "added"
                            git commit -m "generated changelog from running instance"
                            echo "committed"

                            # Get current branch name
                            CURRENT_BRANCH=$(git rev-parse --abbrev-ref HEAD)
                            echo "Current branch: $CURRENT_BRANCH"

                            # Add remote repository
                            git remote add gitlab https://john.doe%40yxz.com:<+secrets.getValue("johndoe")>@gitlab.com/<username>/generate-changelog.git
                            echo "remote set"

                            # Push to remote using the current branch name
                            git push -u gitlab $CURRENT_BRANCH -f
                            echo "pushed to $CURRENT_BRANCH branch"
                  stepGroupInfra:
                    type: KubernetesDirect
                    spec:
                      connectorRef: db
            rollbackSteps: []
          serviceDependencies: []
        tags: {}

This step will ensure that the generated changelog file is committed to your Git repository, allowing you to track changes and maintain version control over your database schema changes.

How changesets work

A changeset is the smallest deployable unit of change to a database. When using database DevOps practices, changesets can be applied or rolled back individually. Which changesets have been applied are tracked inside the database itself in a tracking table called databasechangelog.

A changeset looks something like this:

databaseChangeLog:
  - changeSet:
      id: product-table
      author: animesh
      labels: products-api 
      comment: Creating product table for REST API
      changes:
        - createTable:
            tableName: products
            columns:
              - column:
                  name: id
                  type: SERIAL
                  constraints:
                    primaryKey: true
              - column:
                  name: name
                  type: VARCHAR(100)
                  constraints:
                    nullable: false
              - column:
                  name: price
                  type: NUMERIC(10,2)
                  constraints:
                    nullable: false
                  defaultValue: 0.00

The change management system only executes new changesets or those with modified checksums and records successful executions in the tracking table. If a changeset fails, it will not be recorded in the tracking table, and you can re-run it later. This allows for easy rollback and re-application of changesets as needed.

The database tracking table is used by default, and can be overridden by setting the database-changelog-table-name global parameter.

Next steps

• Go to Apply a DB schema step to deploy your changelog in a pipeline.

• Go to Rollback for database schemas to configure automated rollback for failed deployments.