Slick: Updates not available when fetched just after

Question

I was trying out this slick example and when I try to create an entry and then fetch that right after, I don't get the record. I modified the test case which is here as below.

val response = create(BankProduct("car loan", 1)).flatMap(getById)
whenReady(response) { p =>
  assert(p.get === BankProduct("car loan", 1))
}

The above fails because the created BankProduct cannot be fetched immediately. It is using h2 db for this and below is the configuration.

trait H2DBComponent extends DBComponent {

  val logger = LoggerFactory.getLogger(this.getClass)

  val driver = slick.driver.H2Driver

  import driver.api._

  val randomDB = "jdbc:h2:mem:test" + UUID.randomUUID().toString() + ";"

  val h2Url = randomDB + "MODE=MySql;DATABASE_TO_UPPER=false;INIT=runscript from 'src/test/resources/schema.sql'\\;runscript from 'src/test/resources/schemadata.sql'"

  val db: Database = {
    logger.info("Creating test connection")
    Database.forURL(url = h2Url, driver = "org.h2.Driver")
  }
}


private[repo] trait BankProductTable extends BankTable { this: DBComponent =>

  import driver.api._

  private[BankProductTable] class BankProductTable(tag: Tag) extends Table[BankProduct](tag, "bankproduct") {
    val id = column[Int]("id", O.PrimaryKey, O.AutoInc)
    val name = column[String]("name")
    val bankId = column[Int]("bank_id")
    def bank = foreignKey("bank_product_fk", bankId, bankTableQuery)(_.id)
    def * = (name, bankId, id.?) <> (BankProduct.tupled, BankProduct.unapply)

  }

  protected val bankProductTableQuery = TableQuery[BankProductTable]

  protected def bankProductTableAutoInc = bankProductTableQuery returning bankProductTableQuery.map(_.id)

}

I don't understand why this is happening and how to avoid this? I tried adding the propery autoCommit also but it didn't work either.

Appreciate any help to clarify this ambiguity.

Answer 1

This might be due to in-memory database content being lost after create call closes its connection. According to docs:

By default, closing the last connection to a database closes the database. For an in-memory database, this means the content is lost. To keep the database open, add ;DB_CLOSE_DELAY=-1 to the database URL. To keep the content of an in-memory database as long as the virtual machine is alive, use jdbc:h2:mem:test;DB_CLOSE_DELAY=-1.

However, after adding DB_CLOSE_DELAY=-1, there will be errors due to

runscript from 'src/test/resources/schemadata.sql' 

which is executed on each connection, thus refactoring is neccessary such that database is populated only once on initialization.