Liquibase formatted SQL changelogs and multiple files

Question

We are starting using liquibase for our application on the spring-boot. One of requirements use plain sql for liquibase. We have many sql files for initialization database. I check documentation https://www.liquibase.org/documentation/sql_format.html but not found information how can i create hierarchy of change log sql's files. Spring boot properties liquibase.change-log awaiting single file. I tried to separate file names by , or ; all times got error from spring-boot Cannot find changelog location... So my question:
How can i declare execution of another file or files in "sql format"??
Like xml equivalent:
<include file="second_changelog.sql"/>
<include file="third_changelog.sql"/>

Not worked example first_changelog.sql :
--liquibase formatted sql --changeset author_1:1 UPDATE [dbo].[customers] SET name='HD_1' WHERE id = '11'; --import file=second_changelog.sql --import file=third_changelog.sql

PS. Please do not suggest xml, because of i need only SQL

Answer 1

You can use one XML file which contains multiple references to plain sql (see for details https://www.liquibase.org/documentation/changes/sql_file.html)

Example:

<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
                   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                   xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.3.xsd">

    <changeSet id="init" author="author">
        <sqlFile encoding="utf8" path="first_changelog.sql"/>
        <sqlFile encoding="utf8" path="second_changelog.sql"/>
    </changeSet>

Answer 2

So,

It seems like you do not want to use one big formatted SQL changelog in which each changeset is written in the file. Instead, if you want to have separate SQL files that you point to (that use plain SQL), you would need to have a only-touched-once changelog that looks like:

<changeLog><includeAll path="/path/to/your/sql/directory"></changeLog>

Using the includeAlltells Liquibase to pull in all the SQL files in the directory you point to as if they were individual changeSets. See: http://www.liquibase.org/2015/09/liquibase-without-changelogs.html

P.S.: Depending on your project, you may want to look into Datical - it's a commercial solution that's built on top of Liquibase and makes this all a lot easier. You would just need to check in SQL files into source code control, and Datical has a code packager that can validate and produce an immutable artifact of database changes. You can get out of the business of managing a changeLog entirely.

Answer 3

The feature request is still open and the resolution is "No plans yet" please check the below link for more discussion: http://forum.liquibase.org/topic/can-we-include-a-file-in-rollback

feature request Id in Jira: https://liquibase.jira.com/browse/CORE-1637

Answer 4

I used below structure, Answer by @Yuriy will take sql file as a change set (not the changesets defined in sql files).

<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
               xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
               xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.3.xsd">
  <include file="changelog.sql"/>
<include file="changelog2.sql"/>
</databaseChangeLog>

further changelog.sql(s) are like below. This way liquibase consider the sql changesets too.

--liquibase formatted sql

--changeset nvoxland:1

CREATE TABLE department_1   
(    
     DeptID int NOT NULL PRIMARY KEY CLUSTERED  
   , DeptName varchar(50) NOT NULL  
   , ManagerID INT  NULL  
   , ParentDeptID int NULL  
   , SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL  
   , SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL  
   , PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)     
)    
WITH (SYSTEM_VERSIONING = ON)   
;  
--rollback drop table department_1;

Answer 5

Use master XML file to load sql changesets from multiple files.

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
        http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.8.xsd">
    <includeAll path="sql/"/>
</databaseChangeLog>

Now you can store all .sql files in sql directory. Even it will read from sub-directories as well.