EdgeDB 1.0 is launching on Feb 10th! Watch the live event.
Claim your ticket

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

Copy
module default {
  abstract type Person {
    required property name -> str { constraint exclusive };
  }

  type Hero extending Person {
    property secret_identity -> str;
    multi link villains := .<nemesis[IS Villain];
  }

  type Villain extending Person {
    link nemesis -> Hero;
  }

  type Movie {
    required property title -> str { constraint exclusive };
    required property release_year -> int64;
    multi link characters -> Person;
  }
}

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

Copy
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 an default value (like id).

Copy
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.

Copy
db> 
... 
... 
insert Person {
  name := "The Man With No Name"
};
error: QueryError: cannot insert into abstract object type 'default::Person'

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

Copy
db> 
... 
... 
... 
... 
... 
... 
insert Villain {
  name := "The Mandarin",
  nemesis := (insert Hero {
    name := "Shang-Chi",
    secret_identity := "Shaun"
  })
};
{default::Villain {id: d47888a0-3e7b-11ec-af13-fb68c8777851}}

Now lets write a nested insert for a multi link.

Copy
db> 
... 
... 
... 
... 
... 
... 
... 
... 
... 
... 
insert Movie {
  title := "Black Widow",
  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;

Copy
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, you should pull that subquery into a with block.

Copy
db> 
... 
... 
... 
... 
... 
... 
... 
... 
... 
... 
... 
with black_widow := (select Hero filter .name = "Black Widow")
insert Movie {
  title := "Black Widow",
  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.

Copy
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",
  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.

Copy
db> 
... 
... 
... 
... 
insert Movie {
  title := "Eternals"
}
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.

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.

Copy
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.

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.

Copy
db> 
... 
insert Hero { name := "The Wasp" } # initial insert
unless conflict;
{default::Hero {id: 35b97a92-3e9b-11ec-8e39-6b9695d671ba}}
Copy
db> 
... 
insert Hero { name := "The Wasp" } # The Wasp now exists
unless conflict;
{}

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.

Copy
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},
  ...
}
Light
Dark
System