Tech-Today
Slack Discussion
Repositories
I’ve known and experience 2 tools that we can leverage to help us in evolving our database. Namely Flyway and Liquibase. But personally, I prefer Liquibase, the reason is that Liquibase is more portable vs Flyway when dealing with multiple database types. Flyway is much more effective when you are locked down on a single database as you can issue native database-specific SQL statements.
Before we proceed you must download and install the PostgreSQL database first.
[open pom.xml]
Add PostgreSQL and liquibase-core dependencies while commenting on the h2 database.
[open application.properties]
Set the data source connection properties in application.properties file.
Why Postgres? Because h2 is an in-memory database that wipes out the schema and data after each restart. Therefore, we will not be able to demonstrate how Liquibase handles the database versioning and updates.
[open pom.xml again]
How do we run Liquibase to create our database schema?
Before that, we need to define a plugin under the pluginManagement in pom.xml file.
[highlight liquibase-maven-plugin]
It lets us control Liquibase using maven. We intentionally use variables in this plugin so that we can change the connection depending on the profile. For example, we can have development, staging, and production.
[scroll down to profiles]
Which we can see here. Notice, that we have a different value for property liquibase.changeLogFile in our profiles. That is because we normally execute 2 database operation using Liquibase and that is to rebuild or update the database. In the development profile we are in update mode, In rebuild profile, we are rebuilding our database. Rebuild means it will delete all the tables in our database so take note of that.
To run Liquibase we need to define a maven build. In Eclipse it can be done via Run / Run Configurations menu select Maven Build and create a new profile. Set:
For more Liquibase maven commands check:
https://www.liquibase.org/documentation/maven/index.html
How does the change logs look like?
[open db.changelog-rebuild.xml]
It includes files which contain both the actual rebuild and data changesets.
[open rebuild and current schema and data SQL files]
These are how they were defined.
Run Liquibase Rebuild.
Open PGAdmin and execute the following SQL: select * from cat_category.
Now, what if we have some changes in our schema? For example, we want to add a new category.
[open current data.xml]
Then we need to update data.xml in the current folder. As the name implies schema.xml contains the schema definition statements while data.xml contains the insert and update of data statements.
Run Liquibase Update.
And then execute the same SQL: select * from cat_category. Now we have 1 more category as expected.
This is how Liquibase works with this we can version our database. Normally we update the current changesets for version x.
[open current changesets]
And then when we tag version x we move all the changesets to rebuild and empty the current.
[open rebuild changesets]
- Database Initialization Using Script
Slack Discussion Repositories https://github.com/terawarehouse/terawarehouse-cataloghttps://github.com/terawarehouse/terawarehouse-reactThere are times when we want to use a SQL script to generate the schema and populate the database. Personally, I use...
- Database Initialization Using Code
Slack Discussion Repositories https://github.com/terawarehouse/terawarehouse-cataloghttps://github.com/terawarehouse/terawarehouse-reactThere are various ways in which we can initialize the database. Mostly for testing purposes I find setting hibernate.ddl-auto...
- Database Modelling And Tools
Slack Discussion Repositories https://github.com/terawarehouse/terawarehouse-cataloghttps://github.com/terawarehouse/terawarehouse-reactIn this part, we will discuss the tools that we can use in designing our database. We will also create a database schema...
- Catalog And Sellout Management System Using Spring
A catalog and sales order management system is commonly used by companies to manage their catalog and capture their sales data into a centralized database so that a real-time accurate report can be generated instantly. This application can trigger notification...
- Mssql Generate Script To Create A Database Schema Only
I want to duplicate my database schema, but I do not want to copy the data contents. There are 3rd party tools which I can use to achieve the same result. But I prefer to use the Microsoft SQL Server Management Studio. To do so: 1.) Open Microsoft SQL...
Tech-Today
Database Initialization Using Liquibase
Slack Discussion
Repositories
- https://github.com/terawarehouse/terawarehouse-catalog
- https://github.com/terawarehouse/terawarehouse-react
I’ve known and experience 2 tools that we can leverage to help us in evolving our database. Namely Flyway and Liquibase. But personally, I prefer Liquibase, the reason is that Liquibase is more portable vs Flyway when dealing with multiple database types. Flyway is much more effective when you are locked down on a single database as you can issue native database-specific SQL statements.
Before we proceed you must download and install the PostgreSQL database first.
[open pom.xml]
Add PostgreSQL and liquibase-core dependencies while commenting on the h2 database.
[open application.properties]
Set the data source connection properties in application.properties file.
Why Postgres? Because h2 is an in-memory database that wipes out the schema and data after each restart. Therefore, we will not be able to demonstrate how Liquibase handles the database versioning and updates.
[open pom.xml again]
How do we run Liquibase to create our database schema?
Before that, we need to define a plugin under the pluginManagement in pom.xml file.
[highlight liquibase-maven-plugin]
It lets us control Liquibase using maven. We intentionally use variables in this plugin so that we can change the connection depending on the profile. For example, we can have development, staging, and production.
[scroll down to profiles]
Which we can see here. Notice, that we have a different value for property liquibase.changeLogFile in our profiles. That is because we normally execute 2 database operation using Liquibase and that is to rebuild or update the database. In the development profile we are in update mode, In rebuild profile, we are rebuilding our database. Rebuild means it will delete all the tables in our database so take note of that.
To run Liquibase we need to define a maven build. In Eclipse it can be done via Run / Run Configurations menu select Maven Build and create a new profile. Set:
- Update Mode
- Goal=liquibase:update
- Profiles=development
- Rebuild Mode
- Goals=liquibase:dropAll liquibase:update
- Profiles=development, rebuild
For more Liquibase maven commands check:
https://www.liquibase.org/documentation/maven/index.html
How does the change logs look like?
[open db.changelog-rebuild.xml]
It includes files which contain both the actual rebuild and data changesets.
[open rebuild and current schema and data SQL files]
These are how they were defined.
Run Liquibase Rebuild.
Open PGAdmin and execute the following SQL: select * from cat_category.
Now, what if we have some changes in our schema? For example, we want to add a new category.
[open current data.xml]
Then we need to update data.xml in the current folder. As the name implies schema.xml contains the schema definition statements while data.xml contains the insert and update of data statements.
Run Liquibase Update.
And then execute the same SQL: select * from cat_category. Now we have 1 more category as expected.
This is how Liquibase works with this we can version our database. Normally we update the current changesets for version x.
[open current changesets]
And then when we tag version x we move all the changesets to rebuild and empty the current.
[open rebuild changesets]
- Database Initialization Using Script
Slack Discussion Repositories https://github.com/terawarehouse/terawarehouse-cataloghttps://github.com/terawarehouse/terawarehouse-reactThere are times when we want to use a SQL script to generate the schema and populate the database. Personally, I use...
- Database Initialization Using Code
Slack Discussion Repositories https://github.com/terawarehouse/terawarehouse-cataloghttps://github.com/terawarehouse/terawarehouse-reactThere are various ways in which we can initialize the database. Mostly for testing purposes I find setting hibernate.ddl-auto...
- Database Modelling And Tools
Slack Discussion Repositories https://github.com/terawarehouse/terawarehouse-cataloghttps://github.com/terawarehouse/terawarehouse-reactIn this part, we will discuss the tools that we can use in designing our database. We will also create a database schema...
- Catalog And Sellout Management System Using Spring
A catalog and sales order management system is commonly used by companies to manage their catalog and capture their sales data into a centralized database so that a real-time accurate report can be generated instantly. This application can trigger notification...
- Mssql Generate Script To Create A Database Schema Only
I want to duplicate my database schema, but I do not want to copy the data contents. There are 3rd party tools which I can use to achieve the same result. But I prefer to use the Microsoft SQL Server Management Studio. To do so: 1.) Open Microsoft SQL...