The insert
command is used to create instances of object types. The code
samples on this page assume the following schema:
module default {
abstract type Person {
required name: str { constraint exclusive };
}
type Hero extending Person {
secret_identity: str;
multi link villains := .<nemesis[is Villain];
}
type Villain extending Person {
nemesis: Hero;
}
type Movie {
required title: str { constraint exclusive };
required release_year: int64;
multi characters: Person;
}
}
You can insert
instances of any non-abstract object type.
db> ... ... ...
insert Hero {
name := "Spider-Man",
secret_identity := "Peter Parker"
};
{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 a
default
value (like id
).
db> ... ... ...
insert Hero {
name := "Spider-Man"
# secret_identity is omitted
};
{default::Hero {id: b0fbe9de-3e90-11ec-8c12-ffa2d5f0176a}}
You can only insert
instances of concrete (non-abstract) object types.
db> ... ...
insert Person {
name := "The Man With No Name"
};
error: QueryError: cannot insert into abstract object type 'default::Person'
EdgeQL’s composable syntax makes link insertion painless. Below, we insert
“Spider-Man: No Way Home” and include all known heroes and villains as
characters
(which is basically true).
db> ... ... ... ... ... ... ... ... ... ... ... ...
insert Movie {
title := "Spider-Man: No Way Home",
release_year := 2021,
characters := (
select Person
filter .name in {
'Spider-Man',
'Doctor Strange',
'Doc Ock',
'Green Goblin'
}
)
};
{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.
db> ... ... ...
insert Villain {
name := "Doc Ock",
nemesis := (select Hero filter .name = "Spider-Man")
};
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.
db> ... ... ... ... ... ...
insert Villain {
name := "Doc Ock",
nemesis := assert_single((
select Hero
filter .secret_identity = "Peter B. Parker"
))
};
Just as we used subqueries to populate links with existing objects, we can also execute nested inserts.
db> ... ... ... ... ... ...
insert Villain {
name := "The Mandarin",
nemesis := (insert Hero {
name := "Shang-Chi",
secret_identity := "Shaun"
})
};
{default::Villain {id: d47888a0-3e7b-11ec-af13-fb68c8777851}}
Now let’s write a nested insert for a multi
link.
db> ... ... ... ... ... ... ... ... ... ... ...
insert Movie {
title := "Black Widow",
release_year := 2021,
characters := {
(select Hero filter .name = "Black Widow"),
(insert Hero { name := "Yelena Belova"}),
(insert Villain {
name := "Dreykov",
nemesis := (select Hero filter .name = "Black Widow")
})
}
};
{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.
db> ... ... ...
insert Hero {
name := "Ant-Man",
villains := (select Villain)
};
error: QueryError: modification of computed link 'villains' of object type 'default::Hero' is prohibited
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, pulling a subquery into a with
block lets you avoid
duplication.
db> ... ... ... ... ... ... ... ... ... ... ... ...
with black_widow := (select Hero filter .name = "Black Widow")
insert Movie {
title := "Black Widow",
release_year := 2021,
characters := {
black_widow,
(insert Hero { name := "Yelena Belova"}),
(insert Villain {
name := "Dreykov",
nemesis := black_widow
})
}
};
{default::Movie {id: af706c7c-3e98-11ec-abb3-4bbf3f18a61a}}
The with
block can contain an arbitrary number of clauses; later clauses
can reference earlier ones.
db> ... ... ... ... ... ... ... ...
with
black_widow := (select Hero filter .name = "Black Widow"),
yelena := (insert Hero { name := "Yelena Belova"}),
dreykov := (insert Villain {name := "Dreykov", nemesis := black_widow})
insert Movie {
title := "Black Widow",
release_year := 2021,
characters := { black_widow, yelena, dreykov }
};
{default::Movie {id: af706c7c-3e98-11ec-abb3-4bbf3f18a61a}}
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.
db> ... ... ... ... ...
insert Movie {
title := "Eternals",
release_year := 2021
}
unless conflict on .title
else (select Movie);
{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.
Using unless conflict
on multi properties is only supported in 2.10 and later.
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.
db> ... ... ... ... ... ... ... ... ... ...
with
title := "Eternals",
release_year := 2021
insert Movie {
title := title,
release_year := release_year
}
unless conflict on .title
else (
update Movie set { release_year := release_year }
);
{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.
To learn to use upserts by trying them yourself, see our interactive upserts tutorial.
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.
db> ...
insert Hero { name := "The Wasp" } # initial insert
unless conflict;
{default::Hero {id: 35b97a92-3e9b-11ec-8e39-6b9695d671ba}}
db> ...
insert Hero { name := "The Wasp" } # The Wasp now exists
unless conflict;
{}
Bulk inserts are performed by passing in a JSON array as a query
parameter, unpacking
it, and
using a for loop to insert the objects.
db> ... ... ... ...
with
raw_data := <json>$data,
for item in json_array_unpack(raw_data) union (
insert Hero { name := <str>item['name'] }
);
Parameter <json>$data: [{"name":"Sersi"},{"name":"Ikaris"},{"name":"Thena"}] { default::Hero {id: 35b97a92-3e9b-11ec-8e39-6b9695d671ba}, default::Hero {id: 35b97a92-3e9b-11ec-8e39-6b9695d671ba}, default::Hero {id: 35b97a92-3e9b-11ec-8e39-6b9695d671ba}, ... }