sqlite doobie

[swuecho]

package net.bestqa

import cats.effect.{ExitCode, IO, IOApp}
import org.http4s.HttpRoutes
import org.http4s.dsl.io._
import org.http4s.implicits._
import org.http4s.ember.server.EmberServerBuilder
import io.circe.generic.auto._
import org.http4s.circe.CirceEntityEncoder._
import cats.syntax.*
import com.comcast.ip4s.port
import doobie.util.transactor.Transactor
import doobie.implicits.*
import io.getquill.{idiom => _, *}
import io.getquill.doobie.DoobieContext
import doobie._

object Http4DoobieQuillSqliteServer extends IOApp {

  val ctx = new DoobieContext.SQLite(LowerCase)
  import ctx._

  case class Actor(id: Int, name: String)

  def findGreatId = ctx.run(
    quote {
      query[Actor].filter(_.id > 3)
    }
  )

  def findAllActors(xa: Transactor[IO]) = {
    val query = sql"select id, name from actor".query[Actor]
    val action = query.to[List]
    action.transact(xa)
  }

  case class Message(content: String)

  def routes(xa: Transactor[IO]): HttpRoutes[IO] = HttpRoutes.of[IO] {
    case GET -> Root            => Ok(Message("Hello, from http4s-ember-demo!"))
    case GET -> Root / "actors" => Ok(findAllActors(xa))
    case GET -> Root / "actors_quill" => Ok(findGreatId.transact(xa))
  }

  override def run(args: List[String]) =

    val xa = Transactor.fromDriverManager[IO](
      "org.sqlite.JDBC", // JDBC driver class
      "jdbc:sqlite:/home/hwu/dev/bestqa_scala/data/lite", // Connection URL
      "", // User
      "" // Password
    )
    EmberServerBuilder
      .default[IO]
      .withHttpApp(routes(xa).orNotFound)
      .withPort(port"9955")
      .build
      .use(_ => IO.never) // Ensuring the server runs indefinitely
      .as(ExitCode.Success)

}

[swuecho]

val scala3Version = "3.3.1"
val fs2Version = "3.1.0"
val doobieVersion = "1.0.0-RC2"
val circeVersion = "0.14.1"

lazy val root = project
  .in(file("."))
  .settings(
    name := "bestqa_scala",
    version := "0.3.3",
    scalaVersion := scala3Version
  )

// build.sbt
libraryDependencies += "org.scalameta" %% "munit" % "0.7.29" % Test
libraryDependencies += "org.scalameta" %% "munit-scalacheck" % "0.7.29" % Test
libraryDependencies += "org.scalacheck" %% "scalacheck" % "1.16.0" // % Test

libraryDependencies += "dev.zio" %% "zio" % "2.0.0"
libraryDependencies += "dev.zio" %% "zio-streams" % "2.0.0"
libraryDependencies += "dev.zio" %% "zio-test" % "2.0.0"

// libraryDependencies += "io.d11" %% "zhttp"      % "v2.0.0-RC9"
//libraryDependencies += "io.d11" %% "zhttp-test" % "v2.0.0-RC9" % Test

testFrameworks += new TestFramework("munit.Framework")

libraryDependencies += "com.softwaremill.sttp.client3" %% "core" % "3.3.4"

libraryDependencies ++= Seq(
  "org.postgresql" % "postgresql" % "42.2.8",
  "io.getquill" %% "quill-jdbc" % "4.7.3",
  "io.getquill" %% "quill-doobie" % "4.7.3",

)
libraryDependencies +=  "com.h2database" % "h2" % "1.4.200"

libraryDependencies += "org.tpolecat" %% "doobie-core" % doobieVersion
libraryDependencies += "org.tpolecat" %% "doobie-postgres" % doobieVersion
libraryDependencies += "org.tpolecat" %% "doobie-h2" % doobieVersion
libraryDependencies += "org.tpolecat" %% "doobie-h2-circe" % doobieVersion
libraryDependencies +=  "org.xerial" % "sqlite-jdbc" % "3.36.0"      // SQLite driver

val Http4sVersion = "0.23.25"
val CirceVersion = "0.14.1"
libraryDependencies ++= Seq(
  "org.http4s"      %% "http4s-ember-server" % Http4sVersion,
  "org.http4s"      %% "http4s-ember-client" % Http4sVersion,
  "org.http4s"      %% "http4s-circe"        % Http4sVersion,
  "org.http4s"      %% "http4s-dsl"          % Http4sVersion,
  "io.circe"        %% "circe-generic"       % CirceVersion,
)

libraryDependencies += "org.slf4j" % "slf4j-simple" % "1.7.25"

libraryDependencies += "com.lihaoyi" %% "cask" % "0.8.2"
libraryDependencies += "com.lihaoyi" %% "pprint" % "0.7.3"

// libraryDependencies += "com.github.xuwei-k" %% "jwt-scala" % "1.7.0"
// usage: https://jwt-scala.github.io/jwt-scala/jwt-circe.html
libraryDependencies += "com.github.jwt-scala" %% "jwt-circe" % "8.0.1"

//libraryDependencies ++= Seq(
//    "net.debasishg" %% "redisclient" % "3.42"
//)

excludeDependencies ++= Seq(
  ExclusionRule("com.lihaoyi", "geny_2.13")
)

Compile / run / mainClass := Some("net.bestqa.Http4DoobieQuillSqliteServer")

[swuecho]

CREATE TABLE IF NOT EXISTS actor ( id integer primary key, name VARCHAR ); INSERT INTO actor (name) VALUES ('Henry Cavill'); INSERT INTO actor (name) VALUES ('Gal Godot'); INSERT INTO actor (name) VALUES ('Ezra Miller'); INSERT INTO actor (name) VALUES ('Ben Affleck'); INSERT INTO actor (name) VALUES ('Ray Fisher'); INSERT INTO actor (name) VALUES ('Jason Momoa');