Flyway: prvé kroky

Posted on by

O Flyway

Flyway je jednoduchý nástroj na podporu migračných skriptov pre SQL.

Vlastnosti:

  • migračné skripty sú štandardné SQL skripty v textovom formáte, ktoré možno udržiavať vo verzovacom systéme.
  • existuje silná podpora pre buildovacie nástroje (Maven a spol)
  • zámerne neexistuje podpora pre spätnú migráciu: ak to prekáža, použite Liquibase.

Konfigurácia v Mavene

  • dodáme plug-in org.flywaydb:flyway-maven-plugin:3.0.
  • nakonfigurujeme JDBC url
  • a používateľa (user) a heslo
  • nezabudneme dodať ovládač k databáze medzi závislosti

POM môže vyzerať takto:

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>sk.upjs.ics</groupId>
    <artifactId>sql-migrations-demo</artifactId>
    <version>1.0-SNAPSHOT</version>
    <packaging>jar</packaging>
    <build>
        <plugins>
            <plugin>
                <groupId>org.flywaydb</groupId>
                <artifactId>flyway-maven-plugin</artifactId>
                <version>3.0</version>
                <configuration>
                    <url>jdbc:hsqldb:hsql://localhost/databaza</url>
                    <user>sa</user>
                </configuration>
            </plugin>
        </plugins>
    </build>
    <dependencies>
        <dependency>
            <groupId>org.hsqldb</groupId>
            <artifactId>hsqldb</artifactId>
            <version>2.3.2</version>
        </dependency>
    </dependencies>
</project>

Prvá migrácia

Vytvoríme prvý migračný skript V1__Create_person_table.sql. Názov dodržiava mennú konvenciu:

  • V: predpona “verzia”
  • 1: číslo verzie skriptu
  • __: dva podtržníky: oddeľovač verzie od popisu migrácie
  • .sql: prípona

V Mavene patria skripty do src/main/resources/db/migration.

Obsahom je:

create table PERSON (
    ID int not null,
    NAME varchar(100) not null
);

Vyskúšajme si zistenie informácií o migráciách:

mvn compile flyway:info

Krok compile je potrebný na skopírovanie migračných skriptov do CLASSPATH:

[INFO] --- flyway-maven-plugin:3.0:info (default-cli) @ sql-migrations-demo ---
[INFO] Database: jdbc:hsqldb:hsql://localhost/databaza (HSQL Database Engine 2.3
[INFO]
+----------------+----------------------------+---------------------+---------+
| Version        | Description                | Installed on        | State   |
+----------------+----------------------------+---------------------+---------+
| 1              | Create person table        |                     | Pending |
+----------------+----------------------------+---------------------+---------+
[INFO] ------------------------------------------------------------------------

Migrujme!

mvn compile flyway:migrate

Maven vypíše:

[INFO] --- flyway-maven-plugin:3.0:migrate (default-cli) @ sql-migrations-demo ---
[INFO] Database: jdbc:hsqldb:hsql://localhost/databaza (HSQL Database Engine 2.3)
[INFO] Validated 1 migration (execution time 00:00.007s)
[INFO] Creating Metadata table: "PUBLIC"."schema_version"
[INFO] Current version of schema "PUBLIC": << Empty Schema >>
[INFO] Migrating schema "PUBLIC" to version 1
[INFO] Successfully applied 1 migration to schema "PUBLIC" (execution time 00:00.041s).
[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO] ------------------------------------------------------------------------

Druhá migrácia

Vyrobme V2__Add_people.sql s obsahom:

insert into PERSON (ID, NAME) values (1, 'Axel');
insert into PERSON (ID, NAME) values (2, 'Mr. Foo');
insert into PERSON (ID, NAME) values (3, 'Ms. Bar');

Všimnime si, že máme viacero príkazov oddelených bodkočiarkou.

Migrujme!

mvn compile flyway:migrate

Maven vypíše:

mvn compile flyway:migrate

[INFO] --- flyway-maven-plugin:3.0:migrate (default-cli) @ sql-migrations-demo ---
[INFO] Database: jdbc:hsqldb:hsql://localhost/databaza (HSQL Database Engine 2.3)
[INFO] Validated 2 migrations (execution time 00:00.009s)
[INFO] Current version of schema "PUBLIC": 1
[INFO] Migrating schema "PUBLIC" to version 2
[INFO] Successfully applied 1 migration to schema "PUBLIC" (execution time 00:00.021s).
[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO] ------------------------------------------------------------------------

Oprava zlyhanej migrácie

Vyrobme V3__Remove_people.sql:

delete from person where id = 1;
xdelete from person where id = 2

Áno, je tam preklep! Skúsme migrovať:

mvn compile flyway:migrate

Uvidíme:

[INFO] --- flyway-maven-plugin:3.0:migrate (default-cli) @ sql-migrations-demo ---
[INFO] Database: jdbc:hsqldb:hsql://localhost/databaza (HSQL Database Engine 2.3)
[INFO] Validated 3 migrations (execution time 00:00.011s)
[INFO] Current version of schema "PUBLIC": 2
[INFO] Migrating schema "PUBLIC" to version 3
[ERROR] Migration of schema "PUBLIC" to version 3 failed! Please restore backups and roll back database and code!

…

[ERROR] Failed to execute goal org.flywaydb:flyway-maven-plugin:3.0:migrate (default-cli) on project sql-migrations-demo: org.flywaydb.core.internal.dbsupport.FlywaySqlScriptException: Error executing
 statement at line 2: xdelete from person where id = 2: unexpected token: XDELETE -> [Help 1]
c:\Projects\sql-migrations-demo>

Ak by sme videli transakčný log (skúste spustiť Maven v logovacom režime cez -X), videli by sme, že transakcia sa rollbackne. (A kto neverí, nech si SELECTne person).

Flyway odporúča prekontrolovať dáta, prípadne ich obnoviť zo zálohy, a ak je všetko v poriadku, môžeme opraviť metadáta:

mvn compile flyway:repair

A uvidíme:

[INFO] --- flyway-maven-plugin:3.0:repair (default-cli) @ sql-migrations-demo ---
[INFO] Database: jdbc:hsqldb:hsql://localhost/databaza (HSQL Database Engine 2.3)
[INFO] Metadata table "PUBLIC"."schema_version" successfully repaired (execution time 00:00.000s).
[INFO] Manual cleanup of the remaining effects the failed migration may still be required.
[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO] ------------------------------------------------------------------------

Ak by sme opravovanie nevykonali, opakovaný flyway:migrate by začal protestovať:

[ERROR] Failed to execute goal org.flywaydb:flyway-maven-plugin:3.0:migrate (default-cli) on project sql-migrations-demo: 
org.flywaydb.core.api.FlywayException: 
    Schema "PUBLIC" contains a failed migration to version 3 ! -> [Help 1]

Pridaj komentár

Vaša e-mailová adresa nebude zverejnená. Vyžadované polia sú označené *