Advanced support for custom column types

[andersio]

Problem statement

Right now, there is a huge caveat of using custom column types in SQLDelight — aside from types directly representable in primitives (e.g., kotlin.Boolean as integer), content of these types cannot be reliably indexed and/or used in queries as filtering, grouping or sorting conditions.

So when any of these capabilities is needed, it creates a dilemma of which either:

1. we have to flatten levels of schema/declaration into one flat table; or

2. we break out the levels of nested objects into child tables, with foreign key constraints to ensure the relational integrity, and querying with multiple levels of JOINs at worst case; or

3. we store them as opaque blob/text as is, and do filtering/sorting in memory when necessary.

The first two options can be equally overwhelming in schema maintenance and query usage (levels of JOIN), especially if various subgraph of the object is optional, or is a tagged union type. The last option can work, but only for small data sets.

Idea

Many popular databases support JSON reading and manipulation in SQL, including those that SQLDelight targets. For example, SQLite JSON1 extension can read and patch JSON documents stored in columns, and subfields can be indexed since SQLite supports indexing on expressions. PostgreSQL provides similar level of support.

JSON1 is available on iOS since iOS 10. It is also available on Android via xerial/sqlite-jdbc and requery/sqlite-android.

So it feels like there is a huge opportunity to expand support for custom custom types, with the ability to use its content directly in SQL. The pitch here is a deeper integration with kotlinx-serialization, so as to take advantage of its annotations and JSON capabilities.

For example, given a serializable object:

@Serializable
data class Location(val name: String, val longitude: Double, val latitude: Double)

and the corresponding parent entity:

import domain.Location;

CREATE TABLE CalendarEvent(
    date TEXT AS Instant NOT NULL,
    location TEXT AS Location
);

Knowing that Location is serializable through annotation pre-processing, the SQLDelight compiler can allow direct access to object members in its SQ dialect, e.g., in the familiar dot syntax (or any other viable alternatives in the SQL grammar):

CREATE INDEX CalendarEvent_LocationName ON (date, location.name);

allLocations:
SELECT DISTINCT(location.name) FROM CalendarEvent ORDER BY date DESC, location.name DESC;

which can be desugared for SQLite to:

CREATE INDEX CalendarEvent_LocationName ON (date, json_extract(location, "$.name"));

SELECT DISTINCT(json_extract(location, "$.name"))
FROM CalendarEvent
ORDER BY date DESC, json_extract(location, "$.name") DESC;

Result type of the expression can be inferred from annotation processing metadata of @Serializable classes, which can also be used to power autocompletion. Moreover, knowing that it is @Serializable, the generated code can use the KSerializer<*> for column-object mapping, removing the need of an explicitly injected column adapter.

In short, this ends the dilemma by making custom column types more accessible and powerful, even if we don't support manipulation/patching of custom column content at all. Contrasting with the three listed lesser evil options:

we have to flatten levels of schema/declaration into one flat table; or

• It eliminates the need to flatten object members into a flat table;

we break out the levels of nested objects into child tables

• It provides a viable, no-friction option for simple nested objects; and

we store them as opaque blob/text as is, and do filtering/sorting in memory when necessary.

• It enables ideal memory and query performance, by delegating filtering and sorting (when needed) back to the SQLite C engine, and also adds support for indexing specific subfield of these columns.

[AlecKazakova]

theres a few things here:

• we should definitely support json1 extensions and it is top of mind for me for upcoming releases

Knowing that Location is serializable through annotation pre-processing / Result type of the expression can be inferred from annotation processing metadata

this is not possible, the sqldelight compiler has no knowledge of Kotlin/the JVM (and theres no world where i try to add that in).

the IDE plugin does have knowledge of kotlin, so some of the ideas you have could be reworked as features for the intellij plugin, and I think that could be very useful. For example, if you have a json1 column location TEXT AS Location and start typing location.name when you hit enter it could then complete it as json_extract(location, "$.name")

[andersio]

this is not possible, the sqldelight compiler has no knowledge of Kotlin/the JVM (and theres no world where i try to add that in).

That's an unfortunate news. Guess I will have to look into a linter-based approach for checking JSON paths against the defined @Serializable models.