Getting Started

Quick Start

Write and execute your first ExoQuery in minutes

Getting Started with JDBC

JDBC is the standard Java database connectivity API and the most common choice for JVM applications. ExoQuery provides full support for JDBC with connection pooling integration.

Setup

First, add the JDBC runner dependency and your database driver:

dependencies {
  implementation("io.exoquery:exoquery-runner-jdbc:{EXOQUERY_RUNNER_VERSION}")
  // Choose your database driver:
  implementation("org.postgresql:postgresql:42.7.0")           // PostgreSQL
  // implementation("com.mysql:mysql-connector-j:8.3.0")       // MySQL
  // implementation("com.microsoft.sqlserver:mssql-jdbc:12.6.1.jre11") // SQL Server
  // implementation("com.oracle.database.jdbc:ojdbc11:23.3.0.23.09") // Oracle
  // implementation("com.h2database:h2:2.2.224")               // H2
  // implementation("org.xerial:sqlite-jdbc:3.45.1.0")         // SQLite
}

Basic Usage

import io.exoquery.*
import io.exoquery.controller.JdbcControllers
import javax.sql.DataSource

// Create a JVM DataSource (using HikariCP, your framework, etc.)
val ds: DataSource = createMyDataSource()

// Create a JDBC controller for your database
val controller = JdbcControllers.Postgres(ds)

// Write queries using the ExoQuery DSL
val query = sql {
  Table<Person>().filter { p -> p.name == "Joe" }
}

// Build for the right dialect and execute
val results: List<Person> = query.buildFor.Postgres().runOn(controller)

Using HikariCP Configuration

ExoQuery integrates seamlessly with HikariCP for connection pooling:

// src/main/resources/application.conf
myDatabase {
  dataSourceClassName=org.postgresql.ds.PGSimpleDataSource
  dataSource.databaseName=mydb
  dataSource.serverName=localhost
  dataSource.portNumber=5432
  dataSource.user=postgres
  dataSource.password=${?POSTGRES_PASSWORD}
}
// Load the configuration automatically
val controller = JdbcControllers.Postgres.fromConfig("myDatabase")

// Now use the controller
val results = query.buildFor.Postgres().runOn(controller)

Supported Databases

JDBC support is available for:

  • PostgreSQL - Full support including RETURNING clauses and DISTINCT ON
  • MySQL - Full support with ON DUPLICATE KEY UPDATE
  • SQL Server - Full support with OUTPUT clauses and IDENTITY_INSERT handling
  • Oracle - Full support with Oracle-specific syntax
  • H2 - Full support for testing and development
  • SQLite - Full support for embedded databases

Key Features

  • Connection Pooling - Works with HikariCP, C3P0, and other connection pools
  • Prepared Statements - All queries use parameterized statements for security
  • Batch Operations - Efficient batch inserts, updates, and deletes
  • Transactions - Full transaction support with rollback handling
  • Type Safety - Compile-time type checking for queries
  • All Query Features - Supports joins, aggregations, subqueries, window functions, etc.

Getting Started with R2DBC (Reactive)

ExoQuery provides full support for R2DBC (Reactive Relational Database Connectivity), enabling non-blocking, reactive database operations. R2DBC is ideal for high-concurrency applications and reactive frameworks like Spring WebFlux or Ktor.

Setup

First, add the R2DBC runner dependency for your database:

dependencies {
  implementation("io.exoquery:exoquery-runner-r2dbc:{EXOQUERY_RUNNER_VERSION}")
  // Choose your database driver:
  implementation("org.postgresql:r2dbc-postgresql:1.0.5.RELEASE")  // PostgreSQL
  // implementation("io.asyncer:r2dbc-mysql:1.1.0")          // MySQL
  // implementation("io.r2dbc:r2dbc-mssql:1.0.2.RELEASE")    // SQL Server
  // implementation("io.r2dbc:r2dbc-h2:1.0.0.RELEASE")       // H2
  // implementation("com.oracle.database.r2dbc:oracle-r2dbc:1.2.0") // Oracle
}

