ZIO 2: ZIO JDBC examples (SQL select, insert, drop, update, delete, transactions)

package zio.jdbc.examples

import zio._
import zio.jdbc._

object Basic {
  val age = 42

  val ex0: SqlFragment = sql"create table if not exists users(name varchar(255), age int)"

  // Creating SQL statements using interpolation:
  val ex1: SqlFragment = sql"select * from users where age = $age"

  // Selecting into tuples:
  val ex2: Query[(String, Int)] = sql"select name, age from users".query[(String, Int)]

  // Inserting from tuples:
  val ex3: SqlFragment = sql"insert into users (name, age)".values(("John", 42))

  // Inserting from case class
  val ex4: SqlFragment = sql"insert into users (name, age)".values(User("John", 42))

  // dropping table
  val ex5: SqlFragment = sql"drop table if exists users"

  // Composing requests:
  val keyColumn                                  = "key"
  val valueColumn                                = "value"
  def ex4(offset: Long): Query[(String, String)] =
    (s"select $keyColumn, $valueColumn from events" ++ sql"where id > $offset").query[(String, String)]

  // Executing statements:
  val res1: ZIO[ZConnectionPool, Throwable, Option[(String, Int)]] =
    transaction {
      sql"select name, age from users where name = 'Sherlock Holmes'".query[(String, Int)].selectOne
    }
}

import zio._
import zio.jdbc._
import zio.schema.{ Schema, TypeId }

/**
 * You'll need the appropriate JDBC driver, and a database running.
 */
object App extends ZIOAppDefault {
  import zio.jdbc.examples.User._

  val create: ZIO[ZConnectionPool, Throwable, Unit] = transaction {
    Basic.ex0.execute
  }

  val insertRow: ZIO[ZConnectionPool, Throwable, UpdateResult[Long]] = transaction {
    sql"insert into users (name, age)".values(sampleUser1, sampleUser2).insertWithKeys
  }

  val select: ZIO[ZConnectionPool, Throwable, Chunk[User]] = transaction {
    Basic.ex2.as[User].selectAll
  }

  val drop: ZIO[ZConnectionPool, Throwable, Unit] = transaction {
    Basic.ex5.execute
  }

  val zioPoolConfig: ULayer[ZConnectionPoolConfig] =
    ZLayer.succeed(ZConnectionPoolConfig.default)

  val properties: Map[String, String] = Map(
    "user"     -> "postgres",
    "password" -> "postgres"
  )

  /**
   * Pre defined ZConnection Pools exist for:
   *  Postgres, SQL Server, Oracle, MySQL and h2
   *  custom pools, can also be constructed
   */
  val connectionPool: ZLayer[ZConnectionPoolConfig, Throwable, ZConnectionPool] =
    ZConnectionPool.postgres("localhost", 5432, "postgres", properties)

  val program: ZIO[ZConnectionPool, Throwable, Chunk[User]] = for {
    _   <- create *> insertRow
    res <- select
    _   <- drop
  } yield res

  override def run: ZIO[Scope with ZIOAppArgs, Any, Any] =
    for {
      results <- program.provideLayer(zioPoolConfig >>> connectionPool)
      _       <- Console.printLine(results.mkString("\n"))
    } yield ()
}

final case class User(name: String, age: Int)

object User {
  import Schema.Field

  implicit val schema: Schema[User] =
    Schema.CaseClass2[String, Int, User](
      TypeId.parse(classOf[User].getName),
      Field("name", Schema[String], get0 = _.name, set0 = (x, v) => x.copy(name = v)),
      Field("age", Schema[Int], get0 = _.age, set0 = (x, v) => x.copy(age = v)),
      User.apply
    )

  // One can derive a jdbc codec from a zio-schema or
  implicit val jdbcDecoder: JdbcDecoder[User] = JdbcDecoder.fromSchema
  implicit val jdbcEncoder: JdbcEncoder[User] = JdbcEncoder.fromSchema

  val sampleUser1: User = User("John", 42)
  val sampleUser2: User = User("Sandra", 27)

  // a custom decoder from a tuple
  // implicit val jdbcDecoder = JdbcDecoder[(String, Int)].map[User](t => User(t._1, t._2))
}