Insert

The insert command is used to create instances of object types. The code samples on this page assume the following schema:

1. module default {
2. abstract type Person {
3. required property name -> str { constraint exclusive };
4. }

6. type Hero extending Person {
7. property secret_identity -> str;
8. multi link villains := .<nemesis[is Villain];
9. }

11. type Villain extending Person {
12. link nemesis -> Hero;
13. }

15. type Movie {
16. required property title -> str { constraint exclusive };
17. required property release_year -> int64;
18. multi link characters -> Person;
19. }
20. }

Basic usage

You can insert instances of any non-abstract object type.

1. db>
2. ...
3. ...
4. ...

1. insert Hero {
2. name := "Spider-Man",
3. secret_identity := "Peter Parker"
4. };

1. {default::Hero {id: b0fbe9de-3e90-11ec-8c12-ffa2d5f0176a}}

Similar to selecting fields in select, insert statements include a shape specified with curly braces; the values of properties/links are assigned with the := operator.

Optional links or properties can be omitted entirely, as well as those with an default value (like id).

1. db>
2. ...
3. ...
4. ...

1. insert Hero {
2. name := "Spider-Man"
3. # secret_identity is omitted
4. };

1. {default::Hero {id: b0fbe9de-3e90-11ec-8c12-ffa2d5f0176a}}

You can only insert instances of concrete (non-abstract) object types.

1. db>
2. ...
3. ...

1. insert Person {
2. name := "The Man With No Name"
3. };

1. error: QueryError: cannot insert into abstract object type 'default::Person'

Inserting links

EdgeQL’s composable syntax makes link insertion painless. Below, we insert “Avengers: Endgame” and include all known heroes and villains as characters (which is basically true).

1. db>
2. ...
3. ...
4. ...
5. ...
6. ...
7. ...
8. ...
9. ...
10. ...
11. ...
12. ...

1. insert Movie {
2. title := "Spider-Man: No Way Home",
3. characters := (
4. select Person
5. filter .name in {
6. 'Spider-Man',
7. 'Doctor Strange',
8. 'Doc Ock',
9. 'Green Goblin'
10. }
11. )
12. };

1. {default::Movie {id: 9b1cf9e6-3e95-11ec-95a2-138eeb32759c}}

To assign to the Movie.characters link, we’re using a subquery. This subquery is executed and resolves to a singleton set of type Person, which is assignable to characters. Note that the inner select Person statement is wrapped in parentheses; this is required for all subqueries in EdgeQL.

Now let’s assign to a single link.

1. db>
2. ...
3. ...
4. ...

1. insert Villain {
2. name := "Doc Ock",
3. nemesis := (select Hero filter .name = "Spider-Man")
4. };

This query is valid because the inner subquery is guaranteed to return at most one Hero object, due to the uniqueness constraint on Hero.name. If you are filtering on a non-exclusive property, use assert_single to guarantee that the subquery will return zero or one results. If more than one result is returned, this query will fail at runtime.

1. db>
2. ...
3. ...
4. ...
5. ...
6. ...
7. ...

1. insert Villain {
2. name := "Doc Ock",
3. nemesis := assert_single((
4. select Hero
5. filter .secret_identity = "Peter B. Parker"
6. ))
7. };

Nested inserts

Just as we used subqueries to populate links with existing objects, we can also execute nested inserts.

1. db>
2. ...
3. ...
4. ...
5. ...
6. ...
7. ...

1. insert Villain {
2. name := "The Mandarin",
3. nemesis := (insert Hero {
4. name := "Shang-Chi",
5. secret_identity := "Shaun"
6. })
7. };

1. {default::Villain {id: d47888a0-3e7b-11ec-af13-fb68c8777851}}

Now lets write a nested insert for a multi link.

1. db>
2. ...
3. ...
4. ...
5. ...
6. ...
7. ...
8. ...
9. ...
10. ...
11. ...

1. insert Movie {
2. title := "Black Widow",
3. characters := {
4. (select Hero filter .name = "Black Widow"),
5. (insert Hero { name := "Yelena Belova"}),
6. (insert Villain {
7. name := "Dreykov",
8. nemesis := (select Hero filter .name = "Black Widow")
9. })
10. }
11. };

1. {default::Movie {id: af706c7c-3e98-11ec-abb3-4bbf3f18a61a}}

We are using set literal syntax to construct a set literal containing several select and insert subqueries. This set contains a mix of Hero and Villain objects; since these are both subtypes of Person (the expected type of Movie.characters), this is valid.

You also can’t assign to a computed property or link; these fields don’t actually exist in the database.

