Skip to main content

Data Migrations

Migrations are usually used for changing the database schema, but in some cases, there is a need to modify the data stored in the database. For example, adding seed data, or back-filling empty columns with custom default values.

Migrations of this type are called data migrations. In this document, we will discuss how to use Ent to plan data migrations and integrate them into your regular schema migrations workflow.

Migration Types​

Ent currently supports two types of migrations, versioned migration and declarative migration (also known as automatic migration). Data migrations can be executed in both types of migrations.

Versioned Migrations​

When using versioned migrations, data migrations should be stored on the same migrations directory and executed the same way as regular migrations. It is recommended, however, to store data migrations and schema migrations in separate files so that they can be easily tested.

The format used for such migrations is SQL, as the file can be safely executed (and stored without changes) even if the Ent schema was modified and the generated code is not compatible with the data migration file anymore.

There are two ways to create data migrations scripts, manually and generated. By manually editing, users write all the SQL statements and can control exactly what will be executed. Alternatively, users can use Ent to generate the data migrations for them. It is recommended to verify that the generated file was correctly generated, as in some cases it may need to be manually fixed or edited.

Manual Creation​

1. If you don't have Atlas installed, check out its getting-started guide.

2. Create a new migration file using Atlas:

atlas migrate new <migration_name> \
  --dir "file://my/project/migrations"

3. Edit the migration file and add the custom data migration there. For example:

ent/migrate/migrations/20221126185750_backfill_data.sql
-- Backfill NULL or null tags with a default value.
UPDATE `users` SET `tags` = '["foo","bar"]' WHERE `tags` IS NULL OR JSON_CONTAINS(`tags`, 'null', '$');

4. Update the migration directory integrity file:

atlas migrate hash \
  --dir "file://my/project/migrations"

Check out the Testing section below if you're unsure how to test the data migration file.

Generated Scripts​

Currently, Ent provides initial support for generating data migration files. By using this option, users can simplify the process of writing complex SQL statements manually in most cases. Still, it is recommended to verify that the generated file was correctly generated, as in some edge cases it may need to be manually edited.

1. Create your versioned-migration setup, in case it is not set.

2. Create your first data-migration function. Below, you will find some examples that demonstrate how to write such a function:

ent/migrate/migratedata/migratedata.go
package migratedata

// BackfillUnknown back-fills all empty users' names with the default value 'Unknown'.
func BackfillUnknown(dir *migrate.LocalDir) error {
    w := &schema.DirWriter{Dir: dir}
    client := ent.NewClient(ent.Driver(schema.NewWriteDriver(dialect.MySQL, w)))

    // Change all empty names to 'unknown'.
    err := client.User.
        Update().
        Where(
            user.NameEQ(""),
        ).
        SetName("Unknown").
        Exec(context.Background())
    if err != nil {
        return fmt.Errorf("failed generating statement: %w", err)
    }

    // Write the content to the migration directory.
    return w.FlushChange(
        "unknown_names",
        "Backfill all empty user names with default value 'unknown'.",
    )
}

Then, using this function in ent/migrate/main.go will generate the following migration file:

migrations/20221126185750_unknown_names.sql
-- Backfill all empty user names with default value 'unknown'.
UPDATE `users` SET `name` = 'Unknown' WHERE `users`.`name` = '';