Compare database schema with existing slick tables

Question

I am using Scala, Slick and Postgres to build an application. I have used Slick code generator to generate the slick tables.

I want to know if there is any way to validate if the database table schema and the slick table schema matches and do that for all slick tables in my application.

For example:

class DepartmentTable(_tableTag: Tag) extends Table[Department](_tableTag, Some("base"), "Department") {
    val id: Rep[Long] = column[Long]("DepartmentId", O.AutoInc, O.PrimaryKey)
    val name: Rep[String] = column[String]("Name", O.Length(50,varying=true))
    val shortCode: Rep[String] = column[String]("ShortCode", O.Length(50,varying=true))
    def * = ???
    def ? = ???
} 

I changed the database table, say add a column parentDepartmentId to the table and then added the same to the Slick table. Many a times, there have been issues that the alter scripts are not run on the test database and hence we will be getting some run time exceptions.

To avoid such issues, I was trying to implement something to check if the slick table matches with actual postgres table. Is it achievable ?

I tried with reflection, but not able to get all the details from the slick table. eg: actual column name

Slick Version : 3.0

What I am trying to achieve?

On startup of the application, I want to compare the database schema with the slick schema.

My plan:

1. Get all the TableQuery / Slick Tables from my application

2. Get the actual database schema using the Slick Meta

3. Compare slick tablequery structure with the actual db

Now, as Maxim suggested, I can create a registry and add each table to the registry. I just want to check if there is any other way. The reason is that if I or someone else accidentally removed adding a couple of table query to the registry, the check on that table will not be done. I am just trying to be more safer, but not sure if any such method exist.

Answer 1

Here is an example of how you can detect if for a given Slick table the number, names and SQL types of all columns in the database schema that is supposed to correspond with the table equal the number, names and SQL types of columns in the Slick table description of the table

def ?[AT <: AbstractTable[_]](tableQuery: profile.api.TableQuery[AT])
                             (implicit ec: ExecutionContext) = {
  val table = tableQuery.baseTableRow.create_*.map(c =>
    (c.name, profile.jdbcTypeFor(c.tpe).sqlType)).toSeq.sortBy(_._1)
  MTable.getTables(tableQuery.baseTableRow.tableName).headOption.map(
    _.map{_.getColumns.map(
      _.sortBy(_.name).map(c => (c.name, c.sqlType)) == table
    )}
  ) flatMap (_.head)
}

You can also detect whether indexes, primary and foreign keys are identical to some extent. For that you can correspondingly combine

tableQuery.baseTableRow.indexes    
tableQuery.baseTableRow.primaryKeys
tableQuery.baseTableRow.foreignKeys

with the following methods of MTable

getIndexInfo    
getPrimaryKeys
getImportedKeys

as I did with tableQuery.baseTableRow.create_* and getColumns in the excerpt.

Now having this method you can easily check all the tables you have in your code. The only really easy question is how to get their list. To say the truth, I do not even understand how it can be a problem, as it is just a matter of keeping a centralized registry where you can enlist a table each time it is created in your code and which you can query for the objects stored. Let's say you have such registry with the methods enlistTable and listTables then your workflow will look something like

val departmentTable = TableQuery[DepartmentTable]
regsitry.enlistTable(departmentTable)
...
val someTable = TableQuery[SomeTableStructureClass]
regsitry.enlistTable(someTable)   
...
val anotherTable = TableQuery[AnotherTableStructureClass]
regsitry.enlistTable(anotherTable)   
...
for(table <- regsitry.listTables)
  db run ?(table) map ( columnsAndTypesAreIdentical => ... ) 
...

.

The Slick code generator you used "generates Table classes, corresponding TableQuery values,..., as well as case classes for holding complete rows of values" by default. The corresponding TableQuery values have exactly the form val someTable = TableQuery[SomeTableStructureClass].

Answer 2

You can use slick.meta to achieve this. You are not saying which version of slick you are using so I am going to show an example using slick 3.0, but it should be really similar if you were using slick 2.x replacing the DBIO with the old withSession API and removing the reference to ExecutionContext and Future.

Here it is how you can print all the columns every table in the schema assuming that you have an implicit ExecutionContext in scope, that you import YourDriver.api._ and replace the ??? with an actual Database instance:

val db: Database = ???

val tablesWithCols = for {
  tables <- slick.jdbc.meta.MTable.getTables
  withCols <- DBIO.sequence(tables.map(t => t.getColumns.map((t, _))))
} yield withCols

val printLines: DBIO[Seq[String]] = tablesWithCols.map {
  _.map {
    case (t, cs) => s"Table: ${t.name.name} - columns: ${cs.map(_.name).mkString(", ")}"
  }
}

val res: Future[Seq[String]] = db.run(printLines)

res.foreach(println)

Also, please not that the last foreach invocation is performed on a Future so you may want to wait on the future to complete or (better) to chain it with relevant computations; if your program terminates without waiting/chaining you won't probably see anything from there.

Surprisingly, a somewhat more complex matter is getting the information out of the slick table definitions; the only way I found to do it is something like this:

TableQuery[YourTable].toNode.getDumpInfo

That will give you an AST-like structure that you can traverse to get out the definitions you need; the structure itself is not that pleasant to traverse but it should contain everything you need.

Another approach that you could explore to avoid this troubles could be creating a layer that wraps the generation of slick definitions and expose relevant metadata in a more accessible way; not sure if this wouldn't get you in bigger troubles though.