Light
Dark
System
v4latest
v5dev
v4latest
v3
v2
v1

Schema migration tips

This example shows how a property may evolve to be more and more strict over time by looking at a user name field. However, similar evolution may be applicable to other properties that start off with few restrictions and gradually become more constrained and formalized as the needs of the project evolve.

We’ll start with a fairly simple schema:

Copy
type User {
  name: str;
}

At this stage we don’t think that this property needs to be unique or even required. Perhaps it’s only used as a screen name and not as a way of identifying users.

Copy
$ 
edgedb migration create
did you create object type 'default::User'? [y,n,l,c,b,s,q,?]
> y
Created ./dbschema/migrations/00001.edgeql, id:
m14gwyorqqipfg7riexvbdq5dhgv7x6buqw2jaaulilcmywinmakzq
Copy
$ 
edgedb migrate
Applied m14gwyorqqipfg7riexvbdq5dhgv7x6buqw2jaaulilcmywinmakzq
(00001.edgeql)

We’ve got our first migration to set up the schema. Now after using that for a little while we realize that we want to make name a required property. So we make the following change in the schema file:

Copy
type User {
  required name: str;
}

Next we try to migrate:

Copy
$ 
edgedb migration create
did you make property 'name' of object type 'default::User' required?
[y,n,l,c,b,s,q,?]
> y
Please specify an expression to populate existing objects in order to make
property 'name' of object type 'default::User' required:
fill_expr> 'change me'

Oh! That’s right, we can’t just make name required because there could be existing User objects without a name at all. So we need to provide some kind of placeholder value for those cases. We type 'change me' (although any other string would do, too). This is different from specifying a default value since it will be applied to existing objects, whereas the default applies to new ones.

Unseen to us (unless we take a look at the automatically generated .edgeql files inside our /dbschema folder), EdgeDB has created a migration script that includes the following command to make our schema change happen.

Copy
ALTER TYPE default::User {
    ALTER PROPERTY name {
        SET REQUIRED USING (<std::str>'change me');
    };
};

We then run edgedb migrate to apply the changes.

Next we realize that we actually want to make names unique, perhaps to avoid confusion or to use them as reliable human-readable identifiers (unlike id). We update the schema again:

Copy
type User {
  required name: str {
    constraint exclusive;
  }
}

Now we proceed with the migration:

Copy
$ 
edgedb migration create
did you create constraint 'std::exclusive' of property 'name'?
[y,n,l,c,b,s,q,?]
> y
Created ./dbschema/migrations/00003.edgeql, id:
m1dxs3xbk4f3vhmqh6mjzetojafddtwlphp5a3kfbfuyvupjafevya
Copy
$ 
edgedb migrate
edgedb error: ConstraintViolationError: name violates exclusivity
constraint

Some objects must have the same name, so the migration can’t be applied. We have a couple of options for fixing this:

  1. Review the existing data and manually update the entries with duplicate names so that they are unique.

  2. Edit the migration to add an update which will de-duplicate name for any potential existing User objects.

The first option is good for situations where we want to signal to any other maintainer of a copy of this project that they need to make a decision about handling name duplicates in whatever way is appropriate to them without making an implicit decision once and for all.

Here we will go with the second option, which is good for situations where we know enough about the situation that we can make a decision now and never have to duplicate this effort for any other potential copies of our project.

We edit the last migration file 00003.edgeql:

Copy
CREATE MIGRATION m1dxs3xbk4f3vhmqh6mjzetojafddtwlphp5a3kfbfuyvupjafevya
    ONTO m1ndhbxx7yudb2dv7zpypl2su2oygyjlggk3olryb5uszofrfml4uq
{
  with U := default::User
  update default::User
  filter U.name = .name and U != default::User
  set {
    # De-duplicate names by appending a random uuid.
    name := .name ++ '_' ++ <str>uuid_generate_v1mc()
  };

  ALTER TYPE default::User {
      ALTER PROPERTY name {
          CREATE CONSTRAINT std::exclusive;
      };
  };
};

And then we apply the migration:

Copy
$ 
edgedb migrate
edgedb error: could not read migrations in ./dbschema/migrations: could not
read migration file ./dbschema/migrations/00003.edgeql: migration name
should be `m1t6slgcfne35vir2lcgnqkmaxsxylzvn2hanr6mijbj5esefsp7za` but `
m1dxs3xbk4f3vhmqh6mjzetojafddtwlphp5a3kfbfuyvupjafevya` is used instead.
Migration names are computed from the hash of the migration contents. To
proceed you must fix the statement to read as:
  CREATE MIGRATION m1t6slgcfne35vir2lcgnqkmaxsxylzvn2hanr6mijbj5esefsp7za
  ONTO ...
