Support upsert with empty updates

Ah, dang. upsert is gonna need a new type signature.

In the event that we do an INSERT ... ON CONFLICT DO NOTHING RETURNING ??, postgresql does not return anything if we don't insert anything.

So the most proper type of upsert is then upsert :: (PersistEntity ent, OnlyOneUniqueKey ent) => ent -> [Update ent] -> SqlPersistT m (Maybe (Entity ent))

At least, as a default.

Playing around locally, the following form works:

WITH inserted AS (
    INSERT INTO "OneUnique" (name, value) 
    VALUES ("asdf", 0)
    ON CONFLICT (value)
    DO NOTHING
    RETURNING id, name, value
)
SELECT id, name, value
FROM inserted
UNION
SELECT id, name, value
FROM "OneUnique"
WHERE value = 0

But I have no idea what sorts of performance this has. This StackOverflow question/answer has some details and it looks to be somewhat complicated.

Maybe the type of upsert should really be upsert :: entity -> NonEmpty (Update entity) -> SqlPersistT m (Entity entity). Then we have our no-op insertOnConflictDoNothing :: entity -> SqlPersistT m (Maybe (Entity entity)). Then you get:

coolUpsert entity updates = 
    case NonEmpty.nonEmpty updates of
        Just nonEmptyUpdates ->
            Just <$> upsert entity nonEmptyUpdates
        Nothing ->
            insertOnConflictDoNothing entity

It might be tempting to write:

insertOnConflictDoNothing :: record -> SqlPersistT m record 
insertOnCOnflictDoNothing rec = do
    xs <- rawSql thequery thevalues
    case xs of
        [] -> pure rec
        (a : _) -> pure a

But this only means that the uniqueness key is the same, not the rest of the values.

bitemyapp / esqueleto

Support upsert with empty updates #301