How do I create dynamic queries using Slick?

Question

I am migrating my application from Cassandra to Postgres. While I used Datastax driver to dynamically generate cql queries, I am having trouble doing the same with slick.

When I say dynamic, I mean I am getting my column names and their values as a map {(key, v) => key.name -> v} from a service and I wish to plug these in the query generated in my Postgres DAO API using slick.

Here is a sample code for Cassandra:


 private lazy val getStatement: PreparedStatement = {
    val select: Select = QueryBuilder.select(valueColumns.map(_.name): _*).from(KeyspaceName, TableName)

    val where: Where = select.where()

    keyColumns.foreach(col => where.and(QueryBuilder.eq(col.name, bindMarker)))

    table.session.getSession.prepare(select)
  }




 val bs: BoundStatement = getStatement.bind
          val updatedKeys = keys.map { case (column, value) => (column, Option(value)) }
          writeKeys(updatedKeys, bs) //this method modifies the bound statement to plug in values

          val resultSet: ResultSet = table.session.getSession.execute(bs)

What is the alternative to this - for better reuse of a certain query across different tables. Also to further extend it across diff column names ?

How can I achieve this using slick?

score 1 · Answer 1 · answered Jun 28 '21 at 06:31

Slick is designed to allow compile-time checking of database operations by using typed versions of all the tables and their fields, based on a Scala version of the database schema. These data types can be generated automatically from the database schema, or manually created by the programmer, but they are always created before the program is compiled.

Using Slick to dynamically create queries on a schema that is only known at run time isn't really using the strengths of the library. Slick provides Plain SQL Queries that could be used as starting point for dynamic queries, but you would need to provide most of the framework yourself.

score 0 · Answer 2 · answered Jun 26 '21 at 10:01

If I am understanding the question right, you need a way to automatically generate table entities. Please check out slick.codegen.SourceCodeGenerator. https://scala-slick.org/doc/3.2.1/code-generation.html

object SchemaCodeGen extends App {
  slick.codegen.SourceCodeGenerator.main(
    Array(
      // Postgres Config
      "slick.jdbc.PostgresProfile",
      "org.postgresql.Driver",
      "jdbc:postgresql://localhost:5432/db_name?user=user_name",
      "/Users/ashwinsreekumar/Desktop/app_name/app",
      "models.tables"
     
    )
  )
  Seq.empty
}

How do I create dynamic queries using Slick?

2 Answers2