How to Reliable Database Migrations with Liquibase and Spring Boot

In this blog, we look at database migrations with the very popular Liquibase database migration library and how you can use it in the context of a Spring Boot application.

Setting Up Liquibase in Spring Boot

By using default Spring Boot auto-configures Liquibase while we upload the Liquibase dependency to our construct document.
Spring Boot makes use of the primary DataSource to run Liquibase (i.e. the only annotated with @primary if there is a couple of). In case we need to apply a special DataSource we can mark that bean as @LiquibaseDataSource.
rather, we are able to set the spring. liquibase.[URL, user, password]properties, in order that spring creates a Datasource on its personal and uses it to automobile-configure Liquibase.

Through default, Spring Boot runs Liquibase database migrations mechanically on application startup.

It looks for a grasp changelog report in the folder DB/migration within the classpath with the name DB.changelog-master. YAML. If we need to use other Liquibase changelog codecs or use distinctive report naming conventions, we are able to configure the spring. liquibase. exchange-log application property to point to an exclusive grasp changelog record.

for example, to use db/migration/db.change-log.json because of the master changelog document, we set the following assets in application.yml:

liquibase.change-log=classpath:db/migration/db.changelog-master.xml

The master changelog can consist of different changelogs so that we can break up our modifications into logical steps.

Running Our First Database Migration

After setting the whole lot up, permit’s create our first database migration. We’ll create the database desk user_details in this example.

Let’s initiate a file with the term db.changelog-master.yaml and place it in src/main/resources/db/changelog:

<databaseChangeLog
   
   <include file="db/changelog/db.changelog-1.0.0.xml"/> 
                  
 </databaseChangeLog>

The master file is simply a cluster of includes that implies changelogs with the authentic changes.

Next, we build the changelog with the first real changeset and placed it into the file src/main/resources/db/changelog-yaml-example.yaml:

<databaseChangeLog>

<changeSet author="Krishna (generated)" id="1503460396396-1">

		<createTable tableName="employee_table">

			<column autoIncrement="true" name="employee_id" type="INT">
				<constraints primaryKey="true" />

			</column>

			<column name="email" type="VARCHAR(255)" />

			<column name="employee_name" type="VARCHAR(255)" />

			<column name="salary" type="DOUBLE" />

		</createTable>

	</changeSet>
	
	<changeSet author="Kishank (generated)" id="1503460396396-2">

		<createIndex indexName="EMAIL_INDEX" 

tableName="employee_table">

			<column name="email" />

		</createIndex>

	</changeSet>
	
	<changeSet id="1203460396356-3" author="Krishna (generated)">

		<insert tableName="employee_table">

			<column name="employee_name" value="Deepak"></column>

			<column name="email" value="rock.deep@yahoo.com"></column>
			<column name="salary" valueNumeric="85000.00"></column>
		</insert>

		<insert tableName="employee_table">

			<column name="employee_name" value="Manish"></column>

			<column name="email" value="manish.s@yahoo.com"></column>
			<column name="salary" valueNumeric="90000.00">

</column>
		</insert>

	</changeSet>

</databaseChangeLog>

Using Liquibase Context

As defined earlier, context can be used to control which exchange units have to run. let’s use this to add take a look at the information in the check and local environments:

<databaseChangeLog>

 <changeSet 

   author="Krishna Jaiswal" 

   id="1503460396396-1" 

   context="test or local">

   <loadUpdateData

     encoding="UTF-8"

     file="db/data/employee.csv"

     onlyUpdate="false"

     primaryKey="id"

     quotchar="'"

     separator=","

     tableName="emp_details">

   </loadUpdateData>

 </changeSet>

</databaseChangeLog>

We’re using the expression test or local so it runs for these contexts, but not in production.

So now we need to pass the context to Liquibase using the property spring.liquibase.contexts:

spring.profiles: docker

liquibase.parameters.textColumnType: TEXT

 contexts: test

Creating migration file

To create a new migration, you could run the make: migration Artisan command, and on the way bootstrap a new class to your Laravel utility, in the database/migrations folder.

 create table employee_table (

        employee_id integer not null auto_increment,

        email varchar(255),

        employee_name varchar(255),

        salary double precision,

        primary key (employee_id)

  );

Enabling Logging for Liquibase in Spring Boot

Allowing info degree logging for Liquibase will assist to peer the trade sets that Liquibase executes for the duration of the beginning of the application. It additionally enables us to identify that the utility has no longer commenced yet due to the fact it’s miles waiting to collect a changelog lock at some stage in the startup.
upload the subsequent application belongings in application.yml to allow info logs:

logging:

  level:

    "liquibase" : info

Conclusion

Liquibase enables automating database migrations, and Spring Boot makes it less complicated to apply Liquibase. This manual furnished info on a way to use Liquibase in the Spring Boot software and a few best practices.

Reference

https://blog.nimbleways.com/db-migrations-with-liquibase-and-spring-boot/

You can find the example code on GitHub.

Original article source at: https://blog.knoldus.com/

#springboot #liquibase #database 

How to Evolving Database using Spring Boot and Liquibase

Introduction

In this Blog, we will see an example of evolving database using Spring Boot and Liquibase with YAML and SQL configuration. Here we will learn how to build applications using maven build tools.

Liquibase is an open-source library for tracking, managing, and deploying database changes that can be used for any database. It helps you create the schema, run them during deployment, and also helps you write automated tests so that your changes will work in production.

Create Project

We need to create maven based project.

If you are creating maven based project then add the following dependencies.

Create Changelog File

Create a YAML file called db.changelog-master.yaml under the src/main/resources/db folder. It will cover all the changelogs written in different files. The complete master changelog file content is given below:

Notice in the above YAML file, we have not specified any endDelimiter for changeSet ids insertTableAddresses and insertTableUsers because the default end delimiter is ;.

Now create below SQL file 01-create-users-and-addresses-schema.sql under src/main/resources/db/changelog/scripts to create tables in the MySQL database:

NowcreatebelowSQLfile 02-insert-data-addresses.sql under src/main/resources/db/changelog/scripts folder to insert data into the ADDRESSES table:

