Sqlx4k

sqlx4k

sqlx4k is not an ORM: you write the SQL, and sqlx4k runs it with coroutines, maps the rows, and checks your queries at build time. Its code generator (KSP) writes the repository implementations, the CRUD statements and the row mappers.

Full documentation: https://smyrgeorge.github.io/sqlx4k/ (an index for agents: llms.txt at the same address).

Defaults: follow them in this order

Apply these defaults to all new code, and always when setting up a project from scratch. Deviate only when the project already does otherwise, or when the user asks.

1. Set up the project with the Gradle plugin. It adds the driver and configures the code generator. Do not add the sqlx4k dependencies or wire KSP by hand in a new project.
2. Access the data through repositories. Map every table the code reads or writes to a @Table entity, and access it through a @Repository interface extending ContextCrudRepository (context parameters). Write every query as a @Query method, so the build validates it. Use the plain driver API (execute, fetchAll, …) only for what a repository cannot express: DDL, dynamic SQL, or a result that does not map to an entity.
3. Write entities with save. save(entity) inserts a new entity (id 0) and updates an existing one, so the calling code does not need to know which. Call insert or update only when the entity's id is not an Int or Long generated by the database (e.g. a Uuid, or any @Id(insert = true)), where save cannot tell new from existing.

A project from scratch

1. Set up Gradle with the plugin

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

sqlx4k {
    driver = PostgreSQL // also: MySQL, MariaDB, SQLite, SQLiteCipher
    generatedCodePackage = "com.example.db" // where the generated code goes
    // sourceSets = listOf("main") // for a plain kotlin("jvm") project
}

The plugin adds the driver dependency and configures KSP. Without it, the code generator must be set up by hand (see references/code-generation.md).

2. Map a table

@Table("users")
data class User(
    @Id
    val id: Long = 0, // generated by the database; 0 until the entity is saved, so `save` knows it is new
    val name: String, // column `name`
    @Column(name = "mail_address")
    val email: String,
    @Column(insert = false, update = false)
    val createdAt: Instant? = null, // column `created_at` (snake_case): set by the database, read back
)

Prefer a database-generated Long (or Int) id starting at 0: it is what lets save work. Use @Id(insert = true) only for an id the application must set (e.g. a Uuid), and then write with insert/update.

3. Declare a repository

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

    @Query("SELECT * FROM users WHERE name = :name")
    context(context: QueryExecutor)
    suspend fun findAllByName(name: String): Result<List<User>>

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

The build generates UserRepositoryImpl (an object, in generatedCodePackage) with these methods plus the CRUD operations: save (insert or update), delete, batchInsert, batchUpdate, and the lower-level insert and update.

4. Use it

val db = PostgreSQL(url = "postgresql://localhost:5432/app", username = "app", password = "secret")

// The QueryExecutor is the database, a connection or a transaction, taken from the scope.
val updated: User = db.transaction {
    // save inserts a new entity (id = 0) and updates an existing one.
    val alice = UserRepositoryImpl.save(User(name = "Alice", email = "[email protected]")).getOrThrow() // insert
    UserRepositoryImpl.save(alice.copy(name = "Alice Smith")).getOrThrow() // update
}

with(db) {
    val alice: User? = UserRepositoryImpl.findOneByEmail("[email protected]").getOrThrow()
}

Without context parameters, extend CrudRepository<User> instead and pass the executor as the first argument of every method: suspend fun findOneByEmail(context: QueryExecutor, email: String): Result<User?>, called as UserRepositoryImpl.findOneByEmail(db, "...").

Rules that the build enforces

The code generator validates every @Query and fails the build when a rule is broken. Write them right the first time:

• The method name prefix decides the statement and the return type:

  Prefix	Statement	Return type
  findAll / findAllBy…	SELECT	Result<List<T>>
  findOneBy…	SELECT	Result<T?>
  countAll / countBy…	SELECT count(...)	Result<Long>
  existsBy…	SELECT	Result<Boolean>
  deleteAll / deleteBy…	DELETE	Result<Long>
  execute…	INSERT / UPDATE / DELETE	Result<Long>
  streamAll / streamAllBy…	SELECT	Flow<T>

  stream* methods are not suspend: the query runs when the flow is collected.

• Parameters are named: :email binds the method parameter email. Never concatenate values into the SQL.

• Columns must exist on the entity, by their column name (mail_address, not email), in the SELECT list, WHERE, ORDER BY, GROUP BY, HAVING and SET. A single-table query must target the entity's own table.

• find* methods must select every column: use SELECT * (expanded to the entity's columns at build time).

• countBy…, findOneBy… and existsBy… cannot use GROUP BY / HAVING, and a count* must select a single count(...).

• One statement per @Query, and at most one @Id per entity.

• For SQL the parser does not understand (vendor-specific syntax), use @Query(..., checkSyntax = false).

Pitfalls

• Every call returns a Result. Call .getOrThrow() or handle the failure; an ignored Result hides errors. db.transaction { } throws instead (db.transactionCatching { } returns a Result).
• A transaction is rolled back when its block throws or returns a failed Result. Run every statement of the block on its receiver (or, with context parameters, inside the block), not on db, or it runs outside the transaction.
• Entities are immutable: save (like insert/update) returns a new instance with the values the database stored (the generated id, defaults). Keep the returned instance, not the one you passed in: saving the original again would insert a second row.
• save decides from the id: an Int/Long id of 0 (or null) inserts, any other value updates. With another id type, save fails at runtime, not at build time; with an id the application sets (@Id(insert = true)), it would update a row that does not exist yet. In both cases call insert for new entities and update for existing ones.
• findOneBy… fails (MultipleRowsReturned) when the query matches more than one row.
• batchInsert/batchUpdate return the row count, not the entities: generated ids are not read back.
• Reading columns by hand needs imports: asLong(), asInstant(), asUuid(), … are extension functions in io.github.smyrgeorge.sqlx4k.impl.extensions (PostgreSQL arrays: io.github.smyrgeorge.sqlx4k.postgres.extensions).
• SQLite in memory (sqlite::memory:) needs a pool of exactly one connection.
• MySQL has no RETURNING clause (MariaDB has it for INSERT and DELETE only): do not write one in a @Query there. The generated CRUD operations re-read the row themselves.

Errors

Failures are SQLErrors. Branch on kind, which is the same on every database, rather than on vendor codes:

with(db) {
    UserRepositoryImpl.save(user).onFailure { e ->
        when ((e as? SQLError)?.kind) {
            SQLError.Kind.UniqueViolation -> println("The email is already taken.")
            else -> throw e
        }
    }
}

Kinds: UniqueViolation, ForeignKeyViolation, NotNullViolation, CheckViolation, ExclusionViolation, Deadlock, SerializationFailure, LockTimeout (the last three: retry the transaction), Other. code tells what failed (Database, PoolTimedOut, OptimisticLockFailed, …); sqlState and nativeCode carry the vendor codes.

Reference

Read these only when the task needs them:

• references/code-generation.md: entities (@Column, @Transient, @Version, @Converter), repository interfaces (Arrow too), batch operations, hooks, validation options, in-memory test repositories, setup without the plugin.
• references/driver-api.md: plain SQL with execute/fetchAll/fetch, statements and binding, row mappers, supported types, transactions, savepoints, TransactionContext, the connection pool.
• references/databases.md: the driver of each database, connection URLs, platform notes, PostgreSQL LISTEN/NOTIFY and PGMQ.
• references/migrations.md: migration files and migrate.