Versioning database changes is as important as versioning source code. By using a database migration tool we can safely manage how the database evolves, instead of running a bunch of non versioned loose files. In some frameworks like , database versioning occurs along the development. But when it comes to world, I don't see it happening so often. SQL Ruby On Rails Java In this article we’ll see how to integrate with to evolve the database schema of a application using . Liquibase Spring Boot Java MySQL Meet Liquibase Currently, the most popular database tools are and . I've choose the latter due to these benefits: Flyway Liquibase - It's database agnostic - it works for all major database vendors; - You can specify your changes in , , and formats. XML YAML JSON SQL We'll use format. YAML Liquibase concepts These are the key concepts: - : a file that keeps track of all changes that need to run to update the DB; changeLog - : these are atomic changes that would be applied to the database. Each is uniquely identified by 'id' and 'author'. Each is run as a single transaction. changeSet changeSet changeset The Domain Model This is our initial domain model that will be evolved during this article: The ' ' table has a relationship with ' ' table. Library One To Many Book The Rails way When I had my first experience with , back in 2007, the first feature that caught my attention was . is the framework shipped with . Ruby On Rails Active Record Migrations Active Record ORM Ruby On Rails So, for the given domain model above, to generate a migration file that create the two tables: A migration file is created. Then, we add instructions: Alright. Let's fire up the server: Then, using , if we try to access ' ' resource, for example: cURL Book We'll get an error, as we can see at server's log: Self-explanatory: we need to run the migration that will create the tables. Like this: Let's check in . Both tables were created: MySQL OK. suppose that we need to add two fields to ' ' table: ' ' and ' '. To accomplish this, we create another migration file like this: Books isbn publisher Then we open the migration file and add the instructions: Let's run this migration: If we check the table again, both fields were added: What if ' ' field is not necessary anymore? Let's create a migration to fix this: publisher This is the migration file: Let's run this migration: Now if we check the table, ' ' field was removed: publisher The Java way How can we do the same in a Java application? can be seamless integrated with , so let's begin. Liquibase Spring Boot Creating the project is our start point: Spring Initializr We've choose the following dependencies: - : to add 'mysql-connector-java' jar to our project; MySQL - : Starter for using Spring Data JPA with Hibernate; JPA - : Starter for using ; Liquibase Liquibase - : Starter for exposing Spring Data repositories over REST using Spring Data REST. Rest Repositories Additionally, I'm using to ease calling from command line as we'll see in the following examples. Liquibase Maven plugin Liquibase This is the dependency: org.liquibase liquibase-maven-plugin 3.6.3 < > dependency < > groupId </ > groupId < > artifactId </ > artifactId < > version </ > version </ > dependency And this is its configuration: org.springframework.boot spring-boot-maven-plugin org.liquibase liquibase-maven-plugin 3.6.3 src/main/resources/liquibase.yml < > build < > plugins < > plugin < > groupId </ > groupId < > artifactId </ > artifactId </ > plugin < > plugin < > groupId </ > groupId < > artifactId </ > artifactId < > version </ > version < > configuration < > propertyFile </ > propertyFile </ > configuration </ > plugin </ > plugins </ > build As stated in a , I rather not to expose entities or repositories directly, but just for the sake of demonstration, I'll use this time. It makes it easy to build hypermedia-driven REST web services on top of repositories. previous post Rest Repositories Spring Data Configuration This is our ' ' file: src/main/resources/application.yml spring: datasource: url: jdbc:mysql://localhost:3306/liquibase_test?useSSL=false username: root password: jpa: hibernate: dialect: org.hibernate.dialect.MySQL5InnoDBDialect ddl-auto: none liquibase: change-log: classpath:db/liquibase-changelog.yml A few notes: - by setting ' ' to ' ', the schema generation will be delegated to ; spring.jpa.hibernate.ddl-auto none Liquibase - by default, 's changeLog file is expected to be in ' ; but we are changing it by setting ' ' to put it in ' '. Liquibase db/changelog/db.changelog-master.yaml' spring.liquibase.change-log src/main/resources/db/liquibase-changelog.yml And this is our ' ' file, which is used by : src/main/resources/liquibase.yml Liquibase Maven plugin url: jdbc:mysql://localhost:3306/liquibase_test?useSSL=false username: root password: driver: com.mysql.cj.jdbc.Driver outputChangeLogFile: src/main/resources/db/liquibase-OutputChangelog.yml The entities This is our ' ' entity: Book com.tiago.entity; javax.persistence.Column; javax.persistence.Entity; javax.persistence.GeneratedValue; javax.persistence.GenerationType; javax.persistence.Id; javax.persistence.JoinColumn; javax.persistence.ManyToOne; { (strategy = GenerationType.IDENTITY) Long id; (nullable= ) String title; (name= ) Library library; { id;
  } { .id = id;
  } { title;
  } { .title = title;
  } { library;
  } { .library = library;
  }
} package import import import import import import import /**
 * Entity for table "Book" 
 * 
 * Tiago Melo (tiagoharris@gmail.com)
 *
 */ @author @Entity public class Book @Id @GeneratedValue private @Column false private @ManyToOne @JoinColumn "library_id" private Long public getId () return public void setId (Long id) this String public getTitle () return public void setTitle (String title) this Library public getLibrary () return public void setLibrary (Library library) this And this is our ' ' entity: Library com.tiago.entity; java.util.List; javax.persistence.Column; javax.persistence.Entity; javax.persistence.GeneratedValue; javax.persistence.GenerationType; javax.persistence.Id; javax.persistence.OneToMany; { (strategy = GenerationType.IDENTITY) Long id; (nullable= ) String name; (mappedBy = ) List<Book> books; { id;
  } { .id = id;
  } { name;
  } { .name = name;
  } { books;
  } { .books = books;
  }
} package import import import import import import import /**
 * Entity for table "Library" 
 * 
 * Tiago Melo (tiagoharris@gmail.com)
 *
 */ @author @Entity public class Library @Id @GeneratedValue private @Column false private @OneToMany "library" private Long public getId () return public void setId (Long id) this String public getName () return public void setName (String name) this List<Book> public getBooks () return public void setBooks (List<Book> books) this The repositories As mentioned earlier, since we are using , there's no need to write controllers; the repositories will be exposed directly. Rest Repositories This is our ' ': BookRepository com.tiago.repository; org.springframework.data.repository.CrudRepository; com.tiago.entity.Book; { } package import import /**
 * Repository for { Book} entity.
 * 
 * Tiago Melo (tiagoharris@gmail.com)
 *