Now create below SQL file 02-insert-data-users.sql under src/main/resources/db/changelog/scripts to insert data into the USERS table:

Application Properties

Create file src/main/resource/application.properties to load the database changelog file during Spring Boot application startup. Here we configure the database connection in this file.

The default location of the changelog master file is classpath:/db/changelog and Liquibase searches for a file db.changelog-master.yaml. So we need to declare the location of the changelog-master file.

In the Spring Boot application the key liquibase.change-log does not work, so you need to use spring.liquibase.changeLog.

Create Main Class

Here is the main application in order to start the application and the creation and insertion of the above table into the database will be occurring during the application execution.

Testing the Application

After running the project it will have the following output:

Conclusion

You will find tables created in the database. The two rows are inserted into addresses and two rows are inserted into users’ tables. Also, there are three rows inserted into the table DATABASECHANGELOG and the row identifies all details about the executed file. We will also find one row inserted into the table DATABASECHANGELOGLOCK and this row identifies whether the current operation holds a lock on changesets or not.

Original article source at: https://blog.knoldus.com/

#springboot #liquibase #database 

The Top for Managing Liquibase ChangeLogs

In the previous Liquibase blog we saw the essential introduction to Liquibase and its advantages. Now, let’s move to the second step of the series in which we will see the “Best Practices for Managing Liquibase ChangeLogs”.

Let us start by seeing ChangeLog formats

• SQL
• XML
• JSON
• YAML

ChangeLogs are an ordered list of changes to be deployed to the database. You can also try to mix and match the different changelog formats according to your requirements. There was a poll conducted by people at liquibase on LinkedIn to see which changelog format is preferred the most –

SQL and XML became the most preferred and popular changelog formats. Regardless of what format you are using, are all structured the same way.

The basic structure that is being followed for all the formats is –

Types of ChangeLogs In Detail

There are several types of changelogs that can be used in Liquibase, including XML, JSON, YAML, and SQL. XML is the most commonly used changelog format, but you can choose the format that works best for your needs.

XML ChangeLogs – XML changelogs are written using the Liquibase XML format, which is a standardized way of defining database changes. This format allows you to define changes using a series of XML tags, such as <createTable> and <addColumn>. XML changelogs are easy to read and understand, and they are a good choice if you want to write your own changelogs by hand.

JSON ChangeLogs – JSON and YAML changelogs are similar to XML changelogs, but they use different syntaxes. YAML and JSON changelogs are written using a key-value format, which makes them easy to read and understand. JSON and YAML changelogs are good choices if you want to use a tool to automatically generate your changelogs.

SQL ChangeLogs – SQL changelogs are written using standard SQL statements. This means that you can use your existing SQL knowledge to write changelogs, and you can also use tools like MySQL Workbench to generate SQL changelogs. SQL changelogs are a good choice if you want to use your existing SQL skills to manage your database changes.

Importance of ChangeLogs

Changelogs are a crucial component of Liquibase since they give you the ability to monitor and control the alterations you make to your database structure. A changelog is a file that lists all the modifications made to your database, along with the SQL commands used to implement those modifications. As a result, it is simple to view the history of modifications made to your database and, if required, roll those changes back. Changelogs also assist in ensuring that your database is consistently in a known and consistent state, which is critical for preserving the integrity of your data.

Best Practices to Manage ChangeLogs in LiquiBase

1. Use a version control system to keep your changelogs: A version control system, such as Git, is essential. You can easily track changes to your changelogs. As a result, you can go back to prior versions as necessary.
2. Keep your changelogs’ names consistent: It’s a good idea to keep your changelogs’ names consistent. The date and time of the update, for instance, can be included in the changelog’s name. Your ability to keep track of your changelogs and ensure that they are in the correct order will be facilitated by this.
3. Organize your changelogs: Keeping your changelogs organized will make it easier for you to locate and manage them. Using folders and subdirectories, you can group your changelogs according to the project, the environment, or any other consideration that makes sense for your project.
4. Use labels and contexts: Liquibase gives you the ability to use labels and contexts to organise and manage your changelogs. To specify which changelogs should be processed in a specific environment and to organise changelogs that are linked to one another, labels and contexts can be utilised. This might assist you in organizing your changelogs and managing database updates.
5. To automate routine actions related to delivering your database changes, pre- and post-deployment scripts should be employed. For instance, you can use pre-deployment scripts. It is used to backup your database before deploying changes, and post-deployment scripts to verify that the changes were successfully applied. You may reduce the likelihood of errors and ensure the success of your deployments by doing this.

Interested to know more about LiquiBase, please refer to the official documentation of LiquiBase.

To read more tech blogs, please refer to Knoldus Blogs.

Original article source at: https://blog.knoldus.com/

#changelog #liquibase 

How to Use Database Versioning with Spring Boot and Liquibase

What is Liquibase

Liquibase is an open-source database-independent library for tracking, managing, and applying database schema changes. Basically, it works with various types of databases and supports various file formats for defining the database structure. The feature that is probably most useful in Liquibase is its ability to roll changes back and forward from a specific point and save you from needing to know what was the last change you ran on a specific database instance.

Integration with Spring Boot

Liquibase is a Java-based tool integrated with Maven and Spring Boot and now we need to create a spring boot project using Spring Initializr.

Add Mysql configuration in application.properties

After the configuration in the project structure under src/main/resources, we will see that we have a package with the name db and inside there is another package with the name changelog, Now we will add all changelogs.

Here, we need to create the changelog master file as db/changelog-master.xml and it will track all changes.

This is the content of the file:

After that just configure that file in the application.properties file as follows:

spring.liquibase.change-log=classpath:db/changelog/db.changelog-master.xml

Now, Let’s make our first migration in which we will create a new table name as a student which will have id, first name, and last name.

Create a file in db/changelog and name it db-changelog-create_student_table-1.0.sql.

That’s it, now let’s run the application.

If we inspect the database changelog table and we will see our first migration as follow :

Conclusion

We learned how easy and useful Spring Boot Liquibase is. When you are starting a new project this is a database versioning tool that will help in managing the database schema and speed up the productivity of the team and if we have an old project we can still integrate it easily. So, This blog provided details on how to use Liquibase in the Spring Boot application.

