Search
ctrl+/
Ask AI
ctrl+.
Light
Dark
System
Sign in

Insert​
.md
Copy page

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;
  }
}

Basic usage​

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'

By default, insert returns only the inserted object's id as seen in the examples above. If you want to get additional data back, you may wrap your insert with a select and apply a shape specifying any properties and links you want returned:

Copy
db> 
... 
... 
... 
select (insert Hero {
  name := "Spider-Man"
  # secret_identity is omitted
}) {id, name};
{
  default::Hero {
    id: b0fbe9de-3e90-11ec-8c12-ffa2d5f0176a,
    name: "Spider-Man"
  }
}

You can use With to tidy this up if you prefer:

Copy
db> 
... 
... 
... 
... 
... 
... 
... 
with NewHero := (insert Hero {
  name := "Spider-Man"
  # secret_identity is omitted
})
select NewHero {
  id,
  name,
}
{
  default::Hero {
    id: b0fbe9de-3e90-11ec-8c12-ffa2d5f0176a,
    name: "Spider-Man"
  }
}

Inserting links​

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

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

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


Copy
db> 
... 
... 
... 
... 
... 
... 
insert Villain {
  name := "Doc Ock",
  nemesis := assert_single((
    select Hero
    filter .secret_identity = "Peter B. Parker"
  ))
};







Nested inserts​


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







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, 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}}







Conflicts​




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







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.


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.




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
  }
}








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.


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​


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