CommandExample.scala

import cats.effect._
import cats.implicits._
import natchez.Trace.Implicits.noop
import skunk._
import skunk.codec.all._
import skunk.implicits._

object CommandExample extends IOApp {

  // a source of sessions
  val session: Resource[IO, Session[IO]] =
    Session.single(
      host     = "localhost",
      user     = "jimmy",
      database = "world",
      password = Some("banana"),
    )

  // a resource that creates and drops a temporary table
  def withPetsTable(s: Session[IO]): Resource[IO, Unit] = {
    val alloc = s.execute(sql"CREATE TEMP TABLE pets (name varchar, age int2)".command).void
    val free  = s.execute(sql"DROP TABLE pets".command).void
    Resource.make(alloc)(_ => free)
  }

  // a data type
  case class Pet(name: String, age: Short)

  // command to insert a pet
  val insertOne: Command[Pet] =
    sql"INSERT INTO pets VALUES ($varchar, $int2)"
      .command
      .gcontramap[Pet]

  // command to insert a specific list of pets
  def insertMany(ps: List[Pet]): Command[ps.type] = {
    val enc = (varchar ~ int2).gcontramap[Pet].values.list(ps)
    sql"INSERT INTO pets VALUES $enc".command
  }

  // query to select all pets
  def selectAll: Query[Void, Pet] =
    sql"SELECT name, age FROM pets"
      .query(varchar ~ int2)
      .gmap[Pet]

  // some sample data
  val bob     = Pet("Bob", 12)
  val beatles = List(Pet("John", 2), Pet("George", 3), Pet("Paul", 6), Pet("Ringo", 3))

  // our entry point
  def run(args: List[String]): IO[ExitCode] =
    session.flatTap(withPetsTable).use { s =>
      for {
        _  <- s.prepare(insertOne).use(pc => pc.execute(Pet("Bob", 12)))
        _  <- s.prepare(insertMany(beatles)).use(pc => pc.execute(beatles))
        _ <- IO.never
        //ps <- s.execute(selectAll)
        //_  <- ps.traverse(p => IO(println(p)))
      } yield ExitCode.Success
    }

}