Reference:

https://en.wikipedia.org/wiki/Liquibase

Original article source at: https://blog.knoldus.com/

#springboot #liquibase #database 

Liquibase and its Advantages

New to Liquibase? Welcome! This blog will explain the essential introduction to Liquibase and its Advantages.

Common Liquibase Definition

Liquibase is an open-source database-independent library for tracking, managing, and applying database schema changes.

A little background

Before we go into detail about Liquibase, let us see the common problems we face as a developer while working with the application and the database scripts. Now, as a developer, we have version control tools for maintaining the application code but we do not have any tools which actually maintain the version control for your database changes. That’s the reason, once you have promoted the application code to the higher environment, sometimes the developer may further go to execute the database scripts which actually have the dependency for that particular application.

That is the reason we are going to face some problems when we are promoting our application code to the production environment. So to eradicate such kind of problems with respect to the database scripts, we are introducing a tool called Liquibase.

Liquibase will actually act as version control for your database Scripts. Liwuibase is a source control tool for your database. It also supports branching and merging. Like other versioning tools, it supports multiple developers working on it at the same time.

Liquibase in detail/Advantages:

• Liquibase is a source control tool for your database: It is going to maintain the version of the scripts that you are going to execute.
• Liquibase supports code branching and merging: liquibase works best on different files types like XML, YAML, JSON, etc so you can provide your database change-related scripts in these formats so that a particular format is branched out and finally gets merged.
• Support multiple developers: multiple devs can work together and create their own scripts with respect to the database and then commit it as part of their code.
• Supports context-dependent logic: if you have created a few scripts which might have to be executed at a particular version, so you can specify that in the context. It will be executed only for that particular version.
• Cluster-safe database upgrades: if you are creating a fresh project or old projects are there that you want to upgrade to liquibase, it gets very easy with liquibase.
• Generate database change documentation: you can easily generate documentation for your database changes.
• Generate database “diffs”: if you are on two different databases, you can easily see the diffs(differences) between both using liquibase.
• Run through your build process, embedded in your application or on demand. E.g: you can integrate with ant/maven/Gradle build tools.
• Automatically generate SQL scripts for DBA code review (update SQL, rollback SQL).
• Does not require a live database connection.
• Datical is both the largest contributor to the liquibase project and the developer of Datical DB.

Liquibase supports a list of database types

• MySQL
• PostgreSQL
• Oracle
• MSSQL
• Sybase
• asany
• db2
• derby
• hsqldb
• h2
• Informix
• firebird
• SQLite

How does Liquibase work?

Liquibase is a source control version tool for your database scripts.

Liquibase basically works based on the log element that is the Database change log. The database change log file can be in form of XML/JSON/YAML/SQL. Now once you have prepared the database change logs, now you have to move to the changeset. ChangeSet is nothing but SQL that contains the changes that are to be done in the database.

ChangeSet takes two fields ie. id and author. If the id and author match the actual database then further operations are carried out otherwise it throws an exception.

Sample change log file for XML format:

<?xml version="1.0" encoding="UTF-8"?>	
<databaseChangeLog
	xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
	xmlns:pro="http://www.liquibase.org/xml/ns/pro"
	xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
		http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd
		http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd
		http://www.liquibase.org/xml/ns/pro http://www.liquibase.org/xml/ns/pro/liquibase-pro-latest.xsd">
    <preConditions>  
        <runningAs  username="liquibase"/>  
    </preConditions>  

    <changeSet  id="1"  author="nvoxland">  
        <createTable  tableName="person">  
            <column  name="id"  type="int"  autoIncrement="true">  
                <constraints  primaryKey="true"  nullable="false"/>  
            </column>  
            <column  name="firstname"  type="varchar(50)"/>  
            <column  name="lastname"  type="varchar(50)">  
                <constraints  nullable="false"/>  
            </column>  
            <column  name="state"  type="char(2)"/>  
        </createTable>  
    </changeSet>  

    <changeSet  id="2"  author="nvoxland">  
        <addColumn  tableName="person">  
            <column  name="username"  type="varchar(8)"/>  
        </addColumn>  
    </changeSet>  
    <changeSet  id="3"  author="nvoxland">  
        <addLookupTable  
            existingTableName="person"  existingColumnName="state"  
            newTableName="state"  newColumnName="id"  newColumnDataType="char(2)"/>  
    </changeSet>  

</databaseChangeLog>

Sample change log file for JSON format:

{
            "databaseChangeLog": [
              {
                "preConditions": [
                  {
                    "runningAs": {
                      "username": "liquibase"
                    }
                  }
                ]
              },
              {
                "changeSet": {
                  "id": "1",
                  "author": "nvoxland",
                  "changes": [
                    {
                      "createTable": {
                        "tableName": "person",
                        "columns": [
                          {
                            "column": {
                              "name": "id",
                              "type": "int",
                              "autoIncrement": true,
                              "constraints": {
                                "primaryKey": true,
                                "nullable": false
                              },
                              
                            }
                          },
                          {
                            "column": {
                              "name": "firstname",
                              "type": "varchar(50)"
                            }
                          },
                          {
                            "column": {
                              "name": "lastname",
                              "type": "varchar(50)",
                              "constraints": {
                                "nullable": false
                              },
                              
                            }
                          },
                          {
                            "column": {
                              "name": "state",
                              "type": "char(2)"
                            }
                          }
                        ]
                      }
                    }
                  ]
                }
              },
              {
                "changeSet": {
                  "id": "2",
                  "author": "nvoxland",
                  "changes": [
                    {
                      "addColumn": {
                        "tableName": "person",
                        "columns": [
                          {
                            "column": {
                              "name": "username",
                              "type": "varchar(8)"
                            }
                          }
                        ]
                      }
                    }
                  ]
                }
              },
              {
                "changeSet": {
                  "id": "3",
                  "author": "nvoxland",
                  "changes": [
                    {
                      "addLookupTable": {
                        "existingTableName": "person",
                        "existingColumnName": "state",
                        "newTableName": "state",
                        "newColumnName": "id",
                        "newColumnDataType": "char(2)",
                        
                      }
                    }
                  ]
                }
              }
            ]
          }

