Scala Slick In-Memory Database SQLite No Such Table

Question

Using Scala 2.10.2 and Slick 2.1.0, I'm writing a wrapper for an SQLite database. For testing, I'd like the ability to create an in-memory database, populate it with my tables, insert some data, and then run test queries. A toy equivalent of my database is outlined in the following working code snippet:

import org.sqlite.JDBC
import scala.slick.driver.SQLiteDriver.simple._

object MyDB
{
    def testMemory() : Unit = MyDB.withMemoryTestDatabase( test )

    def testFile() : Unit = MyDB.withFileTestDatabase( test )

    val test : ( MyDB ) => Unit =
    {
        ( mydb ) => mydb.database.withSession{ implicit session => {
            mydb.metadata.map( m => ( m.key, m.value ) ).insert( ( "foo", "bar" ) )
            assert( mydb.metadata( "foo" ) == "bar" )
        } }
    }

    def withMemoryTestDatabase( testFunction : ( MyDB ) => Unit ) : Unit =
    {
        val backend = new MyDB( Database.forURL( "jdbc:sqlite::memory:", driver = "org.sqlite.JDBC" ) )

        runTest( backend, testFunction )
    }

    def withFileTestDatabase( testFunction : ( MyDB ) => Unit ) : Unit =
    {
        val backend = new MyDB( Database.forURL( "jdbc:sqlite:test.sqlite", driver = "org.sqlite.JDBC" ) )

        runTest( backend, testFunction )

        val dbPath = new java.io.File( "test.sqlite" )
        dbPath.delete
    }

    def runTest( backend : MyDB, testFunction : ( MyDB ) => Unit ) : Unit =
    {
        backend.database.withSession{ implicit session => backend.metadata.ddl.create }

        testFunction( backend )
    }
}

class MyDB( val database : slick.driver.SQLiteDriver.backend.DatabaseDef )
{
     class Metadata( tag : Tag ) extends Table[(String,String)]( tag, "Metadata" )
     {
         def key =   column[String]( "key" )
         def value = column[String]( "value" )

         def * = ( key, value )
     }

     object metadata extends TableQuery( new Metadata( _ ) )
     {
         def apply( key : String ) : String = database.withSession { implicit session => this.map( m => ( m.key, m.value ) ).run.toMap.apply( key ) }
     }       
}

The problem I'm encountering is that with that code, MyDB.testFile() runs perfectly. However, MyDB.testMemory() fails, saying that the Metadata table doesn't exist. The full error and traceback is here:

scala> MyDB.testFile

