How to execute list of string SQL statements against a PostgreSQL db in Scala using doobie?

Question

I am porting the following 10 lines of Python code to Scala:

import psycopg2

def execute(user, password, database, host, port, *queries):
    connection = psycopg2.connect(user=user, password=password, host=host, port=port, database=database)
    cursor = connection.cursor()
    for sql in queries:
        print(sql)
        cursor.execute(sql)
    connection.commit()
    cursor.close()
    connection.close()

I have the following equivalent Scala code:

def execute(user: String, password: String, database: String, host: String, port: Int, queries: String*): Unit = {
  ???
}    

I want to execute (and print) bunch of SQL statements in a single transaction against the database (assume it to be Postgres) and be done.

How do I do that using doobie?

Note:

1. I cannot change the interface to my execute() (including I cannot add type or implicit params). It must take in String user, password etc. and a vararg of queries: String* and thus keep the interface same as the Python one.

2. Please also mention all imports needed

Answer 1

You can run multiple queries in one transaction in doobie using for-comprehension, for example:

val query = for {
   _  <- sql"insert into person (name, age) values ($name, $age)".update.run
   id <- sql"select lastval()".query[Long].unique
} yield p

But this solution won't work in your case, because you've got a dynamic list of queries. Fortunately, we can use traverse from cats:

import cats.effect.ContextShift
import doobie._
import doobie.implicits._
import cats.effect._
import scala.concurrent.ExecutionContext
import cats.implicits._
import cats._
import cats.data._

def execute(user: String, password: String, database: String, host: String, port: Int, queries: String*): Unit = {

     //you can use other executor if you want
     //it would be better to pass context shift as implicit argument to method
    implicit val cs: ContextShift[IO] = IO.contextShift(ExecutionContext.global) 

    //let's create transactor  
    val xa = Transactor.fromDriverManager[IO](
      "org.postgresql.Driver",
       s"jdbc:postgresql://$host:$port/$database", //remember to change url or make it dynamic, if you run it agains another database
       user,
       password
    )

    val batch = queries
        .toList //we need to change String* to list, since String* doesn't have necessary typeclass for Aplicative
        .traverse(query => Update0(query, None).run) //we lift strings to Query0 and then run them, then we change List[ConnectionIO[Int]] to ConnectionIO[List[Int]]
        //above can be done in two steps using map and sequence

    batch  //now we've got single ConnectionIO which will run in one transaction
      .transact(xa) //let's make it IO[Int]
      .unsafeRunSync() //we need to block since your method returns Unit

  }

Probably your IDE will show you this code is invalid, but it's correct. IDEs just can't handle Scala magic.

You might also consider using unsafeRunTimed instead of unsafeRunSync to add the time limit.

Also, remember to add postgresql driver for jdbc and cats to your build.sbt. Doobie uses cats under the hood, but I think explicit dependency might be necessary.

Answer 2

Try solving it for just one query in a transaction and seeing what that function signature looks like.

Then look at how to get from there to your final destination.