In the same way, you can provide any number of the changelog. To know more about change log patterns you can simply visit the Official Liquibase change log documentation.

Once you are done with preparing the database changelogs, now liquibase needs to identify which type of database you want to execute these scripts. Now in order to identify the database type, whenever we are using a maven/Gradle, you are going to define what type of database it is in the configurations/properties file.

You will define what type of database it is, JDBC Driver, the DB URL, and the password. Once the database is identified, it looks for the database changelogs and identifies the changeset. Now it will prepare the SQL statements according to the identified database and the properties given in the JSON/XML etc file.

Whenever it is executing the scripts, if it is the first time it is executing then liquibase will create two tables and maintain the version control for your database changes.

Tables created in Liquibase:

The first table will be DATABASECHANGELOG, it will contain whatever scripts you are executing. The second table is DATABASECHANGELOGLOCK, it is basically used to maintain the atomic transactions i.e only one person at a time can modify the database scripts at a time.

The next time you execute any database scripts, the tables are not created from the scratch. Only updation will happen in the already created tables.

This is all about the working of Liquibase.

Conclusion

After knowing a briefly about liquibase, we can say that liquibase is beneficial to use to keep the database version control in check, to generate various reports and many more advantages.

To read more about Liquibase, please stay tuned and keep visiting Knoldus Blogs.

Reference links – Liquibase Documentation

Original article source at: https://blog.knoldus.com/

#liquibase 

Database change management with Liquibase

Liquibase is one of the most protean tools for database migration. It operates on almost all SQL database outlets. So whether you use MySQL, SQL Garçon, Oracle, Firebird, or a combination of these or any other common database platforms, it’s got you covered. It also runs in any CI/ CD channel so long as you can run Java, install the connectors, and connect to your databases. This means you can use Liquibase to emplace database changes along with your new features that are released behind point toggles. It takes a bit of planning — especially when you are releasing clashing or destructive changes. Luckily, Liquibase has some mechanisms to make database changes less discouraging. It functions all the DDL procedures and several veritably usable refactorings. occasionally you have to be careful about when you run those database changes on your product cases since they can lock your tables for quite a while. In this post, you will learn how to use Liquibase to make all effects database migration easier.

Features of Liquibase

Some features claimed by Liquibase are

• All changes to the database are stored in XML/ JSON/ Yaml or SQL lines and linked by a combination of an “ ID ” and “ author ” marker as well as the name of the train itself.
• Updates database to the current interpretation
• Rollback last X changes to the database, date/ time bylabel4.SQL for Database updates, and Rollbacks can be saved for manual review
• Stand-alone IDE and decline draw-in
• Database diff report, diff changelog generation
• Capability to produce a changelog to induce a being database
• Database change documentation generation
• DBMS Check, user review, and SQL tab preconditions
• Executable via command line, Ant, Maven, Servlet vessel, or Spring
• Support for further than 10 RDBMS
• Grails DB migration plugin uses Liquibase.

Manual tracking DB changes Possible issues:

• easy to lose sync between law and DB state
• hard to recover from crimes during development( partner in case of applying the wrong statement to DB)
• Frequently bear tore-create DB from scrape during development
• hard to find out the state of a particular DB terrain
• may bear support for 2 performances of changes full and incremental

What makes liquibase different?

• DDL abstraction DSL
• Support for different changelog format
• community- managed trendy and check
• make- in XML, YAML, JSON, and SQL
• Over 30 erected- in database refactorings
• Rollback database changes feature
• Database diff report
• Extensibility

Database Change Log File

• The root of all Liquibase changes is the database changelog file.
• The Sample Change Log File exists as shown below:

<databaseChangeLog> <changeSet id=”1 author=”Chetan Bhagat”>″ 

<createTable tableName=”event”> 

<column name=”id” type=”int”> 

<constraints primaryKey=”true” nullable=”false”/> 

</column> 

<column name=”name” type=”varchar(50)”> 

<constraints nullable=” false”/>

</column> 

<column name=”active” type=”boolean” defaultValueBoolean=”true”/> 

</createTable> 

</changeSet> 

<changeSet author=”sought” id=”tag database-example”> 

<tagDatabase tag=”0.1 />″ 

</changeSet> 

</databaseChangeLog>

Rollback

• Specifying a tag to rollback will roll back all change sets that were executed against the target database after the given tag was applied.
• You can specify the number of change sets to roll back.
• You can determine the date to roll back to.

<changeSet id=”changeRollback” author=”Chetan Bhagat”> 

<createTable tableName=”event”> 

<column name=”id” type=”int”/> 

</createTable> 

<rollback> 

<dropTable tableName=”event”/> 

</rollback> 

</changeSet>

Generating ChangeLog File

• For generating change log files from an existing database, we used the command line tool.
  • Run ./liquibase –changeLogFile=changelog.xml generateChangeLog
• Here is an example of the Liquibase command:

Dump data to XML file Run

./liquibase --changeLogFile=data.xml --diffTypes=data generateChangeLog 

Be ready for OutOfMemoryError

Best Practices Some of the best practices we follow are as mentioned below:

• Organize Change Log File

• We organize our application version files in separate changeLog files and we have a master ChangeLog File.

Below is the master changelog file.

<?xml version=”1.0 encoding=”UTF-8 ?>″ ″ <databaseChangeLog> <include 

file=”com/myapp/db/changelog/db.changelog-1.0.0.xml”/> <include 

file=”com/myapp/db/changelog/db.changelog-1.0.1.xml”/> <include 

file=”com/myapp/db/changelog/db.changelog-1.0.2.xml”/> </databaseChangeLog>

• Change Set ID

• Supply change set ID for all the modifications.

• Use Liquibase from the initial stage of development.

How to run liquibase

On-Demand :

• Command Line
• Ant
• Maven

Automated:

• Servlet Listener
• Spring Listener
• JEE CDI Listener

Java APIs

Liquibase can smoothly be ingrained and managed through its Java APIs.

1. Create property file liquibase. property with connection parameters:

spring.liquibase.enabled=true

spring.application.name=sample-liquibase-app

