Light
Dark
System
v4latest
v5dev
v4latest
v3
v2
v1

Insert

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 name: str { constraint exclusive };
  }

  type Hero extending Person {
    secret_identity: str;
    multi 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.

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 a 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 let’s write a nested insert for a multi link.

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

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, pulling a subquery into a with block lets you avoid duplication.

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

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

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

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.

To learn to use upserts by trying them yourself, see our interactive upserts tutorial.

It can be useful to know the outcome of an upsert. Here’s an example showing how you can return that:

Copy
db> 
... 
... 
... 
... 
... 
... 
... 
... 
... 
... 
... 
... 
... 
... 
... 
with
  title := "Eternals",
  release_year := 2021,
  movie := (
    insert Movie {
      title := title,
      release_year := release_year
    }
    unless conflict on .title
    else (
      update Movie set { release_year := release_year }
    )
  )
select movie {
  is_new := (movie not in Movie)
};
{default::Movie {is_new: true}}

This technique exploits the fact that a select will not return an object inserted in the same query. We know that, if the record exists, we updated it. If it does not, we inserted it.

By wrapping your upsert in a select and putting a shape on it that queries for the object and returns whether or not it exists (as is_new, in this example), you can easily see whether the object was inserted or updated.

If you want to also return some of the Movie object’s data, drop additional property names into the shape alongside is_new. If you’re on 3.0+, you can add Movie.* to the shape alongside is_new to get back all of the Movie object’s properties. You could even silo the data off, keeping it separate from the is_new computed value like this:

Copy
db> 
... 
... 
... 
... 
... 
... 
... 
... 
... 
... 
... 
... 
... 
... 
... 
... 
with
  title := "Eternals",
  release_year := 2021,
  movie := (
    insert Movie {
      title := title,
      release_year := release_year
    }
    unless conflict on .title
    else (
      update Movie set { release_year := release_year }
    )
  )
select {
  data := (select movie {*}),
  is_new := (movie not in Movie)
};
{
  {
    data: {
      default::Movie {
        id: 6880d0ba-62ca-11ee-9608-635818746433,
        release_year: 2021,
        title: 'Eternals'
      }
    },
    is_new: false
  }
}

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 JSON array 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

We use ChatGPT with additional context from our documentation to answer your questions. Not all answers will be accurate. Please join our Discord if you need more help.