*/ @link @author public < , > interface BookRepository extends CrudRepository Book Long And this is our ' ': LibraryRepository com.tiago.repository; org.springframework.data.repository.CrudRepository; com.tiago.entity.Library; { } package import import /**
 * Repository for { Library} entity.
 * 
 * Tiago Melo (tiagoharris@gmail.com)
 *
*/ @link @author public < , > interface LibraryRepository extends CrudRepository Library Long Organizing our changelogs (migration files) Let's take a look at our directory structure: This is ' ' file: src/main/resources/db/liquibase-changelog.yml databaseChangeLog: - includeAll: path: db/changelog/ We are telling to execute all changelog files in ' ' directory. Liquibase src/main/resources/db/changelog/ Changelog #1: creating the tables As a general rule, let's adopt the following naming convention: <migration_number>_<what_does_this_migration_do>.yml This way, will execute changelogs ordered by its number. Liquibase This is our ' ' file. As the name implies, it creates the tables: 1_create_book_and_library_tables.yml databaseChangeLog: - changeSet: author: "tiago" id: "creates_library_table" changes: - createTable: tableName: "library" columns: - column: name: "id" type: "BIGINT" autoIncrement: "true" constraints: primaryKey: "true" - column: name: "name" type: "VARCHAR(255)" constraints: nullable: "false" unique: "true" - changeSet: author: "tiago" id: "creates_book_table" changes: - createTable: tableName: "book" columns: - column: name: "id" type: "BIGINT" autoIncrement: "true" constraints: primaryKey: "true" - column: name: "title" type: "VARCHAR(255)" constraints: nullable: "false" unique: "true" - column: name: "library_id" type: "BIGINT" constraints: foreignKeyName: "fk_book_library" references: "library(id)" Differently from , when we fire up the server, the changelogs will be automatically executed. Let's see: Rails $ mvn spring-boot:run Taking a look at server's log, we notice that the two tables were created: : : INFO --- [           main] liquibase.executor.jvm.JdbcExecutor      : SELECT COUNT(*) FROM liquibase_test.DATABASECHANGELOG : : INFO --- [           main] l.c.StandardChangeLogHistoryService      : Reading liquibase_test.DATABASECHANGELOG : : INFO --- [           main] liquibase.executor.jvm.JdbcExecutor      : SELECT * FROM liquibase_test.DATABASECHANGELOG ORDER BY DATEEXECUTED ASC, ORDEREXECUTED ASC : : INFO --- [           main] liquibase.executor.jvm.JdbcExecutor      : SELECT COUNT(*) FROM liquibase_test.DATABASECHANGELOGLOCK : : INFO --- [           main] liquibase.executor.jvm.JdbcExecutor      : CREATE TABLE liquibase_test.library (id BIGINT AUTO_INCREMENT NOT NULL, name VARCHAR( ) NOT NULL, CONSTRAINT PK_LIBRARY PRIMARY KEY (id), UNIQUE (name)) : : INFO --- [           main] liquibase.changelog.ChangeSet            : Table library created : : INFO --- [           main] liquibase.changelog.ChangeSet            : ChangeSet db/changelog/ _create_book_and_library_tables.yml::creates_library_table::tiago ran successfully ms : : INFO --- [           main] liquibase.executor.jvm.JdbcExecutor      : SELECT MAX(ORDEREXECUTED) FROM liquibase_test.DATABASECHANGELOG : : INFO --- [           main] liquibase.executor.jvm.JdbcExecutor      : INSERT INTO liquibase_test.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, , COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ( , , , NOW(), , , , , , NULL, NULL, , ) : : INFO --- [           main] liquibase.executor.jvm.JdbcExecutor      : CREATE TABLE liquibase_test.book (id BIGINT AUTO_INCREMENT NOT NULL, title VARCHAR( ) NOT NULL, library_id BIGINT NULL, CONSTRAINT PK_BOOK PRIMARY KEY (id), CONSTRAINT fk_book_library FOREIGN KEY (library_id) REFERENCES liquibase_test.library(id), UNIQUE (title)) : : INFO --- [           main] liquibase.changelog.ChangeSet            : Table book created : : INFO --- [           main] liquibase.changelog.ChangeSet            : ChangeSet db/changelog/ _create_book_and_library_tables.yml::creates_book_table::tiago ran successfully ms : : INFO --- [           main] liquibase.executor.jvm.JdbcExecutor      : INSERT INTO liquibase_test.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, , COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ( , , , NOW(), , , , , , NULL, NULL, , ) : : INFO --- [           main] l.lockservice.StandardLockService        : Successfully released change log lock 2019 -03 -06 23 17 14.103 29090 2019 -03 -06 23 17 14.104 29090 from 2019 -03 -06 23 17 14.105 29090 2019 -03 -06 23 17 14.106 29090 2019 -03 -06 23 17 14.123 29090 255 2019 -03 -06 23 17 14.145 29090 2019 -03 -06 23 17 14.145 29090 1 in 23 2019 -03 -06 23 17 14.146 29090 2019 -03 -06 23 17 14.148 29090 `DESCRIPTION` 'creates_library_table' 'tiago' 'db/changelog/1_create_book_and_library_tables.yml' 1 '8:a4b142ffda1ccd5c1840ddad83e249b5' 'createTable tableName=library' '' 'EXECUTED' '3.6.3' '1925034107' 2019 -03 -06 23 17 14.151 29090 255 2019 -03 -06 23 17 14.173 29090 2019 -03 -06 23 17 14.174 29090 1 in 24 2019 -03 -06 23 17 14.175 29090 `DESCRIPTION` 'creates_book_table' 'tiago' 'db/changelog/1_create_book_and_library_tables.yml' 2 '8:a54634bf0781ac011818976bf5e36351' 'createTable tableName=book' '' 'EXECUTED' '3.6.3' '1925034107' 2019 -03 -06 23 17 14.186 29090 Let's check them in : MySQL Great. Now, using , let's access the ' ' resource: cURL Book No books as expected. The same occurs with ' ' resource: Library Changelog #2 and #3: initialization data Let's see how we can initialize our tables. This is ' ': 2_insert_data_books.yml databaseChangeLog: - changeSet: author: "tiago" id: "insert_data_books" changes: - insert: tableName: "book" columns: - column: name: "title" value: "Test Book 1" And this is ' ': 3_insert_data_library.yml databaseChangeLog: - changeSet: author: "tiago" id: "insert_data_library" changes: - insert: tableName: "library" columns: - column: name: "name" value: "Library 1" Now let's try something different. Instead of firing up the server to make to run these changelogs, we'll do it by using the : Liquibase Maven plugin $ mvn liquibase:update -Dliquibase.changeLogFile=db/liquibase-changelog.yml This is the output: [INFO] INSERT INTO book (title) VALUES ( )
[INFO] New row inserted into book
[INFO] INSERT INTO book (title) VALUES ( )
[INFO] New row inserted into book

