sqlx4k

📰 Articles

Usage

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

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.

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

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

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()
}

Compile-time validations

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

Compile-time optimizations

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

Without context parameters

@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

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()
}

Features

Examples

Contributing

Acknowledgements

License