Getting started with Flyway Database Schema Migrations

Getting started with Flyway Database Schema Migrations
Techiio-author
Written by Sagar RabidasFebruary 24, 2022
14 min read
Hibernate
4 VIEWS 0 LIKES 0 DISLIKES SHARE
0 LIKES 0 DISLIKES 4 VIEWS SHARE
Techiio-author
Sagar Rabidas

Software Developer

In this blog, we will discuss Flyway Database Schema Migrations.

Introduction:-

In this text, I’m going to explain to all of u how Flyway works and how you can use it to run automated database schema migrations using incremental SQL migration scripts. Flyway is an open-source project. The database migrations can be defined either as SQL scripts or JDBC-based classes.

Database schema migration scripts:-

Much like the software supply code, the database schema modifications with time. Emigrate the database schema from one version to any other, you need to run DDL scripts.

For instance, we could store the DDL migration scripts in the src/main/folder, like this:

> tree src/main/resources
 
├── flyway
│   └── scripts
│       ├── PostgreSQL
│           ├── migration
│           │   ├── V1_0__post_tag.sql
│           │   ├── V1_1__post_details.sql
│           │   └── V1_2__post_comment.sql

The migration folder contains three incremental migration scripts that follow the Flyway script file naming conventions. The double underline (e.g., __) separates the script version from the script name.

The V1_0__post_tag.sql file is the initial migration script and contains the following DDL statements:

CREATE SEQUENCE hibernate_sequence
START 1 INCREMENT 1;
 
CREATE TABLE post (
    id int8 NOT NULL,
    title varchar(255),
    PRIMARY KEY (id)
);
 
CREATE TABLE tag (
    id int8 NOT NULL,
    name varchar(255),
    PRIMARY KEY (id)
);
 
CREATE TABLE post_tag (
    post_id int8 NOT NULL,
    tag_id int8 NOT NULL,
    PRIMARY KEY (post_id, tag_id)
);
 
ALTER TABLE post_tag
ADD CONSTRAINT POST_TAG_TAG_ID_FK
FOREIGN KEY (tag_id) REFERENCES tag;
 
ALTER TABLE post_tag
ADD CONSTRAINT POST_TAG_POST_ID_FK
FOREIGN KEY (post_id) REFERENCES post;

The V1_1__post_details.sql file is the second migration script, and it creates the post_details table:

CREATE TABLE post_details (
    id int8 NOT NULL,
    created_by varchar(255),
    created_on TIMESTAMP,
    PRIMARY KEY (id)
);
 
ALTER TABLE post_details
ADD CONSTRAINT POST_DETAILS_POST_ID_FK
FOREIGN KEY (id) REFERENCES post;

The V1_2__post_comment.sql file is the third migration script, and it’s responsible for creating the post_comment table:

CREATE TABLE post_comment (
    id int8 NOT NULL,
    review varchar(255),
    post_id int8, PRIMARY KEY (id)
);
 
ALTER TABLE post_comment
ADD CONSTRAINT POST_COMMENT_POST_ID_FK
FOREIGN KEY (post_id) REFERENCES post;

Flyway configuration:-

Flyway could be very easy to configure. All you want to do is instantiate the org.flywaydb.core.Flyway magnificence and set the jdbc data source and the area of the database migration scripts.

If you’re using Spring Framework, then you can use the following Java-based configuration:

@Bean
public Flyway flyway() {
    Flyway flyway = Flyway.configure()
        .dataSource(dataSource())
        .baselineOnMigrate(true)
        .locations(
            String.format(
                "classpath:/flyway/scripts/%1$s/migration",
                databaseType.name().toLowerCase()
            )
    ).load();
    flyway.migrate();
    return flyway;
}

And, we also need to make sure that the JPA EntityManagerFactory is built after the Flyway bean applies the database schema migrations:

@Bean
@DependsOn("flyway")
public LocalContainerEntityManagerFactoryBean entityManagerFactory() {
    ...
}

Running the Flyway database schema migrations:-

When bootstrapping the Spring application context on an empty database schema, we can see in the logs that Flyway applies all the existing incremental migration scripts:

INFO  : Flyway Community Edition 6.4.4 by Redgate
 
DEBUG : Scanning for classpath resources at
        'classpath:flyway/scripts/postgresql/migration' ...
DEBUG : Found resource:
        flyway/scripts/postgresql/migration/V1_0__post_tag.sql