Basic Usage

import io.r2dbc.spi.ConnectionFactories
import io.r2dbc.spi.ConnectionFactoryOptions
import io.exoquery.controller.r2dbc.R2dbcControllers

// Create an R2DBC connection factory
val connectionFactory = ConnectionFactories.get(
  ConnectionFactoryOptions.builder()
    .option(ConnectionFactoryOptions.DRIVER, "postgresql")
    .option(ConnectionFactoryOptions.HOST, "localhost")
    .option(ConnectionFactoryOptions.PORT, 5432)
    .option(ConnectionFactoryOptions.DATABASE, "mydb")
    .option(ConnectionFactoryOptions.USER, "user")
    .option(ConnectionFactoryOptions.PASSWORD, "password")
    .build()
)

// Create an R2DBC controller
val controller = R2dbcControllers.Postgres(connectionFactory = connectionFactory)

// Write queries using the same DSL as JDBC
val query = sql {
  Table<Person>().filter { p -> p.age > 21 }
}

// Execute the query reactively
val results: List<Person> = query.buildFor.Postgres().runOn(controller)

Supported Databases

R2DBC support is available for:

  • PostgreSQL - Full support including RETURNING clauses and DISTINCT ON
  • MySQL - Full support with ON DUPLICATE KEY UPDATE
  • SQL Server - Full support with OUTPUT clauses and IDENTITY_INSERT handling
  • H2 - Full support for testing and development
  • Oracle - Infrastructure ready (connection factory available)

Key Features

  • Non-blocking I/O - Fully reactive, non-blocking database operations
  • Identical DSL - Same query syntax as JDBC for easy migration
  • Type Safety - Compile-time type checking for queries
  • Backpressure - Built-in support through reactive streams
  • All Query Features - Supports joins, aggregations, subqueries, window functions, etc.
  • Batch Operations - Efficient batch inserts, updates, and deletes
  • Transactions - Full transaction support with the same API as JDBC

When to Use R2DBC

R2DBC provides benefits in:

  • High-concurrency applications
  • Reactive frameworks (Spring WebFlux, Ktor)
  • I/O-bound workloads
  • Microservices requiring non-blocking behavior
  • Applications needing efficient resource utilization

For CPU-bound operations or simpler applications, JDBC may be more appropriate.

Getting Started with Android

ExoQuery provides native Android support using the AndroidX SQLite framework. This allows you to use ExoQuery’s type-safe DSL in your Android applications with local SQLite databases.

Setup

Add the ExoQuery plugin and Android runner to your build:

// build.gradle.kts
plugins {
  kotlin("android") version "{KOTLIN_VERSION}"
  id("io.exoquery.exoquery-plugin") version "{EXOQUERY_PLUGIN_VERSION}"
  kotlin("plugin.serialization") version "{KOTLIN_VERSION}"
}

dependencies {
  implementation("io.exoquery:exoquery-runner-android:{EXOQUERY_RUNNER_VERSION}")
  implementation("org.jetbrains.kotlinx:kotlinx-serialization-core:1.6.2")
  implementation("org.jetbrains.kotlinx:kotlinx-serialization-json:1.6.2")
  implementation("androidx.sqlite:sqlite-framework:2.4.0")
}

Construct from ApplicationContext

Use the TerpalAndroidDriver.fromApplicationContext method to create a controller from an Android Application Context:

import io.exoquery.*
import io.exoquery.controller.android.TerpalAndroidDriver
import io.exoquery.controller.android.PoolingMode
import androidx.test.core.app.ApplicationProvider

// Create the Android driver
val controller = TerpalAndroidDriver.fromApplicationContext(
  databaseName = "mydb",
  applicationContext = ApplicationProvider.getApplicationContext(),
  // Optional: A TerpalSchema object defining the database schema and migrations
  // Alternatively, a SqlDelight SqlSchema or SupportSQLiteOpenHelper.Callback can be used
  schema = MyTerpalSchema,
  // Optional: Connection pooling mode (defaults to SingleSessionWal)
  poolingMode = PoolingMode.SingleSessionWal
)

