Scala Slick, how to create Schema ONLY if it does not exist

Question

In Scala Slick, a database schema can be created with the following:

val schema = coffees.schema ++ suppliers.schema
db.run(DBIO.seq(
  schema.create
))

From the bottom of this documentation page http://slick.typesafe.com/doc/3.0.0/schemas.html

However, if the database schema already exists then this throws an exception.

Is there a normal way or right way to create the schema IF AND ONLY IF it does not already exist?

Dmitrii Nikiforov · Answer 1 · 2019-11-08T04:06:45.070

20

In Slick 3.3.0 createIfNotExists and dropIfExists schema methods were added. So:

db.run(coffees.schema.createIfNotExists)

Googled this question and tried several solutions from answers until figured it out.

edited Nov 08 '19 at 04:06

answered Nov 07 '19 at 15:49

Dmitrii Nikiforov

301
4
6

Thanks, this perfectly answers the Question and should be accepted. – Jethro Nov 08 '19 at 21:35

score 11 · Accepted Answer · answered Dec 20 '16 at 06:33

This is what I do for multiple tables, with slick 3.1.1 and Postgres

import slick.driver.PostgresDriver.api._
import slick.jdbc.meta.MTable
import scala.concurrent.Await
import scala.concurrent.duration.Duration
import scala.concurrent.ExecutionContext.Implicits.global

val t1 = TableQuery[Table1]
val t2 = TableQuery[Table2]
val t3 = TableQuery[Table3]
val tables = List(t1, t2, t3)

val existing = db.run(MTable.getTables)
val f = existing.flatMap( v => {
    val names = v.map(mt => mt.name.name)
    val createIfNotExist = tables.filter( table =>
        (!names.contains(table.baseTableRow.tableName))).map(_.schema.create)
    db.run(DBIO.sequence(createIfNotExist))
})
Await.result(f, Duration.Inf)

score 7 · Answer 3 · answered Dec 21 '15 at 05:32

7

With Slick 3.0, Mtable.getTables is a DBAction so something like this would work:

val coffees = TableQuery[Coffees]
try {
  Await.result(db.run(DBIO.seq(
    MTable.getTables map (tables => {
      if (!tables.exists(_.name.name == coffees.baseTableRow.tableName))
        coffees.schema.create
    })
  )), Duration.Inf)
} finally db.close

answered Dec 21 '15 at 05:32

Mike S

935
2
6
18

1

I keep getting an execution context error for this, the error goes away if I import `scala.concurrent.ExecutionContext.Implicits.global` but then the table doesn't get created. How can I solve that? – JoshSGman Jul 24 '16 at 15:36
Try to change map to flatMap in `MTable.getTables map` – Sergei Koledov Dec 19 '18 at 10:19

score 5 · Answer 4 · edited May 23 '17 at 10:29

As JoshSGoman comment points out about the answer of Mike-s, the table is not created. I managed to make it work by slightly modifying the first answer's code :

val coffees = TableQuery[Coffees]

try {
  def createTableIfNotInTables(tables: Vector[MTable]): Future[Unit] = {
    if (!tables.exists(_.name.name == events.baseTableRow.tableName)) {
      db.run(coffees.schema.create)
    } else {
      Future()
    }
  }

  val createTableIfNotExist: Future[Unit] = db.run(MTable.getTables).flatMap(createTableIfNotInTables)

  Await.result(createTableIfNotExist, Duration.Inf)
} finally db.close

With the following imports :

import slick.jdbc.meta.MTable
import slick.driver.SQLiteDriver.api._

import scala.concurrent.{Await, Future}
import scala.concurrent.duration.Duration
import scala.concurrent.ExecutionContext.Implicits.global

Why do we need to declare db.run twice? Shouldn't coffees.schema.create still work in this context? — JoshSGman, Oct 25 '16 at 21:04

score 4 · Answer 5 · answered Nov 26 '15 at 04:44

4

why don't you simply check the existence before create?

val schema = coffees.schema ++ suppliers.schema
db.run(DBIO.seq(
  if (!MTable.getTables.list.exists(_.name.name == MyTable.tableName)){
    schema.create
  }
))

answered Nov 26 '15 at 04:44

suish

3,253
1
15
34

score 2 · Answer 6 · answered Nov 18 '19 at 15:23

cannot use createIfNotExists on schema composed of 3 tables with composite primary key on one of the tables. Here, the 3rd table has a primary key composed from the the primary key of each of the 1st and 2nd table. I get an error on this schema when .createIfNotExists is encountered a 2nd time. I am using slick 3.3.1 on scala 2.12.8.

    class UserTable(tag: Tag) extends Table[User](tag, "user") {
      def id    = column[Long]("id", O.AutoInc, O.PrimaryKey)
      def name  = column[String]("name")
      def email = column[Option[String]]("email")

      def * = (id.?, name, email).mapTo[User]
    }
    val users = TableQuery[UserTable]
    lazy val insertUser = users returning users.map(_.id)

    case class Room(title: String, id: Long = 0L)
    class RoomTable(tag: Tag) extends Table[Room](tag, "room") {
     def id    = column[Long]("id", O.PrimaryKey, O.AutoInc)
     def title = column[String]("title")
     def * = (title, id).mapTo[Room]
    }
    val rooms = TableQuery[RoomTable]
    lazy val insertRoom = rooms returning rooms.map(_.id)

    case class Occupant(roomId: Long, userId: Long)
    class OccupantTable(tag: Tag) extends Table[Occupant](tag, "occupant") {
      def roomId = column[Long]("room")
      def userId = column[Long]("user")

      def pk = primaryKey("room_user_pk", (roomId, userId) )

      def * = (roomId, userId).mapTo[Occupant]
    }
    val occupants = TableQuery[OccupantTable]

