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…”

Source: Lightbend Slick: http://slick.lightbend.com/

Features of „Scala-Forklift“

Migration scripts

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.

Hands-On

Goals

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.

Prerequesites

• Scala (tested with version 2.11.8)
• SBT (tested with version 0.13.9)
• IntelliJ (tested with version 15.0.4)

Setup

As first step the project needs to be created using the Scala-Forklift template. Simply clone the following GitHub respository:

Checkout the following version:

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.

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.

1. Open a command line and navigate to the root folder of your project.
2. Type in sbt to start the SBT console.
3. Execute 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/.
4. Use the command sqlu  to create our first table. sqlu  is a string interpolator for writing plain SQL in Slick.
   
   1.scala

   import slick.driver.H2Driver.api._ import com.liyaos.forklift.slick.SqlMigration object M1 { MyMigrations.migrations = MyMigrations.migrations :+ SqlMigration(1)(List( sqlu"""create table "movies" ("id" INTEGER NOT NULL PRIMARY KEY,"name" VARCHAR NOT NULL)""" )) }

   1 2 3 4 5 6 7 8

   import slick.driver.H2Driver.api._ import com.liyaos.forklift.slick.SqlMigration   object M1 {   MyMigrations.migrations = MyMigrations.migrations :+ SqlMigration(1)(List(     sqlu"""create table "movies" ("id" INTEGER NOT NULL PRIMARY KEY,"name" VARCHAR NOT NULL)"""   )) }

5. The file %root%/migrations/src/main/scala/Codegen.scala lists all tables the generator should consider. Add the table movies to the tableNames.
   
   Codegen.scala

   trait Codegen extends MyCodegen { override def tableNames = List("movies") }

   1 2 3

   trait Codegen extends MyCodegen {     override def tableNames = List("movies") }

Step 2: Alter your table

1. To create another SQL migration, type in mg new s  in your SBT console.
2. Now use sqlu  to alter the table and add the description row:
   
   2.scala

   Scala

   import slick.driver.H2Driver.api._ import com.liyaos.forklift.slick.SqlMigration object M2 { MyMigrations.migrations = MyMigrations.migrations :+ SqlMigration(2)(List( sqlu"""alter table "movies" add column "description" VARCHAR NOT NULL""" )) }

   1 2 3 4 5 6 7 8

   import slick.driver.H2Driver.api._ import com.liyaos.forklift.slick.SqlMigration   object M2 {   MyMigrations.migrations = MyMigrations.migrations :+ SqlMigration(2)(List( sqlu"""alter table "movies" add column "description" VARCHAR NOT NULL""" )) }

Step 3: Type-safe data migration

