# sqlx4k

> A coroutine-first SQL toolkit with compile-time query validations for Kotlin Multiplatform.

# Getting started

# sqlx4k

A coroutine-first SQL toolkit with compile-time query validations for Kotlin Multiplatform. PostgreSQL, MySQL/MariaDB, and SQLite are supported.

**sqlx4k** is not an ORM. Instead, it provides a comprehensive toolkit of primitives and utilities to communicate directly with your database. The focus is on giving you control while catching errors early through compile-time query validation, preventing runtime surprises before they happen (see [SQL syntax and schema validation](<https://smyrgeorge.github.io/sqlx4k/codegen/validation/index.md>)).

The library is designed to be extensible, with a growing ecosystem of tools and extensions like [PGMQ](<https://smyrgeorge.github.io/sqlx4k/extensions/pgmq/index.md>) (PostgreSQL Message Queue), [SQLDelight](<https://smyrgeorge.github.io/sqlx4k/extensions/sqldelight/index.md>) integration, and more.

## Quick look

The suggested way to use sqlx4k: map your tables with `@Table`, declare your queries in a `@Repository` interface, and let the [code generator](<https://smyrgeorge.github.io/sqlx4k/codegen/index.md>) write the implementation (with the CRUD operations), the row mappers, and [validate](<https://smyrgeorge.github.io/sqlx4k/codegen/validation/index.md>) every query while it builds:

```kotlin
@Table("users")
data class User(
    @Id
    val id: Long = 0, // Generated by the database, and read back on insert.
    val name: String,
    @Column(name = "mail_address")
    val email: String,
)

@Repository
interface UserRepository : ContextCrudRepository<User> {
    @Query("SELECT * FROM users WHERE mail_address = :email")
    context(context: QueryExecutor)
    suspend fun findOneByEmail(email: String): Result<User?>
}

// The transaction returns the value of its block: here, the updated user.
val updated: User = db.transaction {
    val alice = UserRepositoryImpl.insert(User(name = "Alice", email = "alice@example.com")).getOrThrow()
    UserRepositoryImpl.update(alice.copy(name = "Alice Smith")).getOrThrow()
}

with(db) {
    val alice: User? = UserRepositoryImpl.findOneByEmail("alice@example.com").getOrThrow()
}
```

See [Repositories](<https://smyrgeorge.github.io/sqlx4k/codegen/repositories/index.md>) for the details.

Or run your SQL directly with the driver:

```kotlin
val db = PostgreSQL(
    url = "postgresql://localhost:15432/test",
    username = "postgres",
    password = "postgres",
)

db.execute("insert into users(id, name) values (1, 'Alice');").getOrThrow()

db.transaction {
    execute("update users set name = 'Bob' where id = 1;").getOrThrow()
    fetchAll("select * from users;").getOrThrow().forEach { println(it) }
}
```

## Features

- **[Supported databases](<https://smyrgeorge.github.io/sqlx4k/databases/postgresql/index.md>)**

  PostgreSQL, MySQL, MariaDB, SQLite and SQLCipher (encrypted SQLite).
- **[Coroutines everywhere](<https://smyrgeorge.github.io/sqlx4k/guide/index.md>)**

  Every query API is a `suspend` function returning a `Result`, on non-blocking I/O on every platform.
- **[Gradle plugin](<https://smyrgeorge.github.io/sqlx4k/getting-started/gradle-plugin/index.md>)**

  One `sqlx4k { }` block adds the driver, the extensions and the code generator.
- **[Code generation](<https://smyrgeorge.github.io/sqlx4k/codegen/index.md>)**

  CRUD and `@Repository` implementations, row mappers, batch operations, optimistic locking.
- **[Compile-time validation](<https://smyrgeorge.github.io/sqlx4k/codegen/validation/index.md>)**

  `@Query` SQL is checked for syntax, columns, tables and statement kind during the build.
- **[In-memory repositories](<https://smyrgeorge.github.io/sqlx4k/codegen/in-memory-repositories/index.md>)**

  Generated, thread-safe in-memory implementations of your repositories for unit tests.
- **[Transactions](<https://smyrgeorge.github.io/sqlx4k/guide/transactions/index.md>)**

  Block-scoped transactions, savepoints and transaction propagation through the coroutine context.
- **[Migrations](<https://smyrgeorge.github.io/sqlx4k/guide/migrations/index.md>)**

  Run pending migrations and validate the ones already applied.

### Supported databases

-
-
-
-
- (encrypted SQLite, `sqlx4k-sqlite-cipher`)

sqlx4k runs on the JVM, Android, iOS, macOS, Linux and Windows: see [Supported targets](<https://smyrgeorge.github.io/sqlx4k/platforms/index.md>).

## Articles

Short deep-dive posts covering Kotlin/Native, FFI, and Rust ↔ Kotlin interop used in sqlx4k, published on the author's [homepage](<https://smyrgeorge.github.io/>) (under construction):

- *Introduction to the Kotlin Native and FFI:* [\[Part 1\]](<https://smyrgeorge.github.io/posts/sqlx4k---introduction-to-the-kotlin-native-and-ffi-part-1/>), [\[Part 2\]](<https://smyrgeorge.github.io/posts/sqlx4k---introduction-to-the-kotlin-native-and-ffi-part-2/>)
- *Interoperability between Kotlin and Rust, using FFI:* [\[Part 1\]](<https://smyrgeorge.github.io/posts/sqlx4k---interoperability-between-kotlin-and-rust-using-ffi-part-1/>), (Part 2 soon)

## License

MIT, see [LICENSE](<https://github.com/smyrgeorge/sqlx4k/blob/main/LICENSE>).

# AI agents

Coding agents write better sqlx4k code when they know its conventions: the Gradle plugin, `@Table` entities, `@Repository` interfaces, and the rules the build enforces on every `@Query`. sqlx4k gives them two ways to learn them.

## Agent skill

sqlx4k ships an [Agent Skill](<https://agentskills.io/>): a short guide to using the library, with reference files the agent reads only when a task needs them. It uses the open `SKILL.md` format, which Claude Code, Codex, Cursor, GitHub Copilot, Gemini CLI and many other agents support.

Install it with the [skills](<https://github.com/vercel-labs/skills>) command-line tool, which detects your agents and asks which ones to install it for:

```shell
npx skills add smyrgeorge/sqlx4k
```

Add `-g` to install it for all your projects instead of the current one.

Or copy the [skills/sqlx4k](<https://github.com/smyrgeorge/sqlx4k/tree/main/skills/sqlx4k>) folder of the repository into your agent's skills directory, e.g. `.claude/skills/` (Claude Code); see your agent's documentation for its directory.

The agent then uses the skill on its own when it works on code that uses sqlx4k. The skill is updated with every release: reinstall it when you upgrade sqlx4k.

## llms.txt

For agents that read documentation from the web, the site publishes:

- [llms.txt](<https://smyrgeorge.github.io/sqlx4k/llms.txt>): an index of the documentation pages, following the [llms.txt](<https://llmstxt.org/>) convention;
- [llms-full.txt](<https://smyrgeorge.github.io/sqlx4k/llms-full.txt>): the whole documentation in a single Markdown file;
- every page as Markdown, at its address followed by `index.md` (e.g. [guide/errors/index.md](<https://smyrgeorge.github.io/sqlx4k/guide/errors/index.md>)).

# Examples

Full runnable applications, built with the [Gradle plugin](<https://smyrgeorge.github.io/sqlx4k/getting-started/gradle-plugin/index.md>), live in the repository:

- PostgreSQL: [examples/postgres](<https://github.com/smyrgeorge/sqlx4k/tree/main/examples/postgres>)
- MySQL: [examples/mysql](<https://github.com/smyrgeorge/sqlx4k/tree/main/examples/mysql>)
- SQLite: [examples/sqlite](<https://github.com/smyrgeorge/sqlx4k/tree/main/examples/sqlite>)

## Running the examples

First, start up the database instances:

```shell
docker compose up -d
```

Then [build the project](<https://smyrgeorge.github.io/sqlx4k/contributing/building/index.md>) and run the examples:

```shell
# For macosArm64
./examples/postgres/build/bin/macosArm64/releaseExecutable/postgres.kexe
./examples/mysql/build/bin/macosArm64/releaseExecutable/mysql.kexe
./examples/sqlite/build/bin/macosArm64/releaseExecutable/sqlite.kexe
# If you run in another platform consider running the correct target.
```

# Gradle plugin

The Gradle plugin (`sqlx4k-gradle-plugin`) sets up sqlx4k in a project from a single `sqlx4k { }` block, replacing the manual KSP wiring shown in [Code generation](<https://smyrgeorge.github.io/sqlx4k/codegen/#manual-setup>). When applied, it:

- applies the KSP Gradle plugin and registers the sqlx4k code generator (`sqlx4k-codegen`) on the configured source sets (`commonMain` by default), at the plugin's own version,
- passes to the code generator the SQL dialect of the chosen `driver` and the `generatedCodePackage`, followed by any option you put in `args` (so an `args` entry can override both),
- adds the generated sources of `commonMain` to the project and orders every Kotlin compilation and KSP task after the `commonMain` code generation, so the generated code exists before anything compiles,
- adds the driver (e.g. `sqlx4k-postgres`) and the enabled extensions (e.g. `sqlx4k-postgres-pgmq`) as dependencies at the matching version, as `implementation` or, with `dependencyScope = Api`, as `api` (for a library module whose public API, e.g. the generated code, exposes sqlx4k types), unless `addDependencies = false`.

A complete build script (see the [examples](<https://smyrgeorge.github.io/sqlx4k/getting-started/examples/index.md>), which are built with the plugin):

```kotlin
plugins {
    kotlin("multiplatform") // or kotlin("jvm")
    id("io.github.smyrgeorge.sqlx4k") version "1.17.0"
}

kotlin {
    jvm()
    macosArm64 { binaries { executable() } }
    // Include other targets as needed
}

sqlx4k {
    driver = PostgreSQL // also: MySQL, MariaDB, SQLite, SQLiteCipher
    generatedCodePackage = "io.github.smyrgeorge.sqlx4k.examples.postgres"
    extensions(Pgmq) // sqlx4k extensions; Pgmq (`sqlx4k-postgres-pgmq`) is PostgreSQL only, Arrow (`sqlx4k-arrow`)
    // Any sqlx4k code-generator option, applied last.
    // See "Code generation" for the full list.
    args = mapOf("expand-select-star" to "false")
}
```

## Options

| Option | Default | Description |
| --- | --- | --- |
| `driver` | required | The database driver. It also selects the SQL dialect of the code generator. |
| `generatedCodePackage` | required | The package of the generated sources (the code generator's `output-package`). |
| `sourceSets` | `["commonMain"]` | The source sets the code generator processes: `commonMain` (generated once, visible to every target), a target's `<target>Main`, or `main` for plain JVM projects. |
| `extensions(...)` | none | The sqlx4k extensions to add: `Pgmq` (PostgreSQL only) and `Arrow`. |
| `args` / `arg(k, v)` | none | Code-generator options, applied last (an entry under `dialect` or `output-package` overrides the derived value). |
| `addDependencies` | `true` | Whether the driver and extension dependencies are added at the plugin's version. Disable to manage them (and their versions) yourself. |
| `dependencyScope` | `Implementation` | The configuration the driver and extension dependencies are added to: `Implementation` or `Api` (`commonMainApi`, or `api` for plain JVM projects). |

The options are documented in detail in [Sqlx4kExtension.kt](<https://github.com/smyrgeorge/sqlx4k/blob/main/sqlx4k-gradle-plugin/src/main/kotlin/io/github/smyrgeorge/sqlx4k/gradle/Sqlx4kExtension.kt>). The code-generator options you can pass through `args` are listed in [Code generation](<https://smyrgeorge.github.io/sqlx4k/codegen/index.md>) and [Validation](<https://smyrgeorge.github.io/sqlx4k/codegen/validation/index.md>).

> [!TIP]
>
> **Plain JVM projects**
>
> For a plain JVM project (`kotlin("jvm")`) set `sourceSets = listOf("main")`; KSP then wires the generated sources into the compilation itself.

# Installation

sqlx4k is published to Maven Central.

## With the Gradle plugin (recommended)

The suggested way to set up sqlx4k: the [Gradle plugin](<https://smyrgeorge.github.io/sqlx4k/getting-started/gradle-plugin/index.md>) adds the driver of your database and sets up the [code generation](<https://smyrgeorge.github.io/sqlx4k/codegen/index.md>) (`@Table`, `@Repository`, compile-time validations and optimizations) for you, from a single `sqlx4k { }` block:

```kotlin
plugins {
    kotlin("multiplatform") // or kotlin("jvm")
    id("io.github.smyrgeorge.sqlx4k") version "1.17.0"
}

sqlx4k {
    driver = PostgreSQL // also: MySQL, MariaDB, SQLite, SQLiteCipher
    generatedCodePackage = "com.example.generated"
}
```

See [Gradle plugin](<https://smyrgeorge.github.io/sqlx4k/getting-started/gradle-plugin/index.md>) for all the options, such as the extensions and the code-generator arguments.

> [!TIP]
>
> **Using an AI coding agent?**
>
> Install the sqlx4k skill, so your agent (Claude Code, Codex, Cursor, Copilot, …) sets up the project and writes the code the recommended way:
>
> ```shell
> npx skills add smyrgeorge/sqlx4k
> ```
>
> See [AI agents](<https://smyrgeorge.github.io/sqlx4k/getting-started/ai-agents/index.md>).

## Without the plugin

Or add the driver of your database yourself:

```kotlin
implementation("io.github.smyrgeorge:sqlx4k-postgres:1.17.0")
```

```kotlin
implementation("io.github.smyrgeorge:sqlx4k-mysql:1.17.0")
```

```kotlin
implementation("io.github.smyrgeorge:sqlx4k-sqlite:1.17.0")
```

```kotlin
// SQLite with encryption.
implementation("io.github.smyrgeorge:sqlx4k-sqlite-cipher:1.17.0")
```

> [!NOTE]
>
> The driver alone does not set up the [code generation](<https://smyrgeorge.github.io/sqlx4k/codegen/index.md>) (`@Table`, `@Repository`, compile-time validations): use the plugin, or configure KSP yourself as described in [Manual setup](<https://smyrgeorge.github.io/sqlx4k/codegen/#manual-setup>).

## Modules

| Module | Description |
| --- | --- |
| `sqlx4k` | The core API: drivers' common interfaces, statements, result sets, transactions, migrations. |
| `sqlx4k-postgres` | The [PostgreSQL](<https://smyrgeorge.github.io/sqlx4k/databases/postgresql/index.md>) driver. |
| `sqlx4k-mysql` | The [MySQL / MariaDB](<https://smyrgeorge.github.io/sqlx4k/databases/mysql/index.md>) driver. |
| `sqlx4k-sqlite` | The [SQLite](<https://smyrgeorge.github.io/sqlx4k/databases/sqlite/index.md>) driver. |
| `sqlx4k-sqlite-cipher` | The [SQLCipher](<https://smyrgeorge.github.io/sqlx4k/databases/sqlcipher/index.md>) (encrypted SQLite) driver. |
| `sqlx4k-codegen` | The KSP [code generator](<https://smyrgeorge.github.io/sqlx4k/codegen/index.md>) for `@Table` entities and `@Repository` interfaces. |
| `sqlx4k-codegen-test` | Generates [in-memory repositories](<https://smyrgeorge.github.io/sqlx4k/codegen/in-memory-repositories/index.md>) for unit tests. |
| `sqlx4k-arrow` | Repository interfaces for [Arrow](<https://arrow-kt.io/>) (see [Repositories](<https://smyrgeorge.github.io/sqlx4k/codegen/repositories/#repository-interfaces>)). |
| `sqlx4k-postgres-pgmq` | A client for the [PostgreSQL Message Queue](<https://smyrgeorge.github.io/sqlx4k/extensions/pgmq/index.md>). |
| `sqlx4k-gradle-plugin` | The [Gradle plugin](<https://smyrgeorge.github.io/sqlx4k/getting-started/gradle-plugin/index.md>). |

# Supported targets

- jvm
- android (`sqlx4k-sqlite` and `sqlx4k-sqlite-cipher` only, minSdk 26)
- iosArm64
- iosSimulatorArm64
- androidNativeX64
- androidNativeArm64
- macosArm64
- linuxArm64
- linuxX64
- mingwX64
- wasmWasi (potential future candidate)

On native targets the drivers are backed by [sqlx](<https://github.com/launchbadge/sqlx>) (Rust), through Kotlin/Native FFI. On the JVM they use R2DBC (PostgreSQL, MySQL) and JDBC (SQLite). See each [database](<https://smyrgeorge.github.io/sqlx4k/databases/postgresql/index.md>) page for the details.

## Windows

If you are building your project on Windows, for target mingwX64, and you encounter the following error:

```text
lld-link: error: -exclude-symbols:___chkstk_ms is not allowed in .drectve
```

Please look at this issue: [\#18](<https://github.com/smyrgeorge/sqlx4k/issues/18>)

# Databases

# MySQL / MariaDB

```kotlin
implementation("io.github.smyrgeorge:sqlx4k-mysql:1.17.0")
```

The same driver serves both MySQL and MariaDB. On native targets it is backed by [sqlx](<https://github.com/launchbadge/sqlx>) (Rust), on the JVM by [r2dbc-mysql](<https://github.com/asyncer-io/r2dbc-mysql>).

## Connecting

```kotlin
val db = MySQL(
    url = "mysql://localhost:13306/test",
    username = "mysql",
    password = "mysql",
    options = options // see Connection pool
)
```

In a multiplatform setup, use the `mySQL(...)` function instead, which takes the same parameters.

The connection URL follows the pattern described by [MySQL](<https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-jdbc-url-format.html>):

```text
mysql://[host][:port][/database][?properties]
```

The port defaults to 3306. The supported properties are the ones the native driver (sqlx) reads: `ssl-mode` (`disabled`, `preferred`, `required`, `verify_ca`, `verify_identity`), `ssl-ca`, `ssl-cert`, `ssl-key`, `socket` and `timezone` (the session time zone, UTC by default), as well as r2dbc's `ssl=true|false`. `charset`, `collation` and `statement-cache-capacity` are accepted and have no effect on the JVM. Any other property is rejected.

See [Connection pool](<https://smyrgeorge.github.io/sqlx4k/guide/connection-pool/index.md>) for the pool `options`.

## Notes

- `executeWithResult` reports the `AUTO_INCREMENT` id of an inserted row; for a multi-row insert, the first generated id (see [Running queries](<https://smyrgeorge.github.io/sqlx4k/guide/queries/#executewithresult>)).
- MySQL and MariaDB have no `UPDATE ... RETURNING`, so the generated `update()` re-selects the row afterwards (see [Optimistic locking](<https://smyrgeorge.github.io/sqlx4k/codegen/entities/#optimistic-locking-with-version>)).
- Pick the `mysql` or `mariadb` dialect for the code generator: `mariadb` uses `INSERT ... RETURNING` (MariaDB 10.5+) for `insert()` (see [Code generation](<https://smyrgeorge.github.io/sqlx4k/codegen/#dialects>)).
- On MySQL and MariaDB the drivers report the *matched* rows as affected, so a `batchUpdate` entity whose values did not change still counts as updated.
- A statement that reads per-connection state left by the previous one, such as `LAST_INSERT_ID()`, must run on the same connection, which a pool does not guarantee. `withSingleConnection { cx -> ... }` runs its block on a single connection (borrowed from the pool, if needed), and `withReadBackTransaction { cx -> ... }` in a single transaction. Both are in `io.github.smyrgeorge.sqlx4k.mysql`, and the generated code uses them to read back the rows it writes.

> [!NOTE]
>
> **Cancelling a streamed query (native targets)**
>
> A `fetch` flow of a `Connection` or `Transaction` whose collector is cancelled stops between rows: the row being read is read to its end first, as a read stopped midway would leave the connection out of step with the server. So the cancellation waits for that row, e.g. until a lock it waits for is released (up to `innodb_lock_wait_timeout`). The rest of the result is read and discarded on the connection's next use. A flow of the pool closes its connection instead, and stops at once.

# PostgreSQL

```kotlin
implementation("io.github.smyrgeorge:sqlx4k-postgres:1.17.0")
```

On native targets the driver is backed by [sqlx](<https://github.com/launchbadge/sqlx>) (Rust), on the JVM by [r2dbc-postgresql](<https://github.com/pgjdbc/r2dbc-postgresql>).

## Connecting

```kotlin
val db = PostgreSQL(
    url = "postgresql://localhost:15432/test",
    username = "postgres",
    password = "postgres",
    options = options // see Connection pool
)
```

In a multiplatform setup, use the `postgreSQL(...)` function instead, which takes the same parameters.

The URL scheme can be either `postgresql://` or `postgres://`, and each of the URL parts is optional:

```text
postgresql://
postgresql://localhost
postgresql://localhost:5433
postgresql://localhost/mydb
```

See [Connection pool](<https://smyrgeorge.github.io/sqlx4k/guide/connection-pool/index.md>) for the pool `options`.

## LISTEN / NOTIFY

```kotlin
// listen suspends while it listens: run it in a coroutine of its own, and cancel that to stop listening.
val listener = launch {
    db.listen("chan0") { notification: Postgres.Notification ->
        println(notification)
    }
}

(1..10).forEach {
    db.notify("chan0", "Hello $it")
    delay(1000)
}

listener.cancel() // Stops listening and closes the listener's connection.
```

`listen` also accepts a list of channels.

## Notes

- `executeWithResult` always reports a `null` `lastInsertId` on PostgreSQL: use `INSERT ... RETURNING id` with `fetchAll` instead (see [Running queries](<https://smyrgeorge.github.io/sqlx4k/guide/queries/#executewithresult>)).
- Set the code generator's dialect to `postgresql` to enable the PostgreSQL array decoders (see [Code generation](<https://smyrgeorge.github.io/sqlx4k/codegen/#dialects>)). The [Gradle plugin](<https://smyrgeorge.github.io/sqlx4k/getting-started/gradle-plugin/index.md>) does this for you.
- For message queues on top of PostgreSQL, see [PGMQ](<https://smyrgeorge.github.io/sqlx4k/extensions/pgmq/index.md>).

# SQLCipher

```kotlin
implementation("io.github.smyrgeorge:sqlx4k-sqlite-cipher:1.17.0")
```

Encrypted SQLite via [SQLCipher](<https://www.zetetic.net/sqlcipher/>). Unlike [`sqlx4k-sqlite`](<https://smyrgeorge.github.io/sqlx4k/databases/sqlite/index.md>), every target, native (FFI) and JVM/Android (JNI), is backed by the same Rust [sqlx](<https://github.com/launchbadge/sqlx>) + SQLCipher core, so the behavior is the same on every platform.

## Connecting

```kotlin
val db = SQLiteCipher(
    url = "sqlite://test.db", // If not found, a new (encrypted) db will be created.
    password = "a-strong-passphrase",
    options = options // see Connection pool
)
```

- The URL forms are the same as for [SQLite](<https://smyrgeorge.github.io/sqlx4k/databases/sqlite/#url-format>).
- The `password` is applied as the SQLCipher `PRAGMA key`, and the database file is created encrypted on first use.
- An empty password opens an unencrypted database, which is useful for migrating an existing plaintext file.
- A passphrase must not contain a NUL character (U+0000). If your key is made of random bytes, encode them as hex (or Base64) instead.

In a multiplatform setup, use the `sqliteCipher(...)` function instead. On Android it additionally takes a `Context`, used to resolve a relative database filename into the app's private storage.

Unlike the plain SQLite driver on Android, savepoints are supported on every target.

# SQLite

```kotlin
implementation("io.github.smyrgeorge:sqlx4k-sqlite:1.17.0")
```

The SQLite driver runs on every [supported target](<https://smyrgeorge.github.io/sqlx4k/platforms/index.md>), including Android:

| Platform | Backed by |
| --- | --- |
| Native | [sqlx](<https://github.com/launchbadge/sqlx>) (Rust), through Kotlin/Native FFI. |
| JVM | [xerial/sqlite-jdbc](<https://github.com/xerial/sqlite-jdbc>), pooled with HikariCP (transitive dependencies). |
| Android | The platform's `android.database.sqlite`, so no native libraries need to be added (minSdk 26). |

Need encryption? See [SQLCipher](<https://smyrgeorge.github.io/sqlx4k/databases/sqlcipher/index.md>).

## Connecting

```kotlin
val db = SQLite(
    url = "sqlite://test.db", // If the `test.db` file is not found, a new db will be created.
    options = options // see Connection pool
)
```

In a multiplatform setup, use the `sqlite(url, options)` function instead. On Android, use the `sqlite(context, url, options)` overload (or `SQLite(context, url, options)`): the `Context` resolves a relative database filename into the app's databases directory.

## URL format

Every target accepts the same sqlx-style URL, so a single string works everywhere:

| URL | Opens |
| --- | --- |
| `sqlite::memory:` | An in-memory database. |
| `sqlite:data.db` | The file `data.db` in the current directory. |
| `sqlite://data.db` | Same as above. |
| `sqlite:///abs/path/data.db` | The file at an absolute path. |
| `sqlite://data.db?mode=ro` | `data.db` for read-only access. |
| `sqlite:///abs/data.db?mode=rwc` | Read-write, creating the file if missing. |
| `sqlite::memory:?cache=shared` | An in-memory database with a shared cache. |

The query parameters are the ones SQLite defines for URI filenames: `mode` (`ro`, `rw`, `rwc`, `memory`), `cache` (`shared`, `private`), `immutable` and `vfs`. Any other parameter is rejected, so a bad URL fails the same way on every target.

- **JVM:** a `jdbc:sqlite:` URL is accepted as well and skips that check, so JDBC-style URLs with driver-specific parameters (e.g. xerial pragmas such as `?journal_mode=WAL`) keep working. HikariCP raises timeouts below its minimums: `acquireTimeout` to 250 ms, `idleTimeout` to 10 seconds and `maxLifetime` to 30 seconds.
- **Android:** a relative filename lives in the app's databases directory (`Context.getDatabasePath`). `cache` is accepted but ignored, and `immutable` and `vfs` cannot be honoured, so they are rejected. A URL without the `sqlite:` prefix is prefixed automatically.

## In-memory databases

An in-memory SQLite database is private to the connection that opened it, so an in-memory database must use a pool of a single connection; on the JVM and Android a larger pool is rejected:

```kotlin
val db = SQLite(
    url = "sqlite::memory:",
    options = ConnectionPool.Options(minConnections = 1, maxConnections = 1)
)
```

## WAL mode

File-based databases are opened in [Write-Ahead Logging (WAL)](<https://www.sqlite.org/wal.html>) mode, which lets readers run concurrently with a writer. In-memory databases do not support WAL.

## Tips and limitations

- **Write concurrency:** SQLite locks the whole database for a write, so writes are serialized whatever the pool size. A larger pool helps read-heavy workloads; a write-heavy workload gains little from more connections.
- **Batch writes in transactions:** many small writes are much faster inside a single [transaction](<https://smyrgeorge.github.io/sqlx4k/guide/transactions/index.md>).
- **`executeWithResult`** reports the rowid of an inserted row; for a multi-row insert, the last one.
- **Foreign keys in migrations:** see the note in [Migrations](<https://smyrgeorge.github.io/sqlx4k/guide/migrations/index.md>).
- **Android:** savepoints (`savepoint`/`releaseSavepoint`/`rollbackToSavepoint`) are not supported, because `android.database.sqlite` intercepts `ROLLBACK TO SAVEPOINT` and ends the whole transaction. Use [SQLCipher](<https://smyrgeorge.github.io/sqlx4k/databases/sqlcipher/index.md>) (`sqlx4k-sqlite-cipher`) if you need savepoints on Android.

For complete working examples, see [examples/sqlite](<https://github.com/smyrgeorge/sqlx4k/tree/main/examples/sqlite>).

# Guide

# Connection pool

Every driver manages a pool of connections. A query issued directly on the driver (`db.fetchAll(...)`) borrows a connection for its duration and returns it; `db.acquire()` and `db.begin()` hold one until it is released, committed or rolled back. The pool is configured with `ConnectionPool.Options`, passed to the driver's constructor.

## Configuration options

| Option | Default | What it controls |
| --- | --- | --- |
| `minConnections` | driver default | The number of connections the pool keeps open at all times, so they are ready during a burst instead of being opened on demand. Must not exceed `maxConnections`. |
| `maxConnections` | `10` | The upper bound on open connections. Size it to what the database can serve: once reached, callers wait for a connection to be returned. |
| `acquireTimeout` | driver default | How long a caller waits for a free connection before the operation fails with `SQLError.Code.PoolTimedOut`. Set it so a saturated pool surfaces as a timely error rather than a hang. |
| `idleTimeout` | driver default | How long an unused connection may stay in the pool before it is closed, letting the pool shrink back after a burst. Must not exceed `maxLifetime`. |
| `maxLifetime` | driver default | The maximum age of a connection. Once reached it is closed and replaced, even if healthy, which keeps long-lived connections from accumulating server-side state or outliving a failover. |

An option left unset keeps the default of the underlying driver (sqlx on native targets, the R2DBC pool on the JVM). Every value must be positive, `minConnections` must not exceed `maxConnections` and `idleTimeout` must not exceed `maxLifetime`; a violation fails at construction, before any connection is opened.

```kotlin
val options = ConnectionPool.Options.builder()
    .minConnections(2)
    .maxConnections(10)
    .acquireTimeout(10.seconds)
    .idleTimeout(10.minutes)
    .maxLifetime(30.minutes)
    .build()
```

Then pass the options to the driver:

```kotlin
val db = PostgreSQL(
    url = "postgresql://localhost:15432/test",
    username = "postgres",
    password = "postgres",
    options = options
)
```

```kotlin
val db = MySQL(
    url = "mysql://localhost:13306/test",
    username = "mysql",
    password = "mysql",
    options = options
)
```

```kotlin
val db = SQLite(
    url = "sqlite://test.db", // If the `test.db` file is not found, a new db will be created.
    options = options
)
```

```kotlin
val db = SQLiteCipher(
    url = "sqlite://test.db", // If not found, a new (encrypted) db will be created.
    password = "a-strong-passphrase",
    options = options
)
```

In a multiplatform setup, use the `postgreSQL(...)`, `mySQL(...)`, `sqlite(...)` and `sqliteCipher(...)` functions instead. The URL formats and the notes of each driver are described in its page under [Databases](<https://smyrgeorge.github.io/sqlx4k/databases/postgresql/index.md>).

## Acquiring and using connections

The driver provides two complementary ways to run queries:

- Directly through the database instance (recommended). Each call acquires a pooled connection, executes the work, and returns it to the pool automatically.
- Manually acquire a connection from the pool when you need to batch multiple operations on the same connection without starting a transaction.

> [!WARNING]
>
> When you manually acquire a connection, you must release it to return it to the pool.

```kotlin
// Manual connection acquisition (remember to release)
val conn: Connection = db.acquire().getOrThrow()
try {
    conn.execute("insert into users(id, name) values (2, 'Bob');").getOrThrow()
    val rs = conn.fetchAll("select * from users;").getOrThrow()
    // ...
} finally {
    conn.close().getOrThrow() // Return to pool
}
```

### Setting the transaction isolation level

You can set the transaction isolation level on a connection to control the degree of visibility between concurrent transactions.

```kotlin
val conn: Connection = db.acquire().getOrThrow()
// Set the isolation level before starting operations
conn.setTransactionIsolationLevel(Transaction.IsolationLevel.Serializable).getOrThrow()
```

The levels are `ReadUncommitted`, `ReadCommitted`, `RepeatableRead` and `Serializable`. A connection starts at the database's default level: `ReadCommitted` on PostgreSQL, `RepeatableRead` on MySQL and MariaDB. The level applies to the transactions begun on that connection only, and is reset to the default when the connection returns to the pool. On SQLite the call has no effect: its transactions are always serializable.

## Pool state and shutdown

`poolSize()` returns the number of connections currently open, and `poolIdleSize()` how many of them are idle, e.g. to export them as metrics:

```kotlin
println("Connections: ${db.poolSize()} open, ${db.poolIdleSize()} idle.")
```

Close the pool when the application shuts down: `close()` closes its connections, and any later call on it fails (on PostgreSQL and MySQL, with an `SQLError` whose code is `PoolClosed`).

```kotlin
db.close().getOrThrow()
```

# Error handling

The query APIs do not throw: they return a `Result`, and a failure holds an `SQLError`. Call `getOrThrow()` to throw it (as the examples do), or inspect it with `onFailure`, `fold`, `exceptionOrNull()`, … With the [Arrow repositories](<https://smyrgeorge.github.io/sqlx4k/codegen/repositories/#repository-interfaces>), the generated methods return an `Either<SQLError, T>` instead.

```kotlin
val res: Result<Long> = db.execute("delete from users where id = 1;")
res.onFailure { e -> println("Delete failed: ${e.message}") }
```

The cancellation of the calling coroutine is never turned into a failed `Result`: it is rethrown, as usual with coroutines.

## SQLError

Every `SQLError` carries a `code`, the failure from the library's point of view. When the database itself rejected the statement (code `Database`), it also carries what the database said about it:

| Property | Set on | What it holds |
| --- | --- | --- |
| `code` | every error | The sqlx4k error code: `Database`, `PoolTimedOut`, `OptimisticLockFailed`, … (see [Error codes](<#error-codes>)). |
| `kind` | every error | A portable classification of the database error, the same on every database and platform (see [Error kinds](<#error-kinds>)). `Other` when not mapped. |
| `sqlState` | PostgreSQL, MySQL | The five-character SQLSTATE of the SQL standard, e.g. `23505` (unique violation) on PostgreSQL. `null` on SQLite, which has none. |
| `nativeCode` | MySQL, SQLite | The database's own error number: the server error on MySQL (e.g. `1062`, `ER_DUP_ENTRY`), the extended result code on SQLite (e.g. `2067`). `null` on PostgreSQL. |
| `cause` | when a lower layer failed | The original exception, e.g. the driver's exception on the JVM. |

## Error kinds

`kind` covers what application code usually branches on, without parsing database-specific codes: the integrity constraint violations (report them to the user) and the concurrency failures (retry the transaction).

| Kind | Meaning |
| --- | --- |
| `UniqueViolation` | A unique or primary key constraint was violated. |
| `ForeignKeyViolation` | A foreign key constraint was violated. |
| `NotNullViolation` | A not-null constraint was violated. |
| `CheckViolation` | A check constraint was violated. |
| `ExclusionViolation` | An exclusion constraint was violated (PostgreSQL only). |
| `Deadlock` | The transaction was chosen as a deadlock victim (PostgreSQL `40P01`, MySQL `1213`). |
| `SerializationFailure` | The transaction could not be serialized with concurrent ones (PostgreSQL `40001`). |
| `LockTimeout` | A lock could not be acquired in time (PostgreSQL `55P03`, MySQL `1205`, SQLite busy or locked). |
| `Other` | Not a database error, or one that is not mapped: use `sqlState` and `nativeCode`. |

For instance, to report a duplicate email:

```kotlin
val statement = Statement
    .create("insert into users(name, email) values (:name, :email)")
    .bind("name", "Alice")
    .bind("email", "alice@example.com")

db.execute(statement).onFailure { e ->
    when ((e as? SQLError)?.kind) {
        SQLError.Kind.UniqueViolation -> println("The email is already taken.")
        else -> throw e
    }
}
```

Or to retry a transaction that lost to a concurrent one:

```kotlin
suspend fun <T> retrying(attempts: Int = 3, block: suspend () -> T): T {
    repeat(attempts - 1) {
        try {
            return block()
        } catch (e: SQLError) {
            if (e.kind != SQLError.Kind.Deadlock && e.kind != SQLError.Kind.SerializationFailure) throw e
        }
    }
    return block()
}

val total: Long = retrying {
    db.transaction {
        execute("update accounts set balance = balance - 10 where id = 1;").getOrThrow()
        execute("update accounts set balance = balance + 10 where id = 2;").getOrThrow()
    }
}
```

> [!NOTE]
>
> Constraint violations are classified by the driver itself. The concurrency failures are derived from the SQLSTATE on PostgreSQL, the error number on MySQL and the result code on SQLite, with the same mapping on the native and the JVM drivers. On the SQLite driver for Android, `nativeCode` is recovered from the exception message, and is therefore best-effort.

## Error codes

| Code | When |
| --- | --- |
| `Database` | The database rejected the statement: see `kind`, `sqlState` and `nativeCode`. |
| `PoolTimedOut` | No connection became available within the pool's `acquireTimeout` (see [Connection pool](<https://smyrgeorge.github.io/sqlx4k/guide/connection-pool/index.md>)). |
| `PoolClosed` | The pool was closed. |
| `Pool` | A connection could not be opened, e.g. the server refused it or the TLS handshake failed. |
| `WorkerCrashed` | The driver's background worker failed. |
| `ConnectionIsClosed` | The connection was used after it was closed (returned to the pool). |
| `TransactionIsClosed` | The transaction was used after its commit or rollback. |
| `TransactionCommitFailed`, `TransactionRollbackFailed` | The commit or the rollback of a [transaction](<https://smyrgeorge.github.io/sqlx4k/guide/transactions/index.md>) failed. |
| `CannotDecode` | A `NULL` column was read with a function that does not allow it, e.g. `asInt()` (see [Reading values](<https://smyrgeorge.github.io/sqlx4k/guide/mapping/#reading-values>)). |
| `CannotDecodeEnumValue` | `asEnum<T>()` read a value that is not the name of a constant of `T`. |
| `PositionalParameterOutOfBounds`, `NamedParameterNotFound` | A value was bound to a parameter the statement does not have. |
| `PositionalParameterValueNotSupplied`, `NamedParameterValueNotSupplied` | A parameter of the statement was left without a value. |
| `MissingValueConverter` | A bound value has a type the driver cannot send: register a [value converter](<https://smyrgeorge.github.io/sqlx4k/guide/mapping/#custom-value-converters>). |
| `EmptyCollection` | An empty collection was bound (see [Binding collections](<https://smyrgeorge.github.io/sqlx4k/guide/queries/#binding-collections>)). |
| `InvalidIdentifier`, `UnsafeStringContent` | A name or a string the library writes into SQL itself (e.g. a savepoint or a migration table) was rejected. |
| `Migrate` | A [migration](<https://smyrgeorge.github.io/sqlx4k/guide/migrations/index.md>) failed: a duplicate or missing version, a changed or empty file, … |
| `EmptyResultSet` | A generated repository method expected a row and got none, e.g. `delete` of an entity that does not exist. |
| `MultipleRowsReturned` | A generated `findOneBy…` method matched more than one row. |
| `RowMismatch` | A generated `batchInsert` or `batchUpdate` affected a different number of rows than the number of entities. |
| `OptimisticLockFailed` | A versioned entity was modified or deleted since it was read (see [Optimistic locking](<https://smyrgeorge.github.io/sqlx4k/codegen/entities/#optimistic-locking-with-version>)). |
| `UnknownError` | Any other failure. |

# Async I/O and coroutines

Every query API in sqlx4k is a `suspend` function that returns a `Result`: `execute`, `fetchAll`, `begin`, `transaction { }` and the generated repository methods suspend instead of blocking the calling thread, and report failures through the `Result` instead of throwing. Database calls therefore compose with the rest of your coroutine code, from `async` and `withTimeout` to structured concurrency, and the I/O underneath is non-blocking on every platform.

This guide covers the core API, shared by every driver:

- [Connection pool](<https://smyrgeorge.github.io/sqlx4k/guide/connection-pool/index.md>): configuring the pool, and acquiring connections.
- [Running queries](<https://smyrgeorge.github.io/sqlx4k/guide/queries/index.md>): `execute`, `executeWithResult`, `fetchAll`, `fetch` and prepared statements.
- [Mapping rows](<https://smyrgeorge.github.io/sqlx4k/guide/mapping/index.md>): row mappers and custom value converters.
- [Transactions](<https://smyrgeorge.github.io/sqlx4k/guide/transactions/index.md>): transactions, savepoints and `TransactionContext`.
- [Migrations](<https://smyrgeorge.github.io/sqlx4k/guide/migrations/index.md>): running and validating database migrations.

For the generated repositories, see [Code generation](<https://smyrgeorge.github.io/sqlx4k/codegen/index.md>).

# Mapping rows

## Row mappers

A `RowMapper` turns a `ResultSet.Row` into your own type:

```kotlin
object Sqlx4kRowMapper : RowMapper<Sqlx4k> {
    override fun map(row: ResultSet.Row, converters: ValueEncoderRegistry): Sqlx4k {
        val id: ResultSet.Row.Column = row.get(0)
        val test: ResultSet.Row.Column = row.get(1)
        // Use built-in mapping methods to map the values to the corresponding type.
        return Sqlx4k(id = id.asInt(), test = test.asString())
    }
}

val res: List<Sqlx4k> = db.fetchAll("select * from sqlx4k limit 100;", Sqlx4kRowMapper).getOrThrow()
```

For `@Table` entities, the code generator writes the row mapper for you: see [Auto-generated RowMapper](<https://smyrgeorge.github.io/sqlx4k/codegen/#auto-generated-rowmapper>).

## Supported types

### Binding values

These types can be bound to the parameters of a [statement](<https://smyrgeorge.github.io/sqlx4k/guide/queries/#prepared-statements>) as they are:

| Kotlin type | Notes |
| --- | --- |
| `String`, `Char` |  |
| `Boolean` |  |
| `Byte`, `Short`, `Int`, `Long`, `Float`, `Double` |  |
| `ByteArray` | Binary columns (`bytea`, `BLOB`, …). |
| `kotlin.time.Instant` | A point in time (`timestamptz`, …). |
| `kotlinx.datetime.LocalDate`, `LocalTime`, `LocalDateTime` | Dates and times without a time zone. |
| `kotlin.uuid.Uuid` |  |
| Enums | Bound by name, unless a [value converter](<#custom-value-converters>) is registered for the enum. |
| Collections and arrays | Expanded into one parameter per element: see [Binding collections](<https://smyrgeorge.github.io/sqlx4k/guide/queries/#binding-collections>). |
| `null` | See [Binding nulls](<https://smyrgeorge.github.io/sqlx4k/guide/queries/#binding-nulls>). |

Any other type (e.g. `BigDecimal`, or your own value classes) needs a [value converter](<#custom-value-converters>); without one, the statement fails with an `SQLError` whose code is `MissingValueConverter`.

### Reading values

`row.get("name")` (or `row.get(0)`, by position) returns the `ResultSet.Row.Column` of a row. `asString()` reads its value as text, and `isNull()` tells whether it is `NULL`. The extension functions of `io.github.smyrgeorge.sqlx4k.impl.extensions` read it as the other types:

| Function | Returns |
| --- | --- |
| `asChar()` | `Char` |
| `asShort()`, `asInt()`, `asLong()` | `Short`, `Int`, `Long` |
| `asUShort()`, `asUInt()`, `asULong()` | `UShort`, `UInt`, `ULong` |
| `asFloat()`, `asDouble()` | `Float`, `Double` |
| `asBoolean()` | `Boolean`, from `true`/`false`, `t`/`f` or `1`/`0` |
| `asUuid()` | `kotlin.uuid.Uuid` |
| `asEnum<T>()` | The constant of the enum `T` with the column's value as its name |
| `asLocalDate()`, `asLocalTime()`, `asLocalDateTime()` | `kotlinx.datetime.LocalDate`, `LocalTime`, `LocalDateTime` |
| `asInstant()` | `kotlin.time.Instant` |
| `asByteArray()` | `ByteArray` |

Each one has an `…OrNull()` variant (`asIntOrNull()`, …) that returns `null` for a `NULL` column; the plain variant fails with an `SQLError` whose code is `CannotDecode` instead.

```kotlin
import io.github.smyrgeorge.sqlx4k.impl.extensions.asInstant
import io.github.smyrgeorge.sqlx4k.impl.extensions.asLong
import io.github.smyrgeorge.sqlx4k.impl.extensions.asUuidOrNull

db.fetchAll("select id, created_at, external_id from orders;").getOrThrow().forEach { row ->
    val id: Long = row.get("id").asLong()
    val createdAt: Instant = row.get("created_at").asInstant()
    val externalId: Uuid? = row.get("external_id").asUuidOrNull()
}
```

PostgreSQL arrays are read with the functions of `io.github.smyrgeorge.sqlx4k.postgres.extensions` (each with its `…OrNull()` variant too):

| Function | Returns |
| --- | --- |
| `asBooleanArray()`, `asShortArray()`, `asIntArray()`, `asLongArray()`, `asFloatArray()`, `asDoubleArray()` | `BooleanArray`, `ShortArray`, … |
| `asStringList()`, `asNullableStringList()` | `List<String>`, `List<String?>` |
| `asUuidList()`, `asLocalDateTimeList()`, `asInstantList()`, `asByteArrayList()` | `List<Uuid>`, `List<LocalDateTime>`, `List<Instant>`, … |

## Custom value converters

For custom types that don't have builtin decoders, you can register custom `ValueEncoder` implementations. A `ValueEncoder` provides bidirectional conversion between your custom type and the database representation.

**Creating a custom encoder:**

```kotlin
// Define your custom type
data class Money(val amount: BigDecimal, val currency: String) {
    override fun toString(): String = "$amount $currency"

    companion object {
        fun parse(value: String): Money {
            val parts = value.split(" ")
            return Money(BigDecimal(parts[0]), parts[1])
        }
    }
}

// Create a ValueEncoder for your type
object MoneyEncoder : ValueEncoder<Money> {
    override fun encode(value: Money): Any = value.toString()
    override fun decode(value: ResultSet.Row.Column): Money = Money.parse(value.asString())
}
```

**Registering and using custom encoders:**

```kotlin
// Create a registry and register your encoder
val registry = ValueEncoderRegistry()
    .register<Money>(MoneyEncoder)

// Use the registry when mapping rows
val mapper = Sqlx4kAutoRowMapper
val entity = mapper.map(row, registry)
```

For entity properties, a converter can also be declared directly on the property with [`@Converter`](<https://smyrgeorge.github.io/sqlx4k/codegen/entities/#property-level-converters-converter>), with no registry lookup.

# Migrations

Run any pending migrations against the database, and validate previously applied migrations against the current migration source to detect accidental changes in previously applied migrations.

```kotlin
val res: Migrator.Results = db.migrate(
    path = "./db/migrations",
    table = "_sqlx4k_migrations",
    afterFileMigration = { m, d -> println("Migration of file: $m, took $d") }
).getOrThrow()
println("Migration completed. $res")
```

The applied migrations are recorded in a table, `_sqlx4k_migrations` by default, which is created when missing. The result reports the `total` number of files, how many were `applied` and `validated`, and the `executionTime`. For more information, take a look at the [examples](<https://smyrgeorge.github.io/sqlx4k/getting-started/examples/index.md>).

The same migration files can be used to validate your `@Query` SQL against the schema at compile time: see [SQL schema validation](<https://smyrgeorge.github.io/sqlx4k/codegen/validation/#sql-schema-validation>).

## Migration files

A migration is a `.sql` file named `<version>_<description>.sql`, e.g. `1_create_users.sql`, `2_add_email.sql`. A file can hold several statements.

- The files run in the order of their versions, which must be consecutive (`1`, `2`, `3`, …) and unique.
- Each file runs in a single transaction: if one of its statements fails, the whole file is rolled back. On MySQL and MariaDB, DDL statements (`CREATE`, `ALTER`, `DROP`, …) commit implicitly and cannot be rolled back.
- A file already applied is not run again; its checksum is compared with the one recorded when it was applied, and the migration fails if the file was changed since. Add a new file to change the schema further.

A failure (a gap or a duplicate in the versions, a changed or empty file, a failed statement) fails the migration with an `SQLError` whose code is `Migrate` or `Database`.

## Options

| Parameter | Default | What it does |
| --- | --- | --- |
| `path` | `"./db/migrations"` | The directory of the migration files. |
| `table` | `"_sqlx4k_migrations"` | The table that records the applied migrations. |
| `schema` | the default schema | The schema of the migrations table, in which the files run too (PostgreSQL's `search_path`, MySQL's `USE`). Not supported on SQLite. |
| `createSchema` | `false` | Creates the `schema` when it does not exist. |
| `afterStatementExecution` | no-op | Called after each statement, with the `Statement` and the time it took. |
| `afterFileMigration` | no-op | Called after each file, with the recorded `Migration` and the time it took. |

## Migration files from code

Instead of a directory, `migrate` also takes the migration files themselves, as a list of `MigrationFile` (or a function that supplies it), e.g. when there is no file system to read them from, as on mobile:

```kotlin
val files = listOf(
    MigrationFile(name = "1_create_users.sql", content = "create table users(id bigint primary key, name text);"),
    MigrationFile(name = "2_add_email.sql", content = "alter table users add column email text;"),
)

db.migrate(files = files).getOrThrow()
```

On the JVM, `readMigrationFilesFromResources` reads them from the classpath, also from inside a JAR:

```kotlin
db.migrate(supplier = { readMigrationFilesFromResources("db/migrations") }).getOrThrow()
```

> [!NOTE]
>
> **SQLite and foreign keys**
>
> Each migration file runs in a single transaction. On SQLite, where every target enforces foreign keys, `PRAGMA foreign_keys` has no effect inside a transaction, so a migration cannot turn them off: rebuilding a table that other tables reference (create a new table, copy the rows, drop the old one, rename the new one, as SQLite's documentation describes) deletes the referencing rows (`ON DELETE CASCADE`) or fails (`NO ACTION` / `RESTRICT`) when the old table is dropped. Drop or rebuild the referencing tables in the same migration, or alter the table in place (`ALTER TABLE ... ADD / RENAME / DROP COLUMN`) where SQLite allows it.

# Running queries

All database interactions go through the [QueryExecutor](<https://github.com/smyrgeorge/sqlx4k/blob/main/sqlx4k/src/commonMain/kotlin/io/github/smyrgeorge/sqlx4k/QueryExecutor.kt>) interface, which provides a consistent, coroutine-based API for executing SQL statements. This interface is implemented by:

- **Database drivers** (`PostgreSQL`, `MySQL`, `SQLite`, `SQLiteCipher`): for direct query execution using pooled connections.
- **Connection**: for [manual connection management](<https://smyrgeorge.github.io/sqlx4k/guide/connection-pool/#acquiring-and-using-connections>).
- **Transaction**: for [transactional](<https://smyrgeorge.github.io/sqlx4k/guide/transactions/index.md>) query execution.

## execute

For SQL statements that modify data. Returns the number of affected rows (INSERT, UPDATE, DELETE, DDL statements):

```kotlin
// With raw SQL string
val affected: Long = db.execute("insert into users(id, name) values (1, 'Alice');").getOrThrow()
```

## executeWithResult

For inserts that generate an id. Like `execute`, but also returns the id the database generated for an inserted row (`AUTO_INCREMENT` on MySQL/MariaDB, the rowid on SQLite), with no extra query:

```kotlin
val res: ExecuteResult = db.executeWithResult("insert into users(name) values ('Alice');").getOrThrow()
res.rowsAffected // 1
res.lastInsertId // the generated id, or null when the statement generated none
```

`lastInsertId` is always `null` on PostgreSQL, which has no such value: use `INSERT ... RETURNING id` with `fetchAll` there. For a multi-row insert, MySQL reports the first generated id and SQLite the last.

## fetchAll

For queries that return data. Returns a `ResultSet` containing all rows (SELECT queries):

```kotlin
// With raw SQL string
val result: ResultSet = db.fetchAll("select * from users;").getOrThrow()
```

## fetch

For streaming large results. Returns a cold `Flow` of rows (SELECT queries). Unlike `fetchAll`, the rows are not collected in memory: the query starts when the flow is collected and the rows are emitted as the database produces them, in chunks of `fetchSize` rows (1,000 by default). The connection serving the query is busy until the flow completes or the collector is cancelled; a flow of a `Connection` or `Transaction` holds it for the whole collection. A failure fails the collection with an `SQLError`.

```kotlin
db.fetch("select * from events order by id;", fetchSize = 1_000)
    .map { it.get("payload").asString() }
    .collect { println(it) }

// With a RowMapper.
db.fetch("select * from users;", UserRowMapper).collect { user -> println(user) }
```

> [!NOTE]
>
> On MySQL (native targets), cancelling the collector of a `Connection` or `Transaction` flow waits for the row being read: see [MySQL](<https://smyrgeorge.github.io/sqlx4k/databases/mysql/index.md>).

## Prepared statements

A `Statement` keeps the values apart from the SQL text: bind your values instead of concatenating them into the query, which prevents SQL injection. Statements support both named and positional parameters:

```kotlin
// With named parameters:
val st1 = Statement
    .create("select * from sqlx4k where id = :id")
    .bind("id", 65)

db.fetchAll(st1).getOrThrow().map {
    val id: ResultSet.Row.Column = it.get("id")
    Test(id = id.asInt())
}

// With positional parameters:
val st2 = Statement
    .create("select * from sqlx4k where id = ?")
    .bind(0, 65)

db.fetchAll(st2).getOrThrow().map {
    val id: ResultSet.Row.Column = it.get("id")
    Test(id = id.asInt())
}
```

### Binding collections

A collection (an `Iterable` or an array) bound to a single parameter expands into one placeholder per element, wrapped in parentheses, which makes `IN` lists straightforward:

```kotlin
// Runs: select * from users where id in ($1, $2, $3)
val st = Statement
    .create("select * from users where id in :ids")
    .bind("ids", listOf(1, 2, 3))
```

Wrap the collection in `NoWrappingTuple` to expand it without the parentheses, e.g. for `select array[?]` on PostgreSQL. Binding an empty collection fails with an `SQLError` whose code is `EmptyCollection`, as `in ()` is not valid SQL.

### Binding nulls

Bind `null` like any other value. Some columns need the type of the `NULL` to be sent along with it (e.g. `uuid`, `timestamptz` or an array on PostgreSQL): bind it with `bindNull`, which takes the Kotlin type of the parameter:

```kotlin
val st = Statement
    .create("update users set external_id = :externalId where id = :id")
    .bindNull("externalId", Uuid::class)
    .bind("id", 1)
```

The types you can bind are listed in [Supported types](<https://smyrgeorge.github.io/sqlx4k/guide/mapping/#supported-types>).

To map the rows to your own types, see [Mapping rows](<https://smyrgeorge.github.io/sqlx4k/guide/mapping/index.md>).

# Transactions

```kotlin
val tx1: Transaction = db.begin().getOrThrow()
tx1.execute("delete from sqlx4k;").getOrThrow()
tx1.fetchAll("select * from sqlx4k;").getOrThrow().forEach { println(it) }
tx1.commit().getOrThrow()
```

You can also execute entire blocks in a transaction scope.

```kotlin
db.transaction {
    execute("delete from sqlx4k;").getOrThrow()
    fetchAll("select * from sqlx4k;").getOrThrow().forEach { println(it) }
    // At the end of the block will auto commit the transaction.
    // If any error occurs, it will automatically trigger the rollback method.
}
```

`transaction` returns the value of its block. The transaction is rolled back when the block throws, and also when it returns a failed `Result`. Either way, the error is rethrown; a failed commit throws an `SQLError` whose code is `TransactionCommitFailed`. If the calling coroutine is cancelled, the transaction is rolled back too, as its work did not complete.

To get a `Result` instead of an exception, use `transactionCatching`:

```kotlin
val res: Result<Long> = db.transactionCatching {
    execute("update accounts set balance = balance - 10 where id = 1;").getOrThrow()
    execute("update accounts set balance = balance + 10 where id = 2;").getOrThrow()
}
res.onFailure { e -> println("Transfer rolled back: ${e.message}") }
```

See [Error handling](<https://smyrgeorge.github.io/sqlx4k/guide/errors/index.md>) for what a failure holds.

To change the isolation level, see [Setting the transaction isolation level](<https://smyrgeorge.github.io/sqlx4k/guide/connection-pool/#setting-the-transaction-isolation-level>).

## Savepoints

A savepoint lets you roll back part of a transaction without ending it. The block form releases the savepoint on success and rolls back to it on failure. Either way it returns a `Result` and never propagates the error.

```kotlin
db.transaction {
    execute("insert into orders (id, status) values (1, 'new');").getOrThrow()

    // If this fails, only the audit insert is undone.
    val audit: Result<Long> = savepoint {
        execute("insert into audit_log (order_id, event) values (1, 'created');").getOrThrow()
    }
    if (audit.isFailure) println("Audit insert skipped.")
}
```

You can also call `savepoint(name)`, `rollbackToSavepoint(name)` and `releaseSavepoint(name)` directly.

> [!NOTE]
>
> Savepoints are not supported by the SQLite driver on Android: see [SQLite](<https://smyrgeorge.github.io/sqlx4k/databases/sqlite/#tips-and-limitations>).

## TransactionContext (coroutines)

When using coroutines, you can propagate a transaction through the coroutine context using `TransactionContext`. This allows you to write small, composable suspend functions that either:

- start a transaction at the boundary of your use case, and
- inside helper functions call `TransactionContext.current()` to participate in the same transaction without having to propagate `Transaction` or `Driver` parameters everywhere.

```kotlin
val db = PostgreSQL(
    url = "postgresql://localhost:15432/test",
    username = "postgres",
    password = "postgres",
    options = options
)

fun main() = runBlocking {
    TransactionContext.new(db) {
        // `this` is a TransactionContext and also a Transaction (delegation),
        // so you can call query methods directly:
        execute("insert into sqlx4k (id, test) values (66, 'test');").getOrThrow()

        // In deeper code, fetch the same context and keep using the same tx
        doBusinessLogic()
        doMoreBusinessLogic()
        doExtraBusinessLogic()
    }
}

suspend fun doBusinessLogic() {
    // Get the active transaction from the coroutine context
    val tx = TransactionContext.current()
    // Continue operating within the same database transaction
    tx.execute("update sqlx4k set test = 'updated' where id = 66;").getOrThrow()
}

// Or you can use the `withCurrent` method to get the transaction and execute the block in an ongoing transaction.
suspend fun doMoreBusinessLogic(): Unit = TransactionContext.withCurrent {
    // Continue operating within the same database transaction
}

// You can also pass the db instance to `withCurrent`.
// If a transaction is already active, the block runs within it; otherwise, a new transaction is started for the block.
suspend fun doExtraBusinessLogic(): Unit = TransactionContext.withCurrent(db) {
    // Continue operating within the same database transaction
}
```

# Code generation

# Batch operations

The code generator creates batch `INSERT` and `UPDATE` operations for writing multiple entities in a single statement (one database round-trip). They return the number of affected rows, not the entities: the rows are not read back, so values generated by the database (auto-incremented ids, column defaults, the incremented `@Version`) are not returned. Read the rows again if you need them.

The number of affected rows is checked against the number of entities: when they differ, the call fails with an `SQLError` whose code is `RowMismatch` (`OptimisticLockFailed` for a stale versioned `batchUpdate`, see [Optimistic locking with @Version](<https://smyrgeorge.github.io/sqlx4k/codegen/entities/#optimistic-locking-with-version>)). An empty collection succeeds with `0` without touching the database.

## Batch insert

```kotlin
// Insert multiple entities at once
val users = listOf(
    User(name = "Alice", email = "alice@example.com"),
    User(name = "Bob", email = "bob@example.com"),
    User(name = "Charlie", email = "charlie@example.com")
)

// Returns the number of inserted rows (3)
val inserted: Result<Long> = userRepository.batchInsert(db, users)
```

## Batch update

```kotlin
// Update multiple entities at once (each row is matched by the entity's @Id)
val updatedUsers = users.map { it.copy(status = "active") }

// Returns the number of updated rows; fails with RowMismatch if an entity's row does not exist
val updated: Result<Long> = userRepository.batchUpdate(db, updatedUsers)
```

> [!WARNING]
>
> A `batchUpdate` that fails the row count check has already updated the rows that matched. Run it inside a transaction and roll back on failure if you need all-or-nothing semantics.

## Database support

Both operations are supported by every dialect:

| Dialect | `batchInsert` | `batchUpdate` |
| --- | --- | --- |
| PostgreSQL | `INSERT ... VALUES (...), (...)` | `UPDATE ... AS t SET ... FROM (VALUES (...), (...)) AS v(...) WHERE t.id = v.id` |
| SQLite | `INSERT ... VALUES (...), (...)` | `WITH v(...) AS (VALUES (...), (...)) UPDATE ... SET ... FROM v WHERE ...` |
| MySQL/MariaDB | `INSERT ... VALUES (...), (...)` | `UPDATE ... AS t JOIN (SELECT ... FROM ... WHERE false UNION ALL SELECT ...) AS v ON t.id = v.id SET ...` |
| Generic | `INSERT ... VALUES (...), (...)` | Like PostgreSQL |

On PostgreSQL a `null` property value is bound with its type (`bigint`, `timestamptz`, ...), as the `VALUES` list takes its column types from its rows.

> [!NOTE]
>
> On MySQL and MariaDB the drivers report the *matched* rows as affected, so an entity whose values did not change still counts as updated.

> [!NOTE]
>
> The pre-hooks (`preInsertHook`/`preUpdateHook`) run for each entity before the statement; the after-hooks (`afterInsertHook`/`afterUpdateHook`) run for each entity, as written, once the statement succeeded, and their results are not used. See [Repository hooks](<https://smyrgeorge.github.io/sqlx4k/codegen/hooks/index.md>).

# Context parameters

Optional: using `ContextCrudRepository` with [context parameters](<https://kotlinlang.org/docs/context-parameters.html>).

You can opt in to generated repositories that use Kotlin context parameters instead of passing a `QueryExecutor` parameter to every method. This switches your repository to `ContextCrudRepository` and makes all generated CRUD and `@Query` methods require an ambient `QueryExecutor` provided via a context parameter.

To enable this mode:

- Make your repository interface extend `ContextCrudRepository<T>` instead of `CrudRepository<T>`.
- Declare your `@Query` methods with a `context(context: QueryExecutor)` context parameter instead of an explicit `context` argument.

Repository interface example with context parameters:

```kotlin
@Repository
interface Sqlx4kRepository : ContextCrudRepository<Sqlx4k> {
    @Query("SELECT * FROM sqlx4k WHERE id = :id")
    context(context: QueryExecutor)
    suspend fun findOneById(id: Int): Result<Sqlx4k?>

    @Query("SELECT * FROM sqlx4k")
    context(context: QueryExecutor)
    suspend fun findAll(): Result<List<Sqlx4k>>
}
```

Usage with a context parameter (no explicit db parameter on each call):

```kotlin
val record = Sqlx4k(id = 1, test = "test")
with(db) {
    val inserted = Sqlx4kRepositoryImpl.insert(record).getOrThrow()
    val one = Sqlx4kRepositoryImpl.findOneById(1).getOrThrow()
}
```

If you prefer the explicit-parameter style, extend `CrudRepository<T>` instead. In that case, each generated method takes a `QueryExecutor` (e.g., `db` or a transaction) as the first argument.

# Entities

A `@Table` data class maps to a table: every property is a column, and the `@Id` property is the primary key. The annotations below customize the mapping.

## Customizing columns with `@Column`

You can use the `@Column` annotation to override the column name a property is mapped to, and to control how the property participates in the generated `INSERT` and `UPDATE` statements. The latter is useful for database-generated or read-only columns that should be excluded from write operations but still retrieved afterward (via the `RETURNING` clause).

| Property | Effect |
| --- | --- |
| `name = "..."` | Overrides the column name used in all generated SQL and the generated `RowMapper` |
| `insert = false` | Excluded from the INSERT's column list, included in the INSERT's RETURNING clause |
| `update = false` | Excluded from the UPDATE's SET clause, included in the UPDATE's RETURNING clause |

By default (when `name` is empty, or when the property has no `@Column` annotation at all) the column name is derived from the property name using snake\_case conversion (e.g., `createdAt` → `created_at`). Providing an explicit `name` is useful for legacy or non-conventional schemas:

```kotlin
@Table("legacy_users")
data class LegacyUser(
    @Id
    val id: Long,
    // Maps to the legacy "USER_NAME" column instead of the derived "user_name".
    @Column(name = "USER_NAME")
    val userName: String,
    // A custom name can be combined with the insert/update flags.
    @Column(name = "mail_address", update = false)
    val email: String,
    // No @Column: maps to "is_active" (default snake_case conversion).
    val isActive: Boolean
)
```

> [!NOTE]
>
> An explicit `name` must be a plain (unquoted) SQL identifier (`[A-Za-z_][A-Za-z0-9_]*`) and is used verbatim in the generated SQL and when reading result columns by name, so it must match the column name as reported by the database. PostgreSQL folds unquoted identifiers to lowercase, so for PostgreSQL use the lowercase form unless the column was created with a quoted (case-sensitive) name.

```kotlin
@Table("articles")
data class Article(
    @Id
    val id: Long,
    val title: String,
    val content: String,
    // Set only on INSERT by a DB default, never modified afterwards.
    @Column(insert = false, update = false)
    val createdAt: LocalDateTime,
    // Auto-updated by a DB trigger on every write.
    @Column(insert = false, update = false)
    val updatedAt: LocalDateTime
)

@Table("comments")
data class Comment(
    @Id
    val id: Long,
    val content: String,
    // Set on INSERT but never updated.
    @Column(update = false)
    val createdAt: LocalDateTime
)
```

## Excluding properties with `@Transient`

Use the `@Transient` annotation for derived, computed, or cached properties that have **no corresponding database column**. Unlike `@Column(insert = false, update = false)`, which still maps the property from query results, a `@Transient` property is treated as if it were not a column at all. It is excluded from `INSERT`/`UPDATE` statements, from the `RETURNING` clause, and is **not** read by the generated `RowMapper`.

If the annotated property is a primary-constructor parameter, it **must declare a default value** (the generated `RowMapper` omits it and relies on the default). Properties declared in the class body (e.g. `by lazy` or computed `get()`) need no default.

```kotlin
@Table("accounts")
data class Account(
    @Id
    val id: Long,
    val email: String,
    // Transient constructor parameter: requires a default value.
    @Transient
    val displayName: String = email.substringBefore('@')
) {
    // Transient body property: derived lazily, no default required.
    @Transient
    val domain: String by lazy { email.substringAfter('@') }
}
```

## Optimistic locking with `@Version`

Mark a single non-nullable `Int` or `Long` property with `@Version` to enable optimistic locking for the generated CRUD operations. The generator manages the column for you: never set it yourself, just keep the entity instance returned by the repository for the next write.

- `insert()` writes the entity's value as-is (start new entities at `0`) and reads the column back via `RETURNING`.
- `update()` sets `version = version + 1` and adds `AND version = ?` (the entity's current value) to the `WHERE` clause. The incremented value is read back via `RETURNING` and merged into the returned entity.
- `delete()` adds `AND version = ?` to the `WHERE` clause as well.
- `batchUpdate()` matches every row on both the id and the version, and increments the version. It returns only the number of updated rows, so the entities you passed in still carry the old version: read them again before the next write.

When the row has been modified (or deleted) since the entity was read, the statement matches no rows and the repository method fails with an `SQLError` whose code is `OptimisticLockFailed`:

```kotlin
@Table("documents")
data class Document(
    @Id
    val id: Long,
    val title: String,
    @Version
    val version: Long = 0,
)

@Repository
interface DocumentRepository : CrudRepository<Document> {
    @Query("SELECT * FROM documents WHERE id = :id")
    suspend fun findOneById(context: QueryExecutor, id: Long): Result<Document?>
}

val doc = DocumentRepositoryImpl.findOneById(db, 1).getOrThrow()!!                   // version = 3
val updated = DocumentRepositoryImpl.update(db, doc.copy(title = "v2")).getOrThrow() // version = 4

// `doc` still carries version 3, so this write is stale:
val stale = DocumentRepositoryImpl.update(db, doc.copy(title = "v3"))
val error = stale.exceptionOrNull() as SQLError
check(error.code == SQLError.Code.OptimisticLockFailed)
```

The generated `update()` statement looks like this:

```sql
update documents
set title = ?,
    version = version + 1
where id = ?
  and version = ? returning id, version;
```

> [!NOTE]
>
> **Rules enforced at compile time**
>
> At most one `@Version` per entity, it must be a non-nullable `Int` or `Long`, the entity must also declare an `@Id`, and it cannot be combined with `@Id`, `@Transient` or `@Column(update = false)`. `@Column(name = "...")` and `@Column(insert = false)` (to rely on a database default) are fine.

> [!WARNING]
>
> A batch update is a single statement: the rows whose version matched **are** updated, the stale ones are not, and the call fails with `OptimisticLockFailed`. Run versioned `batchUpdate` calls inside a transaction and roll back on failure if you need all-or-nothing semantics.

> [!NOTE]
>
> **MySQL and MariaDB**
>
> MySQL and MariaDB have no `UPDATE ... RETURNING`, so `update()` re-selects the row afterwards. For versioned entities that `SELECT` is keyed on the expected *new* version, so a stale update still surfaces as `OptimisticLockFailed` instead of silently returning the unchanged row.

## Property-level converters (`@Converter`)

For custom types, you can use the `@Converter` annotation to specify a `ValueEncoder` directly on the property. This provides compile-time type safety, avoids runtime registry lookups, and eliminates object instantiation overhead.

**Defining a custom encoder:**

```kotlin
// Define your custom type
data class Money(val amount: Double, val currency: String) {
    override fun toString(): String = "$amount:$currency"

    companion object {
        fun parse(value: String): Money {
            val parts = value.split(":")
            return Money(parts[0].toDouble(), parts[1])
        }
    }
}

// Create a ValueEncoder as an object (singleton) - NOT a class
object MoneyEncoder : ValueEncoder<Money> {
    override fun encode(value: Money): Any = value.toString()
    override fun decode(value: ResultSet.Row.Column): Money = Money.parse(value.asString())
}
```

**Using `@Converter` on properties:**

```kotlin
@Table("invoices")
data class Invoice(
    @Id
    val id: Long,
    val description: String,
    @Converter(MoneyEncoder::class)
    val totalAmount: Money
)
```

> [!NOTE]
>
> When using `@Converter`, you don't need to register the encoder in a `ValueEncoderRegistry`. The encoder object is referenced directly in the generated code, avoiding any instantiation overhead.

# Repository hooks

The repository system provides hooks to implement cross-cutting concerns like metrics, tracing, logging, and monitoring across all database operations. All repository interfaces extend `CrudRepositoryHooks<T>`, which provides the following hooks:

**Entity-level hooks:**

- `preInsertHook(context: QueryExecutor, entity: T): T`: called before an entity is inserted
- `preUpdateHook(context: QueryExecutor, entity: T): T`: called before an entity is updated
- `preDeleteHook(context: QueryExecutor, entity: T): T`: called before an entity is deleted
- `afterInsertHook(context: QueryExecutor, entity: T): T`: called after an entity is inserted
- `afterUpdateHook(context: QueryExecutor, entity: T): T`: called after an entity is updated
- `afterDeleteHook(context: QueryExecutor, entity: T): T`: called after an entity is deleted

**Query-level hook:**

- `aroundQuery(methodName: String, statement: Statement, block: suspend () -> R): R`: wraps all query executions

The `aroundQuery` hook wraps **all** database operations (both `@Query` methods and CRUD operations), giving you a single interception point for implementing metrics, distributed tracing, query logging, and other observability features.

For how the hooks run in [batch operations](<https://smyrgeorge.github.io/sqlx4k/codegen/batch-operations/index.md>), see the note there.

The hooks are available on every [repository interface](<https://smyrgeorge.github.io/sqlx4k/codegen/repositories/#repository-interfaces>).

# In-memory repositories

> [!WARNING]
>
> **Experimental**
>
> In-memory repository generation is currently in early development; its behavior and the generated API may change in future releases.

The companion `sqlx4k-codegen-test` module generates a thread-safe, **in-memory** implementation of every `@Repository` interface, so you can unit-test the code that depends on your repositories without a real database. For a repository named `Sqlx4kRepository`, it generates a class named `InMemorySqlx4kRepository`.

It is a separate module from `sqlx4k-codegen`, so in-memory generation is opt-in per dependency: add it only to the KSP configuration that processes your repositories:

```kotlin
ksp {
    // The generated implementations are emitted into this package (same option used by sqlx4k-codegen).
    arg("output-package", "io.github.smyrgeorge.sqlx4k.examples.postgres")
}

dependencies {
    // Real @Repository implementations (production).
    add("kspMacosArm64", implementation("io.github.smyrgeorge:sqlx4k-codegen:1.17.0"))
    // In-memory implementations for unit tests.
    add("kspMacosArm64Test", "io.github.smyrgeorge:sqlx4k-codegen-test:1.17.0")
}
```

Given the `Sqlx4kRepository` from [Code generation](<https://smyrgeorge.github.io/sqlx4k/codegen/#entities-and-repositories>), use the generated implementation directly in your tests:

```kotlin
val repo = InMemorySqlx4kRepository()

// A QueryExecutor is still accepted to match the interface, but it is never touched:
// it works entirely in memory, so any instance (even a no-op fake) will do.
val saved = repo.insert(db, Sqlx4k(id = 1, test = "test")).getOrThrow()
val all = repo.findAll(db).getOrThrow()
```

## What the generated implementation provides

- **Thread-safe storage:** entities live in a `HashMap` guarded by a `Mutex`.
- **Full CRUD:** `insert`, `update`, `delete`, `save`, `batchInsert`, `batchUpdate`. Numeric `@Id` keys with `insert = false` get auto-incrementing ids; application-provided ids (`@Id(insert = true)`) are stored as given. `@Version` entities get the same optimistic locking as the real implementation: `update`/`delete` only apply when the stored version matches (failing with `OptimisticLockFailed` otherwise) and `update` increments it.
- **Whole-table queries:** `findAll`, `countAll`, `deleteAll`.
- **Derived `@Query` methods:** the in-memory behavior is derived from each method's `@Query` **SQL**: the statement is parsed and its `WHERE` clause becomes a predicate over the stored entities (columns are mapped back to properties via snake\_case / `@Column`). Supports `=`, `<>`, `<`, `<=`, `>`, `>=`, `IS [NOT] NULL`, `AND`/`OR`, named parameters, and simple literals, for `find*`/`stream*`/`count*`/`exists*` (SELECT), `delete*` (DELETE), and `execute*` (UPDATE `SET …`/DELETE). For example `@Query("select * from users where name = :name")` behaves like `filter { it.name == name }`.
- **Repository hooks:** the overridden `preInsert/afterInsert/... /aroundQuery` [hooks](<https://smyrgeorge.github.io/sqlx4k/codegen/hooks/index.md>) are honored exactly like the real generated implementations.
- **Test helpers:** `clear()` empties the store (and resets the id sequence) and `findAllStored()` returns a snapshot.

## Methods the generator cannot translate

Anything the generator cannot translate is emitted as a stub that throws `NotImplementedError`:

- a `WHERE` it doesn't understand (e.g. `LIKE`, `IN`, subqueries, function calls),
- a query whose result also depends on a join, `DISTINCT`, `GROUP BY`/`HAVING`, a `LIMIT`/`OFFSET` or (for `find*`/`stream*`) an `ORDER BY`,
- an `execute*` that isn't a plain UPDATE/DELETE.

The generated class is `open`, so provide the behavior yourself by subclassing and overriding just those methods. Use the `withStore { }` helper for thread-safe access to the backing map (the `MutableMap` is the lambda receiver):

```kotlin
class TestSqlx4kRepository : InMemorySqlx4kRepository() {
    // A method whose WHERE the generator could not translate (e.g. LIKE), so it was left as a stub.
    override suspend fun findAllByTestLike(context: QueryExecutor, pattern: String): Result<List<Sqlx4k>> =
        withStore {
            val prefix = pattern.removeSuffix("%")
            runCatching { values.filter { it.test.startsWith(prefix) } }
        }
}
```

# Code generation

sqlx4k ships a KSP code generator (`sqlx4k-codegen`) that writes, at build time:

- the CRUD operations (`insert`, `update`, `delete`, `save`, [batch operations](<https://smyrgeorge.github.io/sqlx4k/codegen/batch-operations/index.md>)) of every `@Table` entity,
- the implementation of every [`@Repository`](<https://smyrgeorge.github.io/sqlx4k/codegen/repositories/index.md>) interface, including its `@Query` methods,
- a [row mapper](<#auto-generated-rowmapper>) for every entity,

and [validates](<https://smyrgeorge.github.io/sqlx4k/codegen/validation/index.md>) the SQL of every `@Query` method while doing so.

## Setup

> [!TIP]
>
> The [Gradle plugin](<https://smyrgeorge.github.io/sqlx4k/getting-started/gradle-plugin/index.md>) does the whole setup below for you from a single `sqlx4k { }` block. The manual setup is shown here for reference, and for the code-generator options.

### Manual setup

Include the `KSP` plugin in your project, and configure the processor:

```kotlin
plugins {
    alias(libs.plugins.ksp)
}

// Then you need to configure the processor (it will generate the necessary code files).
ksp {
    // Optional: pick the SQL dialect for CRUD generation from @Table classes.
    // Supported dialects:
    // arg("dialect", "mysql")
    // arg("dialect", "mariadb")
    // arg("dialect", "postgresql")
    // arg("dialect", "sqlite")

    // Required: where to place the generated sources.
    arg("output-package", "io.github.smyrgeorge.sqlx4k.examples.postgres")

    // Compile-time SQL syntax checking for @Query methods (default = true).
    // Set to "false" to turn it off if you use vendor-specific syntax not understood by the parser.
    // arg("validate-sql-syntax", "false")
}

dependencies {
    // Will generate code for macosArm64. Add more targets if you want.
    add("kspMacosArm64", implementation("io.github.smyrgeorge:sqlx4k-codegen:1.17.0"))
}
```

The validation and optimization options are listed in [Validation](<https://smyrgeorge.github.io/sqlx4k/codegen/validation/index.md>).

## Entities and repositories

Map a data class to a table with `@Table`, and declare a `@Repository` for it:

```kotlin
@Table("sqlx4k")
data class Sqlx4k(
    @Id(insert = true) // Will be included in the insert query.
    val id: Int,
    val test: String
)

@Repository
interface Sqlx4kRepository : CrudRepository<Sqlx4k> {
    @Query("SELECT * FROM sqlx4k WHERE id = :id")
    suspend fun findOneById(context: QueryExecutor, id: Int): Result<Sqlx4k?>
}
```

The generator writes `Sqlx4kRepositoryImpl`, with the CRUD operations of the entity and your `@Query` methods:

```kotlin
val res: Sqlx4k = Sqlx4kRepositoryImpl.insert(db, Sqlx4k(id = 1, test = "test")).getOrThrow()
val one: Sqlx4k? = Sqlx4kRepositoryImpl.findOneById(db, 1).getOrThrow()
```

- [Repositories](<https://smyrgeorge.github.io/sqlx4k/codegen/repositories/index.md>): the generated CRUD operations, the `@Query` methods and the repository interfaces.
- [Entities](<https://smyrgeorge.github.io/sqlx4k/codegen/entities/index.md>): how the properties map to columns (`@Column`, `@Transient`, `@Version`, `@Converter`).

## Auto-generated RowMapper

When you annotate a class with `@Table`, the code generator automatically creates a `RowMapper` implementation for mapping database rows to your entity. The mapper is named `{ClassName}AutoRowMapper` and is generated in the same file as your CRUD queries.

For example, for a class named `Sqlx4k`, the generator creates `Sqlx4kAutoRowMapper`:

```kotlin
object Sqlx4kAutoRowMapper : RowMapper<Sqlx4k> {
    override fun map(row: ResultSet.Row, converters: ValueEncoderRegistry): Sqlx4k {
        val id = row.get("id").asInt()
        val test = row.get("test").asString()
        return Sqlx4k(id = id, test = test)
    }
}
```

By default, the generated repository uses the auto-generated `RowMapper` of its entity. You can override this by providing a custom mapper explicitly: `@Repository(mapper = CustomRowMapper::class)`.

## Dialects

The `dialect` option adjusts the generated SQL to the database. Set it to `postgresql` to also enable the PostgreSQL-specific decoders (array types):

```kotlin
ksp {
    arg("dialect", "postgresql")  // Enables PostgreSQL array decoders
    arg("output-package", "io.github.smyrgeorge.sqlx4k.examples.postgres")
}
```

| Dialect | Effect |
| --- | --- |
| `mysql` | Adjusts CRUD query generation for MySQL compatibility. |
| `mariadb` | Like `mysql`, but uses `INSERT ... RETURNING` (MariaDB 10.5+) for `insert()`. |
| `postgresql` | Enables PostgreSQL-specific extensions (array types). |
| `sqlite` | Adjusts CRUD query generation for SQLite compatibility. |
| default (or `generic`) | Uses standard SQL with builtin decoders. |

The [Gradle plugin](<https://smyrgeorge.github.io/sqlx4k/getting-started/gradle-plugin/index.md>) sets the dialect from the chosen `driver`.

# Repositories

Annotate an interface with `@Repository` and the code generator writes its implementation: the CRUD operations of its entity, and every `@Query` method you declare. For an interface named `Sqlx4kRepository`, the implementation is named `Sqlx4kRepositoryImpl`.

```kotlin
@Table("sqlx4k")
data class Sqlx4k(
    @Id(insert = true) // Will be included in the insert query.
    val id: Int,
    val test: String
)

@Repository
interface Sqlx4kRepository : CrudRepository<Sqlx4k> {
    // The processor will validate the SQL syntax in the @Query methods.
    // If you want to disable this validation, you can set the "validate-sql-syntax" arg to "false".
    @Query("SELECT * FROM sqlx4k WHERE id = :id")
    suspend fun findOneById(context: QueryExecutor, id: Int): Result<Sqlx4k?>

    @Query("SELECT * FROM sqlx4k")
    suspend fun findAll(context: QueryExecutor): Result<List<Sqlx4k>>

    @Query("SELECT count(*) FROM sqlx4k")
    suspend fun countAll(context: QueryExecutor): Result<Long>

    @Query("SELECT * FROM sqlx4k WHERE id = :id")
    suspend fun existsById(context: QueryExecutor, id: Int): Result<Boolean>
}
```

Then in your code you can use it like:

```kotlin
// Insert a new record.
val record = Sqlx4k(id = 1, test = "test")
val res: Sqlx4k = Sqlx4kRepositoryImpl.insert(db, record).getOrThrow()
// Execute a generated query.
val res: List<Sqlx4k> = Sqlx4kRepositoryImpl.findAll(db).getOrThrow()
```

Every method takes a `QueryExecutor` as its first argument: the database itself, a connection, or a [transaction](<https://smyrgeorge.github.io/sqlx4k/guide/transactions/index.md>), so repository calls compose with the rest of your queries. To pass it as a context parameter instead, see [Context parameters](<https://smyrgeorge.github.io/sqlx4k/codegen/context-parameters/index.md>).

## CRUD operations

Because the interface extends `CrudRepository<T>`, the generated implementation also provides the CRUD operations of the entity:

| Method | Returns | What it does |
| --- | --- | --- |
| `insert` | `Result<T>` | Inserts the entity and returns it as stored, with the columns generated by the database read back via `RETURNING` (e.g. an auto-incremented id). |
| `update` | `Result<T>` | Updates the row matched by the entity's `@Id` and returns the entity as stored. |
| `delete` | `Result<Unit>` | Deletes the row matched by the entity's `@Id`. |
| `save` | `Result<T>` | Inserts the entity if it is new, updates it otherwise: an `Int`/`Long` id of `0` (or `null`, for a nullable id) marks a new entity. |
| `batchInsert` | `Result<Long>` | Inserts many entities with a single statement; see [Batch operations](<https://smyrgeorge.github.io/sqlx4k/codegen/batch-operations/index.md>). |
| `batchUpdate` | `Result<Long>` | Updates many entities with a single statement; see [Batch operations](<https://smyrgeorge.github.io/sqlx4k/codegen/batch-operations/index.md>). |

How the entity maps to the table (column names, columns excluded from writes, optimistic locking) is described in [Entities](<https://smyrgeorge.github.io/sqlx4k/codegen/entities/index.md>).

> [!NOTE]
>
> `save` decides between insert and update from the id, so it needs an `Int` or `Long` `@Id`. With any other id type (e.g. a `Uuid` set by the application), call `insert` or `update` explicitly.

## Query methods

A `@Query` method runs the SQL of its annotation, with the method's parameters bound by name (`:id` binds the `id` parameter). Its name prefix determines its shape and expected return type:

| Prefix | Return type | Notes |
| --- | --- | --- |
| `findAll` / `findAllBy…` | `Result<List<T>>` |  |
| `findOneBy…` | `Result<T?>` |  |
| `countAll` / `countBy…` | `Result<Long>` |  |
| `existsBy…` | `Result<Boolean>` |  |
| `deleteAll` / `deleteBy…`, `execute…` | `Result<Long>` | The number of affected rows. |
| `streamAll` / `streamAllBy…` | `Flow<T>` | Declared as non-`suspend` functions. |

`stream*` methods stream the rows instead of loading them all: the query runs when the flow is collected, and a failure fails the collection. They fetch rows in chunks of `@Query(fetchSize = …)` rows (the driver's default when unset).

```kotlin
@Repository
interface EventRepository : CrudRepository<Event> {
    @Query("SELECT * FROM events WHERE type = :type", fetchSize = 500)
    fun streamAllByType(context: QueryExecutor, type: String): Flow<Event>

    @Query("DELETE FROM events WHERE created_at < :before")
    suspend fun deleteByCreatedAtBefore(context: QueryExecutor, before: Instant): Result<Long>
}
```

The SQL of every `@Query` is checked during the build: the method name must match the statement kind (a `find*` must be a `SELECT`, a `delete*` a `DELETE`, …), the columns must exist on the entity, and more. See [Validation](<https://smyrgeorge.github.io/sqlx4k/codegen/validation/index.md>) for the checks, and for `@Query(checkSyntax = false)` and `@Query(checkSchema = false)`, which turn them off for a single query.

## Row mapping

By default, the generated repository maps rows with the [auto-generated `RowMapper`](<https://smyrgeorge.github.io/sqlx4k/codegen/#auto-generated-rowmapper>) of its entity (e.g., `Sqlx4kAutoRowMapper`). You can override this behavior by explicitly providing a custom mapper:

```kotlin
@Repository(mapper = CustomRowMapper::class)
interface Sqlx4kRepository : CrudRepository<Sqlx4k>
```

See [Mapping rows](<https://smyrgeorge.github.io/sqlx4k/guide/mapping/index.md>) for how to write one.

## Repository interfaces

Pick the base interface by how the methods take their `QueryExecutor` and report their results:

- [CrudRepository](<https://github.com/smyrgeorge/sqlx4k/blob/main/sqlx4k/src/commonMain/kotlin/io/github/smyrgeorge/sqlx4k/CrudRepository.kt>): an explicit `QueryExecutor` argument, `Result` return types.
- [ContextCrudRepository](<https://github.com/smyrgeorge/sqlx4k/blob/main/sqlx4k/src/commonMain/kotlin/io/github/smyrgeorge/sqlx4k/ContextCrudRepository.kt>): the `QueryExecutor` as a [context parameter](<https://smyrgeorge.github.io/sqlx4k/codegen/context-parameters/index.md>).
- [ArrowCrudRepository](<https://github.com/smyrgeorge/sqlx4k/blob/main/sqlx4k-arrow/src/commonMain/kotlin/io/github/smyrgeorge/sqlx4k/arrow/ArrowCrudRepository.kt>) (using the `sqlx4k-arrow` package)
- [ArrowContextCrudRepository](<https://github.com/smyrgeorge/sqlx4k/blob/main/sqlx4k-arrow/src/commonMain/kotlin/io/github/smyrgeorge/sqlx4k/arrow/ArrowContextCrudRepository.kt>) (using the `sqlx4k-arrow` package)

Every one of them extends `CrudRepositoryHooks<T>`, whose [hooks](<https://smyrgeorge.github.io/sqlx4k/codegen/hooks/index.md>) you can override for logging, metrics and tracing. For unit tests, the generator can also write an [in-memory implementation](<https://smyrgeorge.github.io/sqlx4k/codegen/in-memory-repositories/index.md>) of every repository.

For more details, take a look at the [examples](<https://smyrgeorge.github.io/sqlx4k/getting-started/examples/index.md>).

# Validation

The code generator checks the SQL of every `@Query` method during the build, so a broken query fails the build instead of failing at runtime:

- [SQL syntax validation](<#sql-syntax-validation>): the query parses.
- [SQL schema validation](<#sql-schema-validation>) (experimental, opt-in): the tables and columns exist in the schema built from your migrations.
- [Query validations](<#query-validations>): the query is consistent with its method and entity.

It also applies a few [optimizations](<#optimizations>) to the generated SQL.

## SQL syntax validation

- **What it is:** during code generation, sqlx4k parses the SQL string in each `@Query` method using `JSqlParser`. If the parser detects a syntax error, the build fails early with a clear error message pointing to the offending repository method.
- **What it checks:** only SQL syntax. It does not verify that tables/columns exist, parameter names match, or types are compatible.
- **When it runs:** at KSP processing time, before your code is compiled/run.
- **Dialect notes:** validation is dialect-agnostic and aims for an ANSI/portable subset. Some vendor-specific features (e.g., certain MySQL or PostgreSQL extensions) may not be recognized. If you hit a false positive, you can disable validation per module with the KSP arg `validate-sql-syntax=false`, or per query with `@Query(checkSyntax = false)`.
- **Most reliable with:** SELECT, INSERT, UPDATE, DELETE statements. DDL or very advanced constructs may not be fully supported.

Example of a build error you might see if your query is malformed:

```text
> Task :compileKotlin
Invalid SQL in function findAllBy: Encountered "FROMM" at line 1, column 15
```

Keep it enabled to catch typos early; if you rely heavily on vendor-specific syntax not yet supported by the parser, turn it off either globally or just for a specific method:

```kotlin
ksp { arg("validate-sql-syntax", "false") }
```

```kotlin
@Repository
interface UserRepository {
    @Query("select * from users where id = :id", checkSyntax = false)
    suspend fun findOneById(context: QueryExecutor, id: Int): Result<User?>
}
```

## SQL schema validation

> [!WARNING]
>
> **Experimental**
>
> SQL schema validation is currently in early development and may have limitations.

- **What it is:** during code generation, sqlx4k can also validate your `@Query` SQL against a known database schema. It loads your migration files, builds an in-memory schema, and uses Apache Calcite to validate that tables, columns, and basic types referenced by the query exist and are compatible.
- **What it checks:**

  - Existence of referenced tables and columns.
  - Basic type compatibility for literals and simple expressions.
  - It does not execute queries or connect to a database.
- **When it runs:** at KSP processing time, right after syntax validation.
- **Default:** disabled. You must enable it explicitly per module.
- **Requirements:** point the processor to your migrations directory so it can reconstruct the schema. The loader supports a pragmatic subset of DDL: CREATE TABLE, ALTER TABLE ADD/DROP COLUMN, and DROP TABLE, processed in migration order.
- **Dialect notes:** validation is based on Calcite's SQL semantics and a simplified schema model derived from your migrations. Some vendor-specific features and advanced DDL may not be fully supported.

Enable module-wide schema validation by adding KSP args in your `build.gradle.kts`:

```kotlin
ksp {
    arg("validate-sql-schema", "true")
    // Path to your migration .sql files (processed in ascending file version order)
    arg("schema-migrations-path", "./db/migrations")
}
```

You can also disable schema checks for a specific query:

```kotlin
@Repository
interface UserRepository {
    @Query("select * from users where id = :id", checkSchema = false)
    suspend fun findOneById(context: QueryExecutor, id: Int): Result<User?>
}
```

## Query validations and optimizations

Beyond syntax and schema checks, the processor runs a set of extra compile-time checks and codegen rewrites over each `@Query` (and the entity it maps to). Each is controlled by a global KSP option and enabled by default. They fall into two groups: **validations** that fail the build when a `@Query` or entity looks wrong, and **optimizations** that rewrite the generated SQL for efficiency without changing its result.

### Query validations

Compile-time safeguards that fail the build when a `@Query` (or the entity it maps to) is inconsistent: an unknown column or table, the wrong statement kind, an unsupported key shape, … They never change the generated SQL.

| KSP option | Default | What it does |
| --- | --- | --- |
| `reject-stacked-statements` | `true` | Fails the build if a single `@Query` contains more than one SQL statement (a stacked-query guard). |
| `validate-sql-columns` | `true` | Fails the build if a `@Query` references a column that does not exist on the entity, across the `SELECT` list, `WHERE`, `GROUP BY`, `HAVING`, `ORDER BY`, and `UPDATE ... SET`. Applies to single-table queries only (joins/subselects/other tables are skipped). |
| `validate-sql-table` | `true` | Fails the build if a single-table `@Query` targets a table other than the entity's own (`@Table`). Skips subselect `FROM` clauses. |
| `validate-count-projection` | `true` | Fails the build if a `count*` method does not select a single `count(...)` aggregate. |
| `validate-statement-kind` | `true` | Fails the build if a `@Query`'s statement kind doesn't match its prefix: `find*`/`count*`/`exists*` must be a `SELECT`, `delete*` a `DELETE`, and `execute*` a write (`INSERT`/`UPDATE`/`DELETE`). |
| `validate-projection` | `true` | Fails the build if a `find*` method bound by the generated row mapper selects only some columns (which would leave the mapper without a column at runtime). Requires `SELECT *` or every entity column. Skipped when a custom `@Repository(mapper = ...)` is used. |
| `validate-returning-columns` | `true` | Fails the build if a `RETURNING` clause references a column that does not exist on the entity. |
| `reject-grouping-in-scalar` | `true` | Fails the build if a `count*`, `findOne*`, or `exists*` method's `@Query` contains a `GROUP BY`/`HAVING` clause. A grouped query returns one row per group, but these methods read only the first row, so the result would be wrong. |
| `validate-single-id` | `true` | Fails the build if the repository entity declares more than one `@Id` property. Zero (a keyless entity) and one are both allowed; composite primary keys are not supported by the CRUD generator. |

Disable any of them module-wide in your `build.gradle.kts`:

```kotlin
ksp {
    // arg("reject-stacked-statements", "false")
    // arg("validate-sql-columns", "false")
    // arg("validate-sql-table", "false")
    // arg("validate-count-projection", "false")
    // arg("validate-statement-kind", "false")
    // arg("validate-projection", "false")
    // arg("validate-returning-columns", "false")
    // arg("reject-grouping-in-scalar", "false")
    // arg("validate-single-id", "false")
}
```

### Optimizations

Codegen rewrites that make the generated SQL more efficient. They never fail the build; a query that doesn't fit the rewrite is emitted unchanged.

| KSP option | Default | What it does |
| --- | --- | --- |
| `expand-select-star` | `true` | Rewrites a bare `SELECT *` over the entity's table into the entity's explicit columns in the generated statement (e.g. `select * from users` → `select id, name, email from users`). Leaves `count(*)`, joins, and explicit column lists untouched. |
| `findone-limit` | `true` | Appends `LIMIT 2` to `findOne*` queries so the driver fetches at most two rows: it still detects a multi-row result, but transfers far less on wide tables. Skips queries that already declare a `LIMIT`. |
| `expand-returning-star` | `true` | Rewrites a bare `RETURNING *` on a write over the entity's table into the entity's explicit columns (mirrors `expand-select-star`). Leaves an explicit `RETURNING` list, or a write against a different table, untouched. |
| `drop-redundant-order-by` | `true` | Strips a meaningless `ORDER BY` from `exists*`/`count*` queries: their result is a single scalar, so ordering only costs the database a sort. Leaves queries with no `ORDER BY` untouched. |

Disable any of them module-wide in your `build.gradle.kts`:

```kotlin
ksp {
    // arg("expand-select-star", "false")
    // arg("findone-limit", "false")
    // arg("expand-returning-star", "false")
    // arg("drop-redundant-order-by", "false")
}
```

> [!NOTE]
>
> Column/table validation and `SELECT *` expansion map columns back to properties using the same rules as the CRUD generator (snake\_case, or an explicit `@Column(name = "...")`), so they always stay consistent with the generated SQL.

With the [Gradle plugin](<https://smyrgeorge.github.io/sqlx4k/getting-started/gradle-plugin/index.md>), pass these options through `args` instead of a `ksp { }` block.

# Extensions

# Extensions

sqlx4k is designed to be extended: a growing set of tools builds on the drivers and the code generator.

- [PostgreSQL LISTEN/NOTIFY](<https://smyrgeorge.github.io/sqlx4k/databases/postgresql/#listen-notify>): subscribe to PostgreSQL notifications, built into `sqlx4k-postgres`.
- [PostgreSQL Message Queue (PGMQ)](<https://smyrgeorge.github.io/sqlx4k/extensions/pgmq/index.md>): a client for the [PGMQ](<https://github.com/pgmq/pgmq>) PostgreSQL extension, to build asynchronous workers on top of PostgreSQL (`sqlx4k-postgres-pgmq`).
- [SQLDelight](<https://smyrgeorge.github.io/sqlx4k/extensions/sqldelight/index.md>): run the type-safe code [SQLDelight](<https://github.com/sqldelight/sqldelight>) generates from your `.sq` files on the sqlx4k drivers.
- [Arrow](<https://smyrgeorge.github.io/sqlx4k/codegen/repositories/#repository-interfaces>): repository interfaces for [Arrow](<https://arrow-kt.io/>) (`sqlx4k-arrow`).

With the [Gradle plugin](<https://smyrgeorge.github.io/sqlx4k/getting-started/gradle-plugin/index.md>), add `sqlx4k-postgres-pgmq` and `sqlx4k-arrow` with `extensions(Pgmq, Arrow)`.

# PostgreSQL Message Queue (PGMQ)

```kotlin
implementation("io.github.smyrgeorge:sqlx4k-postgres-pgmq:1.17.0")
```

A small, focused Kotlin Multiplatform client around the [PGMQ](<https://github.com/pgmq/pgmq>) PostgreSQL extension. Build reliable, asynchronous workers on top of PostgreSQL with first-class support for LISTEN/NOTIFY and a simple consumer API. It requires `sqlx4k-postgres`, so all the [PostgreSQL driver](<https://smyrgeorge.github.io/sqlx4k/databases/postgresql/index.md>) features are available. With the [Gradle plugin](<https://smyrgeorge.github.io/sqlx4k/getting-started/gradle-plugin/index.md>), add it with `extensions(Pgmq)`.

## Why use it

- PGMQ operations: create/drop/list queues, send/read/pop/ack/nack, purge, archive, metrics
- Send and receive messages with headers and delays
- Visibility timeout and retries with exponential backoff
- PostgreSQL LISTEN/NOTIFY for instant wake-ups (optional)
- Batch operations and lightweight metrics
- Exactly-once processing semantics via transactions (read/process/ack within one DB transaction)
- Works on Kotlin/JVM and Kotlin/Native

## Quick start

```kotlin
// Set up a PostgreSQL connection
val db = PostgreSQL(
    url = "postgresql://localhost:5432/test",
    username = "postgres",
    password = "postgres"
)

// Create client (auto-installs/validates PGMQ)
val pgmq = PgmqClient(
    pg = PgmqDbAdapterImpl(db),
    options = PgmqClient.Options(autoInstall = true, verifyInstallation = true)
)

// Create a queue (enable NOTIFY for near real-time consumers)
val queue = PgmqClient.Queue(name = "my_queue", enableNotifyInsert = true)
pgmq.create(queue).getOrThrow()

// Send
pgmq.send("my_queue", """{"orderId": 1}""").getOrThrow()

// Read + ack
pgmq.read("my_queue", quantity = 1, vt = 30.seconds).getOrThrow().forEach { msg ->
    // process(msg)
    pgmq.ack("my_queue", msg.msgId).getOrThrow()
}
```

## Consumer

`PgmqConsumer` reads messages in the background, acknowledges the ones processed successfully and negatively acknowledges (with backoff) the ones that fail:

```kotlin
val consumer = PgmqConsumer(
    pgmq = pgmq,
    options = PgmqConsumer.Options(
        queue = "my_queue",
        prefetch = 10,
        vt = 30.seconds,
        autoStart = true,
        enableNotifyInsert = true
    ),
    onMessage = { message ->
        // process(message)
        // success -> auto-ack
    },
    onFailToProcess = { error ->
        // failure -> auto-nack with backoff
    }
)

// Stop when you're done
consumer.stop()
```

## Topics

Topic routing (pub/sub style) is part of the PGMQ extension since 1.11.0: bind wildcard patterns to queues, then publish with a routing key and every queue whose pattern matches receives the message.

- `*` matches exactly one segment, `#` matches zero or more (`logs.*` matches `logs.error` but not `logs.error.fatal`; `logs.#` matches both).
- A pattern without wildcards routes on an exact match, and `#` alone fans out every message.

```sql
-- 1) Create queues and bind topic patterns
SELECT pgmq.create('logs_all');
SELECT pgmq.create('logs_error');
SELECT pgmq.bind_topic('logs.#', 'logs_all');
SELECT pgmq.bind_topic('logs.*.error', 'logs_error');

-- 2) Dry-run to see where a key would route (no send)
SELECT *
FROM pgmq.test_routing('logs.api.error');

-- 3) Publish to all matching queues (returns the number of queues it was sent to)
SELECT pgmq.send_topic('logs.api.error', '{"message":"boom"}'::jsonb); -- 2: logs_all and logs_error
SELECT pgmq.send_topic('logs.api.info', '{"message":"up"}'::jsonb);    -- 1: logs_all
```

The client exposes `bindTopic`, `unbindTopic` and `sendTopic`.

## Requirements

- PostgreSQL 12+
- PGMQ extension installed (1.11.0+ for topics)

## Examples

See a runnable example in [examples/postgres](<https://github.com/smyrgeorge/sqlx4k/blob/main/examples/postgres/src/commonMain/kotlin/io/github/smyrgeorge/sqlx4k/examples/postgres/Examples.kt>).

# SQLDelight

[SQLDelight](<https://github.com/sqldelight/sqldelight>) integration for type-safe SQL queries with sqlx4k: write your queries in `.sq` files, and run the generated code on top of the sqlx4k drivers.

The integration lives in its own repository: [smyrgeorge/sqlx4k-sqldelight](<https://github.com/smyrgeorge/sqlx4k-sqldelight>).

# Contributing

# Benchmarks

The benchmarks live under [bench](<https://github.com/smyrgeorge/sqlx4k/tree/main/bench>).

## Setup

- All tests were performed on my local machine.
- The database was installed inside a Docker container, using ARM64 images.
- Apart from the database drivers, the rest of the code was kept as similar as possible.

## Runs

We conducted three scenarios:

- `no-transaction`: A set of 3 queries was executed: two inserts and one fetch.
- `transaction-commit`: 3 queries were executed within a transaction (two inserts and one fetch), followed by a fetch after the transaction was completed.
- `transaction-rollback`: 3 queries were executed within a transaction (two inserts and one fetch), followed by a fetch after the transaction was rolled back.

In each scenario, we used **4 workers**. Each worker executed the test **1000 times** in a loop. We performed **10 runs** per scenario and calculated the average execution time.

# Building

You will need the `Rust` toolchain to build this project. Check here: [https://rustup.rs/](<https://rustup.rs/>)

> [!NOTE]
>
> By default, the project will build only for your system architecture-os (e.g. `macosArm64`, `linuxArm64`, etc.)

Also, make sure that you have installed all the necessary targets (only if you want to build for all targets):

```shell
rustup target add aarch64-apple-ios-sim
rustup target add x86_64-linux-android
rustup target add aarch64-linux-android
rustup target add aarch64-apple-darwin
rustup target add aarch64-unknown-linux-gnu
rustup target add x86_64-unknown-linux-gnu
rustup target add x86_64-pc-windows-gnu
```

On a clean checkout (and after every version bump), bootstrap the build first: it publishes the sqlx4k Gradle plugin to mavenLocal, which the example modules apply before the main build can even configure (see [bootstrap.sh](<https://github.com/smyrgeorge/sqlx4k/blob/main/scripts/bootstrap.sh>)):

```shell
./scripts/bootstrap.sh
```

Then, run the build.

```shell
# will build only for the current target
./gradlew build
```

You can also build for specific targets.

```shell
./gradlew build -Ptargets=macosArm64
```

To build for all available targets, run:

```shell
./gradlew build -Ptargets=all
```

To run the examples, see [Examples](<https://smyrgeorge.github.io/sqlx4k/getting-started/examples/index.md>).

## Documentation

This site is built with [Zensical](<https://zensical.org/>) from the Markdown files under `docs/`, and the API reference with Dokka. To preview the site locally:

```shell
pip install zensical
zensical serve
```

Both are built and deployed to GitHub Pages by the [docs workflow](<https://github.com/smyrgeorge/sqlx4k/blob/main/.github/workflows/docs.yml>) on every release tag. To generate the API reference locally:

```shell
./gradlew :dokka:dokkaGenerate
# Output: dokka/build/dokka/html
```

## Publishing

```shell
./gradlew publishAllPublicationsToMavenCentralRepository -Ptargets=all
```

## Checking for memory leaks

### macOS (using the `leaks` tool)

Check for memory leaks with the `leaks` tool. First, sign the binary:

```shell
codesign -s - -v -f --entitlements =(echo -n '<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "https://www.apple.com/DTDs/PropertyList-1.0.dtd"\>
<plist version="1.0">
    <dict>
        <key>com.apple.security.get-task-allow</key>
        <true/>
    </dict>
</plist>') ./bench/postgres-sqlx4k/build/bin/macosArm64/releaseExecutable/postgres-sqlx4k.kexe
```

Then run the tool:

```shell
leaks -atExit -- ./bench/postgres-sqlx4k/build/bin/macosArm64/releaseExecutable/postgres-sqlx4k.kexe
```

# Roadmap

Next steps (contributions are welcome):

- Driver-level interceptor. A QueryListener on the driver gives query logging, slow-query warnings, and metrics.
- Pool lifecycle hooks and health. afterConnect for session setup such as search\_path, timezone, or application\_name, plus ping () and acquire-wait metrics.
- Postgres COPY. COPY FROM STDIN is an order of magnitude faster than multi-row INSERT for bulk loads.
- `kotlinx.serialization` module. JSON and JSONB columns to and from @Serializable classes, and a generic @Converter for any serializable type.
- Type coverage: a Kotlin decimal type for NUMERIC/decimal (values already decode as exact text), Duration or interval, enum or composite types.
- WASM support (?).

Found a bug or want to propose something? Open an [issue](<https://github.com/smyrgeorge/sqlx4k/issues>).