Verify schema using Slick 3

Question

I am looking to use the Slick 3 framework for a Scala application to manage database interactions. I have been able to automatically generate the necessary table objects using Slick, but I also would like an integration test that verifies that the schemas in the database match the schemas in the objects. This is because sometimes tables get altered without my team being alerted, and so we would prefer to catch the change in an integration test instead of a production application.

One way to do this is to simply run a select query on every single table in a test runner. However, I feel like there should be a more direct way. Furthermore, it is not clear to me how to systematically run through all the tables defined in the file, except to manually append the table object to some sequence the test runner moves through. I notice that there is a schema field, but it only has the ability to generate create and drop statements.

Any help would be greatly appreciated. Thank you!

EDIT:

Here is my solution, but I was hoping for a better one:

class TablesIT extends FunSuite with BeforeAndAfter with ScalaFutures {
  var db: Database = _
  before{ db = Database.forURL( /* personal details */ )}

  object ResultMap extends GetResult[Map[String,Any]] { //Object borrowed from http://stackoverflow.com/questions/20262036/slick-query-multiple-tables-databases-with-getting-column-names
    def apply(pr: PositionedResult) = {
      val rs = pr.rs // <- jdbc result set
      val md = rs.getMetaData
      val res = (1 to pr.numColumns).map{ i=> md.getColumnName(i) -> rs.getObject(i) }.toMap
      pr.nextRow // <- use Slick's advance method to avoid endless loop
      res
    }
  }

  def testTableHasCols[A <: Table[_]](table: slick.lifted.TableQuery[A]): Unit = {
    whenReady(db.run(table.take(1).result.headOption.asTry)) { case Success(t) => t match {
      case Some(r) => logTrace(r.toString)
      case None => logTrace("Empty table")
    }
    case Failure(ex) => fail("Query exception: " + ex.toString)
    }
  }

  def plainSqlSelect[A](query: String)(implicit gr: GetResult[A]): Future[Seq[A]] = {
    val stmt = sql"""#$query""".as[A]
    db.run(stmt)
  }


  def compareNumOfCols[A <: Table[_]](table: slick.lifted.TableQuery[A]) = {
    val tableName = table.baseTableRow.tableName
    val selectStar = whenReady(db.run(sql"""select * from #$tableName limit 1""".as(ResultMap).headOption)) {
      case Some(m) => m.size
      case None => 0
    }
    val model = whenReady(db.run(sql"""#${table.take(1).result.statements.head}""".as(ResultMap).headOption)) {
      case Some(m) => m.size
      case None => 0
    }
    assert(selectStar === model, "The number of columns do not match")
  }

  test("Test table1") {
    testTableHasCols(Table1)
    compareNumOfCols(Table1)
  }

// And on for each table
}

Did you find any other idea? – daydreamer Apr 26 '16 at 16:36 — daydreamer, Apr 26 '16 at 16:36
Yes, actually. Let me post it as an answer. – user3846506 Apr 26 '16 at 20:49 — user3846506, Apr 26 '16 at 20:49

score 1 · Accepted Answer · answered Apr 26 '16 at 21:11

I ended up devising a better solution that uses the following idea. It is more or less the same, and unfortunately I still have to manually create a test for each table, but the method is cleaner, I think. Note, however, that this only works for PostgreSQL because of the information schema, but other database systems have other methods.

class TablesIT extends FunSuite with BeforeAndAfter with ScalaFutures {
  var db: Database = _
  before{ db = Database.forURL( /* personal details */ )}

  def testTableHasCols[A <: Table[_]](table: slick.lifted.TableQuery[A]): Unit = {
    whenReady(db.run(table.take(1).result.headOption.asTry)) { case Success(t) => t match {
      case Some(r) => logTrace(r.toString)
      case None => logTrace("Empty table")
    }
    case Failure(ex) => fail("Query exception: " + ex.toString)
    }
  }

  def compareNumOfCols[A <: Table[_]](table: slick.lifted.TableQuery[A]) = {
    val tableName = table.baseTableRow.tableName
    val selectStar = whenReady(db.run(sql"""select column_name from information_schema.columns where table_name='#$tableName'""".as[String])) {
      case m: Seq[String] => m.size
      case _ => 0
    }
    val model = table.baseTableRow.create_*.map(_.name).toSeq.size

    assert(selectStar === model, "The number of columns do not match")
  }

  test("Test table1") {
    testTableHasCols(Table1)
    compareNumOfCols(Table1)
  }

// And on for each table
}

Note, by design, this does not enforce the **order** of the columns in the tables. — user3846506, Apr 26 '16 at 21:21

Verify schema using Slick 3

1 Answers1