yaksb
2.0.0-kotlin-2.2.0indexedIntuitive DSL for building SQL queries, supporting transactions, migrations, and extensibility. Offers explicit query control and dialect support, enhancing database interaction.
Intuitive DSL for building SQL queries, supporting transactions, migrations, and extensibility. Offers explicit query control and dialect support, enhancing database interaction.
YASB is a Kotlin-friendly DSL for building SQL queries, providing an intuitive and expressive way to interact with databases. It offers a range of features tailored for Kotlin developers, enabling seamless integration with various databases and explicit control over transactions and queries.
| Postgres | Sqlite | |
|---|---|---|
| JVM | :white_check_mark: | :white_check_mark: |
| Android | :x: | :white_check_mark: |
In general YASB uses semver. YASB version consist from four parts:
Any contribution is highly welcome. Feel free to raise PR or issue.
Kotlin-friendly DSL
insertInto(UsersTable) {
it[id] = user.id
it[username] = user.username
it[password] = user.password
}.execute()
update(
UsersTable,
set = {
it[username] = user.username
it[password] = user.password
},
where = {
UsersTable.id.eq(user.id)
}
)
.execute()
delete()
.from(UsersTable)
.where { UsersTable.id.eq(id) }
.execute()
select(UsersTable.allColumns())
.from(UsersTable)
.where { UsersTable.id.eq(id) }
.execute()
.singleOrNull()
?.let {
UserRecord(
id = it[UsersTable.id],
username = it[UsersTable.username],
password = it[UsersTable.password]
)
}
select(PetsTable.allColumns())
.from(PetsTable)
.innerJoin(UsersTable, on = {
PetsTable.owner.eq(UsersTable.id)
})
.where {
UsersTable.username.eq(username)
}.execute()
.map {
it.toPet()
}
Plugin
plugins{
id("io.github.mejiomah17.yaksb")
}
tasks.withType<GenerateTablesTask> {
database = Database.Postgres(DockerImageName.parse("postgres").withTag("16.1"))
packageName = "com.github.mejiomah17.yaksb"
flywayMigrationDirs.add(projectDir.resolve("src/main/resources/db/migration"))
}
Generates
package com.github.mejiomah17.yaksb
object UsersTable : com.github.mejiomah17.yaksb.postgres.jdbc.PostgresJdbcTable<UsersTable> {
override val tableName = "users"
val id = uuid("id")
val password = text("password")
val username = text("username")
}
From migration script
CREATE TABLE public.users
(
id uuid NOT NULL PRIMARY KEY,
password text NOT NULL,
username text NOT NULL
);
context(TransactionAtLeastRepeatableRead)
fun register(username: String, password: String): RegisterResult {
if (userDao.exist(username)) {
return RegisterResult.UserAlreadyExist
}
val user = UserRecord(
id = UUID.randomUUID(),
username = username,
password = hash(password)
)
userDao.create(user)
return RegisterResult.Registered(user)
}
fun callRegister(){
// compiler error: register can't be invoked outside of transaction
register("John", "john_pass")
transactionFactory.repeatableRead{
// ok
register("John", "john_pass")
}
transactionFactory.serializable{
// ok Serializable > RepeatableRead
register("John", "john_pass")
}
transactionFactory.readCommited{
// compiler error: ReadCommited < RepeatebleRead
register("John", "john_pass")
}
}
val query = select(UsersTable.allColumns())
.from(UsersTable)
.where { UsersTable.id.eq(id) }
query.sql() == "SELECT users.id, users.password, users.username FROM users WHERE users.id = ?"
query.parameters() == listOf(UuidParameter(id))
Everything can be extended. Here is an example of how Returning is implemented:
class Returning<DRIVER_DATA_SOURCE, DRIVER_STATEMENT>(
private val insert: InsertQuery<*, DRIVER_DATA_SOURCE, DRIVER_STATEMENT>,
private val expressions: List<Expression<*, DRIVER_DATA_SOURCE, DRIVER_STATEMENT>>
) : ReturningQuery<DRIVER_DATA_SOURCE, DRIVER_STATEMENT> {
override fun returnExpressions(): List<Expression<*, DRIVER_DATA_SOURCE, DRIVER_STATEMENT>> {
return expressions
}
override fun sql(): String {
return insert.sql() + " RETURNING ${expressions.joinToString(", ") { it.sql() }}"
}
override fun parameters(): List<Parameter<*, DRIVER_DATA_SOURCE, DRIVER_STATEMENT>> {
return insert.parameters() + expressions.flatMap { it.parameters() }
}
}
fun <DRIVER_DATA_SOURCE, DRIVER_STATEMENT> InsertQuery<*, DRIVER_DATA_SOURCE, DRIVER_STATEMENT>.returning(
expressions: List<Expression<*, DRIVER_DATA_SOURCE, DRIVER_STATEMENT>>
): Returning<DRIVER_DATA_SOURCE, DRIVER_STATEMENT> {
return Returning(this, expressions)
}
// usage
insertInto(Table) {
it[Table.a] = "abc"
}.returning(Table.id)
// ok. SqliteJdbcDatabaseDialect supports insert with returning
context(SqliteJdbcDatabaseDialect)
fun jdbcInsert(){
insertInto(Table) {
it[Table.a] = "abc"
}.returning(Table.id)
}.execute().single()
}
// compiler error: SqliteAndroidDatabaseDialect does not support insert with returning
context(SqliteAndroidDatabaseDialect)
fun jdbcInsert(){
insertInto(Table) {
it[Table.a] = "abc"
}.returning(Table.id)
}.execute().single()
}
Surfaced from shared tags and platforms — no rankings paid for.