Liquibase A-Z Reference (Almost), JPA Hibernate DDL Options & Using Terminal To Create Database(Part 2 of Spring Boot Recipe App)
Let’s learn how to create database changelog automagically and also learn about Hibernate hbm2ddl.auto-configuration
It has been a long break since I wrote the first tutorial. Long story short, I got a new job, I’m working with an incredibly good team in METU Technopolis, METU is like a home for me LoL. Thus I quickly adapted to the ‘new’ environment and I am about to finish my first big commit, backing up database periodically with Spring Quartz and letting users decide backup interval, backup start date, etc.
Let’s jump into the tutorial. What’s for learning today? Guys & Ladies, this tutorial will mainly be about amazing technology Liquibase and Hibernate hbm2ddl.auto-configurations. Before getting hands dirty, let us see the big picture step by step
- Add Required Dependencies with Maven
- Migrating from h2 to Postgres
- Create user and database in Postgres using Linux terminal
- Use Hibernate to create database tables automagically
- Generate changelog from existing database
- How Liquibase works?
- SQL queries with Liquibase in YAML format
- Constraints in Liquibase
1. ADDING DEPENDENCIES FOR POSTGRES AND LIQUIBASE
I created a branch on GitHub for this so that my dear readers can follow the tutorial better.
h2 removedliquibase core addedpostgresql addedspring boot starter jdbc added
I guess JDBC is required for liquibase. After that dependency management, I added the Liquibase Maven plugin to be able to produce a changelog automagically!
2. MIGRATING FROM H2 TO POSTGRESQL
Before migrating our database to Postgres, I created a new user to configure data source properties using the following commands in the terminal. (You must have installed PostgreSQL on your system to be able to execute the commands)
linux commands aresudo su postgres
CREATE USER recipe_tutorial WITH PASSWORD 'recipe_tutorial';
ALTER USER recipe_tutorial WITH SUPERUSER;
The above properties are mostly about data source properties. Before moving onto, I want to mention about jpa.hibernate.ddl options
spring.jpa.generate-ddl(boolean: true or false) switch the feature on and off
spring.jpa.hibernate.ddl-auto(enum: create, create-drop, validate, update) is a Hibernate feature that controls the behavior. See below for more detail.
create | update | validate | create-drop
Following is the explanation of the behaviour shown by these value:
create :- create the schema, the data previously present (if there) in the schema is lost
update:- update the schema with the given values.
validate:- validate the schema. It makes no change in the DB.
create-drop:- create the schema with destroying the data previously present(if there). It also drop the database schema when the SessionFactory is closed.
Following are the important points worth noting:
In case of update, if schema is not present in the DB then the schema is created.
In case of validate, if schema does not exists in DB, it is not created. Instead, it will throw an error:-
Table not found:<table name>
In case of create-drop, schema is not dropped on closing the session. It drops only on closing the SessionFactory.
In case if i give any value to this property(say abc, instead of above four values discussed above) or it is just left blank. It shows following behaviour:
-If schema is not present in the DB:- It creates the schema
-If schema is present in the DB:- update the schema.
Taken from StackOverflow
3. WHAT IS LIQUIBASE AND WHY DO WE NEED IT?
Liquibase is an open-source database-independent library for tracking, managing and applying database schema changes.
Does the definition sound familiar? I mean, it should sound familiar because we have version control systems like git, svn but on those, we generally manage different versions of source code.
Now I hear some of you started to say “I wish there was a way to manage different database per version, per commit’’ so that anyone in my team gets the database update when they get my commits or vice versa.
GOOD NEWS YOUR DREAMS COME TRUE! LIQUIBASE IS THERE FOR YOU!
I briefly explained what is Liquibase and why do we need it.
What we have done until now, can be found in this branch:
Until now, we managed the dependencies and migrate from h2 to postgres. I also run the app, it failed but I got what I want: JPA created database tables for me. I need the tables to show you how to generate a changelog from the existing database.
4. INTEGRATING LIQUIBASE TO SPRING BOOT
I want to list further steps (past steps are excluded) to integrate Liquibase to our Spring Boot App
1. change enable liquibase to true(application.properties file) and add default schema name for liquibase
2. create liquibase properties file under resources
go to the source directory and run: mvn liquibase:generateChangeLog
- make changes on application.properties (do not change other configurations)
2. this file will automatically be detected by spring boot
3. using maven liquibase plugin
sumutella@sumutella-ThinkPad-T540p:~/IdeaProjects/spring-boot-recipe-tutorial$ mvn liquibase:generateChangeLog
Congratulations, you have generated a changelog file which consists of your current database.
Now let’s deep into Liquibase.
I will cover three parts; executing SQL scripts, adding primary key constraint, adding new column to existing database.
4.1 Executing SQL Scripts in ChangeLog file
Before moving on, please only write changes which doesn’t contain multiple transactions, each change must correspond to a unit of transaction
Of course, you can execute them in your database but if you execute them using Liquibase changeset, your teammates will also get the same insert/update, etc. This will enable the team the opportunity to work more coherently
sql: INSERT INTO category (description) VALUES ('American');
INSERT INTO category (description) VALUES ('Italian');
INSERT INTO category (description) VALUES ('Mexican');
INSERT INTO category (description) VALUES ('Fast Food');
INSERT INTO unit_of_measure (description) VALUES ('Teaspoon');
INSERT INTO unit_of_measure (description) VALUES ('Tablespoon');
INSERT INTO unit_of_measure (description) VALUES ('Cup');
INSERT INTO unit_of_measure (description) VALUES ('Pinch');
INSERT INTO unit_of_measure (description) VALUES ('Ounce');
INSERT INTO unit_of_measure (description) VALUES ('Each');
INSERT INTO unit_of_measure (description) VALUES ('Dash');
Later on, I will explain how Liquibase works, don’t worry!
4.2 Primary Key Constraint Using Liquibase
Suppose your team has the same table in their databases and then it is needed to add another primary key to the table. Here is how it could be performed:
columnNames: id, name
forIndexName: A String
tablespace: A String
4.3 Adding New Column To Existing Table
Title is pretty self-explanatory so no need to be verbose, here is the code:
5. How Liquibase Works?
In each changeset, we define an id and author. Liquibase keeps these on a table named “database changelog”. Additionally, it keeps whether changeset executed successfully, and it keeps the content of changeset and much more. Here is that table:
When using Liquibase don’t change the content of an executed changeset. Either update it or delete it (delete corresponding data from Database and delete changeset).
Thank you for sparing your precious time. I’m very well aware of the idea that there could be improvements in my post hence do not hesitate to share your valuable opinions on comments.
Till next time folks, have a good day!