Work with SQL Files

Learn how to organize SQL changelog files, structure includeAll blocks, and write maintainable formatted SQL changesets in Harness Database DevOps.

A well-structured SQL file directory ensures consistent, traceable, and collaborative database change management with Harness Database DevOps. This guide walks through best practices for organizing SQL changelogs, structuring includeAll blocks, and writing maintainable formatted SQL changesets.

Tip Consider this as an example implementation of the changelog directory structure.

SQL file directory organization

A well-organized SQL file directory structure is crucial for maintaining database changes. The following is a recommended structure:

db/
 ├── master.yaml                  # Main changelog file
 ├── changes/
 │   ├── release-1/
 │   │   ├── 001-create-users-table.sql
 │   │   ├── 002-add-email-column.sql
 │   │   └── 003-create-orders-table.sql
 │   └── release-2/
 │       ├── 001-add-payment-table.sql
 │       └── 002-alter-users-table.sql
 └── application/

Below are sample contents for the files referenced in the folder structure above.

db/master.yaml

databaseChangeLog:
  - includeAll:
      path: changes/release-1/
      relativeToChangelogFile: true
  
  - includeAll:
      path: changes/release-2/
      relativeToChangelogFile: true

Liquibase formatted SQL files

Harness Database DevOps supports seamless integration of Liquibase formatted SQL files, enabling you to track database changes effectively. Harness Database DevOps uses metadata in the SQL files to help identify and manage changes.

--liquibase formatted sql

--changeset john-doe:1
CREATE TABLE users (
      id INT PRIMARY KEY,
      name VARCHAR(255),
      mobile_number VARCHAR(255)
);

--changeset john-doe:2
CREATE INDEX idx_users_mobile ON users(mobile_number);

SQL files in Liquibase require special comments that contain metadata for tracking changes. Every Liquibase SQL file must begin with the following comment to indicate it contains these structured comments.

In the above example SQL schema, the comments:

1. --liquibase formatted sql is required at the top of every SQL file to indicate it contains Liquibase metadata.

2. --changeset john-doe:1 defines a changeset with the specified author (john-doe) and ID (1), which is unique within the changelog file. This comment is similar to YAML changesets.

SQL format rules

Do not include a space before --liquibase formatted sql. Formatting variations may cause the Liquibase parser to return an error. When specifying changeset attributes in a formatted SQL changelog, any attribute value that contains spaces must be quoted. For example:

--changeset john-doe:add_columns context:"production" labels:"schema-update critical"
ALTER TABLE users ADD COLUMN first_name VARCHAR(50);
ALTER TABLE users ADD COLUMN last_name VARCHAR(50);

Notice:

• context: "production" does not need quotes, because there are no spaces, but it is valid to include them. Go to What is a context? to understand how contexts control deployment scope.

• labels: "schema-update critical" requires quotes due to the space between words.

Tip

Using a space in an author name is not recommended. Prefer to use dot- or dash-separated names.

SQL statement format

When splitStatements is set to false in your changeset, avoid trailing semicolons on individual SQL statements.

With splitStatements: false, some JDBC drivers treat a trailing semicolon as an additional empty statement, which can cause migration failures. When splitStatements: true (the default), Liquibase uses the semicolon as the statement delimiter, so semicolons are required.

-- splitStatements: false — trailing semicolon causes an empty-statement error
CREATE TABLE customer (
  customer_id VARCHAR(36) NOT NULL
);

-- splitStatements: false — omit the trailing semicolon
CREATE TABLE customer (
  customer_id VARCHAR(36) NOT NULL
)

Include and includeAll tags

Liquibase provides two primary tags for referencing other changelog files in your root changelog: include and includeAll. The include tag allows you to include a single SQL file in your changelog:

YAML Example

databaseChangeLog:
  - changeSet:
      id: 1
      author: john-doe
      changes:
        - include:
            path: changes/release-1/001-create-users-table.sql

SQL Example

--liquibase formatted sql

--changeset john-doe:1
--include: changes/release-1/001-create-users-table.sql

While includeAll allows you to include multiple SQL files in your changelog:

YAML Example

databaseChangeLog:
  - changeSet:
      id: 2
      author: john-doe
      changes:
        - includeAll:
            path: changes/release-2/

SQL Example

--liquibase formatted sql

--changeset john-doe:2
--includeAll: changes/release-2/

Use includeAll for releases or environments where speed and scalability are prioritized, and use include when explicit control and maintainability are needed, especially in controlled isolated environments.

Changeset

Each changeset in a formatted SQL file begins with a comment of the form:

--changeset author:id attribute1:value1 attribute2:value2 [...]

Go to What is a Changeset? to review the full changeset attribute reference.

Rollback actions

If you need to roll back a change, you can specify rollback actions directly in the changeset. These actions run when the changeset is rolled back.

YAML Example

  changes:
    - sql:
        sql: |
            CREATE TABLE test1 (
            id INT PRIMARY KEY,
            name VARCHAR(255)
          );
  rollback:
    - sql:
        sql: DROP TABLE test1;
  changes:
    - sql:
        sql: |
          INSERT INTO test1 (id, name) VALUES (1, 'john');
          INSERT INTO test1 (id, name) VALUES (2, 'josh');
    rollback:
      - sql:
          sql: DELETE FROM test1 WHERE id IN (1, 2);

SQL Example

--liquibase formatted sql

--changeset john-doe:1
create table test1 (  
    id int primary key,
    name varchar(255)  
);  
--rollback drop table test1; 

--changeset john-doe:2 
insert into test1 (id, name) values (1, 'john');
insert into test1 (id,  name) values (2, 'josh');

--rollback delete from test1 where id in (1, 2);

Caution

When using SQL format for your changelog files, rollback actions are required. Go to Automatic and custom rollbacks to understand rollback strategies and implementation options.

A clear and consistent SQL file structure allows teams to manage database changes with properly formatted Liquibase SQL files. By following the best practices for directory organization, changeset formatting, spacing, and rollback definitions, you can ensure your changelogs are well-structured, maintainable, and CI/CD-ready.

Next steps

• Go to What is a changelog? to understand the changelog file format and how changesets are ordered.

• Go to What is a context? to learn how to control which changesets apply to which environments.

• Go to Automatic and custom rollbacks to configure rollback strategies for your SQL files.