sqlite & scala

build.sbt

import Dependencies._

lazy val root = (project in file(".")).
  settings(
    inThisBuild(List(
      organization := "com.example.test",
      scalaVersion := "2.12.7",
      version := "0.1.0-SNAPSHOT"
    )),
    name := "hello-cats",
  )

libraryDependencies += scalaTest % Test
libraryDependencies += cats % Test

libraryDependencies ++= Seq(
  "org.xerial" % "sqlite-jdbc" % "3.25.2",
  "org.tpolecat" %% "doobie-core" % "0.5.3",
  "org.tpolecat" %% "doobie-hikari" % "0.5.3",
  "org.tpolecat" %% "doobie-specs2" % "0.5.3",
)

sqlite.scala

package example

import org.scalatest.{FlatSpec, Matchers}

class HelloSqlite extends FlatSpec with Matchers {
  "Sqlite" should "perform all ops" in {
    import doobie._
    import doobie.implicits._
    import cats.effect.IO
    import cats.implicits._

    Class.forName("org.sqlite.JDBC")

    val context = Transactor.fromDriverManager[IO](
      "org.sqlite.JDBC", "jdbc:sqlite:data.sqlite", "", ""
    )

    val drop =
      sql"""
    DROP TABLE IF EXISTS person
  """.update.run

    val create =
      sql"""
    CREATE TABLE person (
      name TEXT NOT NULL UNIQUE,
      age  INTEGER
    )
  """.update.run

    val res = (drop, create).mapN(_ + _).transact(context).unsafeRunSync
    println(res)

    def insert1(name: String, age: Option[Short]): Update0 =
      sql"insert into person (name, age) values ($name, $age)".update

    insert1("Alice", Some(12)).run.transact(context).unsafeRunSync
    insert1("Bob", None).run.transact(context).unsafeRunSync

    case class Person(id: Long, name: String, age: Option[Short])

    val l = sql"select rowid, name, age from person".query[Person].to[List].transact(context).unsafeRunSync
    l.foreach(println)
  }
}