if this migration is not applied to any database. Alternatively, revert the
changes to the file.

The migration tool detected that we’ve altered the file and asks us to update the migration name (acting as a checksum) if this was deliberate. This is done as a precaution against accidental changes. Since we’ve done this on purpose, we can update the file and run edgedb migrate again.

Finally, we evolved our schema all the way from having an optional property name all the way to making it both required and exclusive. We’ve worked with the EdgeDB migration tools to iron out the kinks throughout the migration process. At this point we take a quick look at the way duplicate User objects were resolved to decide whether we need to do anything more. We can use re_test() to find names that look like they are ending in a UUID:

Copy
db> 
... 
... 
select User { name }
filter
    re_test('.* [a-z0-9]{8}(-[a-z0-9]{4}){3}-[a-z0-9]{12}$', .name);
{
  default::User {name: 'change me bc30d45a-2bcf-11ec-a6c2-6ff21f33a302'},
  default::User {name: 'change me bc30d8a6-2bcf-11ec-a6c2-4f739d559598'},
}

Looks like the only duplicates are the users that had no names originally and that never updated the 'change me' placeholders, so we can probably let them be for now. In hindsight, it may have been a good idea to use UUID-based names to populate the empty properties from the very beginning.

This example shows how to change the type of a property. We’ll use a character in an adventure game as the type of data we will evolve.

Let’s start with this schema:

Copy
type Character {
  required name: str;
  required description: str;
}

We edit the schema file and perform our first migration:

Copy
$ 
edgedb migration create
did you create object type 'default::Character'? [y,n,l,c,b,s,q,?]
> y
Created ./dbschema/migrations/00001.edgeql, id:
m1paw3ogpsdtxaoywd6pl6beg2g64zj4ykhd43zby4eqh64yjad47a
Copy
$ 
edgedb migrate
Applied m1paw3ogpsdtxaoywd6pl6beg2g64zj4ykhd43zby4eqh64yjad47a
(00001.edgeql)

The intent is for the description to provide some text which serves both as something to be shown to the player as well as determining some game actions. Se we end up with something like this:

Copy
db> 
select Character {name, description};
{
  default::Character {name: 'Alice', description: 'Tall and strong'},
  default::Character {name: 'Billie', description: 'Smart and aloof'},
  default::Character {name: 'Cameron', description: 'Dashing and smooth'},
}

However, as we keep developing our game it becomes apparent that this is less of a “description” and more of a “character class”, so at first we just rename the property to reflect that:

Copy
type Character {
  required name: str;
  required class: str;
}

The migration gives us this:

Copy
$ 
edgedb migration create
did you rename property 'description' of object type 'default::Character'
to 'class'? [y,n,l,c,b,s,q,?]
> y
Created ./dbschema/migrations/00002.edgeql, id:
m1ljrgrofsqkvo5hsxc62mnztdhlerxp6ucdto262se6dinhuj4mqq
Copy
$ 
edgedb migrate
Applied m1ljrgrofsqkvo5hsxc62mnztdhlerxp6ucdto262se6dinhuj4mqq
(00002.edgeql)

EdgeDB detected that the change looked like a property was being renamed, which we confirmed. Since this was an existing property being renamed, the data is all preserved:

Copy
db> 
select Character {name, class};
{
  default::Character {name: 'Alice', class: 'Tall and strong'},
  default::Character {name: 'Billie', class: 'Smart and aloof'},
  default::Character {name: 'Cameron', class: 'Dashing and smooth'},
}

The contents of the class property are a bit too verbose, so we decide to update them. In order for this update to be consistently applied across several developers, we will make it in the form of a data migration:

Copy
$ 
edgedb migration create --allow-empty
Created ./dbschema/migrations/00003.edgeql, id:
m1qv2pdksjxxzlnujfed4b6to2ppuodj3xqax4p3r75yfef7kd7jna

Now we can edit the file 00003.edgeql directly:

Copy
CREATE MIGRATION m1qv2pdksjxxzlnujfed4b6to2ppuodj3xqax4p3r75yfef7kd7jna
    ONTO m1ljrgrofsqkvo5hsxc62mnztdhlerxp6ucdto262se6dinhuj4mqq
{
    update default::Character
    set {
        class :=
            'warrior' if .class = 'Tall and strong' else
            'scholar' if .class = 'Smart and aloof' else
            'rogue'
    };
};

