How to implement custom conflict mechanisms?

[pedrommcarrasco]

Hi 👋

I’ve been working on both the backend and iOS to develop a remote database and an iOS client built with GRDB and a custom sync engine.

Part of the sync engine’s safety mechanism relies on blocking updates if the updatedAt property is more recent than the current one.

On the backend, we’re able to perform this check, but I couldn’t find a similar API in GRDB. Am I missing something? What would be the best way to achieve this?

tx
                                .insert(table)
                                .values(object as PgInsertValue<typeof table>)
                                .onConflictDoUpdate({
                                    target:
                                        'id' in table ? table.id : table.userId,
                                    set: object,
                                    setWhere: and(
                                        table.userId
                                            ? eq(
                                                  table.userId,
                                                  object.userId as string,
                                              )
                                            : undefined,
                                        // Update only if the updatedAt is newer
                                        sql`${table.updatedAt} < ${object.updatedAt}`,
                                    ),
                                }),

[groue]

Hello @pedrommcarrasco,

GRDB does not currently support the WHERE clause of the upsert statement, so there is no direct equivalent of this Drizzle ORM construct.

A possible solution is to perform multiple requests instead of one upsert (thus implementing the logic in the app, instead of letting the database handle it).

I hear the feature request for upsert conditions, though 👍

[pedrommcarrasco]

Appreciate the speedy response as always, thank you!

[pedrommcarrasco]

Sorry to bother you again, @groue. I just wanted to confirm—GRDB doesn’t support calling functions within a trigger, correct? If it did, one could implement something similar to what’s achievable in Postgres using Database Triggers:

create or replace function check_updated_at_before_insert_or_update()
returns trigger
language plpgsql
set search_path = ''
as $$
begin
    -- Check if the updated_at field exists and is older than the current one
    if new.updated_at <= old.updated_at then
        -- Ignore the insert/update by returning the old row
        return old;
    end if;
    
    -- If updated_at is newer, proceed with the operation
    return new;
end;
$$;

[groue]

You're not bothering! This time, we're contemplating a PL/pgSQL script, which is not supported by SQLite. You might be able to create an SQLite trigger that reproduce the behavior of this function. SQLite evaluates conditions with the CASE expression. You'll have to execute raw SQL statements (try db.execute(sql: "...")).