1. db>
2. ...
3. ...
4. ...

1. insert Hero {
2. name := "Ant-Man",
3. villains := (select Villain)
4. };

1. error: QueryError: modification of computed link 'villains' of object type
2. 'default::Hero' is prohibited

With block

In the previous query, we selected Black Widow twice: once in the characters set and again as the nemesis of Dreykov. In circumstances like this, you should pull that subquery into a with block.

1. db>
2. ...
3. ...
4. ...
5. ...
6. ...
7. ...
8. ...
9. ...
10. ...
11. ...
12. ...

1. with black_widow := (select Hero filter .name = "Black Widow")
2. insert Movie {
3. title := "Black Widow",
4. characters := {
5. black_widow,
6. (insert Hero { name := "Yelena Belova"}),
7. (insert Villain {
8. name := "Dreykov",
9. nemesis := black_widow
10. })
11. }
12. };

1. {default::Movie {id: af706c7c-3e98-11ec-abb3-4bbf3f18a61a}}

The with block can contain an arbitrary number of clauses; later clauses can reference earlier ones.

1. db>
2. ...
3. ...
4. ...
5. ...
6. ...
7. ...
8. ...

1. with
2. black_widow := (select Hero filter .name = "Black Widow"),
3. yelena := (insert Hero { name := "Yelena Belova"}),
4. dreykov := (insert Villain {name := "Dreykov", nemesis := black_widow})
5. insert Movie {
6. title := "Black Widow",
7. characters := { black_widow, yelena, dreykov }
8. };

1. {default::Movie {id: af706c7c-3e98-11ec-abb3-4bbf3f18a61a}}

Conflicts

EdgeDB provides a general-purpose mechanism for gracefully handling possible exclusivity constraint violations. Consider a scenario where we are trying to insert Eternals (the Movie), but we can’t remember if it already exists in the database.

1. db>
2. ...
3. ...
4. ...
5. ...

1. insert Movie {
2. title := "Eternals"
3. }
4. unless conflict on .title
5. else (select Movie);

1. {default::Movie {id: af706c7c-3e98-11ec-abb3-4bbf3f18a61a}}

This query attempts to insert Eternals. If it already exists in the database, it will violate the uniqueness constraint on Movie.title, causing a conflict on the title field. The else clause is then executed and returned instead. In essence, unless conflict lets us “catch” exclusivity conflicts and provide a fallback expression.

Note that the else clause is simply select Movie. There’s no need to apply additional filters on Movie; in the context of the else clause, Movie is bound to the conflicting object.

Upserts

There are no limitations on what the else clause can contain; it can be any EdgeQL expression, including an update statement. This lets you express upsert logic in a single EdgeQL query.

1. db>
2. ...
3. ...
4. ...
5. ...
6. ...
7. ...
8. ...
9. ...
10. ...
11. ...

1. with
2. title := "Eternals",
3. release_year := 2021
4. insert Movie {
5. title := title,
6. release_year := release_year
7. }
8. unless conflict on .title
9. else (
10. update Movie set { release_year := release_year }
11. );

1. {default::Movie {id: f1bf5ac0-3e9d-11ec-b78d-c7dfb363362c}}

When a conflict occurs during the initial insert, the statement falls back to the update statement in the else clause. This updates the release_year of the conflicting object.

Suppressing failures

The else clause is optional; when omitted, the insert statement will return an empty set if a conflict occurs. This is a common way to prevent insert queries from failing on constraint violations.

1. db>
2. ...

1. insert Hero { name := "The Wasp" } # initial insert
2. unless conflict;

1. {default::Hero {id: 35b97a92-3e9b-11ec-8e39-6b9695d671ba}}

1. db>
2. ...

1. insert Hero { name := "The Wasp" } # The Wasp now exists
2. unless conflict;

1. {}

Bulk inserts

Bulk inserts are performed by passing in a large JSON as a query parameter, unpacking it, and using a for loop to insert the objects.

1. db>
2. ...
3. ...
4. ...
5. ...

1. with
2. raw_data := <json>$data,
3. for item in json_array_unpack(raw_data) union (
4. insert Hero { name := <str>item['name'] }
5. );

1. Parameter <json>$data: [{"name":"Sersi"},{"name":"Ikaris"},{"name":"Thena"}]
2. {
3. default::Hero {id: 35b97a92-3e9b-11ec-8e39-6b9695d671ba},
4. default::Hero {id: 35b97a92-3e9b-11ec-8e39-6b9695d671ba},
5. default::Hero {id: 35b97a92-3e9b-11ec-8e39-6b9695d671ba},
6. ...
7. }

︙