More Robust SQL Queries for Kotlin Apps: Room and SQLDelight Integrations

The PowerSync Kotlin SDK lets you sync your backend database with a client-side SQLite database. On the client, you interact with the database using SQL. However, writing raw SQL queries and parsing results manually can be error-prone. Libraries such as SQLDelight and Room make this process safer by validating your schema and queries at compile-time, and by generating code to map raw SQLite rows into strongly-typed structures. Previously, this mapping had to be done manually by fetching rows with cursors.

As of version 1.6.0, the PowerSync Kotlin SDK supports both SQLDelight and Room.

Get started

Visit the docs to get started.

We don’t have many examples published yet, so if you get stuck, hop on Discord and we’ll help out.

Feature states:

• SQLDelight support is in beta. The integration is fairly straightforward, and we’re confident it’s already useful.

• Room support is in an alpha release. The configuration is more complex, so we’re especially keen on your feedback here.

Under the hood

This work was enabled by PR #230, which switched our internals to use the androidx.sqlite bindings.

• Room uses the same APIs, so the integration became much simpler once we were aligned - we can now bridge operations directly without managing a separate database connection in the SDK.

• SQLDelight could have worked with the old API, but the new one gives us the low-level control needed for a proper driver implementation, making the integration much cleaner.

Future work

By adopting androidx.sqlite:sqlite-bundled in the previously-mentioned PR, we can now also support custom SQLite builds such as SQLCipher. Encryption support via SQLCipher for Kotlin is on our roadmap we're much closer to adding it now, though more work is still needed here.

Shoutout

A shoutout to Blake’s write-up of Room X PowerSync. His work validated the use of raw tables for this, and that the ergonomics of raw tables are improved when you have a library like Room taking care of schema management.