How to get Slick create table statements to use InnoDB

I've recently picked up Slick and Scala, and realised my database tables created by Slick where using MYISAM, seems this is the default engine on my MySQL config and Slick create table statements do not specify engine. That's all good but I wanted to change to InnoDB without changing my default table engine in MySQL. Slick documentation has nothing on the matter. Is there anyway to change table engine with Slick (before tables creation) without modying default engine in MySQL my.ini ?

Solution

You can define the engine or otherwise extend the query by manipulating the statements:

    val statements: Iterator[String] = myTableQuery.schema.createStatements
    val action: SqlAction[Int, NoStream, Effect] =
      sqlu"#${createStatement.mkString(" ")} engine=InnoDB"
    db.run(createAction)

Take care to use the # verbatim interpolation prefix for the sqlu interpolator - without it, the statements will be escaped, leading to a syntax error.

EDIT:

I have noticed that the approach does not work if the DDL generates multiple statements, like additional alter table.... In this case, there is an even less elegant solution:

def createTable(ddl: profile.DDL, db: Database)(implicit ec: ExecutionContext): Future[Int] = {

  val mapped = ddl.createStatements.toList map (
      s => if (s.startsWith("create table")) s"$s engine=InnoDB" else s
  )
  mapped.foldLeft(Future.successful(1)) {
    case (acc: Future[Int], s: String) =>
      acc.flatMap(_ => db.run(sqlu"#$s"))
  }
}

You cannot use traverse or map to create the futures, because alter table depends on the table being already present. Use flatMap for sequential execution.