Migrating with SQL

This page describes how to update your database using a formatted SQL changelog. When you use a SQL changelog with Liquibase, schema changes are written using SQL statements that create, modify, or drop database objects. Data changes are written using SQL statements that insert, update, or delete data in the database.

Prerequisites

Before performing any of these steps, you must be able to connect to a local or remote database, accessible via command line or IDE/GUI.

Note: For more information on which databases Liquibase supports or information on running each database with Liquibase, see Supported Databases.

Step 1: Create a formatted SQL changelog

To complete your first migration, create a formatted SQL changelog in your Liquibase project directory so Liquibase can track, version, and deploy changes to your database.

Tip: Formatted SQL changelogs generated by Liquibase versions prior to 4.2 might cause issues because of the lack of space after a double dash ( -- ). To fix those issues, add a space after the double dash. For example: --liquibase formatted sql instead of --liquibase formatted sql and --changeset myname:create-table instead of --changeset myname:create-table.

To create your changelog:

  1. Create a file in your Liquibase project directory called changelog.sql.
  2. Enter the following information into the changelog.sql file for this example:
    3. --liquibase formatted sql

Step 2: Add a changeset

changesets are units of change that Liquibase can execute on a database. When adding a changeset, your change must be defined by both an id attribute and an author attribute. It is best practice to only include one change in each changeset.

To add your changeset:

  1. Locate and open the changelog.sql file.
  2. Enter the following information into the changelog.sql file for this example, then save it.
    3. --liquibase formatted sql 
--changeset bob:1 
create table test1 (  
id int primary key, 
name varchar(255)
);

Note: For more information about SQL syntax, see Example Changelogs: SQL Format.

Step 3: Deploy your changelog

To deploy the changelog and your new changeset, run the update command. When running this command, Liquibase reads your list of changesets in order and checks the DATABASECHANGELOG table for anything that was previously run. Any changesets that have not already been applied to the database will get applied, and Liquibase will track that information.

To apply the changeset:

  1. Open your command prompt or terminal.
  2. Run the following command: liquibase update --changelog-file=changelog.sql

Now, your database contains a table called test1.

Step 4: Check your database

To check your database, open your database IDE to find the change that you made.

Also, you can see two tables that were created along with test1:

  • 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 ensure only one instance of Liquibase is running at one time.

Note: For more information, see DATABASECHANGELOG table and DATABASECHANGELOGLOCK table.

Additional information

You can use this topic when you only have a handful of SQL scripts. However, if your list of scripts becomes too large to maintain in a formatted SQL changelog, you may want to break up your scripts into smaller and more manageable chunks. If you want to perform migrations with multiple SQL files, refer to the Database migrations with multiple SQL files topic. You can also learn about migrations with Change Types by going to the Migrating with Liquibase Change Types topics.

Related links