...
[INFO] INSERT INTO library (name) VALUES ( )
[INFO] New row inserted into library
.... 'Test Book 1' 'Test Book 2' 'Library 1' Then, if we fire up the server and access ' ' resource again... Book Take a look at this: : { : } "library" "href" "http://localhost:8080/books/1/library" We'll use this URL to associate this book to ' ' soon. Library 1 Calling ' ' resource: Library Now we'll associate ' ' to ' ': Test Book 1 Library 1 $ curl -i -X PUT -H -d http: "Content-Type:text/uri-list" "http://localhost:8080/libraries/1" //localhost:8080/books/1/library Then we can check if it worked, by querying the ' ' association of our book: library Changelog #4: adding columns During the development, we found necessary to add ' ' and ' ' fields to our ' ' table. isbn publisher book The first step is to create the changelog file. This is ' ': 4_add_isbn_and_publisher_to_book.yml databaseChangeLog: - changeSet: author: "tiago" id: "add_isbn_and_publisher_to_book" changes: - addColumn: columns: - column: name: "isbn" type: "VARCHAR(255)" constraints: nullable: "false" - column: name: "publisher" type: "VARCHAR(255)" constraints: nullable: "false" tableName: "book" Then, let's run the changelog: $ mvn liquibase:update -Dliquibase.changeLogFile=db/liquibase-changelog.yml This is the output: [INFO] ALTER TABLE book ADD isbn VARCHAR( ) NOT NULL, ADD publisher VARCHAR( ) NOT NULL
[INFO] Columns isbn(VARCHAR( )),publisher(VARCHAR( )) added to book
[INFO] ChangeSet db/changelog/ _add_isbn_and_publisher_to_book.yml::add_isbn::tiago ran successfully ms 255 255 255 255 4 in 60 Let's check it in : MySQL Great. The second step is to change our ' ' entity to add these two new fields: Book com.tiago.entity; javax.persistence.Column; javax.persistence.Entity; javax.persistence.GeneratedValue; javax.persistence.GenerationType; javax.persistence.Id; javax.persistence.JoinColumn; javax.persistence.ManyToOne; { (strategy = GenerationType.IDENTITY) Long id; (nullable= ) String title; (name= ) Library library; (nullable= ) String isbn; (nullable= ) String publisher; { id;
  } { .id = id;
  } { title;
  } { .title = title;
  } { library;
  } { .library = library;
  } { isbn;
  } { .isbn = isbn;
  } { publisher;
  } { .publisher = publisher;
  }
} package import import import import import import import /**
 * Entity for table "Book" 
 * 
 * Tiago Melo (tiagoharris@gmail.com)
 *
 */ @author @Entity public class Book @Id @GeneratedValue private @Column false private @ManyToOne @JoinColumn "library_id" private @Column false private @Column false private Long public getId () return public void setId (Long id) this String public getTitle () return public void setTitle (String title) this Library public getLibrary () return public void setLibrary (Library library) this String public getIsbn () return public void setIsbn (String isbn) this String public getPublisher () return public void setPublisher (String publisher) this Now let's update our book to set ' ' and ' ': isbn publisher Changelog #5: dropping a column The ' ' field is not necessary anymore. publisher The first step is to create the changelog file. This is ' ': 5_drop_publisher_from_book.yml databaseChangeLog: - changeSet: author: "tiago" id: "drop_publisher_from_book" changes: - dropColumn: columnName: "publisher" tableName: "book" Then, let's run the changelog: $ mvn liquibase:update -Dliquibase.changeLogFile=db/liquibase-changelog.yml This is the output: [INFO] ALTER TABLE book DROP COLUMN publisher
