SQL adapter​

Connecting​

EdgeDB server supports PostgreSQL connection interface. It implements PostgreSQL wire protocol as well as SQL query language.

As of EdgeDB 6.0, it also supports a subset of Data Modification Language, namely INSERT, DELETE and UPDATE statements.

It does not, however, support PostgreSQL Data Definition Language (e.g. CREATE TABLE). This means that it is not possible to use SQL connections to EdgeDB to modify its schema. Instead, the schema should be managed using ESDL (EdgeDB Schema Definition Language) and migration commands.

Any Postgres-compatible client can connect to an EdgeDB database by using the same port that is used for the EdgeDB protocol and the branch name, username, and password already used for the database.

Here’s how you might connect to a local instance on port 10701 (determined by running edgedb instance list) on a branch main using the psql CLI:

$ 

psql -h localhost -p 10701 -U edgedb -d main

You’ll then be prompted for a password. If you don’t have it, you can run edgedb instance credentials --insecure-dsn and grab it out of the DSN the command returns. (It’s the string between the second colon and the “at” symbol: edgedb://edgedb:PASSWORD_IS_HERE@<host>:<port>/<branch>)

db> 
... 
... 

alter role edgedb {
  set password := 'my-password'
};

OK: ALTER ROLE

create superuser role sql {
  set password := 'your-password'
};

$ 

psql -h localhost -p 10701 -U sql -d main

Querying​

Object types in your EdgeDB schema are exposed as regular SQL tables containing all the data you store in your EdgeDB database.

If you have a database with the following schema:

module default {
    type Person {
        name: str;
    };

    type Movie extending common::Content {
        release_year: int32;
        director: Person;
        star: Person {
            role: str;
        };
        multi actors: Person {
            role: str;
        };
        multi labels: str;
    };
}
module common {
    type Content {
        title: str;
    };
}

you can access your data after connecting using the following SQL queries:

SELECT id, name FROM "Person";
SELECT id, title, release_year, director_id, star_id FROM "Movie";

Because the link star has link properties, it has its own table. source is the id of the Movie. target is the id of the Person.

SELECT source, target, role FROM "Movie.star";

Links are in separate tables.

SELECT source, target, role FROM "Movie.actors";

Multi properties are in separate tables. source is the id of the Movie. target is the value of the property.

SELECT source, target FROM "Movie.labels";

When using inheritance, parent object types’ tables will by default contain all objects of both the parent type and any child types. The query below will return all common::Content objects as well as all Movie objects.

SELECT id, title FROM common."Content";

To omit objects of child types, use ONLY. This query will return common::Content objects but not Movie objects.

SELECT id, title FROM ONLY common."Content";

The SQL adapter supports a large majority of SQL language, including:

• SELECT and all read-only constructs (WITH, sub-query, JOIN, …),

• INSERT / UPDATE / DELETE,

• COPY ... FROM,

• SET / RESET / SHOW,

• transaction commands,

• PREPARE / EXECUTE / DEALLOCATE.

SELECT id, 'Title is: ' || tittle
FROM "Movie" m
JOIN "Person" d ON m.director_id = d.id
WHERE EXISTS (
    SELECT 1
    FROM "Movie.actors" act
    WHERE act.source = m.id
);

The SQL adapter emulates the information_schema and pg_catalog views to mimic the catalogs provided by Postgres 13.

Tested SQL tools​

• pg_dump

• Metabase

• Cluvio

• Tableau

• DataGrip

• Airbyte [1]

• Fivetran [1]

• Hevo [1]

• Stitch [1]

• dbt [2]

[1] At the moment, EdgeDB does not support “Log replication” (i.e., using the Postgres replication mechanism). Supported replication methods include XMIN Replication, incremental updates using “a user-defined monotonically increasing id,” and full table updates.

[2] dbt models are built and stored in the database as either tables or views. Because the EdgeDB SQL adapter does not allow writing or even creating schemas, view, or tables, any attempt to materialize dbt models will result in errors. If you want to build the models, we suggest first transferring your data to a true Postgres instance via pg_dump or Airbyte. Tests and previews can still be run directy against the EdgeDB instance.

ESDL to PostgreSQL​

As mentioned, the SQL schema of the database is managed trough EdgeDB Schema Definition Language (ESDL). Here is a breakdown of how each of the ESDL construct is mapped to PostgreSQL schema:

• Objects types are mapped into tables. Each table has columns id UUID and __type__ UUID and one column for each single property or link.

• Single properties are mapped to tables columns.

• Single links are mapped to table columns with suffix _id and are of type UUID. They contain the ids of the link’s target type.

• Multi properties are mapped to tables with two columns: - source UUID, which contains the id of the property’s source object type, - target, which contains values of the property.

• Multi links are mapped to tables with columns: - source UUID, which contains the id of the property’s source object type, - target UUID, which contains the ids of the link’s target object type, - one column for each link property, using the same rules as properties on object types.

• Aliases are not mapped to PostgreSQL schema.

• Globals are mapped to connection settings, prefixed with global ``. For example, a ``global default::username: str can be set using SET "global default::username" TO 'Tom'.

• Access policies are applied to object type tables when setting apply_access_policies_sql is set to true.

• Mutation rewrites and triggers are applied to all DML commands.

DML commands​

When using INSERT, DELETE or UPDATE on any table, mutation rewrites and triggers are applied. These commands do not have a straight-forward translation to EdgeQL DML commands, but instead use the following mapping:

• INSERT INTO "Foo" object table maps to insert Foo,

• INSERT INTO "Foo.keywords" link/property table maps to an update Foo { keywords += ... },

• DELETE FROM "Foo" object table maps to delete Foo,

• DELETE FROM "Foo.keywords" link property/table maps to update Foo { keywords -= ... },

• UPDATE "Foo" object table maps to update Foo set { ... },

• UPDATE "Foo.keywords" is not supported.

Connection settings​

SQL adapter supports a limited subset of PostgreSQL connection settings. There are the following additionally connection settings:

• allow_user_specified_id (default false),

• apply_access_policies_sql (default false),

• settings prefixed with "global " can use used to set values of globals.

Note that if allow_user_specified_id or apply_access_policies_sql are unset, they default to configuration set by configure current database EdgeQL command.

Example: gradual transition from ORMs to EdgeDB​

When a project is using Object-Relational Mappings (e.g. SQLAlchemy, Django, Hibernate ORM, TypeORM) and is considering the migration to EdgeDB, it might want to execute the transition gradually, as opposed to a total rewrite of the project.

In this case, the project can start the transition by migrating the ORM models to EdgeDB Schema Definition Language.

For example, such Hibernate ORM model in Java:

@Entity
class Movie {
    @Id
    @GeneratedValue(strategy = GenerationType.UUID)
    UUID id;

    private String title;

    @NotNull
    private Integer releaseYear;

    // ... getters and setters ...
}

… would be translated to the following EdgeDB SDL:

type Movie {
    title: str;

    required releaseYear: int32;
}

A new EdgeDB instance can now be created and migrated to the translated schema. At this stage, EdgeDB will allow SQL connections to write into the "Movie" table, just as it would have been created with the following DDL command:

CREATE TABLE "Movie" (
    id UUID PRIMARY KEY DEFAULT (...),
    __type__ UUID NOT NULL DEFAULT (...),
    title TEXT,
    releaseYear INTEGER NOT NULL
);

When translating the old ORM model to EdgeDB SDL, one should aim to make the SQL schema of EdgeDB match the SQL schema that the ORM expects.

When this match is accomplished, any query that used to work with the old, plain PostgreSQL, should now also work with the EdgeDB. For example, we can execute the following query:

INSERT INTO "Movie" (title, releaseYear)
VALUES ("Madagascar", 2012)
RETURNING id, title, releaseYear;

To complete the migration, the data can be exported from our old database into an .sql file, which can be import it into EdgeDB:

$ 
  
  

pg_dump {your PostgreSQL connection params} \
  --data-only --inserts --no-owner --no-privileges \
  > dump.sql

$ 

psql {your EdgeDB connection params} --file dump.sql

Now, the ORM can be pointed to EdgeDB instead of the old PostgreSQL database, which has been fully replaced.

Arguably, the development of new features with the ORM is now more complex for the duration of the transition, since the developer has to modify two model definitions: the ORM and the EdgeDB schema.

But it allows any new models to use EdgeDB schema, EdgeQL and code generators for the client language of choice. The ORM-based code can now also be gradually rewritten to use EdgeQL, one model at the time.

For a detailed migration example, see repository edgedb/hibernate-example.