spring.liquibase.change-log=classpath:db/changelog/changelog-master.xml

spring.datasource.url=jdbc:mysql://localhost:3306/liquibase

spring.datasource.driver-class-name=com.mysql.jdbc.Driver

spring.datasource.username=root

spring.datasource.password=pass

2. Run liquibase as

 ./liquibase <command> 

Conclusion :

In this Blog, we demonstrated different ways to use Liquibase for different database changes.

To learn more about Manage Your Database Schema, you can visit this link.

To read more tech blogs, feel free to visit Knoldus Blogs.

Original article source at: https://blog.knoldus.com/

#liquibase #database 

How to using Liquibase with Spring Boot

The purpose of this blog is to show you the process of using Liquibase as a piece of your Spring Boot workflow. Springboot makes it easy to create standalone, production-maven Spring-based applications.

Introduction

Liquibase is an open-source database that has an independent library for tracking, managing, and applying database schema changes. Liquibase was started in 2006 and it is used to allow easier tracking of database changes, especially in an agile software development environment.

Prerequisites

Make sure that you have  Java Development Kit (JDK 8, 11, or 16).

Use of Liquibase with Spring Boot as a Maven Project

Use Liquibase with spring-boot to create and configure single spring applications and automate your database updates. Spring-boot with build systems like Maven permits you to create Java applications initiated by running java -jar or war deployments.

The Liquibase Spring Boot integration makes the application database updated together with the application code by applying Spring Boot auto-configuration and features.

To use Liquibase and Spring Boot:

1. Firstly, install a maven project and add it to your system.
2. Make sure you have Java Development Kit (JDK 8, 11, or 16).
3. Generate a basic maven project by using your IDE :

• If you already have an existing spring-boot project, then add the liquibase-core dependency on your project pom.xml.
• To dynamically create a new spring-boot project, follow the Spring Boot Getting Started documentation.
• To create a basic spring-boot project, you can also use a web-based service that is called Spring Initializr.

Insert the below information in Spring Initializr.

• Project Type: Maven
• Language Used: Java
• Spring-Boot: the version you need
• Project Description :
  • Group: com. knoldus.liquibase
  • Artifact: spring-boot-liquibase-example project
  • Name: spring-boot-project
  • Description: Liquibase-spring-boot project example
  • Package name: com.knoldus.spring-boot.liquibase-example project
  • Packaging Type: Jar
  • Java version: 8, 11, or 16
• Dependencies: Spring Data JPA and Liquibase Migration. This service will add our database driver dependency and any developer tool.
• Now click on  GENERATE to download your created project template as a .zip file.

After selecting the above options, the project window needs to look similar to the below screenshot:

Finally, Click GENERATE to upload your project template as a zip file. After you require to extract it and open it in your IDE.

Run the Liquibase database with Spring Boot and the Maven project

1. Open the existing Spring Boot application.properties file. To get the file, traverse to src/main/resources/application.properties.
2. Add the following properties to run Liquibase migrations. Now update the values depending on your database prerequisite:

spring.datasource.url=jdbc:mysql://localhost:3306/liquibase;

DB_CLOSE_ON_EXIT=FALSE

spring.datasource.username = ${dbName}

spring.datasource.password = ${dbPass}

spring.liquibase.change-log=classpath:db/changelog/db.changelog-master.xml

1. Create a new text file known as pom.xml or utilize the pom.xml file created for your project using the Spring Initializr.
2. Specify attributes in your pom.xml based on the example:

<?xml version="1.0" encoding="UTF-8"?>

<project xmlns="http://maven.apache.org/POM/4.0.0"
 
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 

https://maven.apache.org/xsd/maven-4.0.0.xsd">

	<modelVersion>4.0.0</modelVersion>

	<parent>

		<groupId>org.springframework.boot</groupId>

		<artifactId>spring-boot-starter-parent</artifactId>

		<version>2.7.4</version>

		<relativePath/> <!-- lookup parent from repository -->

	</parent>

	<groupId>com.knoldus</groupId>

	<artifactId>spring-boot-liquibase-example</artifactId>

	<version>0.0.1-SNAPSHOT</version>

	<name>spring-boot-liquibase-example</name>

	<description>Demo project for Spring Boot</description>

	<properties>

		<java.version>11</java.version>

	</properties>

	<dependencies>

		<dependency>

			<groupId>org.springframework.boot</groupId>

			<artifactId>spring-boot-starter-data-jpa</artifactId>

		</dependency>

		<dependency>

			<groupId>org.springframework.boot</groupId>

			<artifactId>spring-boot-starter-jdbc</artifactId>

		</dependency>

		<dependency>

			<groupId>org.liquibase</groupId>

			<artifactId>liquibase-core</artifactId>

		</dependency>

		<dependency>

			<groupId>mysql</groupId>

			<artifactId>mysql-connector-java</artifactId>

			<scope>runtime</scope>

		</dependency>

		<dependency>
			<groupId>org.projectlombok</groupId>

			<artifactId>lombok</artifactId>

			<optional>true</optional>

		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>

			<artifactId>spring-boot-starter-test</artifactId>

			<scope>test</scope>

		</dependency>

	</dependencies>

	<build>

	  <plugins>

	    <plugin>

		<groupId>org.springframework.boot</groupId>

		 <artifactId>spring-boot-maven-plugin</artifactId>

		  <configuration>

                     <excludes>

			<exclude>

			   <groupId>org.projectlombok</groupId>

			   <artifactId>lombok</artifactId>

			</exclude>

		     </excludes>

		  </configuration>

	     </plugin>

	  </plugins>

	</build>

</project>

Setting your changelog file :

• Create a changelog file: src/main/resources/db/changelog/db.changelog-master.xml. Liquibase is also keep up the .sql, .yaml, or .json changelog formats. To get more information, see Introduction to Liquibase and Getting Started with Liquibase.
• Add the following code snippet to your changelog file, including changesets:

<?xml version="1.0" encoding="UTF-8"?>