[INFO] Column book.publisher dropped Let's check it in : MySQL OK. The second step is to change our ' ' entity to remove ' ' property: Book publisher com.tiago.entity; javax.persistence.Column; javax.persistence.Entity; javax.persistence.GeneratedValue; javax.persistence.GenerationType; javax.persistence.Id; javax.persistence.JoinColumn; javax.persistence.ManyToOne; { (strategy = GenerationType.IDENTITY) Long id; (nullable= ) String title; (name= ) Library library; (nullable= ) String isbn; { id;
  } { .id = id;
  } { title;
  } { .title = title;
  } { library;
  } { .library = library;
  } { isbn;
  } { .isbn = isbn;
  }
} package import import import import import import import /**
 * Entity for table "Book" 
 * 
 * Tiago Melo (tiagoharris@gmail.com)
 *
 */ @author @Entity public class Book @Id @GeneratedValue private @Column false private @ManyToOne @JoinColumn "library_id" private @Column false private Long public getId () return public void setId (Long id) this String public getTitle () return public void setTitle (String title) this Library public getLibrary () return public void setLibrary (Library library) this String public getIsbn () return public void setIsbn (String isbn) this Now let's fire up the server and check our ' ' resource: Book Great! The ' ' property does not exists anymore. publisher Conclusion Versioning database changes is as important as versioning source code, and tools like makes it possible to do it in a safe and manageable way. Liquibase Through this simple example we learnt how we can evolve database in a Java application by integrating with . Liquibase Spring Boot Download the source Here: https://bitbucket.org/tiagoharris/liquibase-hibernate-example/src/master/

BUNCH

Go: Handling JSON in MySQL

Read My Stories

Too Long; Didn't Read

Make resilience your competitive advantage

Integrate Java Database Versioning with Liquibase using MySQL [A Step by Step Guide]

About Author

Comments

TOPICS

THIS ARTICLE WAS FEATURED IN

Related Stories

Untitled Story

Golang: Debugging CLI Applications in VSCode

10 Ways to Optimize Your Database

10 Ways to Reduce Data Loss and Potential Downtime Of Your Database

10 Principles of Proper Database Benchmarking

10 Minute Guide to Fixing Damaged SQL Databases - No Recovery Required!

Golang: Debugging CLI Applications in VSCode

10 Ways to Optimize Your Database

10 Ways to Reduce Data Loss and Potential Downtime Of Your Database

10 Principles of Proper Database Benchmarking

10 Minute Guide to Fixing Damaged SQL Databases - No Recovery Required!

Light-Mode

Classic

Newspaper

Dark-Mode

Neon Noir

Minty

HN StartUps