Migrasi Pangkalan Data dengan Flyway

1. Pengenalan

Artikel ini menerangkan konsep utama Flyway dan bagaimana kita dapat menggunakan kerangka ini untuk terus mengubah skema pangkalan data aplikasi kita dengan andal dan mudah. Pada akhirnya, kami akan menunjukkan contoh pengurusan pangkalan data H2 dalam memori menggunakan plugin Maven Flyway.

Flyway mengemas kini pangkalan data dari satu versi ke versi berikutnya menggunakan migrasi. Kita boleh menulis migrasi sama ada dalam SQL dengan sintaks khusus pangkalan data atau di Java untuk transformasi pangkalan data lanjutan.

Migrasi boleh dibuat versi atau berulang. Yang pertama mempunyai versi unik dan digunakan sekali sahaja. Yang terakhir ini tidak mempunyai versi. Sebagai gantinya, mereka (kembali) digunakan setiap kali checksum mereka berubah.

Dalam satu proses migrasi, migrasi berulang selalu berlaku terakhir, setelah migrasi versi yang belum selesai telah dilaksanakan. Migrasi berulang diterapkan mengikut urutan mereka. Untuk satu migrasi, semua penyataan dijalankan dalam satu transaksi pangkalan data.

Dalam artikel ini, kami memfokuskan pada bagaimana kami menggunakan plugin Maven untuk melakukan migrasi pangkalan data.

2. Plugin Flyway Maven

Untuk memasang plugin Flyway Maven, mari tambahkan definisi plugin berikut ke pom.xml kami :

 org.flywaydb flyway-maven-plugin 4.0.3 

Kami boleh menyemak versi terkini plugin yang terdapat di Maven Central.

Plugin Maven ini boleh dikonfigurasikan dalam empat cara yang berbeza. Sila rujuk dokumentasi untuk mendapatkan senarai semua sifat yang boleh dikonfigurasi.

2.1. Konfigurasi Plugin

Kami mungkin mengkonfigurasi pemalam secara langsung melalui tag dalam definisi pemalam pom.xml kami :

 org.flywaydb flyway-maven-plugin 4.0.3  databaseUser databasePassword  schemaName  ...  

2.2. Harta Tanah Maven

Kami juga boleh mengkonfigurasi pemalam dengan menentukan sifat yang boleh dikonfigurasi sebagai sifat Maven di pom kami:

 ...  databaseUser databasePassword schemaName ...  ... 

2.3. Fail Konfigurasi Luaran

Kami juga boleh menyediakan konfigurasi pemalam dalam fail .properties yang berasingan :

flyway.user=databaseUser flyway.password=databasePassword flyway.schemas=schemaName ...

Nama fail konfigurasi lalai adalah flyway.properties dan ia harus berada di direktori yang sama dengan fail pom.xml . Pengekodan ditentukan oleh flyway.encoding (Lalai adalah UTF-8 ).

Sekiranya anda menggunakan nama lain (mis. CustomConfig.properties ) sebagai fail konfigurasi, maka ia harus dinyatakan secara eksplisit ketika memanggil perintah Maven:

$ mvn -Dflyway.configFile=customConfig.properties

2.4. Sifat Sistem

Akhirnya, semua sifat konfigurasi juga boleh dinyatakan sebagai sifat sistem semasa memanggil Maven pada baris arahan:

$ mvn -Dflyway.user=databaseUser -Dflyway.password=databasePassword -Dflyway.schemas=schemaName

Berikut adalah urutan keutamaan apabila konfigurasi ditentukan dalam lebih dari satu cara:

  1. Sifat sistem
  2. Fail konfigurasi luaran
  3. Sifat batu
  4. Konfigurasi pemalam

3. Contoh Penghijrahan

Pada bahagian ini, kita melalui langkah-langkah yang diperlukan untuk memindahkan skema pangkalan data ke pangkalan data H2 dalam memori menggunakan plugin Maven. Kami menggunakan fail luaran untuk mengkonfigurasi Flyway.

3.1. Kemas kini POM

Pertama, mari tambah H2 sebagai pergantungan:

 com.h2database h2 1.4.196 

Kami sekali lagi dapat memeriksa versi pemacu terkini yang terdapat di Maven Central. Kami juga akan menambah plugin Flyway seperti yang dijelaskan sebelumnya.

3.2. Konfigurasikan Flyway Menggunakan Fail Luaran

Next, we create myFlywayConfig.properties in $PROJECT_ROOT with the following content:

flyway.user=databaseUser flyway.password=databasePassword flyway.schemas=app-db flyway.url=jdbc:h2:mem:DATABASE flyway.locations=filesystem:db/migration

The above configuration specifies that our migration scripts are located in the db/migration directory. It connects to an in-memory H2 instance using databaseUser and databasePassword.

The application database schema is app-db.

Of course, we replace flyway.user, flyway.password, and flyway.url with our own database username, database password, and database URL appropriately.

3.3. Define First Migration

Flyway adheres to the following naming convention for migration scripts:

__.sql

Where:

  • – Default prefix is V, which may be configured in the above configuration file using the flyway.sqlMigrationPrefix property.
  • – Migration version number. Major and minor versions may be separated by an underscore. The migration version should always start with 1.
  • – Textual description of the migration. The description needs to be separated from the version numbers with a double underscore.