<databaseChangeLog

  xmlns="http://www.liquibase.org/xml/ns/dbchangelog"

  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

  xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog

         http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd">

  <changeSet id="01" author="Sankata">

    <createTable tableName="books"

      remarks="A table to contain all books">

      <column name="id" type="int" autoIncrement="true">

        <constraints nullable="false" unique="true" primaryKey="true"/>

      </column>

      <column name="name" type="varchar(255)">

        <constraints nullable="false" unique="true"/>

      </column>

      <column name="author" type="int">

        <constraints nullable="false"/>

      </column>

    </createTable>

    <createTable tableName="authors"

      remarks="A table to contain all the authors">

      <column name="id" type="int" autoIncrement="true">

        <constraints nullable="false" primaryKey="true"/>

      </column>

      <column name="name" type="varchar(100)">

        <constraints nullable="false"/>

      </column>

    </createTable>

    <addForeignKeyConstraint baseTableName="books" baseColumnNames="author"

      constraintName="author_fk"

      referencedTableName="authors" referencedColumnNames="id"/>

  </changeSet>

</databaseChangeLog>

To run the first migration with the below command

mvn compile package

Reference links

• https://www.liquibase.com
• https://www.liquibase.org/get-started/database-migration

Original article source at: https://blog.knoldus.com/

#springboot #liquibase 

How to Managing Database Schema with Liquibase

What is Liquibase?

First, Liquibase is an open-source database schema change management tool that makes it simple for you to handle database change revisions.

How Does Liquibase Work?

Regardless of your database platform, changes are defined in a platform-neutral language. In essence, you maintain a running list of modifications. And, Liquibase uses its execution engine to make those modifications for you. It requires the appropriate JDBC driver to update the database because it runs on Java. You must also have the most recent JRE installed, of course. You can run Liquibase manually or from any deployment pipeline because it operates from a shell or command line. To maintain consistency and prevent corruption due to wrongly modified changelogs, Liquibase tracks changes using its own tables in your schema. To prevent you from mistakenly executing two updates at once, it “locks” your database while operating.

Where can we use Liquibase?

• Database schema change using CD
• Controlling schema change versions
• Application and database modifications should be deployed simultaneously to ensure consistency.

Liquibase basics – changeLog files

The changeLog files are the foundation of Liquibase usage. A changelog file is an XML document that records every change that needs to be made to update the database. Tag is parsing when we run the Liquibase migrator, the databaseChangeLog>. We can add changeSet> tags to the databaseChangeLog> tag to organize database changes. The ‘id’ and ‘author’ attributes as well as the name of the changelog file classpath serve to identify each changeSet specifically.

Example:

<databaseChangeLog

    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"

    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog 

http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.5.xsd">

</databaseChangeLog>

Updating the database

We will examine how to edit the database to add, update, and remove tables, columns, and data. Now that we have a simple changeLog file. By the principle of “one change per changeSet,” it is counseles to create a new changeSet for each distinct insert, update, and delete action. As a result, we use Liquibase to execute a version-based database migration while updating an existing database.

Manipulating database schema and tables

For the most part, you require some fundamental Data Definition Language (DDL) functions to define data structures in databases.

1) Create schema: Since Liquibase is planned to manage objects within the application’s schema, there is no “create schema” tag. However, we can incorporate the creation of a schema into our migration by using a unique SQL statement inside a “SQL” tag.

<changeSet author="asdf" id="1234">

    <sql dbms="h2" endDelimiter=";">

        CREATE SCHEMA schema

    </sql>

</changeSet>

2) Create Table: The following code is added to the databaseChangeLog tag when a table is created:

<changeSet author="asdf" id="1234">

    <createTable tableName="newTable">

        <column type="INT" name="newColumn"/>
 
    </createTable>

</changeSet> 

3) Drop Table: We must mention the table’s name and the schema when deleting a table. We also remove all constraints referring to primary and unique keys in the drop table when cascadeConstraints are set to true. It will after delete the corresponding records in the child table or tables. The database will return an error and won’t drop the table if there is a referential integrity constraint but we don’t set the cascadeConstraints to true.

<changeSet author="asdf" id="1234">

    <dropTable tableName="newTable" schemaName="public"
 
cascadeConstraints="true"/>

</changeSet>

4) Change existing data structure with alter table: The table can be changed by adding, renaming, and dropping columns as well as changing the data type. To change the table’s name, use the tag renameTable.

4) a. Rename Table: We must specify the new table name, the old table name, and the schema name inside the tag “renameTable”.

<changeSet author="asdf" id="1234">

    <renameTable newTableName="newName" oldTableName="table" 

schemaName='schema'/>

</changeSet>

4) b. Rename Column: A column’s data type, new column name, old column name, schema name, and table name must all be provided to rename a column.

<changeSet author="asdf" id="1234">

    <renameColumn columnDataType="varchar(255)" newColumnName="newColumn" 

oldColumnName="column" schemaName="schema" tableName="table"/>

</changeSet>

4) c. Add Column: The schema name, table name, and the name and type of the new column must be included in the inner tag <column> of the tag <addColumn>.

<changeSet author="asdf" id="1234">

    <addColumn schemaName="schema" tableName="table">

        <column name="newColumn" type="varchar(255)"/>

    </addColumn>

</changeSet>

4) d. Drop column: Column name, table name, and schema name must all be specified to delete a column.

<changeSet author="asdf" id="1234">

    <dropColumn columnName="column" tableName="table", schemaName="schema"/>

</changeSet>

4) e. Modify the data type: We need the column name, new data type, schema name, and table name when changing a data type.

<changeSet author="asdf" id="1234">

    <modifyDataType columnName="column" newDataType="int" schemaName="schema"
 
tableName="table"/>

</changeSet>

Liquibase with Maven

To configure and run Liquibase with Maven, we need to add the following configuration to our pom.xml file:

<dependency>

    <groupId>org.liquibase</groupId>

    <artifactId>liquibase-core</artifactId>

    <version>x.x.x</version>

</dependency> 

Maven also enables us to automatically generate a changelog file from:

• already existing database

mvn liquibase:generateChangeLog

• the difference between the two databases

mvn liquibase:diff

Conclusion:

In conclusion, in this blog, we have learned about how can we manage databases with the help of Liquibase. I will be covering more topics on Liquibase in my future blogs, stay connected. Happy learning