I can successfully create schema and add user, room and occupant at first. On the second usage of .createIfNotExists as follows below, I get an error on duplicate primary key:

  println("\n2nd run on .createIfNotExists using different values for users, rooms and occupants")
  val initdup = for {
    _         <- users.schema.createIfNotExists
    _         <- rooms.schema.createIfNotExists
    _         <- occupants.schema.createIfNotExists
      curlyId   <- insertUser += User(None, "Curly", Some("curly@example.org"))
      larryId   <- insertUser += User(None, "Larry")
      moeId     <- insertUser += User(None, "Moe", Some("moe@example.org"))
      shedId   <- insertRoom += Room("Shed")
      _         <- occupants += Occupant(shedId, curlyId)
      _         <- occupants += Occupant(shedId, moeId)
    } yield ()

The exception is as below:

2nd run on .createIfNotExists using different values for users, rooms and occupants
[error] (run-main-2) org.h2.jdbc.JdbcSQLException: Constraint "room_user_pk" already exists; SQL statement:
[error] alter table "occupant" add constraint "room_user_pk" primary key("room","user") [90045-197]
[error] org.h2.jdbc.JdbcSQLException: Constraint "room_user_pk" already exists; SQL statement:
[error] alter table "occupant" add constraint "room_user_pk" primary key("room","user") [90045-197]
[error]         at org.h2.message.DbException.getJdbcSQLException(DbException.java:357)
[error]         at org.h2.message.DbException.get(DbException.java:179)
[error]         at org.h2.message.DbException.get(DbException.java:155)
[error]         at org.h2.command.ddl.AlterTableAddConstraint.tryUpdate(AlterTableAddConstraint.java:110)
[error]         at org.h2.command.ddl.AlterTableAddConstraint.update(AlterTableAddConstraint.java:78)
[error]         at org.h2.command.CommandContainer.update(CommandContainer.java:102)
[error]         at org.h2.command.Command.executeUpdate(Command.java:261)
[error]         at org.h2.jdbc.JdbcPreparedStatement.execute(JdbcPreparedStatement.java:249)
[error]         at slick.jdbc.JdbcActionComponent$SchemaActionExtensionMethodsImpl$$anon$6.$anonfun$run$7(JdbcActionComponent.scala:292)
[error]         at slick.jdbc.JdbcActionComponent$SchemaActionExtensionMethodsImpl$$anon$6.$anonfun$run$7$adapted(JdbcActionComponent.scala:292)
[error]         at slick.jdbc.JdbcBackend$SessionDef.withPreparedStatement(JdbcBackend.scala:425)
[error]         at slick.jdbc.JdbcBackend$SessionDef.withPreparedStatement$(JdbcBackend.scala:420)
[error]         at slick.jdbc.JdbcBackend$BaseSession.withPreparedStatement(JdbcBackend.scala:489)
[error]         at slick.jdbc.JdbcActionComponent$SchemaActionExtensionMethodsImpl$$anon$6.$anonfun$run$6(JdbcActionComponent.scala:292)
[error]         at slick.jdbc.JdbcActionComponent$SchemaActionExtensionMethodsImpl$$anon$6.$anonfun$run$6$adapted(JdbcActionComponent.scala:292)
[error]         at scala.collection.Iterator.foreach(Iterator.scala:941)
[error]         at scala.collection.Iterator.foreach$(Iterator.scala:941)
[error]         at scala.collection.AbstractIterator.foreach(Iterator.scala:1429)
[error]         at scala.collection.IterableLike.foreach(IterableLike.scala:74)
[error]         at scala.collection.IterableLike.foreach$(IterableLike.scala:73)
[error]         at scala.collection.AbstractIterable.foreach(Iterable.scala:56)
[error]         at slick.jdbc.JdbcActionComponent$SchemaActionExtensionMethodsImpl$$anon$6.run(JdbcActionComponent.scala:292)
[error]         at slick.jdbc.JdbcActionComponent$SchemaActionExtensionMethodsImpl$$anon$6.run(JdbcActionComponent.scala:290)
[error]         at slick.jdbc.JdbcActionComponent$SimpleJdbcProfileAction.run(JdbcActionComponent.scala:28)
[error]         at slick.jdbc.JdbcActionComponent$SimpleJdbcProfileAction.run(JdbcActionComponent.scala:25)
[error]         at slick.basic.BasicBackend$DatabaseDef$$anon$3.liftedTree1$1(BasicBackend.scala:276)
[error]         at slick.basic.BasicBackend$DatabaseDef$$anon$3.run(BasicBackend.scala:276)
[error]         at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
[error]         at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
[error]         at java.lang.Thread.run(Thread.java:748)
[error] Nonzero exit code: 1
[error] (Compile / run) Nonzero exit code: 1

Additionally, I can use .createIfNotExists more than once on schema where all tables are created with O.PrimaryKey convention.

Am I able to do something to massage code? Is there a workaround so that .createIfNotExists is still usable on composite primary key case?

I Note that removing " _ <- occupants.schema.createIfNotExists" is only solution so far. — Sam Dawisha, Nov 18 '19 at 15:27
Do you suggest that the currently selected answer is wrong and I should change to a different answer? — Phil, Nov 19 '19 at 01:57
Answer given for your question is correct. @ДмитрийНикифоров correctly suggested using createIfNotExists for pre-existing tables in your one-to-many database design problem. And this will work in your slick code. Sorry if I introduced any confusion. I have different issue--pre-existing tables in a many-to-many database design--which can't be solved with createIfNotExists. And, I shall post a new question. — Sam Dawisha, Nov 19 '19 at 15:32

Scala Slick, how to create Schema ONLY if it does not exist

6 Answers6