Method in DataClass to access primary key columns

[stefan-s29]

I would like to be able to retrieve a unique identifier (i.e. a combination of all primary key columns) for a generic DataClass object.

I found out that I can get the set of primary key columns from tableInfo.primaryKey. However, to access the corresponding values in a generic DataClass dataset, the only way seems to be to convert it to JSON, which seems a bit inefficient to me.

Would it be feasible to add a method Set<Column> primaryKeyColumns() to DataClass?

A workaround would be to manually create extensions on all the generated DataClass classes of my app, but that would create more unnecessary overhead and boilerplate code.

I might create a PR myself if it's a reasonable addition and relatively straightforward to implement.

---

Excursus: The reason I need it is that I would like to increase performance for streams of large database tables by introducing a StreamTransformer that compares the latest data list to a cache, only passing a set of changes (CRUD) to the user of the repository. If I could easily get a unique identifier for a DataClass object, I could use a map from that identifier to the actual object as the cache, and I would only have to look up the elements of the latest data list within that map and compare the objects.

[simolus3]

Would it be feasible to add a method Set<Column> primaryKeyColumns() to DataClass?

I try to be cautious around generating more code, drift is already generating lots of code and once we add a feature or structure generated code in a certain way it's hard to reduce the size of generated code later.

A workaround would be to manually create extensions on all the generated DataClass classes of my app, but that would create more unnecessary overhead and boilerplate code.

That's a fair point. It's possible to write a generic method for this that works similar to the JSON-serialization approach except that it uses toColumns:

extension ExtractPk<T extends Table, D> on TableInfo<T, D> {
  List<Object?> extractPrimaryKey(Insertable<D> row) {
    final sqlValues = row.toColumns(false);
    return $primaryKey.map((col) {
      if (sqlValues[col.name] case Variable(:final value)) {
        return value;
      }
      return null;
    }).toList();
  }
}

FWIW this is likely not much more efficient than serializing to JSON and looking primary keys up in there. We could speed things up by making the companions implement the Map<String, Expression> returned by toColumns directly, that would avoid creating a full map when we only need to extract some columns (without changing the API). I'll take a look!

[stefan-s29]

Thanks for the quick reply!

I try to be cautious around generating more code, drift is already generating lots of code and once we add a feature or structure generated code in a certain way it's hard to reduce the size of generated code later.

That's fair. I guess I could make a fork of Drift, but that would be a lot of effort for a tiny little addition...

Could it be something you can turn on in the build via a flag? I guess it would be a bit confusing to have an "optional" part within the API...

Thanks for the code suggestion! That already helps me a lot.

[simolus3]

I think we should have a better way to extract individual columns from data classes. Apart from extracting the PK, there are other potentially interesting operators like extracting specific foreign keys and so on. I suppose it would be difficult to design a flag-based system that is flexible enough for that.

We provide access to the schema at runtime, so if extracting individual fields was more efficient than turning everything into a map, we could support all these operators directly without having to add specific support for them.

[catsclaw]

I'm having a similar problem, in that I have the list of Columns on the table and I'd like to fetch the data in the row for each of them, but there doesn't seem to be a straightforward way of doing it without hardcoding the fields.