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

By Alvin Alexander. Last updated: March 8, 2024

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. The . portion of the command means, “Look for a main method in all the files in the current directory and then run it.” If there are no main methods the command will fail, and if there are multiple main methods, you’ll be prompted for the one to run, or you’ll be shown the syntax to run the single, desired main method.