Your First Migration with SQL

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. View the Supported Databases topic for more information on which databases we support.

You can also view the Database Tutorials topic for more information on running each database with Liquibase.

Step 1: Create a Formatted SQL Changelog

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

To Create your Changelog

1. Create a file in your Liquibase project directory called changelog.sql.
2. For this example, enter the following information into the changelog.sql file.

--liquibase formatted sql

Step 2: Add a Change Set

Change sets are units of change that Liquibase can execute on a database. When adding a change set, your change must be defined by both an “id” attribute and an “author” attibute. 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. For this example, enter the following information into the changelog.sql file, then save it.

See the Formatted SQL Changelogs topic for more information about SQL Syntax.

--liquibase formatted sql

--changeset bob:1
create table test1 (
id int primary key,
name varchar(255)
);

Step 3: Deploy your Changelog

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

To Apply the Change Set

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

Your database now 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.

Notice that two tables 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 make sure two machines don’t attempt to modify the database at the same time.

View DATABASECHANGELOG Table and DATABASECHANGELOGLOCK Table topics for more information.

Additional Information

This topic is great 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 more manageable chunks.

See the Database Migrations with Multiple SQL Files topic for more information on how to Migrate with Multiple SQL files.

You can also learn how to create your First Migrations with Liquibase Functions.

Summary

In this tutorial we covered:

• Creating Formatted SQL Changelogs
• Adding Changesets to your Changelog
• Running your Changelog
• Checking your Database

Next Up: