Question: How to correctly upsert an association to a REST Provider?

[devj3ns]

Context: I am using the Supabase REST API as a remote provider.

In my data schema, every customer has an address. In the remote database, I store the ID of that address as a foreign key to the addresses table in the customers table. This means that every address has an ID and is stored in the addresses table, and every row in the customers table has a column which stores the ID of the customers' address.

The customer field in the customer Dart model looks like this:

@Sqlite(index: true)
final Address address;

When upserting a new customer locally, the customer data is correctly stored in the customers SQLite table and the address in the addresses table. This is handled by the automatically generated code inside the Customer Adapter's toSqlite method:

'address_Address_brick_id': instance.address.primaryKey ?? await provider.upsert<Address>(instance.address, repository: repository),

However, in the toRest method, the address is just converted to JSON using the AddressAdapter:

'address': await AddressAdapter().toRest(instance.address, provider: provider, repository: repository),

This leads to an issue where the PostgREST API returns an error stating that the input syntax of the address field is not a UUID, and the data is not inserted.

When I change my Customer model's address field to have the following annotation:

@Rest(toGenerator: '%INSTANCE_PROPERTY%.id')

The Customer adapter changes to convert the address field to the addresses ID instead of JSON, which is correct in terms of the remote DB schema. However, the REST API rejects the upsert request because there is no matching address for this ID in the addresses table where the foreign key points to.

---

I need a way to insert the address into the addresses table via the REST API first, before inserting the customer, just like it is automatically done for the SQLite database.

@tshedor, do you have any examples or advice for handling such situations with Brick? Thanks for taking the time to read this question 🙏

[tshedor]

@devj3ns Ok, it sounds like the problem is upsert only works when you're updating a model but not when you're inserting a model. Am I reading that correctly?

The first thing that stands out is this chunk:

@Sqlite(index: true)
final Address address;

indexing in SQLite is designed to be columnar instead of a complex series of fields (i.e. an int or a String). Could you use a computed field instead and annotate the computed field to be renamed in Rest?

@Rest(name: 'address', ignoreFrom: true)
String get addressId => address.id;

@Rest(ignoreTo: true)
final Address address;

[devj3ns]

Fair point with the index.

My problem is that I am not sure how to handle the insertion of an object with a 1:1 relationship to the remote DB correctly. When upserting the customer via Brick, its address is automatically upserted to the local SQLite table inside the ToSqlite method, but not sent to the REST-API.

I see two possible solutions when creating/updating a customer in my app:

1. call provider.upsert<Address> inside the ToRest method to automatically send the address to the REST-API whenever upserting the customer
2. upsert the address using Repository<Address>.upsert before upserting the customer using Repository<Customer>.upsert

[tshedor]

I'm working with limited information, so a few clarifying questions:

Is this for both insert and update cases, or just one?

What does the remote API accept for Address on Customer? Is it all fields or just an ID?

[devj3ns]

It's for both update and insert cases, as in both the association (the address) could be changed.

The PostgREST-API accepts the ID for the Address field on the Customer.

The problem is that when a new Customer or an existing Customer with a new Address is sent to the REST-API, the address the ID is pointing to has not been created in the addresses table, which makes the creation of the Customer fail.

[tshedor]

@devj3ns unfortunately, since the Address requires a remote ID, you'll need to upsert it before upserting the Customer object. If the Customer endpoint accepted nested fields for associations, you could just submit everything at once.

The alternative to this is to create a Supabase function that spreads the objects to both objects and handles the processing at a custom endpoint. Depending on your scale, this may be much more expensive than using their regular PostgREST endpoints. Something like

const address = {...payload.address};
const result = await client.from("addresses").upsert(address);
if (!result.error) {
  const customer = {...payload, address_id: result.id};
  const result = await client.from("customer").upsert(address);
}

[tshedor]

Closing