Yet Another SQL Builder

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.

Key Features

1. Kotlin-friendly DSL

   1. Insert

      insertInto(UsersTable) {
          it[id] = user.id
          it[username] = user.username
          it[password] = user.password
      }.execute()

   2. Update

      update(
          UsersTable,
          set = {
              it[username] = user.username
              it[password] = user.password
          },
          where = {
              UsersTable.id.eq(user.id)
          }
      )
      .execute()

   3. Delete

      delete()
          .from(UsersTable)
          .where { UsersTable.id.eq(id) }
          .execute()

   4. Select

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

   5. Select with Inner Join

      select(PetsTable.allColumns())
          .from(PetsTable)
          .innerJoin(UsersTable, on = {
              PetsTable.owner.eq(UsersTable.id)
          })
          .where {
              UsersTable.username.eq(username)
          }.execute()
          .map {
              it.toPet()
          }

2. Gradle Plugin for Generating Kotlin Declarations from Migration Scripts

   Plugin

   plugins{
       id("io.github.mejiomah17.yasb")
   }
   tasks.withType<GenerateTablesTask> {
       database = Database.Postgres(DockerImageName.parse("postgres").withTag("16.1"))
       packageName = "com.github.mejiomah17.yasb"
       flywayMigrationDirs.add(projectDir.resolve("src/main/resources/db/migration"))
   }

   Generates

   package com.github.mejiomah17.yasb
   
   object UsersTable : com.github.mejiomah17.yasb.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
   );

3. Explicit Transaction Management

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

4. Explicit Queries

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

5. Extensible

   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)

6. Explicit Dialect Support

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

Example

Example

Supported Dialects

	Postgres	Sqlite
JVM	✅	✅
Android	❌	✅

Versioning

In general YASB uses semver. YASB version consist from four parts:

1. Major - incompatible API changes
2. Minor - new functionality in a backward compatible manner
3. Patch - backward compatible bug fixes
4. Kotlin compiler - YASB relies on experimental kotlin feature Context receivers. Each YASB version ends with kotlin compiler version until Context receivers release.

Contributions

Any contribution is highly welcome. Feel free to raise PR or issue.