Example: V1_1_0__my_first_migration.sql

So, let's create a directory db/migration in $PROJECT_ROOT with a migration script named V1_0__create_employee_schema.sql containing SQL instructions to create the employee table:

CREATE TABLE IF NOT EXISTS `employee` ( `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY, `name` varchar(20), `email` varchar(50), `date_of_birth` timestamp )ENGINE=InnoDB DEFAULT CHARSET=UTF8;

3.4. Execute Migrations

Next, we invoke the following Maven command from $PROJECT_ROOT to execute database migrations:

$ mvn clean flyway:migrate -Dflyway.configFile=myFlywayConfig.properties

This should result in our first successful migration.

The database schema should now be depicted as follows:

employee: +----+------+-------+---------------+ | id | name | email | date_of_birth | +----+------+-------+---------------+

We can repeat definition and execution steps to do more migrations.

3.5. Define and Execute Second Migration

Let's see what a second migration looks like by creating a second migration file with name V2_0_create_department_schema.sql containing the following two queries:

CREATE TABLE IF NOT EXISTS `department` ( `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY, `name` varchar(20) )ENGINE=InnoDB DEFAULT CHARSET=UTF8; ALTER TABLE `employee` ADD `dept_id` int AFTER `email`;

We'll execute a similar migration like we did the first time.

And now, our database schema has changed to add a new column to employee and a new table:

employee: +----+------+-------+---------+---------------+ | id | name | email | dept_id | date_of_birth | +----+------+-------+---------+---------------+
department: +----+------+ | id | name | +----+------+

We may now verify that both migrations were indeed successful by invoking the following Maven command:

$ mvn flyway:info -Dflyway.configFile=myFlywayConfig.properties

4. Disabling Flyway in Spring Boot

Sometimes we may need to disable Flyway migrations under certain circumstances.

For example, it's a common practice to generate database schema based on the entities during tests. In such a situation, we can disable Flyway under the test profile.

Let's see how easy it is in Spring Boot.

4.1. Spring Boot 1.x

All we need to do is to set the flyway.enabled property in our application-test.properties file:

flyway.enabled=false

4.2. Spring Boot 2.x

In the more recent versions of Spring Boot, this property has been changed to spring.flyway.enabled:

spring.flyway.enabled=false

4.3 Empty FlywayMigrationStrategy

If we only want to disable automatic Flyway migration on startup, but still be able to trigger the migration manually, then using the properties described above isn't a good choice.

That's because in such a situation Spring Boot will not auto-configure the Flyway bean anymore. Consequently, we'd have to provide it on our own which isn't very convenient.

So if this is our use case, we can leave Flyway enabled and implement an empty FlywayMigrationStrategy:

@Configuration public class EmptyMigrationStrategyConfig { @Bean public FlywayMigrationStrategy flywayMigrationStrategy() { return flyway -> { // do nothing }; } }

This will effectively disable Flyway migration on application startup.

But we'll still be able to trigger the migration manually:

@RunWith(SpringRunner.class) @SpringBootTest public class ManualFlywayMigrationIntegrationTest { @Autowired private Flyway flyway; @Test public void skipAutomaticAndTriggerManualFlywayMigration() { flyway.migrate(); } }

5. How Flyway Works

To keep track of which migrations have already been applied, when and by whom, it adds a special bookkeeping table to your schema. This metadata table also tracks migration checksums and whether or not the migrations were successful.

The framework performs the following steps to accommodate evolving database schemas:

  1. It checks a database schema to locate its metadata table (SCHEMA_VERSION by default). If the metadata table does not exist, it will create one
  2. It scans an application classpath for available migrations
  3. It compares migrations against the metadata table. If a version number is lower or equal to a version marked as current, it is ignored
  4. It marks any remaining migrations as pending migrations. These are sorted based on version number and are executed in order
  5. As each migration is applied, the metadata table is updated accordingly

6. Commands

Flyway supports the following basic commands to manage database migrations.

  • Info: Prints current status/version of a database schema. It prints which migrations are pending, which migrations have been applied, what is the status of applied migrations and when they were applied.
  • Migrate: Migrates a database schema to the current version. It scans the classpath for available migrations and applies pending migrations.
  • Baseline: Baselines an existing database, excluding all migrations, including baselineVersion. Baseline helps to start with Flyway in an existing database. Newer migrations can then be applied normally.
  • Validate : Mengesahkan skema pangkalan data semasa terhadap migrasi yang tersedia.
  • Pembaikan : Membaiki jadual metadata.
  • Bersih : Jatuhkan semua objek dalam skema yang dikonfigurasi. Semua objek pangkalan data dijatuhkan. Sudah tentu, anda tidak boleh menggunakan clean pada pangkalan data pengeluaran.

7. Kesimpulannya

Dalam artikel ini, kami telah menunjukkan bagaimana Flyway berfungsi dan bagaimana kami dapat menggunakan kerangka ini untuk merombak pangkalan data aplikasi kami dengan pasti.

Kod yang menyertakan artikel ini terdapat di GitHub.