KQLite

KQLite is a Lightweight, Kotlin Multiplatform DSL to write typesafe SQL queries. It allows you to write SQL queries using a Kotlin object-style DSL instead of raw SQL strings.

Highlights

Features Object-relational Mapping (ORM) style interaction with tables.
Returns typesafe cursor which executes lazily on read and auto-closed when fully iterated.
Typesafe values binding and reading.
Entity binding and mapping.
Cursor result as Flow which emits on every INSERT, UPDATE and DELETE on table.
Quick convenience API.
Builtin SQLite functions. Scalar, Aggregate, Math, Date & Time, JSON.
SQLite familiar DSL.

KQLite supports following KMP targets

Android
iOS
JVM
macOS
Linux
tvOS
watchOS

Adding KQLite dependency

sourceSets {
        commonMain.dependencies {
            implementation("com.kqlite:kqlite:0.2.1")
        }
    }

KQLite Sample Queries

// Selecting all contacts
val cursor: KQLiteCursor = TblContact.select().execute()
cursor.forEach {  it: KQLiteCursor 
    println(it[TblContact.firstName])
}

// Selecting specific columns
TblContact
    .select(TblContact.id, TblContact.firstName)
    .where { it.firstName LIKE "%John%" }
    .execute()
    .forEach {  it: KQLiteCursor 
        println("id=${it[TblContact.id]}, name=${it[TblContact.firstName]}")
    }

// Mapping results to other types
val cursor = TblContact
    .select(TblContact.id, TblContact.firstName)
    .where {  it: TblContact 
        it.firstName LIKE "%John%"
    }.execute()

cursor
    .asSequence()
    .map {  it: KQLiteCursor 
        Pair(it[TblContact.id], it[TblContact.firstName])
    }.forEach {  it: Pair<Int,String> 
        println(it)
    }

// Quick select convenience API
TblContact.quickSelect {  it: TblContact 
    it.firstName LIKE "%John%"
}.forEach {  it: KQLiteCursor 
    println(it[TblContact.firstName])
}

// Mapping results to Contact entity
val list: List<Contact> =
    TblContact.quickSelect {  it: TblContact 
        it.deleted NOT_EQ true
    }.mapToList(TblContact::mapper)

// Flow re-emit on table updates (INSERT/UPDATE/DELETE)
val list: Flow<List<Contact>> =
    TblContact.quickSelect {  it: TblContact 
        it.deleted NOT_EQ true
    }
    .asCallbackFlow()
    .mapToList(mapper = TblContact::mapper)

// Getting single row
val contact: Contact =
    TblContact.quickSelect {  it: TblContact 
        it.id EQ 1
    }.mapToSingle(TblContact::mapper)

// Insert returning row id
val id: Int =
    TblContact
        .insert(TblContact.firstName, TblContact.phone)
        .bind {  it: TblContact 
            it.firstName.bind("John")
            it.phone.bind(JSON_ARRAY("1234567890", "0987654321"))
        }.use {
            it.executeReturning(TblContact.id)
        }

// Insert Contact entity
TblContact
    .insert(onConflict = Action.REPLACE)
    .bind {  it: TblContact 
        it.binder(this, contact)
    }.execute()

// Quick insert item
TblContact.quickInsert {  it: TblContact 
    it.binder(this, contact)
}

// Update anything
TblContact
    .update {  it: TblContact 
        it.firstName.bind("Jane")
    }
    .where {  it: TblContact 
        it.id EQ 1
    }.execute()

// Delete anything
TblContact.quickDelete {  it: TblContact 
    it.id EQ 1
}

Tables ⮕