Inserting data​

The types used in these queries are defined here.

Insert basic movie stub:

1. insert Movie {
2. title := 'Dune',
3. year := 2020,
4. image := 'dune2020.jpg',
5. directors := (
6. select Person
7. filter
8. .full_name = 'Denis Villeneuve'
9. )
10. }

Alternatively, insert a movie using JSON input value:

1. with
2. # Cast the JSON $input into a tuple, which we will
3. # use to populate the Person record.
4. data := <tuple<
5. title: str,
6. year: int64,
7. image: str,
8. directors: array<str>,
9. actors: array<str>
10. >> <json>$input
11. insert Movie {
12. title := data.title,
13. year := data.year,
14. image := data.image,
15. directors := (
16. select Person
17. filter
18. .full_name in array_unpack(data.directors)
19. ),
20. actors := (
21. select Person
22. filter
23. .full_name in array_unpack(data.actors)
24. )
25. }

Insert several nested objects at once:

1. # Create a new review and a new user in one step.
2. insert Review {
3. body := 'Dune is cool',
4. rating := 5,
5. # The movie record already exists, so select it.
6. movie := (
7. select Movie
8. filter
9. .title = 'Dune'
10. and
11. .year = 2020
12. # the limit is needed to satisfy the single
13. # link requirement validation
14. limit 1
15. ),
16. # This is a new user, so insert one.
17. author := (
18. insert User {
19. name := 'dune_fan_2020',
20. image := 'default_avatar.jpg',
21. }
22. )
23. }

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:

1. # Try to create a new User
2. insert User {
3. name := "Alice",
4. image := "default_avatar.jpg",
5. }
6. # and do nothing if a User with this name already exists
7. unless conflict

If more than one property is exclusive, it is possible to specify which one of them is considered when a conflict is detected:

1. # Try to create a new User
2. insert User {
3. name := "Alice",
4. image := "default_avatar.jpg",
5. }
6. # and do nothing if a User with this name already exists
7. unless conflict on .name

“Upserts” can be performed by using the unless conflict clause and specifying what needs to be updated:

1. select (
2. # Try to create a new User,
3. insert User {
4. name := "Alice",
5. image := "my_face.jpg",
6. }

8. # but if a User with this name already exists,
9. unless conflict on .name
10. else (
11. # update that User's record instead.
12. update User
13. set {
14. image := "my_face.jpg"
15. }
16. )
17. ) {
18. name,
19. image
20. }

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:

1. # Create a new review and a new user in one step.
2. insert Review {
3. body := 'Loved it!!!',
4. rating := 5,
5. # The movie record already exists, so select it.
6. movie := (
7. select Movie
8. filter
9. .title = 'Dune'
10. and
11. .year = 2020
12. # the limit is needed to satisfy the single
13. # link requirement validation
14. limit 1
15. ),

17. # This might be a new user or an existing user. Some
18. # other part of the app handles authentication, this
19. # endpoint is used as a generic way to post a review.
20. author := (
21. # Try to create a new User,
22. insert User {
23. name := "dune_fan_2020",
24. image := "default_avatar.jpg",
25. }

27. # but if a User with this name already exists,
28. unless conflict on .name
29. # just pick that existing User as the author.
30. else User
31. )
32. }

︙