Sqldelight: VIEW models no longer have encapsulated, null-safe data models from joined tables

[mhernand40]

Let's take the following *.sq files:

-- Animal.sq
CREATE TABLE animal(
    id INTEGER PRIMARY KEY AUTOINCREMENT
);

animal_view:
CREATE VIEW animal_view AS
SELECT A.*, H.*, C.*
FROM animal A
LEFT JOIN human H ON H.animal_id = A.id
LEFT JOIN cat C ON C.animal_id = A.id;

```SQL
-- Human.sq
CREATE TABLE human(
id INTEGER PRIMARY KEY AUTOINCREMENT,
animal_id INTEGER NOT NULL,
first_name TEXT NOT NULL,
last_name TEXT NOT NULL,
FOREIGN KEY(animal_id) REFERENCES animal(id)
);

```SQL
Cat.sq
CREATE TABLE cat(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    animal_id INTEGER NOT NULL,
    name TEXT,
    pattern TEXT,
    FOREIGN KEY(animal_id) REFERENCES animal(id)
);

For SqlDelight 0.9.0, or pre-1.0 for that matter, the animal_view model looks like so:

  interface Animal_viewModel<T1 extends AnimalModel, T2 extends HumanModel, T3 extends CatModel> {
    @NonNull
    T1 A();

    @Nullable
    T2 H();

    @Nullable
    T3 C();
  }

Where for T1, T2, and T3 we have:

public interface AnimalModel {
  ...
  long id();
  ...
}

public interface HumanModel {
  ...
  long id();

  long animal_id();

  @NonNull
  String first_name();

  @NonNull
  String last_name();
  ...
}

public interface CatModel {
  ...
  long id();

  long animal_id();

  @Nullable
  String name();

  @Nullable
  String pattern();
  ...
}

Notice how we have encapsulation of the joined table models and some null-safety thanks to the nullable annotations. Even though the generated code is in Java, Kotlin can infer the null type-safety thanks to the annotations.

When compiling these same *.sq files with SqlDelight 1.1.3, we get the following, more flat-structured model for animal_view:

interface Animal_view {
    val id: Long

    val id_: Long?

    val animal_id: Long?

    val first_name: String?

    val last_name: String?

    val id__: Long?

    val animal_id_: Long?

    val name: String?

    val pattern: String?
    ...

Two issues I see with the newer model structure:

1. Slight naming conflict with id and animal_id. It is not straightforward to distinguish between id, id_, and id__ or animal_id and animal_id_. This can become even more confusing/error prone with larger projections.
2. Everything aside from the first id is nullable. This potentially leads to code that requires !! or checkNotNull if the code is certain that for a given row entry, a subset of of the columns are non-null.

In many ways, I find the pre-version 1 view models to be safer to use. Any reason why we would not want to bring a similar structure back? In Kotlin of course. 🙂

[mhernand40]

From https://www.reddit.com/r/androiddev/comments/adxwr3/announcing_sqldelight_10_alec_strong_medium/

And when you do that, you also stop (ab)using star selects in your queries and selecting only the minimal amount of columns necessary rather than selecting entire tables so you can reuse entities.

Thank you @JakeWharton! 🙂

Using my example above, I decided to update the view to the following:

animal_view:
CREATE VIEW animal_view AS
SELECT A.id, H.first_name, H.last_name, C.name, C.pattern
FROM animal A
LEFT JOIN human H ON H.animal_id = A.id
LEFT JOIN cat C ON C.animal_id = A.id;

Interestingly enough, SqlDelight 0.9.0 no longer generated the T1, T2, and T3 models and instead opted for the flatter structure:

  interface Animal_viewModel {
    long id();

    @Nullable
    String first_name();

    @Nullable
    String last_name();

    @Nullable
    String name();

    @Nullable
    String pattern();
  }

Since it no longer has the same level of encapsulation that we had when using the the start (*) projections, the columns are now all @Nullable, and rightfully so. It is essentially now similar to what SqlDelight 1.1.3 generates:

interface Animal_view {
    val id: Long

    val first_name: String?

    val last_name: String?

    val name: String?

    val pattern: String?
    ...
}

[AlecStrong]

yup, thats exactly the intention - to use fine grained projections instead of star projections. Gonna close this out since I don't think there's anything here we'll be changing

[ansman]

Well even if you use fine grain projections it could still generate a model for a subset. As it is right now it's pretty inconvenient because of name clashes and when you have utility methods for the full model.

[eygraber]

@AlecStrong That seems to be punishing use cases where star projections are necessary. I have a schema where this is the case, and the table that is star projected in the view has 22 columns. That table shows up twice in my view, so I now have 44 fields in the model where I could just have 2.

[eygraber]

Any chance this will ever be re-evaluated? There are many instances throughout my code base that would benefit from using a star projection, even though there are other places where star projections are bad.

I've been sort of using mappers, but I interface directly with SqlDelight in all of our modules so it's difficult. I wanted to make a "store" abstraction so we can only expose what we need to, but there are others on my team who don't want that. My point being that real world scenarios can _sometimes_ outweigh idealistic practices.