SQLinK
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.
Features
- Type-Safe Builders: Define your tables and columns with Kotlin types, reducing errors.
- Expressive DSL: Write SQL queries in a way that feels natural in Kotlin.
- Readability: Generated SQL can be easily logged or inspected.
- Zero Platform-Specific Code: The core library is pure Kotlin and does not contain any
actual/expect implementations tied to specific platforms.
- No Third-Party Runtime Dependencies: SQLinK is self-contained and adds no external library dependencies to your project, keeping it lean and minimizing potential conflicts.
Getting Started
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")
}
}
}
Usage
Below are examples demonstrating how to use SQLinK, based on the Sample.kt provided in the Sample module.
1. Defining a Table
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()
}
2. Building SQL Queries
Use the sql { ... } builder to construct your queries.
a. SELECT Query
val selectQuery = sql {
select(Person.all)
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)
b. UPDATE Statement
val updateQuery = sql {
update(Person) {
it.age set (it.age + 1)
it.remark set 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)
d. UNION of SELECT Statements
val unionQuery = sql {
sql {
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")
from(Person)
}
println(countQuery)
f. Common Table Expressions (CTE / WITH clause)
val cteQuery = sql {
val adultPersons = QueryResultSet("adult_persons")
with(adultPersons) {
select(Person.all)
from(Person)
where { Person.age more 18 }
}
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"
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) {
WHEN(Gender.MALE, string("M"))
WHEN(Gender.FEMALE, string("F"))
ELSE(string("O"))
} AS "gender_code"
)
from(Person)
}
println(caseQuery)
These examples cover some of the core functionalities of SQLinK. Explore the library for more advanced features and combinations.
Contributing
Contributions are welcome! Please feel free to submit issues or pull requests.
License
This project is licensed under the Apache 2.0 License.