ZIO: A ZIO 2 + Scala 3 + MySQL database + ZIO HTTP server example application

By Alvin Alexander. Last updated: November 25, 2024

Without much explanation, the purpose of the following ZIO 2 + Scala 3 code is to show the absolute basics of a working ZIO HTTP + MySQL application. In this case I use the Scalikejdbc library, but as you can see from the code, you can use any Scala, Java, or JVM SQL library you want.

This is almost the “simplest possible”, “Hello world” application that shows all these features.

Very soon I will have a much more complete example including the use of packages, configuration, repository, service, api, and logging, in my free video courses at LearnScala.dev.

Here’s the code:

import zio.*
import zio.http.*
import scalikejdbc.*

/**
 * This is a ZIO 2 + Scala 3 + MySQL + ZIO HTTP server
 * application.
 */
case class User(id: Long, name: String)

object Main extends ZIOAppDefault:

    Class.forName("com.mysql.cj.jdbc.Driver")
    ConnectionPool.singleton(
        "jdbc:mysql://localhost:8889/zio_http",
        "root",
        "root"
    )

    class UserRepository:
        def getAllUsers(): Task[List[User]] =
            ZIO.attempt {
                DB.readOnly { implicit session =>
                    sql"select id, name from users"
                        .map(rs => User(rs.long("id"), rs.string("name")))
                        .list
                        .apply()
                }
            }

    val userRepo = UserRepository()

    // WORKS - v1
    val app = Routes(
        Method.GET / "users" -> handler { (_: Request) =>
            userRepo.getAllUsers()
                .fold(
                    _ => Response.status(Status.InternalServerError), // Failure case
                    {
                        case Nil =>
                            Response.status(Status.NoContent) // Empty list
                        case users =>
                            Response.text(users.map(u => s"${u.id}: ${u.name}").mkString("\n"))
                    }
                )
        }
    )

    override def run =
        Server
            .serve(app)
            .provide(Server.default)

build.sbt file

And here’s the corresponding SBT build.sbt file you’ll need:

ThisBuild / scalaVersion := "3.3.1"

// you really want this when working with ZIO-HTTP.
// helps with killing the running app, and not having
// to exit the `sbt` shell:
run / fork := true

lazy val root = (project in file("."))
    .settings(
        name := "zio-http-mysql",
        libraryDependencies ++= Seq(
            "dev.zio"         %% "zio-http" % "3.0.1",
            "org.scalikejdbc" %% "scalikejdbc" % "4.2.0",
            "mysql"           %  "mysql-connector-java" % "8.0.33",
            "ch.qos.logback"  %  "logback-classic" % "1.4.11"
        )
    )

Database schema

This is the SQL schema to create the users database table:

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);

More info

Also, here are three ways you can implement the routes:

    // WORKS - v1
    val app = Routes(
        Method.GET / "users" -> handler { (_: Request) =>
            userRepo.getAllUsers()
                .fold(
                    _ => Response.status(Status.InternalServerError), // Failure case
                    {
                        case Nil =>
                            Response.status(Status.NoContent) // Empty list
                        case users =>
                            Response.text(users.map(u => s"${u.id}: ${u.name}").mkString("\n"))
                    }
                )
        }
    )

    // WORKS - v2
    // val app = Routes(
    //     Method.GET / "users" -> handler { (_: Request) =>
    //         userRepo.getAllUsers()
    //             .map { users =>
    //                 if
    //                     users.isEmpty then Response.status(Status.NoContent)
    //                 else 
    //                     Response.text(users.map(u => s"${u.id}: ${u.name}").mkString("\n"))
    //             }
    //             .catchAll { error => 
    //                 ZIO.succeed(Response.status(Status.NoContent))
    //             }
    //     }
    // )
    
    // WORKS - v3
    // val app = Routes(
    //     Method.GET / "users" -> handler { (request: Request) =>
    //         for
    //             users <- userRepo.getAllUsers().option
    //         yield users match
    //             case Some(theUsers) => 
    //                 val text = theUsers.map{ u =>
    //                      s"${u.id}: ${u.name}"
    //                 }.mkString("\n")
    //                 Response.text(text)
    //             case None => 
    //                 Response.status(Status.NoContent)                
    //     }
    // )

Summary

If you ever want to use ZIO 2, Scala 3, ZIO HTTP, and MySQL together, I hope this sample code will help. Also, see LearnScala.dev for more in-depth coverage and better code organization.

scala

database