Supabase upsert issue with associations

[nyejon]

Hi,

I've been trying to get upserting to work with supabase but keep running into this error:

#upsert supabase failure: PostgrestException(message: {"code":"PGRST200","details":"Searched for a foreign key relationship between 'table_name' and 'null' using the hint 'column_id' in the schema 'public', but no matches were found.","hint":null,"message":"Could not find a relationship between 'table_name' and 'null' in the schema cache"}, code: 400, details: Bad Request, hint: null)

I've renamed my table to 'table_name' and the foreign key column as 'column_id'

The table_name.column_id is what I put into the @Supabase(foreignKey: "column_id") and in the table I'm referencing the column name is "id"

Not quite sure where I'm going wrong here, maybe someone has encountered this issue?

One thing I have noticed is that the request to supabase is including the name of the reference instead of the _id foreign key field. I don't have any column name in my table with the referenced object besides for the _id

Thanks!

[tshedor]

@nyejon Do you have multiple joins on the model you're using? If you don't, try removing the @Supabase annotation and rerunning the build_runner.

If this doesn't work, could you please provide a rough schema of the model you're using the annotation on and the schema of the model you're associating?

[nyejon]

Hey @tshedor, I do have multiple joins.

Here is my schema:

@JsonSerializable()
@ConnectOfflineFirstWithSupabase(
  sqliteConfig: SqliteSerializable(),
  supabaseConfig: SupabaseSerializable(tableName: 'sightings'),
)
class Sighting extends OfflineFirstWithSupabaseModel implements Equatable {
  Sighting({
    String? id,
    DateTime? createdAt,
    DateTime? seenAt,
    this.animal,
    // this.location,
    this.animalActivity,
    this.numberSeen = 1,
    this.description,
    this.visibility = 3,
    this.user,
  })  : id = id ?? const Uuid().v4(),
        createdAt = createdAt ?? DateTime.now(),
        seenAt = seenAt ?? DateTime.now();

  factory Sighting.fromJson(Map<String, dynamic> json) =>
      _$SightingFromJson(json);

  @Sqlite(index: true, unique: true)
  @Supabase(unique: true)
  final String id;
  final DateTime createdAt;
  final DateTime seenAt;
  // @Supabase(
  //     ignoreTo: true, foreignKey: 'animal_id', nullable: false) // Also tried this
  @Supabase(
      ignoreTo: true, foreignKey: 'sightings_animal_id_fkey', nullable: false)
  final Animal? animal;
  // @Supabase(
  //     toGenerator:
  //         "'POINT(%INSTANCE_PROPERTY%?.longitude ?? 0 %INSTANCE_PROPERTY%?.latitude ?? 0)'",
  //     fromGenerator:
  //         "LatLng(%DATA_PROPERTY%['coordinates'][1] as double, %DATA_PROPERTY%['coordinates'][0] as double)")
  // final LatLng? location;
  @Supabase(enumAsString: true)
  final AnimalActivity? animalActivity; // This is an Enum
  final int numberSeen;
  final String? description;
  final int visibility;
  // @Supabase(
  //     ignoreTo: true, foreignKey: 'user_id', nullable: false) // Also tried this
  @Supabase(
      ignoreTo: true, foreignKey: 'sightings_user_id_fkey1', nullable: false)
  @Sqlite(ignoreFrom: true)
  final Profile? user;

  // // If the association will be created by the app, specify
  // // a field that maps directly to the foreign key column
  // // so that Brick can notify Supabase of the association.
  @Sqlite(ignore: true)
  // @Supabase(ignore: true)
  String? get animalId => animal?.id;

  @Sqlite(ignore: true)
  // @Supabase(ignore: true)
  String? get userId => user?.userId;
  // @Supabase(
  //   // name: 'animal_id',
  //   ignoreTo: true,
  // )
  // String? animalId;

  // @Supabase(
  //   // name: 'user_id',
  //   ignoreTo: true,
  // )
  // String? userId;

  // ignore: comment_references
  /// Connect the generated [_$AnimalToJson] function to the `toJson` method.
  Map<String, dynamic> toJson() => _$SightingToJson(this);

