Upsert

Subtopics

There are times when rather than an "upsert" you need to select an object or insert it, if it wasn't there. Consider, for example, the functionality to create a new account or retrieve an existing one:

Input

Run

# Wrap everything in a `select` to also display the
# entire inserted structure.
select (
  # Although it may seem more natural to think
  # of it as a select-or-insert command, it is
  # actually implemented as an
  # insert-or-select one.
  insert Account {
    username := 'Alice',
  } unless conflict on .username else Account
    # No need to add a filter, because the
    # User here is referring to the
    # conflicting User from the insert part.
) {
  id,
  username,
  watchlist: {
    title,
  }
}

# Wrap everything in a `select` to also display the
# entire inserted structure.
select (
  # Although it may seem more natural to think
  # of it as a select-or-insert command, it is
  # actually implemented as an
  # insert-or-select one.
  insert Account {
    username := 'Alice',
  } unless conflict on .username else Account
    # No need to add a filter, because the
    # User here is referring to the
    # conflicting User from the insert part.
) {
  id,
  username,
  watchlist: {
    title,
  }
}

Output Objects

Press the 'Run' button to evaluate the input

You can experiment with creating accounts with different usernames and see whether they seem to be fetching a pre-existing record or not by looking at the id or watchlist.

Input

Run

select (
  insert Account {
    username := 'New',
  } unless conflict on .username else Account
) {
  id,
  username,
  watchlist: {
    title,
  }
}

select (
  insert Account {
    username := 'New',
  } unless conflict on .username else Account
) {
  id,
  username,
  watchlist: {
    title,
  }
}

Output Objects

Press the 'Run' button to evaluate the input

Prev NextIntrospection