Setting up and writing database migrations
Today I will tell you how to connect and configure Liquibase in a Spring Boot application, generate scripts for initialization and migration of the database schema, and also supplement existing changelog files with new migration scripts along with Amplicode!
This guide is also published in video format on YouTube:
Introduction
During the development of an application, we have to make changes to the JPA model from time to time, which entails the evolution of the database schema.
Without using a version control system, there is a risk of data loss and difficulties when several developers work together on a project. Today, the most common version control system is git, which is great for tracking changes in all project files.
However, in addition to the code, it is equally important to version the database to avoid data loss during changes and to be able to roll back to previous versions of the database schema in the event of an unsuccessful update or an error during development.
For efficient database version management, there are specialized solutions such as Liquibase.
This guide is about using Liquibase in Spring Boot applications with Amplicode for the most convenient and efficient database management.
To complete the guide yourself in IntelliJ IDEA, you can go to repository with guides and follow the steps described in instructionsto set up the project.
List of tasks covered in this guide
Connecting and configuring Liquibase.
Extending the JPA model and writing database migration scripts.
Validation of JPA model and database.
Application Review
Before you begin solving the tasks, you need to study the structure of the application you are using. BlogApplicaion using the Amplicode Explorer panel:
To analyze the application in the context of the frameworks and libraries used using the Amplicode Explorer panel, click on the arrow icon next to the project name to expand it. Here we can find out which modules are connected to our project, what the data layer is, what endpoints are available, and what files for deploying applications are already in the project.
Based on Amplicode's knowledge of our project, we can draw the following conclusions:
For convenient work with the persistent layer, the application uses the Spring Data JPA module (1).
PostgreSQL (2) is used as the DBMS.
We can take a closer look at our application's data model:
MappedSuperclass BaseEntity (3.1) is used as the base for all entities.
The Post and User entities are related by a many-to-one relationship (3.2).
Finally, we can look at the docker-compose.yaml file and its elements (4).
Connecting and configuring Liquibase to the application
The first task is to connect and configure Liquibase to a Spring Boot application with a previously developed JPA model. In the process of solving this task, in addition to adding the necessary dependency and configuring the file application.propertieswe will also need to create a database initialization script.
It would be convenient to also have the pgAdmin service to interact with PostgreSQL. Amplicode suggests pgAdmin in the list of recommended services, as it sees the PostgreSQL service in the current file. To use this feature, click on the light bulb icon and select Add pgAdmin service in the menu that appears:
A pop-up dialog will open. Add pgAdmin to Docker ComposeTo use the feature provided by Amplicode to set up automatic PostgreSQL connection to pgAdmin:
Check the checkbox Configure DB server connections.
Leave all other settings at their default values.
Click OK.
Amplicode will generate the following code:
pgadmin:
image: dpage/pgadmin4:8.6
restart: always
ports:
- "5050:80"
volumes:
- pgadmin_data:/var/lib/pgadmin
- ./docker/pgadmin/servers.json:/pgadmin4/servers.json
- ./docker/pgadmin/pgpass:/pgadmin4/pgpass
environment:
PGADMIN_DEFAULT_EMAIL: admin@admin.com
PGADMIN_DEFAULT_PASSWORD: root
PGADMIN_CONFIG_SERVER_MODE: "False"
PGADMIN_CONFIG_MASTER_PASSWORD_REQUIRED: "False"
healthcheck:
test: wget --no-verbose --tries=1 --spider http://localhost:80/misc/ping || exit -1
interval: 10s
timeout: 5s
start_period: 10s
retries: 5
entrypoint: /bin/sh -c "chmod 600 /pgadmin4/pgpass; /entrypoint.sh;"
volumes:
pgadmin_data:Now you need to run all the services to see the current state of the database. To do this, you need to click on the double arrow icon opposite the word services in file docker-compose.yaml
Once launched, Amplicode will add an inlay for easy opening of the service-related URL directly from IntelliJ IDEA.
At this point, the database is in the same state as the JPA model. Let's move on to connecting Liquibase to our application.
Amplicode Explorer allows you to add the necessary starters and libraries to your project. To do this, you need to:
Right click on the node Configuration.
Select option Add Configuration.
In the window that opens, select DB Migration Configuration.
A dialog box will open DB Migration Settings.
Next you need to do the following:
In the pop-up dialog box that opens, select Liquibase.
Leave the name of the main changelog file, its format and the directory where it will be created as default.
Check the checkboxes Create init DB scripts And Run changelog-sync:.
Selecting the first checkbox will automatically redirect you to the database script generation window. Selecting the second checkbox will cause Amplicode to execute the Liquibase command. changelog-sync immediately after generating the database initialization scripts. This command will allow you to mark the scripts as executed, without actually executing them, since everything that will be described in them is already in the database.
We will select a database as a data source for generating the initialization script. To analyze its structure, Amplicode will need a connection to the database.
Select the option to create a new connection.
It is worth noting here that Amplicode allows you to create a database connection from scratch or based on the information specified for the data source in the application. Here you should choose the second option, since the data source in the application is already configured.
The following dialog box will appear:
Click Create DB Connection. Now you will see the following window:
Click Test Connectionto check the connection. After that, click OK.
Click OK also in the main dialog box.
Amplicode will add the necessary dependencies to
build.gradleand properties inapplication.propertieswill generate a filedb.changelog-master.xmland will begin the process of generating the database initialization script.
In the window that opens Change Preview We can change the location, type and other parameters of the file, as well as ensure the correctness of the entire file and its individual changesets.
Click OK.
Based on the results of execution, Amplicode will save the changelog to the specified path and execute the command changelog-syncbecause the corresponding checkbox was previously checked.
As a result of the actions performed, the command will be successfully executed, the Liquibase system tables will be added to the database, and the table databasechangelog will receive information about executed scripts. The first task is now complete.
Modifying the JPA model
Let's start modifying the current JPA model. Since fax usage has become rare, we should remove it from the model by deleting the following (highlighted) code and the associated getter and setter:
To ensure completeness of the data, we will make an attribute email mandatory using Amplicode Designer.
We also need to add a new entity – Comment and establish a many-to-many relationship with the entity Post.
To do both of these actions using Amplicode Designer:
Go to entity
Postand open the association creation window by double-clicking on the menu item Attributes -> Association in the palette:
Create a new entity by clicking the plus button, give it a name and select a parent class:
Change the association cardinality to many-to-many (1). Amplicode has already chosen the most optimal type for a many-to-many association —
Setbut for completely correct operationSetwith JPA entities it is also necessary that its methods are correctly overriddenequals()AndhashCode(). Amplicode is aware of this and warns that the current implementation may not be optimal in terms of performance and will offer to generate an implementation of the methodsequals()AndhashCode()(2) To agree with this proposal, click on the appropriate link (3).
Click OK to create the entity.
Comment.
Amplicode will generate the following code for the entity:
@Entity
@Table(name = "comment")
public class Comment extends BaseEntity {
@Override
public final boolean equals(Object o) {
if (this == o) return true;
if (o == null) return false;
Class<?> oEffectiveClass = o instanceof HibernateProxy
? ((HibernateProxy) o).getHibernateLazyInitializer()
.getPersistentClass()
: o.getClass();
Class<?> thisEffectiveClass = this instanceof HibernateProxy
? ((HibernateProxy) this).getHibernateLazyInitializer()
.getPersistentClass()
: this.getClass();
if (thisEffectiveClass != oEffectiveClass) return false;
Comment comment = (Comment) o;
return getId() != null && Objects.equals(getId(), comment.getId());
}
@Override
public final int hashCode() {
return this instanceof HibernateProxy
? ((HibernateProxy) this).getHibernateLazyInitializer()
.getPersistentClass()
.hashCode()
: getClass().hashCode();
}
}Now we need to generate for the entity Comment new base attribute textand also create a many-to-one relationship with the entity User. For this:
Place your cursor on the class name
Commentin the corresponding file.Call a pop-up window Generateby clicking Alt+Insert for Windows/Linux or ⌘+N for macOS.
Select Entity Attribute.
Enter type String and press Enter.
Enter the attribute name text:
Click OK.
Amplicode will generate the attribute code, as well as a getter and setter for it:
@Column(name = "text")
private String text;
public String getText() {
return text;
}
public void setText(String text) {
this.text = text;
}Call the window again Generate and select Entity Attribute.
In the attribute type box, enter
User:
Specify the attribute name (
author) and make it mandatory:
Click OK.
Amplicode will generate the attribute code, as well as a getter and setter for it:
@ManyToOne(fetch = FetchType.LAZY, optional = false)
@JoinColumn(name = "author_id", nullable = false)
private User author;
public User getAuthor() {
return author;
}
public void setAuthor(User author) {
this.author = author;
}The task of modifying the JPA model is completed. The next step is generating Liquibase migration scripts.
Generate Liquibase migration scripts to synchronize JPA model and DB schema
To create a Liquibase migration script, you need to go to the Amplicode Explorer panel and select the item in the DB Versioning section Liquibase Diff Changelog.
In the window that opens, you should make sure that the persistence unit and connection to the database are selected correctly.
We have already become familiar with the changelog file preview window during the process of creating the database initialization script.
It is worth noting that in this case the color scheme of some changesets is different, for example, the script for deleting a column fax marked in red, and the script for adding NotNull restrictions – yellow.
Both scripts are potentially dangerous due to possible data loss, so Amplicode tries to draw maximum attention to them from the developer.
By developing the JPA model, we solved two business problems: changing the entity User and creation of an entity Comment. We would not like to put scripts related to different business tasks in one file. Amplicode allows you to distribute changeset scripts into several changelog files without leaving the current window.
To do this you need:
Select the scripts related to the user change (1) and in the top panel select the action of transferring the scripts to a new changelog file (2):
Set a name for each changelog file:
For the modification file with the users table scripts, change the path to the file (1), set the name (2) and specify the main changelog (3), in which the current file will need to be included.
Also note that for the add script NotNull limitations Amplicode wants to suggest some improvements and signals us about it with a light bulb icon. Since we are adding NotNull constraint, we must be sure that the table does not already contain null values. Amplicode allows us to enhance an existing script by specifying a value that will be set for all null values before adding the constraint.
Before saving changelog files, we can once again make sure that they are correct in the preview window.
By the way, there is one more script that we missed. This is the script for deleting an index from a table Usersin the section Ignored.
Amplicode automatically placed this script in this section because creating an index can be quite an expensive operation, and adding it at the JPA model level is not very common. Therefore, in real life, it is extremely rare to remove it from the database.
But if necessary, you can always remove a script from the list of ignored scripts, or, on the contrary, expand the list of ignored scripts by selecting and dragging unnecessary scripts into the section Ignored directly in the preview window or by configuring such a list in advance in the settings.
Both changelog files are successfully generated and included in our main file. db.changelog-master.xml.
All that remains is to execute the scripts and roll the changes into the database. Of course, the most correct approach to using migration scripts would be to configure the Liquibase plugin for Maven or Gradle, since it will be needed in any case during the CI/CD pipeline setup. But in order not to be distracted by this task at this stage, within the framework of this guide you can use the action Liquibase Update in Amplicode and roll out scripts without preliminary plugin configuration, postponing this task for later.
When you select this item from the menu, the following pop-up window will appear. Click the button in our Update.
All scripts were executed successfully, and information about their successful execution was written to the table. databasechangelog.
Adding new migration scripts to an existing file
While modifying the JPA model, we forgot to make an attribute text for the essence Comment compulsory.
It's quite easy to correct this oversight.
To do this, use the Amplicode Designer panel and mark the attribute as required:
However, it is not advisable to clutter the project with multiple Liquibase changelog files. In addition, this script refers specifically to the changelog file containing scripts related to table creation Comments. Due to the fact that Liquibase executes and records the execution of changesets that make up a changelog file, rather than the execution of the entire changelog file, we can supplement existing changelog files with new changeset scripts. Amplicode is well aware of this possibility and allows you to generate the necessary scripts into an existing file.
For this:
Open the changelog file we need and go to the menu Generate from IntelliJ IDEA and select the item Liquibase Diff Changes from Amplicode.
In the pop-up window that opens, click OK.
The new changeset will be added to the existing changelog file.
<changeSet id="1718798931904-1" author="georgii (generated)">
<addNotNullConstraint columnDataType="VARCHAR(255)" columnName="text"
tableName="comment" validate="true"/>
</changeSet> Running a Spring Boot Application (Amplicode Log Analysis)
A good practice when developing a Spring Boot application and using a database versioning system is to use the JPA model and database schema validation capabilities with Hibernate. By using the Hibernate property spring.jpa.hibernate.ddl-auto with meaning validate we can ensure that the JPA model and database schema are consistent.
Add the following code to the file application-properties:
spring.jpa.hibernate.ddl-auto=validateTo do this quickly, start typing ddl-auto and Amplicode will offer you the corresponding property. It will be enough to select it from the drop-down list and click Enter.
If a mismatch is detected, Spring Boot will fail to start the application and return an error.
Run the application to make sure that there are indeed no exceptions in the logs.
If the application runs without problems, the log should look something like this:
It is also important to note that if there was a mismatch between the JPA model and the database schema, Amplicode would report the problem and provide an opportunity to resolve it directly from the stacktrace.
Conclusion
To sum it up, all the tasks set in this guide were successfully completed in about 20 minutes.
You have learned:
Connect and configure Liquibase for a Spring Boot application.
Extend the JPA model and write database migration scripts.
Validate that the JPA model matches the database schema.
Subscribe to our Telegram And YouTubeso as not to miss new materials about AmplicodeSpring and related technologies!
And if you want to try Amplicode in action, you can install it absolutely free right now, as in IntelliJ IDEA/GigaIDEand in VS Code.