For more, you can refer to the Liquibase documentation: https://docs.liquibase.com/home.html

For a more technical blog, you can refer to the Knoldus blog: https://blog.knoldus.com/

Original article source at: https://blog.knoldus.com/

#database #schema #liquibase 

Learn Features Of Liquibase

Liquibase

With Liquibase, you can specify the database modification you desire using SQL or a number of other database-independent forms, such as XML, YAML, and JSON. It is incredibly simple for developers to submit updates to many database types by abstracting the database logic.

How does Liquibase work?

The changeLog file is how Liquibase functions most frequently. One or more changeSets are contained in this text file. A hierarchy of files is created when a changeLog contains further changeLogs.

A changeSet is the basic unit of change for a database. The change instruction itself and the related metadata are the two types of data that make up a changeSet. Although an instruction may contain one or more atomic changes (such as adding a table, changing a column, adding a constraint, inserting rows, etc.), when it is executed, it treats all of those changes as belonging to a single changeSet.

Liquibase is not a difference-based system

Liquibase uses the DATABASECHANGELOG table instead of a difference-based method because of a number of advantages. The most important aspect of database schema changes is their meaning. There are several techniques to change the database schema. Some of them could modify or modify related data. It allows for the specific procedure by which alterations are to be performed to be described.

Another aspect preventing Liquibase from being built on differences is performance. It is unable to compare data rapidly enough to make modifications because of large databases.

Development environment

As Liquibase is a cross-platform application, it can be used with Linux, Windows, or Mac OS. Both the workstation and the server can use it.

It was created using Java. Java 1.6 or later is necessary for Liquibase’s most recent version (3.5.3).

JDBC driver is used to establish a connection to the database. For instance, the Oracle Database 12.1.0.1 JDBC Driver can be utilized with Oracle Database Express Edition 11g Release.

Database

One of Liquibase’s numerous benefits is that it is compatible with a variety of database types. The compatible databases are listed in the following table.

• MySQL
• PostgreSQL
• Oracle
• SQL Server
• Sybase_Enterprise
• Sybase_Anywhere
• DB2
• Apache_Derby
• HSQL
• H2
• Informix
• Firebird
• SQLite

By utilizing extensions, Liquibase may support different database architectures.

Deployment

Liquibase can be run in a number of different methods, such as an Apache Ant task or a direct Java call, but the command line is the method that is most frequently used because it allows for full Liquibase functionality.

ChangeLog file formats

ChangeLogs can be written in many different formats. Liquibase supports the following file formats.

• XML Format
• YAML Format
• JSON Format
• SQL Format
• Other Formats

The fact that the XML, YAML, and JSON formats enable all Liquibase functionalities is their main benefit. There are several restrictions associated with using the SQL format, such as the absence of automatic rollback generation.

Rollback in Liquibase

The ability to undo modifications is one of Liquibase’s most practical important features. This can be carried out automatically or with the aid of a predefined SQL script.

It is possible to use the automatically created rollback for commands like “CREATE TABLE,” “CREATE VIEW,” “ADD COLUMN,” and others. However, some changes, like “DROP TABLE,” cannot be reversed. In that situation, rollback must be manually defined.

Conclusion

Liquibase enables you to specify the desired database modification using SQL or a variety of other database-independent forms, such as XML, YAML, and JSON. To make it incredibly simple to publish updates to several database types, developers can abstract the database logic.

Original article source at: https://blog.knoldus.com/

#liquibase #java 

How to Automate Database Script Deployment Using Liquibase and Git

Many organizations have implemented DevOps in their applications, that’s true. But, at the same time, their database change process hasn’t realized any of these benefits and is still left in the dark ages. But what if you could automate that too? Yeah, you guessed right — it can be done using Liquibase. In this tutorial, we'll learn How to Automate Database Script Deployment Using Liquibase and Git

1. Is This Liquibase Tutorial for You?
2. What is Liquibase Exactly?
3. Start With Installing Liquibase
4. Configure Your Project and Liquibase
5. Use Oracle Wallet (Optional)
6. Connect Liquibase With A Database
7. Track Other Database Changes (Packages, Views, etc.)
8. How to Install Liquibase in An Existing Software Project?

#liquibase #database #script #java 

JPA Goes Even Easier With its Buddy

JPA Buddy is a tool that is supposed to become your faithful coding assistant for projects with JPA and everything related.

So, Hello World… After almost a year of development, the first version of JPA Buddy has finally been released! This is a free tool that is supposed to become your faithful coding assistant for projects with JPA and everything related: Hibernate, Spring Data, Liquibase, and other mainstream stacks.

“Why would I use it?” - this is a fair question towards any new tool or framework. In short, if you use JPA for data persistence, JPA Buddy will help you to be more efficient. In this article, I’ll present an overview of the tool. I hope it will take its fair place among the most loved tools for Java developers, ones who use JPA, Spring, Liquibase, and of course the most advanced Java IDE - IntelliJ IDEA.

#java #tools #hibernate #jpa #spring data #liquibase

Cloud Spanner Adds Support for Liquibase

The extension provides an example [changelog.yaml] file that demonstrates a changelog applied to Spanner using the[ Liquibase CLI] and helps applying Liquibase [best practices] on Cloud Spanner.

Among the current [limitations], there are unsupported Spanner features, such as views and stored procedures, and DML limits for the number of rows affected during DML, with the recommendation to use partitioned DML. Using the [Spanner JDBC driver], this can be configured using the AUTOCOMMIT_DML_MODE.

Liquibase is not the only open source database version control tool currently supported by Cloud Spanner.

#database #liquibase #cloud spanner #added support for liquibase

Flyway vs Liquibase

I believe that there is no need for describing why the usage of database migration tools is vital for modern days apps that utilize relational database engines. I will only say that they can make our life much easier and help us to automatize a complex and repetitive process. Through the course of this article, I will provide some more insights into similarities and differences between two of the most common open-source migration tools, namely Flyway and Liquibase. I will start with quick descriptions of both tools.

Flyway

