Database Migration in production using Flyway

Introduction#

Database migration is the process of transitioning your database from one state to another. It involves updating the schema or structure of your database as your application evolves. This is crucial in production environments where maintaining data integrity and consistency is paramount.

As applications grow, version control becomes essential to manage changes in the database schema effectively. Flyway is a powerful tool that simplifies database migrations, ensuring that your database remains in sync with your application's codebase.

What is Flyway?#

Flyway is an open-source database migration tool that enables you to version your database schema and apply changes systematically. It supports multiple databases and integrates seamlessly with Spring Boot, allowing for automatic migrations during application startup.

Why use Flyway:#

It is not good to use spring.jpa.hibernate.ddl-auto=update in your production environment

• Because :
  1. Unpredictable Schema Changes: Renaming a column in an entity class may result in the creation of a new column in the database rather than renaming the existing one, leading to potential data loss or inconsistency.
  2. Data Loss Risk: Removing an entity might cause Hibernate to drop the column or table, which could remove important production data.
  3. Lack of Control and Irreversible.
• Flyway offers structured database schema management and version control.
• It automates migration script execution and maintains a version history for seamless migrations across environments.
• Integration with Spring Boot streamlines deployment, allowing developers to focus on feature development rather than migration management.

Key Features of Flyway:#

• Versioning: Track changes to your database schema through versioned migration scripts.
• Repeatable Migrations: Apply migrations repeatedly without duplicating data.
• Easy Integration: Works effortlessly with Spring Boot applications.

Getting started#

1. Adding spring.jpa.hibernate.ddl-auto=validate#

In application-prod.properties file we will add spring.jpa.hibernate.ddl-auto=validate, this will validate our schema against the schema present in prod environment always when we run our spring boot application. So if we change any schema and run our application this will throw error.

2. Adding Fly way dependencies in POM.XML file#

Open pom.xml file and add this two dependencies.

<dependency>
	<groupId>org.flywaydb</groupId>
	<artifactId>flyway-core</artifactId>
</dependency>
<dependency>
	<groupId>org.flywaydb</groupId>
	<artifactId>flyway-database-postgresql</artifactId>
</dependency>

Now the flyway will use migration files, basically we need to write the migration scripts every time if we make changes in our schema. This scripts follow naming convention as V1__actions.sql we will understand this with example as we move further

But before moving further when an organization starts using flyway there are two possiblities:

1. They already have schema present in production environment before using the flyway and now they want to use flyway. Then they have to add spring.flyway.baseline-on-migrate=true in the application-prod.properties file.
2. They are just getting started with flyway and yet to deploy prod database schemas on prod environment. Then no need to add any config related to flyway and start writing the migration scripts.

3. Writing migration scripts.#

Lets say If you are running your spring boot employee-service application first time with ddl-auto=validate in application propertie file with employee entity,

@Getter
@Setter
@Entity
@Builder
@AllArgsConstructor
@NoArgsConstructor
public class Employee {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(unique = true)
    private String email;

    private String fullName;

    private Long salary;
}

then this will throw error on running stating that missing table employee as we have not writen the migration script.

So we will write the migration scripts, this scripts you have to write under db.migration folder inside resource folder.

1. File name : V1__initialize_db.sql

CREATE TABLE employee (
  id BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
   email VARCHAR(255),
   full_name VARCHAR(255),
   salary BIGINT,
   CONSTRAINT pk_employee PRIMARY KEY (id)
);

ALTER TABLE employee ADD CONSTRAINT uc_employee_email UNIQUE (email);

After adding the file run the application and you will see that our migration for employee entity is validated successfully.

Look into database you will see that employee entity is created and also flyway schema history has been stored for our script.

Now if you want to change, delete or update the fields in the employee entity then for that changes you have to write a new migration file and you are also not allowed to modify the previous migration if you do so and try then it will throw an checksum error as it creates cheksum also on whatever you have written in migration file you can see the checksum field also in the above image.

3, Now lets create new entity department entity and associate it with employee entity.#

@Entity
public class Department {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String title;
}

//Add mapping with employee entity

@Getter
@Setter
@Entity
@Builder
@AllArgsConstructor
@NoArgsConstructor
public class Employee {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(unique = true)
    private String email;

    private String fullName;

    private Long salary;

    @ManyToOne
    private Department department;
}

Now after adding the department entity you have to create the migration script for this also.

File name : V2__adding_department.sql

CREATE TABLE department (
  id BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
   title VARCHAR(255),
   CONSTRAINT pk_department PRIMARY KEY (id)
);

ALTER TABLE employee ADD department_id BIGINT;

ALTER TABLE employee ADD CONSTRAINT FK_EMPLOYEE_ON_DEPARTMENT FOREIGN KEY (department_id) REFERENCES department (id);

After adding the migration script for department run your application.

You will see that our both previous migration scripts are validated.

Deparment table is successfully created in database also the version is added.

So this how the data migration works!

In this article, we explored the importance of database migration and the advantages of using Flyway as a migration tool in your Spring Boot applications. We covered how to set up Flyway, create migration scripts, and manage schema changes effectively while ensuring data integrity and consistency. By adopting Flyway, developers can streamline the migration process, maintain version control over their database schemas, and focus more on feature development without worrying about migration management. Embracing Flyway will lead to smoother deployments and a more robust application lifecycle.