  Sighting copyWith({
    String? id,
    DateTime? createdAt,
    DateTime? seenAt,
    Animal? animal,
    // LatLng? location,
    AnimalActivity? animalActivity,
    int? numberSeen,
    String? description,
    int? visibility,
    Profile? user,
  }) =>
      Sighting(
        id: id ?? this.id,
        createdAt: createdAt ?? this.createdAt,
        seenAt: seenAt ?? this.seenAt,
        animal: animal ?? this.animal,
        // location: location ?? this.location,
        animalActivity: animalActivity ?? this.animalActivity,
        numberSeen: numberSeen ?? this.numberSeen,
        description: description ?? this.description,
        visibility: visibility ?? this.visibility,
        user: user ?? this.user,
      );

  @override
  List<Object?> get props => [
        id,
        createdAt,
        seenAt,
        animal.hashCode,
        // location.hashCode,
        animalActivity,
        numberSeen,
        description,
        visibility,
        user.hashCode,
      ];

  @Sqlite(ignore: true)
  @Supabase(ignore: true)
  @override
  // TODO: implement stringify
  bool? get stringify => throw UnimplementedError();
}

I have a profiles and animals table which the foreign keys point to. They are all UUIDs and the users table has a primary key of user_id instead of 'id' if that makes any difference. Those tables currently don't have any foreign keys of their own.

I've tried a few combinations of annotations, commenting out the get userId and animalId, but nothing seems to have worked. I must be missing something from the docs...

[tshedor]

@nyejon Thanks for that. However, I'm not seeing the column_id or table_name that you've referenced in the PostgREST error or your original comment. There are a few things in your code that I'll call out (my comments start with !!!) and maybe that can resolve the issue.

Based on what I can infer from the error and your code, this looks like maybe you haven't made the association in Supabase? Also, you don't have multiple joins to the same table in this model, so you don't need to specify the foreign key. Specifying the foreign key will only help the get requests, and it's not clear if that's exactly where the error is coming from (I know you said upsert but it looks like a get error?).

/// !!! You generally won't need `JsonSeriazable`. 
/// Instead, you can use `final json = await repository.supabaseModelDictionary.adapterFor[Sighting].toSupabase(sighting, provider: repository.supabaseProvider, repository: repository)`.
// @JsonSerializable()
@ConnectOfflineFirstWithSupabase(
  sqliteConfig: SqliteSerializable(),
  supabaseConfig: SupabaseSerializable(tableName: 'sightings'),
)
class Sighting extends OfflineFirstWithSupabaseModel implements Equatable {
  Sighting({
    String? id,
    DateTime? createdAt,
    DateTime? seenAt,
    this.animal,
    // this.location,
    this.animalActivity,
    this.numberSeen = 1,
    this.description,
    this.visibility = 3,
    this.user,
  })  : id = id ?? const Uuid().v4(),
        createdAt = createdAt ?? DateTime.now(),
        seenAt = seenAt ?? DateTime.now();

  factory Sighting.fromJson(Map<String, dynamic> json) =>
      _$SightingFromJson(json);

  @Sqlite(index: true, unique: true)
  @Supabase(unique: true)
  final String id;
  final DateTime createdAt;
  final DateTime seenAt;
  // @Supabase(
  //     ignoreTo: true, foreignKey: 'animal_id', nullable: false) // Also tried this
  @Supabase(
      /// !!! `ignoreTo` will prevent an upsert of associations
      // ignoreTo: true, 

      /// !!! Does your `sightings` table have a column named `sightings_animal_id_fkey`?
      /// If it doesn't, the fetch will not work.
      /// That said, you do not need to specify the column ID here if you've made
      /// the association in Supabase. You only need to specify the column name
      /// if you're making multiple joins to the same table. 
      foreignKey: 'sightings_animal_id_fkey', 
      /// !!! The `Animal?` is `nullable` so `nullable` should be true.
      nullable: true
  )
  final Animal? animal;
  /// !!! Consider using an `OfflineFirstSerdes` class for `LatLng`. 
  /// That will store as JSON in your table, which it looks like what you're trying to do

