Skip to main content
Version: 2.0.x

Getting Started with ZIO JDBC

Writing Queries

Basic.scala (see zio.jdbc.examples in project)

  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))

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

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
}

Creating a Connection Pool


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

val properties = Map(
"user" -> "mysql",
"password" -> "mysql"
)

val connectionPool: ZLayer[ZConnectionPoolConfig, Throwable, ZConnectionPool] =
ZConnectionPool.mysql("localhost", 3306, "mysql", properties)

val live: ZLayer[ZConnectionPoolConfig, Throwable, ZConnectionPool] = createZIOPoolConfig >>> connectionPool

Full Example

App.scala (see zio.jdbc.examples in project)


import zio._
import zio.jdbc._
import zio.schema.Schema

/**
* You'll need the appropriate JDBC driver, and a database running.
*/
object App extends ZIOAppDefault {
final case class User(name: String, age: Int)

object User {
import Schema.Field

implicit val schema: Schema[User] =
Schema.CaseClass2[String, Int, User](
Field("name", Schema[String]),
Field("age", Schema[Int]),
(name, age) => User(name, age),
_.name,
_.age
)

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

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

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

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

val insertRow: ZIO[ZConnectionPool, Throwable, Long] = transaction {
Basic.ex3.insert
}

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

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

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

val properties = 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[ZEnv with ZIOAppArgs, Any, Any] =
for {
results <- program.provideLayer(createZIOPoolConfig >>> connectionPool)
_ <- Console.printLine(results.mkString("\n"))
} yield ()
}