Slick: create database

Question

Is there a way to get slick to create the database if it doesn't already exist?

Database.forURL("jdbc:mysql://127.0.0.1/database", driver = "com.mysql.jdbc.Driver", user = "root") withSession {
  // create tables, insert data
}

"database" doesn't exist, so I want slick to create it for me. Any ideas? Thanks.

kumetix · Answer 1 · 2015-12-01T13:12:42.000

The answer above is relevant to Slick 2.x where withSession is deprecated, so this is how it is done with Slick 3.0.0 API :

import scala.concurrent.Await
import scala.concurrent.duration._
import org.postgresql.util.PSQLException
import slick.driver.PostgresDriver
import slick.driver.PostgresDriver.api._

object SlickPGUtils {

  private val actionTimeout = 10 second
  private val driver = "org.postgresql.Driver"

  def createDb(host: String, port: Int, dbName: String, user: String, pwd: String) = {
    val onlyHostNoDbUrl = s"jdbc:postgresql://$host:$port/"
    using(Database.forURL(onlyHostNoDbUrl, user = user, password = pwd, driver = driver)) { conn =>
      Await.result(conn.run(sqlu"CREATE DATABASE #$dbName"), actionTimeout)
    }
  }

  def dropDb(host: String, port: Int, dbName: String, user: String, pwd: String) = {
    val onlyHostNoDbUrl = s"jdbc:postgresql://$host:$port/"
    try {
      using(Database.forURL(onlyHostNoDbUrl, user = user, password = pwd, driver = driver)) { conn =>
        Await.result(conn.run(sqlu"DROP DATABASE #$dbName"), actionTimeout)
      }
    } catch {
      // ignore failure due to db not exist
      case e:PSQLException => if (e.getMessage.equals(s""""database "$dbName" does not exist""")) {/* do nothing */}
      case e:Throwable => throw e // escalate other exceptions
    }
  }

  private def using[A <: {def close() : Unit}, B](resource: A)(f: A => B): B =
    try {
      f(resource)
    } finally {
      Try {
        resource.close()
      }.failed.foreach(err => throw new Exception(s"failed to close $resource", err))
    }
}

score 9 · Accepted Answer · answered Dec 25 '13 at 06:22

9

You can connect to the database engine using only "jdbc:mysql://localhost/" as JDBC URL and then issue a pure SQL create database query:

import scala.slick.driver.MySQLDriver.simple._
import scala.slick.jdbc.{StaticQuery => Q}

object Main extends App
{
    Database.forURL("jdbc:mysql://localhost/", driver = "com.mysql.jdbc.Driver") withSession {
        implicit session =>
            Q.updateNA("CREATE DATABASE `dataBaseName`").execute
        .
        .
        .
    }
}

answered Dec 25 '13 at 06:22

Răzvan Flavius Panda

21,730
17
111
169

thanks @Răzvan, can you tell how to first check if the db exist? Also, withSession is deprecated in slick 3, and I still haven't figured out what is the equivalent in the new API. any idea? – kumetix Nov 29 '15 at 07:53
You could use http://stackoverflow.com/a/838993/750216. For withSession see: http://grokbase.com/t/gg/scalaquery/15250knfya/slick-3-removes-withsession-dont-get-caught-by-surprise – Răzvan Flavius Panda Nov 30 '15 at 01:00

Slick: create database

2 Answers2