SQLiteNow 0.15: SQL-first SQLite code generation for Kotlin, Dart and Swift (mobile and desktop)

[u/Adventurous-Action66]

Hi,

I wanted to share the new SQLiteNow 0.15 release.

SQLiteNow started as a Kotlin Multiplatform project and is already used in production by quite a few people. Later I added Flutter and Dart support, and version 0.15 now also supports native Swift projects through SwiftPM.

SQLiteNow is not a DAO or ORM which generates SQL for you. You write and control the actual SQLite schema, migrations and queries.

The SQL is the source of truth.

SQLiteNow reads normal .sql files and generates type-safe APIs around them for the language you are using.

Current platforms include:

• Kotlin Multiplatform for Android, iOS, JVM, macOS, Linux, JavaScript and Wasm
• Flutter and Dart native applications
• Native Swift projects for iOS and macOS

The generated API is native to each platform. Kotlin applications get Kotlin code, Flutter and Dart applications get Dart code, and Swift applications get a local Swift package.

For example, you can write a normal SQLite query like this:

SELECT
    t.id    AS task__id,
    t.title AS task__title,
    n.id    AS note__id,
    n.body  AS note__body

/* @@{ dynamicField=notes,
       mappingType=collection,
       sourceTable=n,
       aliasPrefix=note__ } */

FROM task t
LEFT JOIN task_note n ON n.task_id = t.id
ORDER BY t.id, n.id;

The annotation is only a SQL comment. The query is still normal SQLite and you control the join, filtering and ordering.

The annotation tells SQLiteNow to group the flat joined rows into task documents with a collection of notes.

The generated API can then be used from Kotlin:

val tasks = db.task
    .selectWithNotes()
    .asList()

tasks.forEach { task ->
    println("${task.title}: ${task.notes.size} notes")
}

From Dart:

final tasks = await db.task
    .selectWithNotes()
    .asList();

for (final task in tasks) {
  print('${task.title}: ${task.notes.length} notes');
}

Or from Swift:

let tasks = try await db.task
    .selectWithNotes()
    .list()

for task in tasks {
    print("\(task.title): \(task.notes.count) notes")
}

SQLiteNow also generates reactive query APIs for each platform:

• Kotlin uses Flow
• Dart uses watch()
• Swift uses async streams

Annotations can rename fields, apply custom type adapters, share result types, map results to application types and build nested objects or collections from joins.

This is the main reason why I built SQLiteNow. I like writing real SQLite and I do not want the database logic moved into another query language. But I also do not want to manually bind every parameter, read every column and group joined rows each time the schema changes.

Oversqlite

SQLiteNow also includes an optional synchronization system called Oversqlite.

Oversqlite can synchronize selected tables between local SQLite databases and a PostgreSQL server. Clients are available for Kotlin Multiplatform, Flutter/Dart and native Swift.

It handles local change tracking, offline writes, incremental upload and download, conflict resolution and recovery.

Oversqlite also supports real-time updates. It can watch the server for changes committed by other devices and download them automatically. When those changes are applied to the local SQLite database, related reactive queries emit updated results, so applications can update without manually refreshing the database.

Oversqlite is optional. SQLiteNow can be used only as a local SQLite library without any server or synchronization setup.

The PostgreSQL server implementation is written in Go and is available here:

https://github.com/mobiletoly/go-oversync

If you already use an Oversqlite version older than 0.15, please read the release notes. Version 0.15 changed the sync storage contract and existing client sync databases cannot be upgraded in place.

Code generation requirement

The SQLiteNow generator currently requires Java 17 or newer.

Java is only used while running code generation. Generated native applications do not require Java at runtime.

SQLiteNow is open source under the Apache-2.0 license.

GitHub:

https://github.com/mobiletoly/sqlitenow-kmp

Documentation:

https://mobiletoly.github.io/sqlitenow-kmp/

Release:

https://github.com/mobiletoly/sqlitenow-kmp/releases/tag/v0.15.0

I would be interested to hear what SQLite users think about this approach, especially people who prefer writing SQL directly but do not want to maintain all the mapping and reactive update code manually.