Conditional insert/update based on .id field

[divan]

How to write a single query for the conditional insert/update depending whether .id field is provided?

The use case is some nested update and nested object can be either existing (has .id) or new one (in this case I need to create one).

I tried to use UNLESS CONFLICT ON .id, but that returns cannot assign to id and consider enabling the "allow_user_specified_id" configuration parameter to allow setting custom object ids error (which makes sense).

[divan]

Trying this approach:

INSERT Foo {
    bar := (SELECT Bar FILTER .id = <uuid>$0) ?? (SELECT (INSERT BAR { ... })))
}

This returns:

edgedb.QueryError: INSERT statements cannot be used inside conditional expressions

[elprans]

How to write a single query for the conditional insert/update depending whether .id field is provided?

The answer depends on whether

1. You are passing an optional argument containing a valid object id and want to insert if input is empty, or
2. You are passing some uuid and want to insert if that uuid does not match any existing object.

Can you clarify which is it, please?

[divan]

@elprans probably the first one.

Let's say I want to insert Movie with a required Director field. If Director is not in the database, I want query to insert a new person, otherwise just select existing one. In the client (pseudo) code I would just write it as:

if exists Movie.Director {
   edgedb.Query('INSERT Movie { director := (SELECT Person ...) }');
} else {
   edgedb.Query('INSERT Movie { director := (INSERT Person ...) }');
}

but as I understand that can be done in a single query.

If Person's has a unique property (like .fullname), then it's easy with UNLESS CONFLICT. But what if there is no unique fields? That's where I'm trying to employ ".id" field in a client code as an indicator whether entry exists or it should be created.

Not sure I answered your question, but hopefully clarified a bit.

[divan]

Any solutions for this? I feel like this is going to be pretty common case, so would be nice to understand how to implement this upsert logic properly.

[elprans]

You probably want something along the lines of #4907 (comment), e.g

insert Movie {
  director := (
    with
      old := (select Person filter .id = <optional uuid>$movie_director),
      new := (for _ in (select () filter not exists old) union (
        insert Person { ... }
      )),
    select {old, new}
  )
}

We have a plan to allow conditional DML to make this prettier (see #4437).

[divan]

@elprans thanks! That's a workaround, so I'll probably go with conditions in the client code for now for clarity.

I'm not sure if conditional DMLs is a good approach here. Mentally this use case is literally the same as UNLESS CONFLICT use case. It's just the way how we determine the conflict is based on .id, not on other custom fields.

I believe that once users learn about UNLESS CONFLICT, that's where they will be looking for the solution for such a case.

Would it be possible to somehow allow "inserting .id" if UNLESS CONFLICT ON .id is specified? I don't know EdgeDB internals, so I'm just guessing here, of course, but it would be nice if there is any way to use UNLESS CONFLICT for this case.

[elprans]

There is a reason why explicit ids are disabled by default: they are expensive. Unlike a local constraint, a to check for an id conflict we must scan the entire database. And if we don't, then the check would be unsound.

On the other hand, conditional DML would not be all that bad:

director := <optional Person>$movie_director_id ?? (insert Person { ... })

Here I used the 3.0 feature of casting a UUID to an object (equivalent to select ... filter .id = )

[divan]

That looks nice and pretty clean. The only downside I see is, again, cognitive load – now users should learn two different ways to do "upserts" (by .id – use conditional DML, for other properties – UNLESS CONFLICT).

Is 3.0 ready to try on non-production system?

[elprans]

learn two different ways

Not really. Once conditional dml is supported nicely you can replace unless conflict with coalesce over any free expression, not just the id pattern as above.

Is 3.0 ready to try on non-production system?

Yes! Please try the beta!