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

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.