How to Use Liquibase for Oracle Database Deployment Automation
You can write your database change code in your selected authoring tool in SQL, YAML, JSON, or XML formats. Liquibase refers to these change scripts as ChangeSets. ChangeSets are grouped into ChangeLogs and placed in version control systems.
In this tutorial, we are going to cover the SQL database change code.
Some sqlFile Liquibase Considerations:
- sqlFile works well for complex changes that Liquibase does not support through automated Change Types, such as stored procedures. You can have multi-line SQL in sqlFile.
- For Single-line SQL statements, separate them with a ; at the end of your final SQL line, or use a GO statement. This statement has to be on a separate line between your SQL statements.
- For Multi-line SQL statements, you can only use a ; or GO statement to finish the line.
- For single SQL statements, you do not need to use either a ; or GO statement.
- You can add comments to sqlFile as follows:
- Multi-line comments need to be contained within /* and */.
- Single-line comments can start with – and will finish with that line.
- Liquibase’s default behavior is to split statements with a ; or GO at the line end. If you’re using a comment or non-statement that ends with either of those, keep them away from the end of the line to avoid SQL errors. sqlFile supports comments in these formats:
- A multi-line comment that starts with /* and ends with */.
- A single-line comment starting with -- and finishing at the end of the line.
- You can also nest sqlFile within the rollback tag in a changeset:
Example: changeset-sql.txt
--changeset dev:1
create table test1(
id int primary key,
name varchar(255)
);
Run sqlFile
- Add the Change Type to your changeset.
- Deploy your changeset by running the update command.
COMMANDS:
--To Execute
./liquibase update
--To Tag
./liquibase tag "BeforeChange2.0"
--To Rollback last 4 changes
./liquibase rollbackCount 4
--To Rollback to a tag BeforeChange2.0
./liquibase rollback BeforeChange2.0
--To see the liquibase commands
./liquibase --help
--To display the commands to execute
./liquibase updateSQL
Note: Liquibase 4.26.0+ allows you to use the rollbackSqlFile statement to specify rollback SQL for a changeset in a separate file:
EXAMPLE:
--changeset liquibase-user:1
DROP PROCEDURE hello_world;
--rollbackSqlFile path:release_1.0/rollback_45895.sql
Example execution if you’re using JIRA and provide the custom properties file:
liquibase --defaults-file=dev.liquibase.properties update-sql --label-filter=JIRA-1
liquibase --defaults-file=dev.liquibase.properties update --label-filter=JIRA-1
Once the update is completed, the changes will be applied as long as there are no typos, SQL issues, or permission problems. New log rows will be inserted into the DATABASECHANGELOG table for future reference and rollback functionality.
Explore more database resources from the experts at Datavail.com.