  // @Supabase(
  //     toGenerator:
  //         "'POINT(%INSTANCE_PROPERTY%?.longitude ?? 0 %INSTANCE_PROPERTY%?.latitude ?? 0)'",
  //     fromGenerator:
  //         "LatLng(%DATA_PROPERTY%['coordinates'][1] as double, %DATA_PROPERTY%['coordinates'][0] as double)")
  // final LatLng? location;
  @Supabase(enumAsString: true)
  final AnimalActivity? animalActivity; // This is an Enum
  final int numberSeen;
  final String? description;
  final int visibility;
  // @Supabase(
  //     ignoreTo: true, foreignKey: 'user_id', nullable: false) // Also tried this
  @Supabase(
      /// !!! `ignoreTo` will prevent an upsert of associations
      // ignoreTo: true, 
      ignoreTo: true, 
      /// !!! Does your `sightings` table have a column named `sightings_user_id_fkey1`?
      /// If it doesn't, the fetch will not work.
      /// That said, you do not need to specify the column ID here if you've made
      /// the association in Supabase. You only need to specify the column name
      /// if you're making multiple joins to the same table. 
      // foreignKey: 'sightings_user_id_fkey1', 
      /// !!! The `Profile?` is `nullable` so `nullable` should be true.
      nullable: false,
  )
  @Sqlite(ignoreFrom: true)
  final Profile? user;

  // If the association will be created by the app, specify
  // a field that maps directly to the foreign key column
  // so that Brick can notify Supabase of the association.
  @Sqlite(ignore: true)
  // @Supabase(ignore: true)
  String? get animalId => animal?.id;

  @Sqlite(ignore: true)
  // @Supabase(ignore: true)
  String? get userId => user?.userId;
  // @Supabase(
  //   // name: 'animal_id',
  //   ignoreTo: true,
  // )
  // String? animalId;

  // @Supabase(
  //   // name: 'user_id',
  //   ignoreTo: true,
  // )
  // String? userId;

  /// !!! Again, you can use the adapter `toSupabase` method to generate JSON if you really need it.
  // ignore: comment_references
  /// Connect the generated [_$AnimalToJson] function to the `toJson` method.
  // Map<String, dynamic> toJson() => _$SightingToJson(this);

  Sighting copyWith({
    String? id,
    DateTime? createdAt,
    DateTime? seenAt,
    Animal? animal,
    // LatLng? location,
    AnimalActivity? animalActivity,
    int? numberSeen,
    String? description,
    int? visibility,
    Profile? user,
  }) =>
      Sighting(
        id: id ?? this.id,
        createdAt: createdAt ?? this.createdAt,
        seenAt: seenAt ?? this.seenAt,
        animal: animal ?? this.animal,
        // location: location ?? this.location,
        animalActivity: animalActivity ?? this.animalActivity,
        numberSeen: numberSeen ?? this.numberSeen,
        description: description ?? this.description,
        visibility: visibility ?? this.visibility,
        user: user ?? this.user,
      );

  @override
  List<Object?> get props => [
        id,
        createdAt,
        seenAt,
        animal.hashCode,
        // location.hashCode,
        animalActivity,
        numberSeen,
        description,
        visibility,
        user.hashCode,
      ];

  @Sqlite(ignore: true)
  @Supabase(ignore: true)
  @override
  // TODO: implement stringify
  bool? get stringify => throw UnimplementedError();
}

[nyejon]

Hey @tshedor, thanks so much for the tips!

I can confirm that it is indeed @JsonSerializable that causes the error.

[OfflineFirstRepository] #upsert supabase failure: PostgrestException(message: {"code":"PGRST200","details":"Searched for a foreign key relationship between 'sightings' and 'null' in the schema 'public', but no matches were found.","hint":null,"message":"Could not find a relationship between 'sightings' and 'null' in the schema cache"}, code: 400, details: Bad Request, hint: null)

Once I remove it, everything works as expected! Should have definitely tried that...