If you are working with Spring Boot there are several ways to initialize the database.
You could create a
schema.sql, add it to the classpath and set
Spring will execute the SQL script on startup.
Or you could use the Hibernate built-in functionality and set
But both approaches are very limited and if you have to deal with an evolving database you may look for something more sophisticated.
That’s where database migration tools fill the gap.
Database migration tools
In the Java environment, there are two open source projects available: Flyway and Liquibase. The difference between these two is that Flyway uses SQL for the migrations and Liquibase supports additionally abstractions in XML, YAML or JSON.
Both tools are very well integrated with Spring Boot. You simply have to add the right dependency in your pom.xml or Gradle build file.
Read more about why you should care about database migrations here.
Flyway with Spring Boot
In my example, I use Flyway with Spring Boot. To use it simply add this dependency and Spring Boot will auto-configure Flyway right away.
<dependency> <groupId>org.flywaydb</groupId> <artifactId>flyway-core</artifactId> </dependency>
Now you’re ready to start adding your migration SQL files to your classpath in the path
To have Flyway execute your SQL scripts you have to follow some naming conventions.
- Prefix (V, U or R)
- V = Versioned migrations that are executed only once
- U = Undo migrations (only supported in the commercial version)
- R = Repeatable migrations. Scripts that could run with every execution
- Separator __
- Suffix: (.sql)
V1__Init.sql could look like
CREATE TABLE address ( id integer NOT NULL, city character varying(255), state character varying(255), street character varying(255), zip character varying(255), created_date timestamp, last_modified_date timestamp );
How it Works
Flyway uses a table to store the version of the database. The default name of this table is flyway_schema_history and the content looks like:
As you can see Flyway stores the actual version and is, therefore, able to know which migrations scripts have to be executed.
That was just a brief introduction. Flyway has way more to offer and you will find all you need to know in the official documentation on flywaydb.org