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.