Kuery - strongly typed SQL in Kotlin

The library is a strongly typed alternative to plain text SQL. The main goal of this project is to make database-related code easier to develop and maintain. The project uses some of the Kotlin language features to achieve type safety.

Features

SQL-like syntax. Use language constructions you already know. Designed to cover the most common SQL features.
Strongly typed DSL makes it harder to make mistakes. Some of the most common errors are catched at compile time.
IDE's assist in code editing.
Easier and safer refactoring/renaming.
No reflection

Foundation

Database structure is defined by classes/objects inherited from the Table class. Tables are not domain model classes. Their purpose is to simply define relationships between tables and columns.

import tel.egram.kuery.*

object Organizations : Table("organizations") {
	val id = Column("id")
	val name = Column("name")
}

object Employees : Table("employees") {
	val id = Column("id")
	val name = Column("name")
	val organizationId = Column("organization_id")
}

Statements are the building blocks of the library. A statement usually starts with one of the following function calls:

over(table) - used for CREATE TABLE and DROP TABLE statements
into(table) - used for INSERT statements
from(table) - used for SELECT, UPDATE and DELETE statements

Dialects are responsible for converting statements into actual SQL:

import tel.egram.kuery.*
import tel.egram.kuery.sqlite.*

val statement = from(Employees).where { e -> e.id eq 1 }.select { e -> e.name }
val sql = statement.toString(SQLiteDialect)
print(sql) // SELECT "name" FROM "employees" WHERE "id" = 1

Data Definition Language

Some parts of data definition language are specific to SQL dialects. An example for SQLite might look like this:

CREATE TABLE statement

import tel.egram.kuery.*
import tel.egram.kuery.sqlite.*

// CREATE TABLE "organizations" ...
over(Organizations)
    .create {
        integer(it.id).primaryKey(autoIncrement = true)..
        text(it.name).unique().notNull()
    }
    
// CREATE TABLE "employees" ...
over(Employees)
    .create {
        integer(it.id).primaryKey(autoIncrement = true)..
        text(it.name).unique().notNull()..
        integer(it.organizationId).foreignKey(references = Organizations.id)
    }

DROP TABLE statement

// DROP TABLE "employees"
over(Employees).drop()

Data Manipulation Language

Data manipulation is the most powerfull and complex part of SQL. The library supports INSERT, SELECT, UPDATE and DELETE statements.

INSERT statement

// INSERT INTO "employees"("name", "organization_id") VALUES("John Doe", 1)
into(Employees)
    .insert { e -> e.name("John Doe") .. e.organizationId(1) }

SELECT statement

The library provides the following operators to compose queries:

and
or
not
eq (equals)
ne (not equals)
lt (less than)
lte (less than or equal to)
gt (greater than)
gte (greater than or equal to)

// SELECT "id", "name" FROM "organizations" WHERE ...
from(Employees)
    .where { e -> (e.organizationId ne null) and (e.name eq "John Doe") }
    .groupBy { e -> e.name }
    .having { e -> e.id ne null }
    .orderBy { e -> e.name.asc .. e.id.desc }
    .limit { 10 }
    .offset { 10 }
    .select { e -> e.id .. e.name }

JOINs are also supported in select statements

// SELECT ... FROM "organizations" JOIN "employees" ON ...
from(Organizations)
    .join(Employees).on { o, e -> o.id eq e.organizationId }
    .select { o, e -> o.name .. e.name }

UPDATE statement

// UPDATE "organizations" SET "name" = 'John Doe' WHERE "id" = 1
from(Organizations)
    .where { o -> o.id eq 1 }
    .update { o -> o.name("John Doe") }

DELETE statement

// DELETE FROM "organizations" WHERE "id" = 0
from(Organizations)
    .where { o -> o.id eq 0 }
    .delete()

Download

Maven:

<!-- Core library -->
<dependency>
  <groupId>tel.egram.kuery</groupId>
  <artifactId>core</artifactId>
  <version>0.5.3</version>
  <type>pom</type>
</dependency>

<!-- SQLite dialect -->
<dependency>
  <groupId>tel.egram.kuery</groupId>
  <artifactId>sqlite</artifactId>
  <version>0.5.3</version>
  <type>pom</type>
</dependency>

Gradle:

// Core library
compile 'tel.egram.kuery:core:0.5.3'
// SQLite dialect
compile 'tel.egram.kuery:sqlite:0.5.3'

x2bool/kuery