Slick-DuckDB

Slick extension for DuckDB for your type-safe and in-memory data processing needs.

This extension was written and tested for DuckDB JDBC driver versions 1.3.2.0, 1.4.1.0, and 1.5.1.0. Future versions will likely work, but of course your mileage may vary since they are currently not being tested.

Additionally, the extension does not fully support or map all DuckDB features to Slick. Known limitations are:

• Blobs are handled as byte arrays as a workaround to missing JDBC driver functionality; be cautious when sizing byte arrays and watch memory consumption and performance
• DuckDB extensions, and syntax related to them, are not supported beyond the SQL standard

How to use it

Installation

Add slick-duckdb to your build.sbt along with the DuckDB JDBC driver:

libraryDependencies += "org.duckdb" % "duckdb_jdbc" % "1.5.1.0",
libraryDependencies += "io.github.algebrazebra" % "slick-duckdb_2.13" % "0.1.0"

Usage

To demonstrate the usage, let's create a simple table and query it:

import duckdbslick.DuckDBProfile.api.*

import scala.concurrent.Await
import scala.concurrent.duration.*

// Here we define the `User` table using Slick
case class User(id: Int, name: String, age: Int)
class Users(tag: Tag) extends Table[User](tag, "users") {
  def id   = column[Int]("id", O.PrimaryKey)
  def name = column[String]("name")
  def age  = column[Int]("age")
  def *    = (id, name, age) <> (User.tupled, User.unapply)
}
val users = TableQuery[Users]

// Next we create the DuckDB database connection.
// If `example.duckdb` does not exist, the file will be created automatically.
val db = Database.forURL("jdbc:duckdb:./example.duckdb", driver = "org.duckdb.DuckDBDriver")

// Alternatively, you can specify `jdbc:duckdb:memory:example` as the URL to use DuckDB in in-memory mode.
// The `keepAliveConnection` parameter must be set.
// If it's not set, each query will be executed against its own fresh in-memory database.
// For the same reason, the database name (here: `example`) must be specified.
val inMemoryDb = Database.forURL(
  "jdbc:duckdb:memory:example",
  driver = "org.duckdb.DuckDBDriver",
  keepAliveConnection = true
)


// Let's create the table and insert the example user records
val exampleUsers = Seq(
  User(1, "Alice", 30),
  User(2, "Bob", 25),
  User(3, "Charlie", 35),
  User(4, "Diana", 28)
)
val insertUsers = db.run(
  DBIO.seq(
    users.schema.createIfNotExists,
    users ++= exampleUsers
  )
)
Await.result(insertUsers, 5.seconds)

// Finally, we can execute queries against the table with the Slick DSL
val queryUsersOlderThan25 = users.filter(_.age > 25)
val usersOlderThan25 = Await.result(db.run(queryUsersOlderThan25.result), 5.seconds)

println("Users older than 25:")
usersOlderThan25.foreach { user =>
  println(s"  - ${user.name} (ID: ${user.id}, Age: ${user.age})")
}
assert(usersOlderThan25.size == exampleUsers.count(_.age > 25))

// After all this fun stuff, we have to tidy up
db.close()

Features worth noting

Check constraints

DuckDB-specific named check constraints use the column-oriented O.Check option. Multiple constraints may be declared on a column, and a predicate may refer to other columns included in the same table DDL:

class Ranges(tag: Tag) extends Table[(Option[Int], Int)](tag, "ranges") {
  def minimum = column[Option[Int]]("minimum")
  def maximum = column[Int](
    "maximum",
    O.Check[Int]("maximum_non_negative")(_ >= 0),
    O.Check[Int]("valid_range")(max => minimum <= max.?)
  )
  def * = (minimum, maximum)
}

Predicates may return Rep[Boolean] or Rep[Option[Boolean]]. As in SQL, a check result of NULL passes. Predicates are schema-definition code that may be evaluated repeatedly, so they must be deterministic, side-effect-free, and independent of the mutable application state.

Checks support scalar comparisons, Boolean and arithmetic operators, casts, case expressions, literal products such as IN, safely named SimpleFunctions, and functions supported by the DuckDB query builder. Subqueries, binds, aggregates, windows, sequences, raw SQL nodes, cross-table references, and references to columns omitted from the table DDL are rejected while Slick constructs the DDL.

Length constraints

Use O.Length on a VARCHAR column to enforce its maximum length. Its varying parameter controls whether the column is emitted as VARCHAR or CHAR. DuckDB does not enforce declared VARCHAR lengths, so this option is emitted as a DuckDB CHECK constraint and values longer than the declared limit are rejected by the database:

class Users(tag: Tag) extends Table[(Int, String)](tag, "users") {
  def id = column[Int]("id", O.PrimaryKey)
  def name = column[String]("name", O.Length(100))
  def * = (id, name)
}