Scala 3, Scala CLI, JDBC, and SQL (examples)

As a brief note to self today, here are two examples of how to perform SQL queries with Scala 3, using (a) plain JDBC, and also (b) using a little Scala 3 library known as SimpleSQL. These examples use Scala CLI, which as I have mentioned before, makes these examples so much easier to share with you.

A sample SQLite database

The following examples assume that you have this SQLite database in a file named ToDoList.sqlite in the current directory:

create table tasks (
    id integer primary key autoincrement,
    task text not null,
    date_created integer default (cast(strftime('%s','now') as int)),
    date_updated integer default (cast(strftime('%s','now') as int))
);

Scala 3, Scala CLI, and JDBC example

Here’s the first example:

//> using scala "3"
//> using lib "org.xerial:sqlite-jdbc:3.39.2.1"
package sqlitetest

// run me with 'scala-cli SQLite1.scala'
// note above that you use ':' for java libraries

import java.sql.*

@main def SQLiteTest1 =
    val url = "jdbc:sqlite:./ToDoList.sqlite"

    try
        val conn = DriverManager.getConnection(url)
        val statement = conn.createStatement
        val rs = statement.executeQuery("select * from tasks")
        while rs.next do
            val id = rs.getInt("id")
            val task = rs.getString("task")
            println(s"ID = $id, TASK = $task")
        end while
    catch
        case e: Exception => System.err.println(e.getMessage())
    finally
        println("Came to try>finally")

As mentioned, that uses plain Java JDBC with Scala 3 and Scala CLI.

Scala 3, Scala CLI, and SimpleSQL example

Next up, this example uses the SimpleSQL library:

//> using scala "3"
//> using lib "org.xerial:sqlite-jdbc:3.39.2.1"

package sqlitetest

import java.sql.*
import org.sqlite.SQLiteDataSource
import simplesql as sq

case class Task(id: Int, task: String) derives sq.Reader

@main def SQLiteTest3 =

    val ds = SQLiteDataSource()
    ds.setUrl("jdbc:sqlite:./ToDoList.sqlite")

    sq.transaction(ds) {
        val tasks: List[Task] = sq.read[Task](sql"select id, task from tasks")
        for t <- tasks do
            println(s"ID: ${t.id}, TASK: ${t.task}")
    }

In this example — and for my particular need — I didn’t include SimpleSQL as a pre-built library, I included its source code in my project, as is mentioned in its Github repository. Therefore you don’t see it as an import here.

Because I included it as a separate source code file, I ran all files in my directory with Scala CLI like this:

$ scala-cli . --watch

The --watch part isn’t completely necessary, but it’s nice to see my code recompiled as I work.

Summary

If for some reason you’d like to use Scala 3 with Scala CLI and SQLite, I hope these examples are helpful.