1. For our third migration we use mg new d . This creates 3.scala and prepares it for type-safe Slick queries.
2. Implement your type-safe migrations as DBIO actions. Insert data into the table as shown below:
   
   3.scala

   Scala

   import slick.driver.H2Driver.api._ import com.liyaos.forklift.slick.DBIOMigration import datamodel.v2.schema.tables._ object M3 { MyMigrations.migrations = MyMigrations.migrations :+ DBIOMigration(3)( DBIO.seq(Movies ++= Seq( MoviesRow(1, "Pulp Fiction", "A movie about gangsters, made by Quentin Tarantino"), MoviesRow(2, "Star Wars", "Probably the most famous science fiction movie") ))) }

   1 2 3 4 5 6 7 8 9 10 11

   import slick.driver.H2Driver.api._ import com.liyaos.forklift.slick.DBIOMigration import datamodel.v2.schema.tables._   object M3 {   MyMigrations.migrations = MyMigrations.migrations :+ DBIOMigration(3)(     DBIO.seq(Movies ++= Seq(       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

1. Execute the command mg init  to initialize your database. The table __migrations__ is created, this table tracks which scripts have been applied.
2. Use ~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.

1. In your application sub-project, you find the App.scala. Import the current schema via:
   
   App.scala

   Scala

   import datamodel.latest.schema.tables._

   1	import datamodel.latest.schema.tables._

2. 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.
   
   App.scala

   object Application { def main(args: Array[String]) { try { val f: Seq[MoviesRow] = Await.result(MyDatabase.db.run(Movies.result), Duration.Inf) f.foreach(println) } finally { MyDatabase.db.close() } } }

   1 2 3 4 5 6 7 8 9 10

   object Application {   def main(args: Array[String]) {     try {       val f: Seq[MoviesRow] = Await.result(MyDatabase.db.run(Movies.result), Duration.Inf)       f.foreach(println)    } finally {       MyDatabase.db.close()     }   } }

3. To run your application execute the command app/run in your SBT console. This should result in console output like this:
   

Step 6: Packaging

To package the application, SBT with SBT Native Packager is used.

1. To import the SBT Native Packager, create a plugins.sbt file inside your folder %root%/project. Add this content:
   
   plugins.sbt

   addSbtPlugin("com.typesafe.sbt" % "sbt-native-packager" % "1.0.3")

   1	addSbtPlugin("com.typesafe.sbt" % "sbt-native-packager" % "1.0.3")

2. To package the project we aggregate the sub-projects and define the entry point of the application. Add the following code snippet into your build.sbt:
   
   build.sbt

   lazy val tutorial = Project("tutorial", file(".")) .aggregate(app, migrations, migrationManager, generatedCode, tools) .dependsOn(app) .settings(commonSettings:_*) .settings(mainClass in Compile := Some("Application"))

   1 2 3 4 5

   lazy val tutorial = Project("tutorial", file("."))   .aggregate(app, migrations, migrationManager, generatedCode, tools)   .dependsOn(app)   .settings(commonSettings:_*)   .settings(mainClass in Compile := Some("Application"))

   
   Source: Scala-Forklift: https://github.com/lastland/scala-forklift/tree/develop/example
   ‌
   mainclass in Compile := Some(„Application“)  (line 5) defines Application as the main class of our project. This also enables us to use run instead of app/run, after restarting the SBT console.
   ‌
3. 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:
   
   build.sbt

   import com.typesafe.sbt.packager.MappingsHelper._ import com.typesafe.sbt.packager.archetypes.JavaAppPackaging enablePlugins(JavaAppPackaging) // -- mappings for the database migrations -- mappings in Universal ++= contentOf(baseDirectory.value / "migrations").map { case (file, dest) => file -> s"db/migrations/$dest" } mappings in Universal ++= contentOf(baseDirectory.value / "migration_manager").map { case (file, dest) => file -> s"db/migration_manager/$dest" } mappings in Universal ++= contentOf(baseDirectory.value / "generated_code").map { case (file, dest) => file -> s"db/generated_code/$dest" } mappings in Universal ++= contentOf(baseDirectory.value / "project").map { case (file, dest) => file -> s"db/project/$dest" } mappings in Universal ++= contentOf(baseDirectory.value / "app" / "src" / "main" / "resources").map { case (file, dest) => file -> s"db/app/src/main/resources/$dest" } mappings in Universal += { ((baseDirectory in Compile).value / "build.sbt") -> "db/build.sbt" }

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

   import com.typesafe.sbt.packager.MappingsHelper._ import com.typesafe.sbt.packager.archetypes.JavaAppPackaging   enablePlugins(JavaAppPackaging)   // -- mappings for the database migrations -- mappings in Universal ++= contentOf(baseDirectory.value / "migrations").map { case (file, dest) => file -> s"db/migrations/$dest" } mappings in Universal ++= contentOf(baseDirectory.value / "migration_manager").map { case (file, dest) => file -> s"db/migration_manager/$dest" } mappings in Universal ++= contentOf(baseDirectory.value / "generated_code").map { case (file, dest) => file -> s"db/generated_code/$dest" } mappings in Universal ++= contentOf(baseDirectory.value / "project").map { case (file, dest) => file -> s"db/project/$dest" } mappings in Universal ++= contentOf(baseDirectory.value / "app" / "src" / "main" / "resources").map { case (file, dest) => file -> s"db/app/src/main/resources/$dest" } mappings in Universal += { ((baseDirectory in Compile).value / "build.sbt") -> "db/build.sbt" }

   
   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.
   ‌
4. 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. 😉

Links

Scala-Forklift: https://github.com/lastland/scala-forklift

Slick: http://slick.lightbend.com/

Scala: http://www.scala-lang.org/

IntelliJ: https://www.jetbrains.com/idea/

SBT Native Packager: http://www.scala-sbt.org/sbt-native-packager/