// Write queries using the ExoQuery DSL
val query = sql {
  Table<Person>().filter { p -> p.name == "Joe" }
}

// Execute the query
val results: List<Person> = query.buildFor.Sqlite().runOn(controller)

Pooling Modes

ExoQuery Android supports different connection pooling strategies:

// Default mode using Android WAL compatibility mode
poolingMode = PoolingMode.SingleSessionWal

// MVCC-like transaction isolation with real multi-reader concurrency
// Uses multiple SupportSQLiteOpenHelper instances - be careful with memory
poolingMode = PoolingMode.MultipleReaderWal(2)

// Pre-WAL mode for compatibility with older Android versions
poolingMode = PoolingMode.SingleSessionLegacy

Construct from SupportSQLiteOpenHelper

You can also create a controller from an existing SupportSQLiteOpenHelper:

import androidx.sqlite.db.SupportSQLiteOpenHelper
import androidx.sqlite.db.framework.FrameworkSQLiteOpenHelperFactory

val myOpenHelperInstance = FrameworkSQLiteOpenHelperFactory().create(
  SupportSQLiteOpenHelper.Configuration.builder(androidApplicationContext)
    .name(databaseName)
    // Other options: callback, factory, etc.
    .build()
)

val controller = TerpalAndroidDriver.fromSingleOpenHelper(
  openHelper = myOpenHelperInstance
)

Construct from SupportSQLiteDatabase

For even more control, create a controller from an existing database instance:

val myDatabaseInstance = myOpenHelperInstance.writableDatabase

val controller = TerpalAndroidDriver.fromSingleSession(
  database = myDatabaseInstance
)

Database Schema Management

Define your database schema using a TerpalSchema object:

import io.exoquery.controller.TerpalSchema
import io.exoquery.controller.Controller

object MyTerpalSchema : TerpalSchema<Unit> {
  override val version: Long = 1

  override suspend fun create(driver: Controller<*>) {
    driver.runActions(
      """
      CREATE TABLE Person (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        age INTEGER NOT NULL
      )
      """,
      """
      CREATE TABLE Address (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        owner_id INTEGER REFERENCES Person(id),
        street TEXT NOT NULL,
        city TEXT NOT NULL
      )
      """
    )
  }

  override suspend fun migrate(
    driver: Controller<*>,
    oldVersion: Long,
    newVersion: Long
  ) {
    // Handle migrations between versions
    if (oldVersion < 2) {
      driver.runActions("ALTER TABLE Person ADD COLUMN email TEXT")
    }
  }
}

Key Features

  • AndroidX SQLite - Uses the modern AndroidX SQLite framework
  • Coroutine Support - All constructors are suspend functions to avoid blocking the main thread
  • Flexible Pooling - Multiple WAL modes for different concurrency needs
  • Schema Migrations - Works with TerpalSchema, SqlDelight SqlSchema, or SupportSQLiteOpenHelper.Callback
  • Same DSL - Identical query syntax as JDBC and R2DBC
  • Type Safety - Compile-time type checking for all queries

Note: Most constructors on TerpalAndroidDriver are suspended functions. This is because creating a SupportSQLiteDatabase frequently involves database schema creation and migration. These controllers must be created from a coroutine to avoid blocking the main thread.

Getting Started with Native (iOS, macOS, Linux, Windows)

ExoQuery supports Kotlin Multiplatform Native targets, allowing you to use the same type-safe query DSL across iOS, macOS, Linux, and Windows applications. ExoQuery Native uses SQLighter as the underlying database driver.

Setup

Configure your Kotlin Multiplatform project with the ExoQuery plugin:

// build.gradle.kts
plugins {
  kotlin("multiplatform") version "{KOTLIN_VERSION}"
  id("io.exoquery.exoquery-plugin") version "{EXOQUERY_PLUGIN_VERSION}"
  kotlin("plugin.serialization") version "{KOTLIN_VERSION}"
}

