Database migration using Slick made easy – „Scala-Forklift“
You want to benefit from the power of type-safe Slick queries? This post will explain how to migrate databases using Slick and Scala-Forklift. Moreover, we will package our project with the SBT Native Packager.
What is Slick?
The developers describe Slick as
“…a modern database query and access library for Scala. It allows you to work with stored data almost as if you were using Scala collections while at the same time giving you full control over when a database access happens and which data is transferred…”
Commonly databases are updated with migration scripts. Scala-Forklift allows you to write these scripts in plain SQL or as type-safe Slick queries.
Automatic schema creation
Everytime you apply a migration script, Scala-Forklift will automatically create a new schema. This schema defines your database tables as scala classes. Inside these classes you find table row classes, which set the datatypes and constraints for the columns of a table. Queries using these schema definitions will be type-safe.
After this tutorial you will be able to:
create a Scala-Forklift project
use SQL and Slick migration scripts
query your database, based on generated schemas
package the project with the SBT Native Packager
For demonstration purposes a database containing movies, with their names and descriptions, will be created.
Import the template as SBT project in your IntelliJ IDE.
The config file scala-forklift-template/app/src/main/ressources/application.conf defines the path to your database. Change the URL to an absolute URL on your hard disk. For example:
The initial setup is done, let‘s take a look at the project structure.
app – your application code
migrations – the home of your migration scripts
migration_manager – the migration manager
generated_code – the generated schemas
tools – a Git tool to help you manage your database in developement
Knowing this, the database migration may start!
Step 1: Create the first table
First of all a table containing the basic information of movies needs to be created.
Open a command line and navigate to the root folder of your project.
sbt to start the SBT console.
mg new s to create a new plain SQL migration. A new Scala file named 1.scala appears inside scala-forklift-start-template/migrations/src_migrations/main/scala/.
Use the command
sqluto create our first table.
sqluis a string interpolator for writing plain SQL in Slick.
MoviesRow(1,"Pulp Fiction","A movie about gangsters, made by Quentin Tarantino"),
MoviesRow(2,"Star Wars","Probably the most famous science fiction movie")
Note: Possible compiler errors can safely be ignored. The reason: We have not generated the schemas yet.
Step 4: Migrate your database
Execute the command
mg init to initialize your database. The table __migrations__ is created, this table tracks which scripts have been applied.
~mg migrate to migrate your database. Press enter after the command finished and displays „Waiting for source changes…“.
Let’s take a closer look at the four steps which build up the mg migrate command:
mg update– checks which migration scripts have been applied and updates the Summary.scala. To compile the new migration scripts, links will be created in the source folder pointing to the uncompiled scripts.
mg preview – outputs the next migrations in the SBT console.
mg apply – updates the database, including the __migrations__ table.
mg codegen – creates a new schema file for every migration script. Your application should refer to the latest schema.
If you use the
mg migrate command with
~ as prefix, all migrations will be applied instead of just the next one. Now you can use your schemas to create type-safe Slick queries for the updated database.
Step 5: Make use of your database schema
To demonstrate how to use the database schemas, we write a simple example that prints the data of our movies table to the console.
In your application sub-project, you find the App.scala. Import the current schema via:
Write a Slick query to output all movies saved in your database. The MyDatabase object provides the database connection. The result of the query is a sequence of MoviesRow.
In addition to the binaries we add the sources to our package. These sources are used to migrate our database during deployment. To achieve this the build.sbt has to be enhanced with the following code:
enablePlugins(JavaAppPackaging) (line 4) activates the SBT Native Packager. The sources are packaged in a db folder by the command mappings. If you open this folder in the SBT console, you can use all Scala-Forklift commands you have learned about.
To zip the package, type in
universal:packageBin after reopening the SBT console. You will find the zip file in ./target/universal/. It contains three folders: bin – scripts to start your application db – resources used by Scala-Forklift to migrate the database lib – the application with its dependencies
Congratulations! You have successfully migrated your database using Scala-Forklift. Furthermore you have learned how to use the SBT Native Packager to make your project deployment ready. Now it’s up to you, to automate your deployment with your prefered CI server. 😉