scala> MyDB.testMemory
java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (no such table: Metadata)
    at org.sqlite.DB.newSQLException(DB.java:383)
    at org.sqlite.DB.newSQLException(DB.java:387)
    at org.sqlite.DB.throwex(DB.java:374)
    at org.sqlite.NativeDB.prepare(Native Method)
    at org.sqlite.DB.prepare(DB.java:123)
    at org.sqlite.PrepStmt.<init>(PrepStmt.java:42)
    at org.sqlite.Conn.prepareStatement(Conn.java:404)
    at org.sqlite.Conn.prepareStatement(Conn.java:399)
    at scala.slick.jdbc.JdbcBackend$SessionDef$class.prepareStatement(JdbcBackend.scala:152)
    at scala.slick.jdbc.JdbcBackend$BaseSession.prepareStatement(JdbcBackend.scala:389)
    at scala.slick.jdbc.JdbcBackend$SessionDef$class.withPreparedStatement(JdbcBackend.scala:190)
    at scala.slick.jdbc.JdbcBackend$BaseSession.withPreparedStatement(JdbcBackend.scala:389)
    at scala.slick.driver.JdbcInsertInvokerComponent$BaseInsertInvoker.preparedInsert(JdbcInsertInvokerComponent.scala:170)
    at scala.slick.driver.JdbcInsertInvokerComponent$BaseInsertInvoker.internalInsert(JdbcInsertInvokerComponent.scala:180)
    at scala.slick.driver.JdbcInsertInvokerComponent$BaseInsertInvoker.insert(JdbcInsertInvokerComponent.scala:175)
    at MyDB$$anonfun$testMemory$1$$anonfun$apply$1.apply(MyDb.scala:9)
    at MyDB$$anonfun$testMemory$1$$anonfun$apply$1.apply(MyDb.scala:8)
    at scala.slick.backend.DatabaseComponent$DatabaseDef$class.withSession(DatabaseComponent.scala:34)
    at scala.slick.jdbc.JdbcBackend$DatabaseFactoryDef$$anon$4.withSession(JdbcBackend.scala:61)
    at MyDB$$anonfun$testMemory$1.apply(MyDb.scala:8)
    at MyDB$$anonfun$testMemory$1.apply(MyDb.scala:8)
    at MyDB$.withMemoryTestDatabase(MyDb.scala:28)
    at MyDB$.testMemory(MyDb.scala:8)
    at .<init>(<console>:8)
    at .<clinit>(<console>)
    at .<init>(<console>:7)
    at .<clinit>(<console>)
    at $print(<console>)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)
    at scala.tools.nsc.interpreter.IMain$ReadEvalPrint.call(IMain.scala:734)
    at scala.tools.nsc.interpreter.IMain$Request.loadAndRun(IMain.scala:983)
    at scala.tools.nsc.interpreter.IMain.loadAndRunReq$1(IMain.scala:573)
    at scala.tools.nsc.interpreter.IMain.interpret(IMain.scala:604)
    at scala.tools.nsc.interpreter.IMain.interpret(IMain.scala:568)
    at scala.tools.nsc.interpreter.ILoop.reallyInterpret$1(ILoop.scala:745)
    at scala.tools.nsc.interpreter.ILoop.interpretStartingWith(ILoop.scala:790)
    at scala.tools.nsc.interpreter.ILoop.command(ILoop.scala:702)
    at scala.tools.nsc.interpreter.ILoop.processLine$1(ILoop.scala:566)
    at scala.tools.nsc.interpreter.ILoop.innerLoop$1(ILoop.scala:573)
    at scala.tools.nsc.interpreter.ILoop.loop(ILoop.scala:576)
    at scala.tools.nsc.interpreter.ILoop$$anonfun$process$1.apply$mcZ$sp(ILoop.scala:867)
    at scala.tools.nsc.interpreter.ILoop$$anonfun$process$1.apply(ILoop.scala:822)
    at scala.tools.nsc.interpreter.ILoop$$anonfun$process$1.apply(ILoop.scala:822)
    at scala.tools.nsc.util.ScalaClassLoader$.savingContextLoader(ScalaClassLoader.scala:135)
    at scala.tools.nsc.interpreter.ILoop.process(ILoop.scala:822)
    at scala.tools.nsc.interpreter.ILoop.main(ILoop.scala:889)
    at xsbt.ConsoleInterface.run(ConsoleInterface.scala:69)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)
    at sbt.compiler.AnalyzingCompiler.call(AnalyzingCompiler.scala:102)
    at sbt.compiler.AnalyzingCompiler.console(AnalyzingCompiler.scala:77)
    at sbt.Console.sbt$Console$$console0$1(Console.scala:23)
    at sbt.Console$$anonfun$apply$2$$anonfun$apply$1.apply$mcV$sp(Console.scala:24)
    at sbt.TrapExit$.sbt$TrapExit$$executeMain$1(TrapExit.scala:33)
    at sbt.TrapExit$$anon$1.run(TrapExit.scala:42)

Can anyone show me where I'm going wrong?

N.B. the :memory: argument to the JDBC constructor seems to work; it certainly doesn't create a :memory: file.

I'm not sure, but if you're making more than one connection to the db then the data doesn't persist. See here: sqlite.org/inmemorydb.html. You may need to add the incantation '?cache=shared' to the end of the jdbc uri. — Alex Wilson
Sounds plausible, but a URL of jdbc:sqlite::memory:?cache=shared creates a file called :memory:?cache=shared, so that's not quite right. — paulmdavies
If you're using the xerial library for sqlite jdbc, this may help: groups.google.com/forum/#!topic/xerial/gGeqCw3KzvU — Alex Wilson
@paulmdavies, connection URI must be jdbc:sqlite:file::memory:?cache=shared — Robertiano

cvogt cvogt · Accepted Answer · 2014-09-24T23:39:05

Try to keep the connection open (this helps in H2, only speculating about SQLite):

def runTest( backend : MyDB, testFunction : ( MyDB ) => Unit ) : Unit =
{
    backend.database.withSession{ implicit session =>
      backend.metadata.ddl.create
      testFunction( backend )
    }
}

Scala Slick In-Memory Database SQLite No Such Table

2 Answers