kotlin {
  // Define your targets (iOS, macOS, Linux, Windows)
  iosX64()
  iosArm64()
  iosSimulatorArm64()
  macosX64()
  macosArm64()
  linuxX64()
  mingwX64()

  sourceSets {
    val commonMain by getting {
      dependencies {
        implementation("org.jetbrains.kotlinx:kotlinx-serialization-core:1.6.2")
        implementation("io.exoquery:exoquery-runner-native:{EXOQUERY_RUNNER_VERSION}")
      }
    }
  }
}

Basic Usage

Create the TerpalNativeDriver using the fromSchema method:

import io.exoquery.*
import io.exoquery.controller.native.TerpalNativeDriver

// Create the native driver with a schema
val controller = TerpalNativeDriver.fromSchema(
  schema = MyTerpalSchema
)

// Write queries using the ExoQuery DSL
val query = sql {
  Table<Person>().filter { p -> p.age > 21 }
}

// Execute the query
val results: List<Person> = query.buildFor.Sqlite().runOn(controller)

Driver Configuration Options

The TerpalNativeDriver supports several configuration options:

val controller = TerpalNativeDriver.fromSchema(
  // A TerpalSchema object defining the database schema and migrations
  // Alternatively, a SqlDelight SqlSchema object can be used
  schema = MyTerpalSchema,
  // Name of the database file to use
  databaseName = "mydb",
  // Base path for the database file (defaults to Kotlin working directory)
  basePath = "/my/custom/path"
)

Database Schema Definition

Define your database schema using a TerpalSchema object:

import io.exoquery.controller.TerpalSchema
import io.exoquery.controller.Controller

object MyTerpalSchema : TerpalSchema<Unit> {
  override val version: Long = 1

  override suspend fun create(driver: Controller<*>) {
    driver.runActions(
      """
      CREATE TABLE Person (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        age INTEGER NOT NULL
      )
      """,
      """
      CREATE TABLE Address (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        owner_id INTEGER REFERENCES Person(id),
        street TEXT NOT NULL,
        city TEXT NOT NULL
      )
      """
    )
  }

  override suspend fun migrate(
    driver: Controller<*>,
    oldVersion: Long,
    newVersion: Long
  ) {
    // Handle migrations between versions
    if (oldVersion < 2) {
      driver.runActions("ALTER TABLE Person ADD COLUMN email TEXT")
    }
  }
}

Supported Platforms

Native support is available for:

  • iOS - iosX64, iosArm64, iosSimulatorArm64
  • macOS - macosX64, macosArm64
  • Linux - linuxX64, linuxArm64
  • Windows - mingwX64

Key Features

  • True Multiplatform - Share query code across all native platforms
  • SQLighter Backend - Uses SQLighter as the underlying database driver
  • No JVM Required - Runs natively without JVM overhead
  • Same DSL - Identical query syntax as JVM platforms
  • Type Safety - Compile-time type checking on all platforms
  • Schema Support - Works with TerpalSchema or SqlDelight SqlSchema objects

Shared Code Example

With Kotlin Multiplatform, you can define queries in common code:

// commonMain/kotlin/Repository.kt
class PersonRepository(private val controller: TerpalNativeDriver) {

  suspend fun getAdults(): List<Person> {
    val query = sql {
      Table<Person>().filter { p -> p.age >= 18 }
    }
    return query.buildFor.Sqlite().runOn(controller)
  }

  suspend fun findByName(name: String): List<Person> {
    val query = sql {
      Table<Person>().filter { p -> p.name == param(name) }
    }
    return query.buildFor.Sqlite().runOn(controller)
  }
}

This repository can then be used from iOS Swift code, macOS apps, or any other native target.

Next Steps

  • Learn about SQL Blocks to understand how the sql { } construct works
  • Explore Query Operations to see what you can do with queries
  • Check out Parameters to learn how to use runtime data in queries