[SQLDelight] Data Mapping talk, need brainstorm. I...

# squarelibraries

[Vlad]

[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

[Vlad]

Perhaps anyone knows open source project with complicated database scheme and resolves all the concerns?

[jw]

You seem to want an ORM and SQL Delight is very much not an ORM

[Vlad]

Yea. I used to work with ORM that's why it seems so.
But can I make it work for me tho?

[jw]

I mean, it'll work, but you're fighting against how it wants you to write queries. It's basically a level below an ORM

[Vlad]

Yes. But I am fighting it because I used to work only with ORMs and have no clue how to be-friend with the SQL Delight path.
And I am actually fighting myself trying to learn how to comfortably work with it.
There is not much guides really, and like absolute most of them ends up with a single table schemas

[hfhbd]

We don't have public projects but projects with big and complex data structure. The idea of sqldelight is to create a tailored query for each use-case to only fetch the data you actually need.
With ORM frameworks, you often fetch more data because it creates an entity for each table and you do the filtering in the business logic (to optimize this, many frameworks use reflection or lazy fetching and instantiation).