How to upsert a row? Getting type error.

[rubenferreira97]

I am trying to insert a row, however if it already exist I would like to add to the old value.

Future<void> addStatistic(Statistic statistic) {
    return into(statisticsTable).insert(
      StatisticsTableCompanion.insert(
          local: statistic.local,
          date: new DateTime(statistic.date.year, statistic.date.month,
              statistic.date.day, statistic.date.hour),
          cliques: statistic.cliques),
      onConflict: DoUpdate((old) => StatisticsTableCompanion.custom(
          cliques: old.cliques + Constant(statistic.cliques))), //Getting error here
    );
}

The argument type 'Expression<int?>' can't be assigned to the parameter type 'Expression<int>?'

Is this a bug or am I doing something wrong?

[simolus3]

Hm, is cliques a nullable column? I think you can wrap the offending expression in a .dartCast<int>() to fix the type error, but I'm a bit surprised that this would be necessary in the first place here.

[rubenferreira97]

No, the column is declared as: IntColumn get cliques => integer()();
Yes, as a workaround I am using this (but still not tested on run-time):

Future<void> addStatistic(Statistic statistic) {
    return into(statisticsTable).insert(
      StatisticsTableCompanion.insert(
          local: statistic.local,
          date: new DateTime(statistic.date.year, statistic.date.month,
              statistic.date.day, statistic.date.hour),
          cliques: statistic.cliques),
      onConflict: DoUpdate((old) => StatisticsTableCompanion.custom(
          cliques: (old.cliques + Constant(statistic.cliques)).dartCast())),
    );
  }

Do I need to dartCast<int>() explicitly? However this is more like a trick right?

[simolus3]

Now I remember what's going on here. I had to make table columns nullable in the query builder because they could be accessed from outer joins (where they would be nullable). So even though cliques is non-nullable in the data class, it's a Column<int?> in the end. This isn't great at all, but I couldn't find a better way to express SQL nullability in Dart's typesystem (yet).

So I think something like this should work (and it doesn't alter the generated SQL, dartCast is effectively a noop that only affects Dart types):

Future<void> addStatistic(Statistic statistic) {
    return into(statisticsTable).insert(
      StatisticsTableCompanion.insert(
          local: statistic.local,
          date: new DateTime(statistic.date.year, statistic.date.month,
              statistic.date.day, statistic.date.hour),
          cliques: statistic.cliques),
      onConflict: DoUpdate((old) => StatisticsTableCompanion.custom(
          cliques: old.cliques.dartCast<int>() + Constant(statistic.cliques))),
    );
  }