Database migrations with multiple SQL files

Step 1: Create an SQL folder

To complete a migration, you must create an SQL folder in your Liquibase project folder. The SQL folder is where you will place all your SQL scripts that Liquibase uses to track, version, and deploy changes to your database.

Step 2: Create or generate a changelog

To use SQL script files, you must also have a Changelog file. While the SQL scripts define all of your database changes, Liquibase still requires the use of a changelog to tell it where your scripts are located. While you can use XML, JSON, YAML, or formatted SQL when creating a changelog, you must create an XML changelog to use multiple SQL Scripts.

Creating changelog files manually

  1. Create a file in your Liquibase project directory called myChangeLog.xml.
  2. For this example, enter the following information into the myChangeLog.xml file.
<?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"
    xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
    xmlns:pro="http://www.liquibase.org/xml/ns/pro"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
        http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd
        http://www.liquibase.org/xml/ns/dbchangelog-ext
        http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd
        http://www.liquibase.org/xml/ns/pro
        http://www.liquibase.org/xml/ns/pro/liquibase-pro-latest.xsd">
</databaseChangeLog>

When you have completed your work, save your file.

Generating changelog files

If you have an existing database, you can generate a changelog file that reflects the current state of your database. For more information on how to generate a changelog, visit the generate-changelog command topic, and read the article about How to set up Liquibase with an Existing Project and Multiple Environments.

Step 3: Configure your changelog

To run SQL scripts, you must tell Liquibase where your SQL scripts are located. To configure your changelog, use the includeAll tag to point Liquibase to the correct folder.

<?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"
    xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
    xmlns:pro="http://www.liquibase.org/xml/ns/pro"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
        http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd
        http://www.liquibase.org/xml/ns/dbchangelog-ext
        http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd
        http://www.liquibase.org/xml/ns/pro
        http://www.liquibase.org/xml/ns/pro/liquibase-pro-latest.xsd">
    <includeAll  path="sql"/>  
</databaseChangeLog>

Step 4: Add an SQL script to the SQL folder

To add SQL scripts to your SQL folder:

  1. In your SQL folder, create a .sql file.
  2. For this example, enter the following information:
create  table  PERSON  (  
    ID  int  not  null,  
    FNAME  varchar(100)  not  null  
);

When you have completed your work, save your file.

Step 5: Deploy your script

Once you have created and configured your changelog, and have also added your SQL scripts, you are ready to deploy.

To deploy your script

  1. Open your command prompt or terminal.
  2. Run one of the following commands:
    • Linux/Unix/Mac:LB_HOME/liquibase update
    • Windows:LB_HOME\liquibase.bat update

Note: In place of LB_HOME, use the folder name where you extracted Liquibase.

Your database should now contain a table called PERSON.

Step: Check your database

To check your database:

  1. Open your command prompt or terminal.
  2. Navigate to the folder where you placed your driver jar.
  3. Run: java -jar (driver-version.jar)

Note: Where (driver-version.jar) is listed, enter your driver name and version number.

Example: java -jar h2-1.4.199.jar

If you use a Liquibase properties file, enter the JDBC URL, user name, and password information. Notice that two tables were created:

  • DATABASECHANGELOG
  • DATABASECHANGELOGLOCK

The DATABASECHANGELOG table contains a list of all the changes that have been run against the database. The DATABASECHANGELOGLOCK table is used to make sure two machines don't attempt to modify the database at the same time.

View the DATABASECHANGELOG table and DATABASECHANGELOGLOCK table topics for more information.

Summary

In this tutorial we covered:

  • Creating/generating changelogs
  • Adding changesets to your changelog
  • Running your changelog
  • Checking your database