Getting Started

A step-by-step introduction to Foundations JDBC — from setup to executing queries. Foundations JDBC is MIT-licensed and open source.

Dependencies

Pick the one dependency for your language:

Java

build.gradle.kts

implementation("dev.typr:foundations-jdbc:1.0.0-RC1")

pom.xml

<dependency>
    <groupId>dev.typr</groupId>
    <artifactId>foundations-jdbc</artifactId>
    <version>1.0.0-RC1</version>
</dependency>

Kotlin

build.gradle.kts

implementation("dev.typr:foundations-jdbc-kotlin:1.0.0-RC1")

pom.xml

<dependency>
    <groupId>dev.typr</groupId>
    <artifactId>foundations-jdbc-kotlin</artifactId>
    <version>1.0.0-RC1</version>
</dependency>

Scala

build.gradle.kts

implementation("dev.typr:foundations-jdbc-scala_3:1.0.0-RC1")

pom.xml

<dependency>
    <groupId>dev.typr</groupId>
    <artifactId>foundations-jdbc-scala_3</artifactId>
    <version>1.0.0-RC1</version>
</dependency>

Each module includes everything you need — the Kotlin and Scala modules depend on the core transitively.

Imports

Java

import dev.typr.foundations.*;           // Core: Fragment, RowCodec, Operation, Transactor, *Types
import dev.typr.foundations.connect.*;    // Connection: SimpleDataSource, *Config
import dev.typr.foundations.data.*;       // Data types: Json, Range, Uint4, etc.

Kotlin

import dev.typr.foundationskt.*         // Core: Fragment, RowCodec, Operation, Transactor, *Types
import dev.typr.foundationskt.connect.* // Connection: SimpleDataSource, *Config
import dev.typr.foundationskt.data.*    // Data types: Json, Range, Uint4, etc.

Scala

import dev.typr.foundationssc.*         // Core: Fragment, RowCodec, Operation, Transactor, *Types
import dev.typr.foundationssc.connect.* // Connection: SimpleDataSource, *Config
import dev.typr.foundationssc.data.*    // Data types: Json, Range, Uint4, etc.

Setting Up a Connection

The quickest way to get started is with DuckDB in-memory using SingleConnectionDataSource. DuckDB is an embedded database that requires no Docker, no server, and no setup — just add the dependency and go:

Kotlin

val tx: Transactor =
    SingleConnectionDataSource.create(DuckDbConfig.inMemory().build())
        .transactor()

val result: Int = sql { "SELECT 42" }
    .queryExactlyOne(DuckDbTypes.integer)
    .transact(tx)

Java

Transactor tx =
    SingleConnectionDataSource.create(
            DuckDbConfig.inMemory().build())
        .transactor();

int result = Fragment.of("SELECT 42")
    .queryExactlyOne(DuckDbTypes.integer)
    .transact(tx);

Scala

val tx: Transactor =
  SimpleDataSource.create(
      DuckDbConfig.inMemory().build())
    .transactor()

val result: Int = sql"SELECT 42"
  .queryExactlyOne(DuckDbTypes.integer)
  .transact(tx)

Show entire file

For production connection setup with PostgreSQL, MariaDB, and other databases, see Transactors.

Your First Query

Define a record, a row codec that maps columns to fields, and a query that returns typed results:

Kotlin

data class City(val name: String, val population: Int)

val cityCodec: RowCodecNamed<City> =
    RowCodec.namedBuilder<City>()
        .field("name", DuckDbTypes.varchar, City::name)
        .field("population", DuckDbTypes.integer, City::population)
        .build(::City)

val findCities: Operation<List<City>> =
    sql { "SELECT ${cityCodec.columnList} FROM city ORDER BY population DESC" }
        .query(cityCodec.all())

Java

record City(String name, int population) {}

static final RowCodecNamed<City> cityCodec =
    RowCodec.<City>namedBuilder()
        .field("name", DuckDbTypes.varchar, City::name)
        .field("population", DuckDbTypes.integer, City::population)
        .build(City::new);

static final Operation<List<City>> findCities =
    Fragment.of("SELECT ")
        .append(cityCodec.columnList())
        .append(" FROM city ORDER BY population DESC")
        .query(cityCodec.all());

Scala

case class City(name: String, population: Int)

val cityCodec: RowCodecNamed[City] = RowCodec.namedBuilder[City]()
  .field("name", DuckDbTypes.varchar)(_.name)
  .field("population", DuckDbTypes.integer)(_.population)
  .build(City.apply)

val findCities: Operation[List[City]] =
  sql"SELECT ${cityCodec.columnList} FROM city ORDER BY population DESC"
    .query(cityCodec.all())

Show entire file

The RowCodecNamed maps database columns to record fields by name. Use .all() to collect all rows, .exactlyOne() for a single result, or .maxOne() for an optional result. See Row Codecs for more.

Parameterized Queries

Use your types and codecs in a plain function to build parameterized queries:

Kotlin

fun findCityByName(name: String): Operation<City?> =
    sql { "SELECT ${cityCodec.columnList} FROM city WHERE name = ${DuckDbTypes.varchar(name)}" }
        .query(cityCodec.maxOne())

Java

static Operation<Optional<City>> findCityByName(String name) {
    return Fragment.of("SELECT ")
        .append(cityCodec.columnList())
        .append(" FROM city WHERE name = ")
        .value(DuckDbTypes.varchar, name)
        .query(cityCodec.maxOne());
}

Scala

def findCityByName(name: String): Operation[Option[City]] =
  sql"SELECT ${cityCodec.columnList} FROM city WHERE name = ${DuckDbTypes.text(name)}"
    .query(cityCodec.maxOne())

Show entire file

Values are always bound as JDBC parameters — never concatenated into SQL strings.

What's Next

Continue reading: Fragments → Row Codecs → Operations → Transactors → Query Analysis

Jump to a topic: Database Types (type catalog) · Templates (Advanced: reusable param holes)

Full Examples

• example-kotlin — DuckDB with domain types, repositories, services, and query analysis
• example-spring-boot — Java Spring Boot with HikariCP connection pooling