Lightweight, type-safe DSL for building complex SQL query strings programmatically, focusing on SQLite. Offers type-safe builders, expressive DSL, and zero platform-specific code, ensuring enhanced readability without third-party dependencies.
SQLinK is a Kotlin Multiplatform, lightweight, type-safe DSL for building SQL query strings. Without any platform code or third-party dependency
Only support SQLite
It helps you construct complex SQL queries programmatically without worrying about string concatenation errors or SQL injection vulnerabilities that can arise from manual string building. SQLinK focuses purely on generating the SQL string; it does not include any database drivers or execute queries. You are responsible for using the generated SQL string with your preferred database connection library.
actual/expect implementations tied to specific platforms.To use SQLinK in your Kotlin project, add the following dependency to your build.gradle.kts file:
kotlin {
sourceSets {
commonMain.dependencies {
implementation("com.gargantua.sqlink:sqlink:1.0.0")
}
}
}
Below are examples demonstrating how to use SQLinK, based on the Sample.kt provided in the Sample module.
First, define your table structure using the Table object and property delegates:
enum class Gender {
MALE, FEMALE
}
object Person : Table("Person") {
val id = text("id")
val name = text("name")
val age = number()
gender = <Gender>()
spouse = text()
remark = text()
}
Use the sql { ... } builder to construct your queries.
a. SELECT Query
val selectQuery = sql {
select(Person.all) // Select all columns from Person
from(Person)
where { (Person.gender eq Gender.FEMALE) or ((Person.age less 18) and (Person.name like "Michel")) }
orderBy(Person.id.asc, Person.age.desc)
}
println(selectQuery)
// Output (example):
// SELECT Person.id, Person.name, Person.age, Person.gender, Person.spouse, Person.remark FROM Person WHERE ((Person.gender = 'FEMALE') OR ((Person.age < 18) AND (Person.name LIKE 'Michel'))) ORDER BY Person.id ASC, Person.age DESC
b. UPDATE Statement
val updateQuery = sql {
update(Person) {
it.age set (it.age + 1) // Increment age
it.remark set null // Set remark to null
}
where {
and {
exp(Person.age more 18)
or {
and {
exp(Person.age less 60)
exp(Person.gender eq Gender.MALE)
}
and {
exp(Person.age less )
exp(Person.gender eq Gender.FEMALE)
}
}
}
}
}
println(updateQuery)
c. DELETE Statement
val userIdsToDelete = listOf("001", "002")
val deleteQuery = sql {
delete(Person)
where { Person.id IN userIdsToDelete }
}
println(deleteQuery)
// Output (example):
// DELETE FROM Person WHERE Person.id IN ('001', '002')
d. UNION of SELECT Statements
val unionQuery = sql {
sql { // First SELECT
select(Person.id AS )
from(Person)
{ (Person.age more ) and (Person.gender eq Gender.MALE) }
}
union
sql {
select(Person.id AS )
from(Person)
{ (Person.age more ) and (Person.gender eq Gender.FEMALE) }
}
orderBy(Person.id.asc)
}
println(unionQuery)
e. Aggregate Functions (e.g., COUNT)
val countQuery = sql {
select(count(Person.gender eq Gender.FEMALE) AS "female_count") // Count females
from(Person)
}
println(countQuery)
// Output (example):
// SELECT COUNT(Person.gender = 'FEMALE') AS female_count FROM Person
f. Common Table Expressions (CTE / WITH clause)
val cteQuery = sql {
val adultPersons = QueryResultSet("adult_persons") // Define a CTE name
with(adultPersons) { // Define the CTE body
select(Person.all)
from(Person)
where { Person.age more 18 }
}
// Now use the CTE in the main query
select(Person.id AS , Person.name)
from(adultPersons)
{ Person.gender eq Gender.MALE }
}
println(cteQuery)
g. JOINs (e.g., LEFT JOIN with table aliases)
val joinQuery = sql {
val selfTable = Person AS "self" // Alias for Person table
val spouseTable = Person AS "spouse_alias"
select(
selfTable { name } AS ,
spouseTable { name } AS
)
from(selfTable)
leftJoin(spouseTable) {
selfTable { spouse } eq spouseTable { id }
}
{
spouseTable { id }.isNotNull
}
}
println(joinQuery)
h. CASE Expressions
val caseQuery = sql {
select(
Person.name,
case(Person.gender) { // CASE expression on gender
WHEN(Gender.MALE, string("M")) // WHEN MALE THEN 'M'
WHEN(Gender.FEMALE, string("F")) // WHEN FEMALE THEN 'F'
ELSE(string("O")) // ELSE 'O' (Optional)
} AS "gender_code" // Alias for the case expression result
)
from(Person)
}
println(caseQuery)
// Output (example):
// SELECT Person.name, CASE Person.gender WHEN 'MALE' THEN 'M' WHEN 'FEMALE' THEN 'F' ELSE 'O' END AS gender_code FROM Person
These examples cover some of the core functionalities of SQLinK. Explore the library for more advanced features and combinations.
Contributions are welcome! Please feel free to submit issues or pull requests.
This project is licensed under the Apache 2.0 License.
Surfaced from shared tags and platforms — no rankings paid for.