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 in one or more directories referenced by the locations property.

New sql migrations are discovered automatically through classpath 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 (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');