DEBUG : Found resource:
        flyway/scripts/postgresql/migration/V1_1__post_details.sql
DEBUG : Found resource:
        flyway/scripts/postgresql/migration/V1_2__post_comment.sql
 
INFO  : Current version of schema "public": << Empty Schema >>
 
DEBUG : Parsing V1_0__post_tag.sql ...
DEBUG : Starting migration of schema "public"
        to version 1.0 - post tag ...
DEBUG : Successfully completed migration of schema "public"
        to version 1.0 - post tag
DEBUG : Schema History table "public"."flyway_schema_history"
        successfully updated to reflect changes
 
DEBUG : Parsing V1_1__post_details.sql ...
DEBUG : Starting migration of schema "public"
        to version 1.1 - post details ...
DEBUG : Successfully completed migration of schema "public"
        to version 1.1 - post details
DEBUG : Schema History table "public"."flyway_schema_history"
        successfully updated to reflect changes
 
DEBUG : Parsing V1_2__post_comment.sql ...
DEBUG : Starting migration of schema "public"
        to version 1.2 - post comment ...
DEBUG : Successfully completed migration of schema "public"
        to version 1.2 - post comment
DEBUG : Schema History table "public"."flyway_schema_history"
        successfully updated to reflect changes
 
INFO  : Successfully applied 3 migrations to schema "public"
        (execution time 00:00.146s)

Running a new Flyway database schema migration script:-

Now, allow’s assume we are imposing a new application function that calls for adding a brand new database desk, known as customers. For this, we want to create a new migration script with a model that’s more than any of the previously finished migration scripts.

So, we want to create a brand new migration script, called v1_3__users. Square, within the same src/predominant/resources/flyway/scripts/PostgreSQL/migration folder, where the other migration scripts are already stored.

The V1_3__users.sql script contains the following DDL statements:

CREATE TABLE users (
    id bigint NOT NULL,
    name varchar(255),
    PRIMARY KEY (id)
);

When restarting the Spring application, Flyway is going to discover the new V1_3__users.sql migration script and run it, as illustrated by the logs:

INFO  : Current version of schema "public": 1.2
 
DEBUG : Parsing V1_3__users.sql ...
DEBUG : Starting migration of schema "public"
        to version 1.3 - users ...
DEBUG : Successfully completed migration of schema "public"
        to version 1.3 - users
DEBUG : Schema History table "public"."flyway_schema_history"
        successfully updated to reflect changes
         
INFO  : Successfully applied 1 migration to schema "public"
        (execution time 00:00.064s)

Conclusion:-

Incremental migrations scripts are the first-class way to capture the changes gone through by way of a given database schema, and similar to you shop the software supply code in VCs (version manipulate gadget) (e.G., git), the schema migration scripts ought to also are living in VCs. In this manner, if you marvel while a given schema exchange has passed off, you could locate the data through scanning the dedicated log.

What’s notable approximately automatic schema migration equipment, like flyway, is that the migrations may be demonstrated inside the QA (high-quality assurance) environments, so, when deploying to manufacturing, we understand that the migration scripts are going to be completed successfully. Without an automated database schema migration device, it'd be not possible to deploy the QA or production servers robotically.

All in all, you should never run migration scripts manually. Manual actions are liable to human mistakes, so it’s better to have a tool that runs the migration scripts mechanically when upgrading a given gadget.

Hibernate
Database
Flyway Database
Java
4 VIEWS 0 LIKES 0 DISLIKES SHARE
0 LIKES 0 DISLIKES 4 VIEWS SHARE
Was this blog helpful?
techiio-price-plantechiio-price-plantechiio-price-plantechiio-price-plantechiio-price-plan
You must be Logged in to comment
Code Block
Techiio-author
Sagar Rabidas
Software Developer
Techiio-followerTechiio-followerTechiio-followerTechiio-followerTechiio-follower
+8 more
300 Blog Posts
14 Discussion Threads
Trending Technologies
15
Software40
DevOps46
Frontend Development24
Backend Development20
Server Administration17
Linux Administration26
Data Center24
Sentry24
Terraform23
Ansible83
Docker70
Penetration Testing16
Kubernetes21
NGINX20
JenkinsX17
Techiio-logo

Techiio is on the journey to build an ocean of technical knowledge, scouring the emerging stars in process and proffering them to the corporate world.

Follow us on:

Subscribe to get latest updates

You can unsubscribe anytime from getting updates from us
Developed and maintained by Wikiance
Developed and maintained by Wikiance