[SQLDelight] Data Mapping talk, need brainstorm.
I learned that SQLDelight doesn't generate entity's data classes for Join queries and just generate a result class which will contain all the fields from the query.
Lets define Customer and Purchases tables:
CREATE TABLE Customer (
id INTEGER NOT NULL PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE Purchase (
id INTEGER NOT NULL PRIMARY KEY,
name TEXT NOT NULL,
customerId INTEGER NOT NULL,
FOREIGN KEY (customerId) REFERENCES Customer(id) ON DELETE CASCADE
);
And the query with LEFT JOIN assuming that it is possible for a Customer to have zero purchases:
getConsumerWithPurchases:
SELECT Customer.*, Purchase.*
FROM Customer
LEFT JOIN Purchase ON Customer.id = Purchase.customerId;
That will generate for us GetConsumerWithPurchases data class for the result set:
public data class GetConsumerWithPurchases(
public val id: Long,
public val name: String,
public val id_: Long?,
public val name_: String?,
public val customerId: Long?,
)
1.
First concern: we can see that we have
name*_*
prefixed to avoid collisions if the entities have the same names, which they often do with common names. That is kinda error prone (we will need to map it later), but I assume we can avoid that writing named SQLs and have
Purchase.name as purchaseName
2.
Second concern it that as we can see that all the LEFT JOINed entity's fields are nullable. That is because it is possible that the row does not exist at all. How do we go with that on the Mapping phase? We check if
id_ != null
and then use
name_!!
,
customerId!!
peeking into the table definitions so we know it is not nullable in fact? Next day we will define that
Purchase.name
can be actually nullable and all our mappers (we will have many) will be crashing in runtime because of the
!!
?
So we want to map these results row into our shiny application structure:
data class AppCustomer(
val id: Long,
val name: String,
val purchases: List<Purchase>
)
data class AppPurchase(
val id: Long,
val name: String
)
3. Repetitive mapping concern: So we will have to create our AppPurchase instance using
GetConsumerWithPurchases.id_
and
GetConsumerWithPurchases.name_
fields via:
val purchase = Purchase(
id = id_!!,
name = name_!!
)
We very likely to have the Purchase fields returned as part of many other queries in the app. So we will have to do that mapping multiple times. If we just copy-paste that mapping (will be initializing data classes on the go) - we will not scale and end up into the issue mentioned in the concern 2: what if we change
Purchase.name
to nullable in the scheme.
To avoid that I assume we want to create a Purchase Mapper which will take raw fields and return created Purchase object:
object PurchaseMapper {
// All fields nullable
fun fromEntity(id: Long?, name: String?): AppPurchase? {
id ?: return null
return AppPurchase(
id = id,
name = name!!
)
}
}
And we delegate the creating of our shiny data classes to such mappers. If schema changes we will have update the mapper only in one place. And if want to add additional field to schema - the compiler error will easily show us where forget to add the new params to the mapper call. Seems reasonable.
4. Relation to many.
So we can have multiple Purchases for the Customer and we already querying them with LEFT JOIN. Before that we assumed that there are only only one or zero.
Imagine we have 1 customer with 10 Purchases.
The query will return to us the
List<GetConsumerWithPurchases>
with size=10. There will be all the same Customer fields and only Purchase fields will differ. And we want to pack/group that in the mapper to our single
AppCustomer
instance with the List of the purchases. Any thoughts how we do so nicely and scalable?
I couldn't find any expanded talks about the 4 concern really. I see most talks cut on the point "yeaaah, we will have some issues in the mappers" and that's it.
All of the KMM example apps show only simple databases even without relations.
I am working in a product company and we decided to try KMP for a new app and see how it goes. I stopped with the SQLDelight mappings because I felt like that building a data stream from API to Database and delivering to the UI from scratch would take, for example, an hour for me. But working with the mappings only will multiple that hour by I dunno, up to 3x-5x depends on how many data with relations we need to fetch for a particular screen.
I hope this all make any sense and not a waste. Cheers
