Table existence check using Slick

I'm using Slick library in my project and need to ensure that all required MySQL tables are created on application start.

The common approach of creating DDL statements for a table in Slick is calling its ddl method that generates database-specific code and then executes it by calling create:

Declare case class Customer that is mapped to table Customers:

1
2
3
4
5
6
7
8
9
10

import scala.slick.driver.MySQLDriver.simple._

case class Customer(id: Option[Long], firstName: String, lastName: String)

object Customers extends Table[Customer]("customers") {
  def id = column[Long]("id", O.PrimaryKey, O.AutoInc)
  def firstName = column[String]("first_name")
  def lastName = column[String]("last_name")
  def * = id.? ~ firstName ~ lastName <> (Customer, Customer.unapply _)
}

... and execute DDL statements for Customers table somewhere in initialization code:

1
2
3
4
5
6
7
8

  ...
    val db = Database.forURL(url = "jdbc:mysql://localhost:3306/DB_NAME",
      user = "DB_USER", password = "DB_PASSWORD", driver = "com.mysql.jdbc.Driver")

    db.withSession {
      Customers.ddl.create
    }
  ...

All things are going nice at the first run, but on the next application start the following exception is thrown:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'customers' already exists

This means that Slick doesn't perform automatic table existence check. Thus, we should care about table existence check ourselves.

Hopefully, Slick library contains MTable object in scala.slick.jdbc.meta package, whose method getTables() allows obtaining metadata of all existing tables or those that match specified name pattern. Existence checks can be provided easily using this functionality. Table initialization code specified above can be updated:

1
2
3
4
5
6
7
8
9
10

  ...
  val db = Database.forURL(url = "jdbc:mysql://localhost:3306/DB_NAME",
    user = "DB_USER", password = "DB_PASSWORD", driver = "com.mysql.jdbc.Driver")

  db.withSession {
    if (MTable.getTables("customers").list().isEmpty) {
      Customers.ddl.create
    }
  }
  ...

Table "customers" will be created only if it does not exist.

Hope you find it helpful,