Mysterious SQLite bugs and how to solve them.

TLDR: Android 30 upgrades SQLite from 3.22.0 -> 3.28.0, this introduces new alter table behavior which will potentially cause runtime exceptions when ALTER TABLE statements are ran on tables which are used in a view. To preserve old behavior turn on PRAGMA legacy_alter_table=ON before running your migrations. Late last week we experienced a weird bug on Cash App happening to a few of our developers - reproducibly but not for everyone.

SQLite Helper Queries

In programming one of the earliest tools we’re given for simplifying complex code is helper functions. Splitting code into smaller more digestible blocks makes it easier to maintain and read, which is usually more important than code that’s easier to write. In SQL we have tools for accomplishing the same thing! One of the bigger ones is views which can help share an underlying query between multiple other ones. Leandro Favarin wrote a great post on how to put views into practice in SQLite here.

Supporting Multiple Dialects in SQLDelight

With SQLDelight 1.3.0 and above we now support multiple dialects of SQL (at time of writing: SQLite 3.18, SQLite 3.24, and MySQL). They’re not complete implementations of any of those dialects but they support necessary features and the real meat of the release is a straightforward framework for adding parts of the dialects incrementally. For example, SQLite 3.24 is just SQLite 3.18 with upsert on top (thank you Angus Holder!).

Integrating Github Actions for Kotlin Multiplatform

With Kotlin Multiplatform (KMP) it’s possible to build artifacts for multiple platforms using the same toolchain, but until portable artifacts for KMP are released you need to build platform artifacts on their respective platforms. With SqlDelight 1.2.2 we now also deploy Windows (mingW) artifacts, meaning it’s impossible to publish from a single OS (since we also support macOS targets). There was no simple setup with Travis and Github Actions is the new hot stuff so we gave that a go and here’s how it works: