What is sqlFile?
The sql tag allows you to embed raw SQL statements directly within a changeset, instead of referencing external .sql files. This is particularly useful for small, declarative changes or when working with simple SQL that doesn't require reuse across environments.
The sql change type supports multi-line SQL statements using delimiters such as ; or GO, and allows inline comments in the following formats:
- Multi-line comments enclosed within
/* ... */ - Single-line comments prefixed by
--
It also offers support for statement splitting via the splitStatements attribute and database-specific targeting using the dbms attribute. For Example:
databaseChangeLog:
- changeSet:
id: sql-example
author: john-deo
changes:
- sql:
dbms: '!h2, oracle, mysql'
endDelimiter: \nGOs
splitStatements: true
sql: |
insert into person (name) values ('Bob')
The sqlFile tag in Harness Database DevOps (which uses Liquibase under the hood) allows you to execute raw SQL stored in external .sql files as part of a changeset. This is especially useful for large SQL scripts, vendor-specific features, or when reusing validated scripts across environments.
Here’s a sample usage within a changeset:
databaseChangeLog:
- changeSet:
id: run-import-script
author: db.team
changes:
- sqlFile:
path: scripts/init_data.sql
splitStatements: true
stripComments: true
encoding: UTF-8
Key Attributes of sqlFile
The sqlFile tag has the following key attributes:
| Attribute | Description | Required |
|---|---|---|
path | Relative or absolute path to the SQL file to be executed. | ✅ |
splitStatements | Whether the file should be split into individual SQL statements. Default: true. | ❌ |
stripComments | Strips SQL comments before execution. Default: false. | ❌ |
encoding | Character encoding of the file (e.g., UTF-8). | ❌ |
endDelimiter | Defines custom delimiter for statements (if splitStatements is true). | ❌ |
Why Use sqlFile?
- Maintain large scripts outside the changelog for better readability and reuse.
- Reuse approved SQL written by DBAs or generated by third-party tools.
- Improve traceability by linking SQL execution to a versioned changeset with author and context.
- Enforce auditability via Harness pipelines, approvals, and deployment logs.
Best Practices in Harness
Place .sql files in version control alongside your changelogs.
Pair each sqlFile with a rollback block if the operation is reversible.
Use context, labels, and logicalFilePath for environment-specific execution and clarity.
Example with Rollback
- changeSet:
id: seed-customers
author: alice.dev
context: dev
changes:
- sqlFile:
path: seed/customers.sql
splitStatements: true
rollback:
sqlFile:
path: rollback/delete_customers.sql
This example shows how to use sqlFile to seed data in a development environment, with a corresponding rollback script to remove the seeded data if needed.
When to Use sql vs sqlFile
| Use Case | Use sql | Use sqlFile |
|---|---|---|
| Small or simple SQL | ✅ | — |
| SQL must be embedded | ✅ | — |
| Reusing large or vendor scripts | — | ✅ |
| Managing external files | — | ✅ |
| Need full CI/CD preview/audit | ✅ (in Harness) | ✅ (full preview + rollback logic) |
Both tags support rollback, split statements, and comment stripping, but sqlFile is generally preferred for large or externalized logic, especially in Harness where preview, approval, and audit workflows are deeply integrated.
Conclusion
The sqlFile tag enables teams to execute and manage raw SQL in a structured, controlled, and auditable way through Harness Database DevOps. It bridges manual DBA processes with modern CI/CD pipelines, empowering teams to scale database automation without sacrificing control or compliance.
FAQ
1. What is the sqlFile tag used for in Harness Database DevOps?
The sqlFile tag lets you execute raw SQL stored in external files as part of a Liquibase changeset. In Harness Database DevOps, it is used to version and run large SQL scripts—like data imports or vendor-specific DDL—within a CI/CD pipeline, while retaining auditability and rollback options.
2. Can I preview the SQL inside a sqlFile before deployment?
Yes. Harness automatically previews the contents of sqlFile executions during pull requests and pipeline runs. This visibility ensures DBAs and reviewers can validate the exact SQL that will be executed, reducing risk and improving governance.
3. How does rollback work with sqlFile in Harness?
To enable rollback, you can define a companion sqlFile under the rollback section of the changeset. Harness will automatically trigger this rollback SQL if the deployment fails or is manually rolled back, ensuring consistent and safe recovery.
4. Where should I store .sql files used by sqlFile?
Store your .sql files in version control alongside your changelogs. This ensures traceability, supports GitOps workflows, and allows Harness to track file changes, validate checksums, and enforce pipeline approvals.
5. Is sqlFile better than inline SQL in Liquibase changesets?
Use sqlFile when:
- The SQL is too large or complex for inline syntax.
- You’re reusing scripts written or approved by DBAs.
- You need to preserve dialect-specific formatting.
For smaller, portable, or declarative changes, inline YAML or XML is preferred.
6. When should I use the inline sql tag instead of sqlFile?
Use the inline sql tag when your SQL statement is simple, small, and does not require reuse across multiple environments. It’s ideal for one-off DDL or DML operations that are easily readable and maintainable directly within the changelog. Inline SQL is also useful when you want to minimize file dependencies or when working in early-stage development environments. For larger, reusable, or vendor-specific scripts, sqlFile is recommended.
7. Does Harness support approvals for sqlFile execution?
Absolutely. Harness integrates approval gates into your database pipeline. Any changeset using sqlFile can be subjected to review and approval workflows—ensuring that all SQL changes are vetted before execution in sensitive environments like staging or production.