Try Doobie modernised

build.sbt

libraryDependencies ++= Seq(
   "org.xerial" % "sqlite-jdbc" % "3.36.0.3",
   "org.tpolecat" %% "doobie-core"      % "1.0.0-RC2",
   "org.tpolecat" %% "doobie-hikari"    % "1.0.0-RC2", // HikariCP transactor.
   "org.tpolecat" %% "doobie-specs2"    % "1.0.0-RC2", // Specs2 support for typechecking statements.
   "org.tpolecat" %% "doobie-scalatest" % "1.0.0-RC2",  // ScalaTest support for typechecking statements.
)

trydoobie.scala

import cats.effect._

import doobie._
import doobie.implicits._

object TryDoobie extends IOApp.Simple {

  import cats._
  import cats.data._
  import cats.implicits._

  def run = {

    val xa = Transactor.fromDriverManager[IO](
      "org.sqlite.JDBC",
      "jdbc:sqlite:sample.db",
      "",
      ""
    )

    val y = xa.yolo
    import y._

    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

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

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

    for {
      res <- (drop, create).mapN(_ + _).transact(xa)

      _ = println(res)

      _ <- insert1("Alice", Some(12)).run.transact(xa)

      _ <- insert1("Bob", None).quick

      l <- sql"select rowid, name, age from person"
        .query[Person]
        .to[List]
        .transact(xa)

      _ = l.foreach(println)

    } yield ()
  }
}