2. Database and Schema

Syntax-highlighter packages are available for Atom, Visual Studio Code, Sublime Text, and Vim.

First step in a brand new project is to create the database for it:

edgedb> 
CREATE DATABASE tutorial;
CREATE

The above command creates a new database in the EdgeDB instance. Now we should connect to it:

edgedb> 
\c tutorial
tutorial>

Now we need to set up the schema. Let’s set up a basic schema for a movies database. It will have 2 types of objects: movies and people who directed and acted in them.

For the next step, there are two ways of setting this up. Please pick one of the methods: SDL (recommended) or DDL.

The EdgeDB schema definition language provides a way to describe a migration to a specific schema state. It is great for setting up a new database because it focuses on expressing the final types and their relationships without worrying about the order of the definitions.

Migrations have to be done inside a transaction:

tutorial> 
START TRANSACTION;
START TRANSACTION
tutorial> 
......... 
......... 
......... 
......... 
......... 
......... 
......... 
......... 
......... 
......... 
......... 
......... 
CREATE MIGRATION movies TO {
    type Movie {
        required property title -> str;
        # the year of release
        property year -> int64;
        required link director -> Person;
        multi link cast -> Person;
    }
    type Person {
        required property first_name -> str;
        required property last_name -> str;
    }
};
CREATE MIGRATION
tutorial> 
COMMIT MIGRATION movies;
COMMIT MIGRATION
tutorial> 
COMMIT;
COMMIT TRANSACTION

The name of a migration doesn’t matter much beyond providing a way to specify the particular migration which must be committed. Once the transaction is committed the schema is updated and we’re ready to populate the database with data.

The entire DDL section is an alternative to SDL. If you have completed the SDL steps you don’t need to follow the steps in this section.

The data definition language focuses on transforming the current schema state into the desired target step by step. This method is equally valid, but it is a lower level and more explicit approach to altering the schema. It is also less transparent in terms of giving a clear picture of the final resulting state.

In DDL the order of the commands matters, so the Person type must be created first:

tutorial> 
......... 
......... 
......... 
CREATE TYPE Person {
    CREATE REQUIRED PROPERTY first_name -> str;
    CREATE REQUIRED PROPERTY last_name -> str;
};
CREATE

Now a Movie type can be created with links referring to Person:

tutorial> 
......... 
......... 
......... 
......... 
......... 
......... 
CREATE TYPE Movie {
    CREATE REQUIRED PROPERTY title -> str;
    # the year of release
    CREATE PROPERTY year -> int64;
    CREATE REQUIRED LINK director -> Person;
    CREATE MULTI LINK cast -> Person;
};
CREATE

Now that the schema is set up we’re ready to populate the database with data.