Lesson 4.1: Inserting simple data
The Insert query
To insert new data into a database, we use an Insert query. The following query inserts a new user.
insert
$evelin isa user,
has id "u0011",
has name "Evelin Griffith",
has birth-date 1994-08-03;
Run and commit
This Insert query consists of a single insert clause, which inserts a single entity of type user and assigns it three attributes: one each of types id, name, and birth-date.
Using a data session and a write transaction, try running this query. After running it, make sure to commit the transaction. The new user should now be in the database! You can check by switching to a data session and read transaction and then running the following query, which lists the names of users in the database.
match
$user isa user;
fetch
$user: name; Run
If you have correctly executed the Insert query, the new user Evelin should appear in the results.
Insert a new promotion entity with the following attributes:
-
A
codeattribute with value"SPR24". -
A
namewith value"Spring Sale 2024". -
A
start-timestampwith value2024-03-15T00:00:00. -
An
end-timestampwith value2024-03-31T23:59:59.
Sample solution
insert
$spring-sale isa promotion,
has code "SPR24",
has name "Spring Sale 2024",
has start-timestamp 2024-03-15T00:00:00,
has end-timestamp 2024-03-31T23:59:59; Run and commit
|
Datetime literals can be represented in any of the following formats:
All datetimes are stored at millisecond precision, with missing digits filled by zeroes. |
Inserting multiple objects
An Insert query can insert any number of entities and relations. In the following query, we insert four new entities: a paperback, a contributor, a publisher, and a publication event, along with two new relations between them: an authoring relation and a publishing relation.
insert
$frankenstein isa paperback,
has isbn-13 "9780486282114",
has title "Frankenstein",
has page-count 166,
has genre "fiction",
has genre "horror",
has price 5.79,
has isbn-10 "0486282112";
$shelley isa contributor,
has name "Shelley, Mary";
$dover isa publisher,
has name "Dover Publications";
$publication isa publication,
has year 1994;
(work: $frankenstein, author: $shelley) isa authoring;
(published: $frankenstein, publisher: $dover, publication: $publication) isa publishing; Run and commit
Notice that we have given the book two genre attributes! This is perfectly permissible, and in fact all attributes in TypeDB are multivalued by default, allowing an entity or relation to own any number of attributes of the same type, as long as they each have different values.
Insert two new users. The first user should have:
-
The user ID "u0012".
-
The name Fiona Carlson.
-
The birth date 10th July 1951.
The second user should have:
-
The user ID "u0013".
-
The name Jordan Duarte.
-
The birth date 31st October 1963.
Sample solution
insert
$fiona isa user,
has id "u0012",
has name "Fiona Carlson",
has birth-date 1951-07-10;
$jordan isa user,
has id "u0013",
has name "Jordan Duarte",
has birth-date 1963-10-31; Run and commit
Inserting references to existing objects
So far, the Insert queries we’ve seen have only created entirely new entities, relations between them, and their attributes. We can also write Insert queries that create relations between existing entities, or add new attributes to existing entities or relations. To do so, we use an Insert query with two clauses: a match clause followed by an insert clause. In the following example, we record the stock of the newly added edition of Frankenstein.
match
$book isa book, has isbn-13 "9780486282114";
insert
$book has stock 20; Run and commit
This query matches the book we inserted previously by its ISBN, and then gives it a new stock attribute. In a Fetch query, the match clause identifies matching data within the database, and the fetch clause specifies what to retrieve from each match. In an Insert query, the match clause serves the same function, but then we insert new data for each match rather than retrieving existing data.
In order to insert a new attribute, we have to match the attribute’s existing owner, represented in the last query by the $book variable. Similarly, in the next query, we insert a new relation by matching the relation’s existing roleplayers, represented by the $city and $publication variables. It records the fact that the publication of this edition of Frankenstein by Dover Publications took place in NYC.
match
$book isa paperback, has isbn-13 "9780486282114";
$publisher isa publisher, has name "Dover Publications";
$publication isa publication, has year 2002;
(published: $book, publisher: $publisher, publication: $publication) isa publishing;
$city isa city, has name "New York City";
insert
(location: $city, located: $publication) isa locating; Run and commit
An Insert query without a match clause always inserts the data described in the insert clause once. However, an Insert query with a match clause will insert the data described in the insert clause multiple times, once for each match found. The following query would assign a stock attribute to every paperback in the database, even if it already had one!
match
$book isa paperback;
insert
$book has stock 20;
Do not run
Likewise, the following query would create a locating relation for every publication entity with a year attribute equal to 2002.
match
$publication isa publication, has year 2002;
$city isa city, has name "New York City";
insert
(location: $city, located: $publication) isa locating; Do not run
|
To prevent unintended data insertions, it is often useful to structure the |
Insert a new login entity with a success attribute with value true, link that entity to the existing user Fiona Carlson (created in the last exercise) using an action-execution relation, and give that relation a timestamp attribute with value 2024-03-04T07:32:03.287.
Hint 1
You can get the labels of the roles in the action-execution relation with the following Fetch query.
match
action-execution relates $role;
fetch
$role;Hint 2
Make sure to identify Fiona by her unique user ID "u0012" so as not to match other users with the same name!
Sample solution
match
$user isa user, has id "u0012";
insert
$login isa login, has success true;
(action: $login, executor: $user) isa action-execution,
has timestamp 2024-03-04T07:32:03.287; Run and commit