It is described by its creators, a company called Redgate, as an open-source database migration tool that prefers simplicity and convention over configuration. As of now, it supports most of the currently used database engines, such as Postgres, Oracle, SQL Server, DB2, H2, MariaDB, and many others. It also supports some cloud base database services like Amazon RDS or Google Cloud SQL or Heroku. The script can be written in pure SQL (many dialects are supported) or in Java (mostly for more complex transformations). It has a command line client but it also provides Maven and Gradle plugins. What is more, it has Java API which also works for Android. For those of you who use .NET and C#, there is a Flyway counterpart named Evolve so if you are interested you can check this one up. The link to its GitHub page will be at the end of the article.

Liquibase

It started in 2006 and is an open-source tool for database migrations. It is based on the concept ofchangelogsandchangesetsfiles which can be written in SQL, XML, YAML, JSON. There we store all the changes which we want to make in our database structure. These files can be further used to apply those changes to any other database instance. Liquibase supports subsequent databases: Postgres, Oracle, DB2, H2, MariaDB, SQL Server, SQLite, and many others. Many cloud-based databases are also supported for example Azure SQL, Amazon RDS, Amazon Aurora. You can run Liquibase migration scripts from shell, using build tools such as Maven Gradle or even Ant. Moreover, you can generate pure SQL queries that can be further executed by your DBA-s/Ops/DevOps team or anyone who is taking care of your database.

Now that we described both tools, we can move to describing similarities and differences between them. Let’s start with the similarities between these two tools.

Similarities

• Both are to some degree open source and provide some part of features for free but also have paid versions that provide more features.
• Both can use plain old SQL to write your migration scripts.
• Both are strongly “Java oriented” and also have built in support for basic build tools like Maven or Gradle as well as integration with the most common Java frameworks, for example: Spring Boot.
• Both can be run as simple shell scripts from command line.
• The list of supported databases is more or less similar. There can be some minor differences in supported versions or drivers but in general, there are no easily visible differences between them in this area.
• Both are based on the same approach for handling database changes, namely Migration Based Database Delivery.
• Both tools try to implement the concept of **Evolutionary database **presented and explained by Martin Fowler (link in the end of the article).

#tools #database #integration #migration #opensource #comparison #discussion #liquibase #flyway

Database Schema Versioning and Migrations Made Simpler For High Speed CI/CD

If you are a back-end developer, you are often faced with having to migrate your database schema with each new release.

The framework called Liquibase can make it easier for you when you need to upgrade your database schema.

In this article, I’ll explain how Liquibase can be used in a Java project, with Spring/Hibernate, to version the database schema.

How does Liquibase work?

Changelog

Liquibase works with Changelog files (the list of all the changes, in order, that need to execute to update the database).

There are 4 supported formats for these Changelogs: SQL, XML, YAML, and JSON.

#liquibase #database #versioning #plugins #java #hackernoon-top-story #database-schema-versioning #database-schema-migration

Distributed SQL Change Management with Liquibase and YugabyteDB on GKE

Liquibase is an open source and extensible change management project that supports a variety of databases including Snowflake, MySQL, and PostgreSQL via JDBC. Liquibase allows users to easily define changes in SQL, XML, JSON, and YAML. These changes are then managed in a version control system so the changes can be documented, ordered, and standardized. For more information on the features and benefits of Liquibase, check out their documentation site.

In this blog post we’ll show you how to:

• Install a 3 node YugabyteDB cluster on Google Kubernetes Engine
• Build the sample Northwind database
• Install and configure Liquibase
• Create a simple changeset and verify the results
• Explore how changes are documented and managed in Liquibase

New to distributed SQL or YugabyteDB? Read on.

What is Distributed SQL?

Distributed SQL databases are becoming popular with organizations interested in moving data infrastructure to the cloud or cloud native environments. This is often motivated by the desire to reduce TCO or move away from the horizontal scaling limitations of monolithic RDBMS like Oracle, PostgreSQL, MySQL, and SQL Server. The basic characteristics of Distributed SQL are:

• They must have a SQL API for querying and modeling data, with support for traditional RDBMS features like foreign keys, partial indexes, stored procedures, and triggers.
• Automatic distributed query execution so that no single node becomes a bottleneck.
• Should support automatic and transparent distributed data storage. This includes indexes, which should be sharded across multiple nodes of the cluster so that no single node becomes a bottleneck. Data distribution ensures high performance and high availability.
• Distributed SQL systems should also provide for strongly consistent replication and distributed ACID transactions.

For a deeper discussion about what Distributed SQL is, check out, “What is Distributed SQL?”

What is YugabyteDB?

YugabyteDB is an open source, high-performance distributed SQL database built on a scalable and fault-tolerant design inspired by Google Spanner. YugabyteDB is PostgreSQL wire compatible, cloud native, offers deep integration with GraphQL projects, plus supports advanced RDBMS features like stored procedures, triggers, and UDFs.

Got questions? Make sure to ask them in our YugabyteDB Slack channel. Ok, let’s dive in…

Step 1: Install YugabyteDB on a GKE Cluster using Helm 3

In this section we are going to install YugabyteDB on the cluster. The complete steps are documented here. We’ll assume you already have a GKE cluster up and running as a starting point.

The first thing to do is to add the charts repository.

$ helm repo add yugabytedb https://charts.yugabyte.com

Now, fetch the updates.

$ helm repo update

Create a namespace. In this case we’ll call it yb-demo.

$ kubectl create namespace yb-demo

Expected output:

namespace/yb-demo created

We are now ready to install YugabyteDB. In the command below we’ll be specifying values for a resource constrained environment.

$ helm install yb-demo yugabytedb/yugabyte \
--set resource.master.requests.cpu=1,resource.master.requests.memory=1Gi,\
resource.tserver.requests.cpu=1,resource.tserver.requests.memory=1Gi,\
enableLoadBalancer=True --namespace yb-demo --wait

To check the status of the cluster, execute the below command:

$ kubectl get services --namespace yb-demo

Note the external-IP for yb-tserver-service which we are going to use to establish a connection between YugabyteDB and Liquibase. From the screenshot above we can see that the IP is 34.72.XX.XX and the YSQL port is 5433.

#databases #distributed sql #google cloud platform #how to #kubernetes #liquibase