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 Validation for more details).

The library is designed to be extensible, with a growing ecosystem of tools and extensions like PGMQ (PostgreSQL Message Queue), SQLDelight integration, and more.

📖 Documentation · 📚 API reference · 🤖 Agent skill

📰 Articles

Short deep‑dive posts covering Kotlin/Native, FFI, and Rust ↔ Kotlin interop used in sqlx4k:

• Introduction to the Kotlin Native and FFI: [Part 1], [Part 2]
• Interoperability between Kotlin and Rust, using FFI: [Part 1], (Part 2 soon)

Usage

The Gradle plugin adds the driver and sets up the code generation for you:

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.generated"
}

Or add the driver yourself:

[!NOTE] The driver alone does not set up the code generation (@Table, @Repository, compile-time validation): use the plugin, or configure KSP yourself as described in Manual setup.

implementation("io.github.smyrgeorge:sqlx4k-postgres:1.18.0")
// or for MySQL
implementation("io.github.smyrgeorge:sqlx4k-mysql:1.18.0")
// or for SQLite
implementation("io.github.smyrgeorge:sqlx4k-sqlite:1.18.0")
// or for SQLite with encryption (SQLCipher)
implementation("io.github.smyrgeorge:sqlx4k-sqlite-cipher:1.18.0")

Showcase

[!TIP] Using an AI coding agent (Claude Code, Codex, Cursor, Copilot, …)? Install the sqlx4k skill, so it sets up the project and writes the code the recommended way:

npx skills add smyrgeorge/sqlx4k

See AI agents.

Map a data class to a table with @Table:

@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,
)

Declare a @Repository: the code generator writes its implementation, UserRepositoryImpl, with your @Query methods and the CRUD operations:

• insert: inserts the entity and returns it as stored, with the generated columns (e.g. the id) read back
• update: updates the row matched by the @Id and returns the entity as stored
• delete: deletes the row matched by the @Id
• save: inserts a new entity (id 0) or updates an existing one
• batchInsert and batchUpdate: insert or update many entities with a single statement

@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>
}

[!TIP] Using Arrow? Extend ArrowContextCrudRepository (or ArrowCrudRepository) from sqlx4k-arrow instead, and the generated methods return Either<SQLError, T> rather than Result<T>. See Repository interfaces.

The methods take the QueryExecutor (the database, a connection or a transaction) from the scope, as a context parameter:

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

// The transaction returns the value of its block: here, the updated user.
val updated: User = db.transaction {
    // Every call in the block runs in the transaction.
    // The entities are immutable: every call returns a new instance, with the values the database stored.
    val user = User(name = "Alice", email = "[email protected]") // id = 0
    val alice = UserRepositoryImpl.insert(user).getOrThrow() // id generated by the database
    val renamed = alice.copy(name = "Alice Smith")
    UserRepositoryImpl.update(renamed).getOrThrow()
}

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

See Repositories, Entities and Context parameters for the details.

Compile-time validations

Every @Query is checked while the code is generated, so a broken query fails the build instead of failing at runtime:

@Query("SELECT * FROM users WHERE emial = :email") // Fails the build: `emial` is not a column of `User`.
context(context: QueryExecutor)
suspend fun findOneByEmail(email: String): Result<User?>

See Query validations for the full list.

Compile-time optimizations

The generated SQL is optimized while the code is generated, without changing its result. For instance, findOneByEmail from the repository above runs as:

-- SELECT * is expanded into the columns of the entity, and LIMIT 2 is enough to tell one row from many.
SELECT id, name, mail_address FROM users WHERE mail_address = :email LIMIT 2

See Optimizations for the full list.

Without context parameters

Extend CrudRepository instead, and pass the QueryExecutor as the first argument of every call:

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

val updated: User = db.transaction {
    // save inserts a new entity (id = 0) and updates an existing one.
    val user = User(name = "Alice", email = "[email protected]")
    val alice = UserRepositoryImpl.save(this, user).getOrThrow() // insert
    UserRepositoryImpl.save(this, alice.copy(name = "Alice Smith")).getOrThrow() // update
}

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

Using the driver directly

The code generation is optional: the driver runs your SQL as it is, with the values bound as the parameters of a prepared statement.

[!NOTE] The compile-time validations and optimizations above apply to the @Query methods of @Repository interfaces only: SQL passed straight to the driver runs as written.

// execute returns the number of affected rows.
val affected: Long = db.execute("delete from users where name = 'Bob'").getOrThrow()

// Bind the values instead of concatenating them into the SQL (named :name or positional ? parameters).
val statement = Statement
    .create("select * from users where name = :name")
    .bind("name", "Alice")

// fetchAll returns all the rows: read their columns yourself...
val emails: List<String> = db.fetchAll(statement).getOrThrow().map { it.get("mail_address").asString() }

// ...or with a RowMapper.
object UserRowMapper : RowMapper<User> {
    override fun map(row: ResultSet.Row, converters: ValueEncoderRegistry): User = User(
        id = row.get("id").asLong(),
        name = row.get("name").asString(),
        email = row.get("mail_address").asString(),
    )
}

val users: List<User> = db.fetchAll(statement, UserRowMapper).getOrThrow()

// fetch streams the rows as a Flow, without loading them all in memory.
db.fetch("select * from users", UserRowMapper).collect { println(it) }

// The transaction is committed at the end of the block, and rolled back if the block fails.
db.transaction {
    execute("insert into users(name, mail_address) values ('Bob', '[email protected]')").getOrThrow()
    execute("update users set name = 'Robert' where name = 'Bob'").getOrThrow()
}

See Running queries, Mapping rows and Transactions for the details.

Features

• Supported databases: PostgreSQL, MySQL, MariaDB, SQLite and SQLCipher (encrypted SQLite)
• Gradle plugin (sqlx4k-gradle-plugin)
• Async I/O and coroutines
• Connection pool and settings
• Acquiring and using connections
• Running queries
  • execute: for SQL statements that modify data
  • executeWithResult: for inserts that generate an id
  • fetchAll: for queries that return data
  • fetch: for streaming large results
• Prepared statements (named and positional parameters)
• Row mappers
• Custom value converters
• Transactions
  • Savepoints
  • TransactionContext (coroutines)
• Code generation: CRUD and @Repository implementations
  • Repositories: generated CRUD operations and @Query methods
  • Customizing columns with @Column
  • Excluding properties with @Transient
  • Optimistic locking with @Version
  • Auto-generated RowMapper
  • Batch operations
  • Property-level converters (@Converter)
  • Context parameters
  • Repository hooks
  • List of repository interfaces
  • SQL syntax validation (compile-time)
  • SQL schema validation (compile-time)
  • Query validations and optimizations
  • In-memory repositories (for unit testing)
• Database migrations
• Extensions
  • PostgreSQL LISTEN/NOTIFY
  • PostgreSQL Message Queue (PGMQ)
  • SQLDelight
• Supported targets: JVM, Android, iOS, macOS, Linux, Windows

Examples

Full runnable applications, built with the Gradle plugin:

• PostgreSQL: examples/postgres
• MySQL/MariaDB: examples/mysql
• SQLite: examples/sqlite

Contributing

See Building for how to build the project (it needs the Rust toolchain), and the Roadmap for what is coming next; contributions are welcome.

The documentation lives in docs and is published with Zensical on every release.

Acknowledgements

sqlx4k stands on the shoulders of excellent open-source projects, such as:

• sqlx
• r2dbc
• sqlite-jdbc
• SQLCipher
• JSqlParser
• Apache Calcite

See the full list.

License

MIT — see LICENSE.