Sql-based migrations

Typical usage

  • DDL changes (CREATE/ALTER/DROP statements for TABLES,VIEWS,TRIGGERS,SEQUENCES,...)
  • Simple reference data changes (CRUD in reference data tables)
  • Simple bulk data changes (CRUD in regular data tables)

Location and discovery

Sql migrations reside on the classpath or on the file system in one or more directories referenced by the locations property.
Unprefixed locations or locations with the classpath: prefix target the classpath.
Locations with the filesystem: prefix target the file system.

New sql migrations are discovered automatically through classpath and file system scanning at runtime.
This scanning is recursive. All migrations in directories below the specified ones are also picked up.

Naming

In order to be picked by the classpath scanner, the sql migrations must follow a naming pattern:

The file name consists of:

  • prefix: Configurable, default: V
  • version: Dots or underscores separate the parts, you can use as many parts as you like
  • separator: Configurable, default: __ (two underscores)
  • description: Underscores or spaces separate the words
  • suffix: Configurable, default: .sql

Sql Script syntax

  • Single- or multi-line statements
  • Flexible placeholder replacement
  • Single- (--) or Multi-line (/* */) comments spanning complete lines
  • Database-specific SQL syntax extensions (PL/SQL, T-SQL, ...)

Sample Script

/* Single line comment */
CREATE TABLE test_user (
  name VARCHAR(25) NOT NULL,
  PRIMARY KEY(name)
);

/*
Multi-line
comment
*/

-- Placeholder
INSERT INTO ${tableName} (name) VALUES ('Mr. T');

Java-based migrations