We’re ready to apply the migration:

Copy
$ 
edgedb migrate
edgedb error: could not read migrations in ./dbschema/migrations:
could not read migration file ./dbschema/migrations/00003.edgeql:
migration name should be
`m1ryafvp24g5eqjeu65zr4bqf6m3qath3lckfdhoecfncmr7zshehq`
but `m1qv2pdksjxxzlnujfed4b6to2ppuodj3xqax4p3r75yfef7kd7jna` is used
instead.
Migration names are computed from the hash of the migration
contents. To proceed you must fix the statement to read as:
  CREATE MIGRATION m1ryafvp24g5eqjeu65zr4bqf6m3qath3lckfdhoecfncmr7zshehq
  ONTO ...
if this migration is not applied to any database. Alternatively,
revert the changes to the file.

The migration tool detected that we’ve altered the file and asks us to update the migration name (acting as a checksum) if this was deliberate. This is done as a precaution against accidental changes. Since we’ve done this on purpose, we can update the file and run edgedb migrate again.

As the game becomes more stable there’s no reason for the class to be a str anymore, instead we can use an enum to make sure that we don’t accidentally use some invalid value for it.

Copy
scalar type CharacterClass extending enum<warrior, scholar, rogue>;

type Character {
  required name: str;
  required class: CharacterClass;
}

Fortunately, we’ve already updated the class strings to match the enum values, so that a simple cast will convert all the values. If we had not done this earlier we would need to do it now in order for the type change to work.

Copy
$ 
edgedb migration create
did you create scalar type 'default::CharacterClass'? [y,n,l,c,b,s,q,?]
> y
did you alter the type of property 'class' of object type
'default::Character'? [y,n,l,c,b,s,q,?]
> y
Created ./dbschema/migrations/00004.edgeql, id:
m1hc4yynkejef2hh7fvymvg3f26nmynpffksg7yvfksqufif6lulgq
Copy
$ 
edgedb migrate
Applied m1hc4yynkejef2hh7fvymvg3f26nmynpffksg7yvfksqufif6lulgq
(00004.edgeql)

The final migration converted all the class property values:

Copy
db> 
select Character {name, class};
{
  default::Character {name: 'Alice', class: warrior},
  default::Character {name: 'Billie', class: scholar},
  default::Character {name: 'Cameron', class: rogue},
}

Each time you create a migration with edgedb migration create, a file containing the DDL for that migration is created in dbschema/migrations. When you apply a migration with edgedb migration apply or edgedb migrate, the database stores a record of the migration it applied.

On rare occasions, you may find you have deleted your migration files by mistake. If you don’t care about any of your data and don’t need to keep your migration history, you can wipe your database and start over, creating a single migration to the current state of your schema. If that’s not an option, all hope is not lost. You can instead recover your migrations from the database.

Run this query to see your migrations:

Copy
select schema::Migration {
  name,
  script,
  parents: {name}
}

You can rebuild your migrations from the results of this query, either manually or via a script if you’ve applied too many of them to recreate by hand. Migrations in the file system are named sequentially starting from 00001.edgeql. They are in this format:

Copy
CREATE MIGRATION m1rsm66e5pvh5ets2yznutintmqnxluzvgbocspi6umd3ht64e4naq
                 # ☝️ Replace with migration name
    ONTO m1l5esbbycsyqcnx6udxx24riavvyvkskchtekwe7jqx5mmiyli54a
         # ☝️ Replace with parent migration name
{
  # script
  # ☝️ Replace with migration script
};

or if this is the first migration:

Copy
CREATE MIGRATION m1l5esbbycsyqcnx6udxx24riavvyvkskchtekwe7jqx5mmiyli54a
                 # ☝️ Replace with migration name
    ONTO initial
{
  # script
  # ☝️ Replace with migration script
};

Replace the name, script, and parent name with the values from your Migration query results.

You can identify the first migration in your query results as the one with no object linked on parents. Order the other migrations by chaining the links. The Migration with the initial migration linked via parents is the second migration — 00002.edgeql. The migration linking to the second migration via parents is the third migration, and so on).

The following query will return the most current migration:

Copy
db> 
... 
... 
... 
... 
... 
with
 module schema,
 lastMigration := (
   select Migration filter not exists .<parents[is Migration]
 )
select lastMigration {*};
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.