Inserting data
The types used in these queries are defined here.
Insert basic movie stub:
insert Movie {
title := 'Dune',
year := 2020,
image := 'dune2020.jpg',
directors := (
select Person
filter
.full_name = 'Denis Villeneuve'
)
}Alternatively, insert a movie using JSON input value:
with
# Cast the JSON $input into a tuple, which we will
# use to populate the Person record.
data := <tuple<
title: str,
year: int64,
image: str,
directors: array<str>,
actors: array<str>
>> <json>$input
insert Movie {
title := data.title,
year := data.year,
image := data.image,
directors := (
select Person
filter
.full_name in array_unpack(data.directors)
),
actors := (
select Person
filter
.full_name in array_unpack(data.actors)
)
}Insert several nested objects at once:
# Create a new review and a new user in one step.
insert Review {
body := 'Dune is cool',
rating := 5,
# The movie record already exists, so select it.
movie := (
select Movie
filter
.title = 'Dune'
and
.year = 2020
# the limit is needed to satisfy the single
# link requirement validation
limit 1
),
# This is a new user, so insert one.
author := (
insert User {
name := 'dune_fan_2020',
image := 'default_avatar.jpg',
}
)
}Sometimes it’s necessary to check whether some object exists and
create it if it doesn’t. If this type of object has an exclusive
property, the unless conflict clause can make the insert
command indempotent. So running such a command would guarantee that a
copy of the object exists without the need for more complex logic:
# Try to create a new User
insert User {
name := "Alice",
image := "default_avatar.jpg",
}
# and do nothing if a User with this name already exists
unless conflictIf more than one property is exclusive, it is possible to specify which one of them is considered when a conflict is detected:
# Try to create a new User
insert User {
name := "Alice",
image := "default_avatar.jpg",
}
# and do nothing if a User with this name already exists
unless conflict on .name“Upserts” can be performed by using the unless conflict clause and
specifying what needs to be updated:
select (
# Try to create a new User,
insert User {
name := "Alice",
image := "my_face.jpg",
}
# but if a User with this name already exists,
unless conflict on .name
else (
# update that User's record instead.
update User
set {
image := "my_face.jpg"
}
)
) {
name,
image
}Rather than acting as an “upsert”, the unless conflict clause can
be used to insert or select an existing record, which is handy for
inserting nested structures:
# Create a new review and a new user in one step.
insert Review {
body := 'Loved it!!!',
rating := 5,
# The movie record already exists, so select it.
movie := (
select Movie
filter
.title = 'Dune'
and
.year = 2020
# the limit is needed to satisfy the single
# link requirement validation
limit 1
),
# This might be a new user or an existing user. Some
# other part of the app handles authentication, this
# endpoint is used as a generic way to post a review.
author := (
# Try to create a new User,
insert User {
name := "dune_fan_2020",
image := "default_avatar.jpg",
}
# but if a User with this name already exists,
unless conflict on .name
# just pick that existing User as the author.
else User
)
}