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:

  1. atlas migrate new <migration_name> \
  2.   --dir "file://my/project/migrations"
  1. Edit the migration file and add the custom data migration there. For example:

ent/migrate/migrations/20221126185750_backfill_data.sql

  1. -- Backfill NULL or null tags with a default value.
  2. UPDATE `users` SET `tags` = '["foo","bar"]' WHERE `tags` IS NULL OR JSON_CONTAINS(`tags`, 'null', '$');
  1. Update the migration directory integrity file:
  1. atlas migrate hash \
  2.   --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:

  • Single Statement
  • Multi Statement
  • Data Seeding

ent/migrate/migratedata/migratedata.go

  1. package migratedata
  3. // BackfillUnknown back-fills all empty users' names with the default value 'Unknown'.
  4. func BackfillUnknown(dir *migrate.LocalDir) error {
  5.     w := &schema.DirWriter{Dir: dir}
  6.     client := ent.NewClient(ent.Driver(schema.NewWriteDriver(dialect.MySQL, w)))
  8.     // Change all empty names to 'unknown'.
  9.     err := client.User.
  10.         Update().
  11.         Where(
  12.             user.NameEQ(""),
  13.         ).
  14.         SetName("Unknown").
  15.         Exec(context.Background())
  16.     if err != nil {
  17.         return fmt.Errorf("failed generating statement: %w", err)
  18.     }
  20.     // Write the content to the migration directory.
  21.     return w.FlushChange(
  22.         "unknown_names",
  23.         "Backfill all empty user names with default value 'unknown'.",
  24.     )
  25. }

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

migrations/20221126185750_unknown_names.sql

  1. -- Backfill all empty user names with default value 'unknown'.
  2. UPDATE `users` SET `name` = 'Unknown' WHERE `users`.`name` = '';

ent/migrate/migratedata/migratedata.go

  1. package migratedata
  3. // BackfillUserTags is used to generate the migration file '20221126185750_backfill_user_tags.sql'.
  4. func BackfillUserTags(dir *migrate.LocalDir) error {
  5.     w := &schema.DirWriter{Dir: dir}
  6.     client := ent.NewClient(ent.Driver(schema.NewWriteDriver(dialect.MySQL, w)))
  8.     // Add defaults "foo" and "bar" tags for users without any.
  9.     err := client.User.
  10.         Update().
  11.         Where(func(s *sql.Selector) {
  12.             s.Where(
  13.                 sql.Or(
  14.                     sql.IsNull(user.FieldTags),
  15.                     sqljson.ValueIsNull(user.FieldTags),
  16.                 ),
  17.             )
  18.         }).
  19.         SetTags([]string{"foo", "bar"}).
  20.         Exec(context.Background())
  21.     if err != nil {
  22.         return fmt.Errorf("failed generating backfill statement: %w", err)
  23.     }
  24.     // Document all changes until now with a custom comment.
  25.     w.Change("Backfill NULL or null tags with a default value.")
  27.     // Append the "org" special tag for users with a specific prefix or suffix.
  28.     err = client.User.
  29.         Update().
  30.         Where(
  31.             user.Or(
  32.                 user.NameHasPrefix("org-"),
  33.                 user.NameHasSuffix("-org"),
  34.             ),
  35.             // Append to only those without this tag.
  36.             func(s *sql.Selector) {
  37.                 s.Where(
  38.                     sql.Not(sqljson.ValueContains(user.FieldTags, "org")),
  39.                 )
  40.             },
  41.         ).
  42.         AppendTags([]string{"org"}).
  43.         Exec(context.Background())
  44.     if err != nil {
  45.         return fmt.Errorf("failed generating backfill statement: %w", err)
  46.     }
  47.     // Document all changes until now with a custom comment.
  48.     w.Change("Append the 'org' tag for organization accounts in case they don't have it.")
  50.     // Write the content to the migration directory.
  51.     return w.Flush("backfill_user_tags")
  52. }

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

migrations/20221126185750_backfill_user_tags.sql

  1. -- Backfill NULL or null tags with a default value.
  2. UPDATE `users` SET `tags` = '["foo","bar"]' WHERE `tags` IS NULL OR JSON_CONTAINS(`tags`, 'null', '$');
  3. -- Append the 'org' tag for organization accounts in case they don't have it.
  4. UPDATE `users` SET `tags` = CASE WHEN (JSON_TYPE(JSON_EXTRACT(`tags`, '$')) IS NULL OR JSON_TYPE(JSON_EXTRACT(`tags`, '$')) = 'NULL') THEN JSON_ARRAY('org') ELSE JSON_ARRAY_APPEND(`tags`, '$', 'org') END WHERE (`users`.`name` LIKE 'org-%' OR `users`.`name` LIKE '%-org') AND (NOT (JSON_CONTAINS(`tags`, '"org"', '$') = 1));

ent/migrate/migratedata/migratedata.go

  1. package migratedata
  3. // SeedUsers add the initial users to the database.
  4. func SeedUsers(dir *migrate.LocalDir) error {
  5.     w := &schema.DirWriter{Dir: dir}
  6.     client := ent.NewClient(ent.Driver(schema.NewWriteDriver(dialect.MySQL, w)))
  8.     // The statement that generates the INSERT statement.
  9.     err := client.User.CreateBulk(
  10.         client.User.Create().SetName("a8m").SetAge(1).SetTags([]string{"foo"}),
  11.         client.User.Create().SetName("nati").SetAge(1).SetTags([]string{"bar"}),
  12.     ).Exec(context.Background())
  13.     if err != nil {
  14.         return fmt.Errorf("failed generating statement: %w", err)
  15.     }
  17.     // Write the content to the migration directory.
  18.     return w.FlushChange(
  19.         "seed_users",
  20.         "Add the initial users to the database.",
  21.     )
  22. }

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

migrations/20221126212120_seed_users.sql

  1. -- Add the initial users to the database.
  2. INSERT INTO `users` (`age`, `name`, `tags`) VALUES (1, 'a8m', '["foo"]'), (1, 'nati', '["bar"]');
  1. In case the generated file was edited, the migration directory integrity file needs to be updated with the following command:
  1. atlas migrate hash \
  2.   --dir "file://my/project/migrations"

Testing

After adding the migration files, it is highly recommended that you apply them on a local database to ensure they are valid and achieve the intended results. The following process can be done manually or automated by a program.

  1. Execute all migration files until the last created one, the data migration file:
  1. # Total number of files.
  2. number_of_files=$(ls ent/migrate/migrations/*.sql | wc -l)
  4. # Execute all files without the latest.
  5. atlas migrate apply $[number_of_files-1] \
  6.   --dir "file://my/project/migrations" \
  7.   -u "mysql://root:pass@localhost:3306/test"
  1. Ensure the last migration file is pending execution:
  1. atlas migrate status \
  2.   --dir "file://my/project/migrations" \
  3.   -u "mysql://root:pass@localhost:3306/test"
  5. Migration Status: PENDING
  6.   -- Current Version: <VERSION_N-1>
  7.   -- Next Version:    <VERSION_N>
  8.   -- Executed Files:  <N-1>
  9.   -- Pending Files:   1

  1. Fill the local database with temporary data that represents the production database before running the data migration file.

  2. Run atlas migrate apply and ensure it was executed successfully.

  1. atlas migrate apply \
  2.   --dir "file://my/project/migrations" \
  3.   -u "mysql://root:pass@localhost:3306/test"

Note, by using atlas schema clean you can clean the database you use for local development and repeat this process until the data migration file achieves the desired result.

Automatic Migrations

In the declarative workflow, data migrations are implemented using Diff or Apply Hooks. This is because, unlike the versioned option, migrations of this type do not hold a name or a version when they are applied. Therefore, when a data is are written using hooks, the type of the schema.Change must be checked before its execution to ensure the data migration was not applied more than once.

  1. func FillNullValues(dbdialect string) schema.ApplyHook {
  2.     return func(next schema.Applier) schema.Applier {
  3.         return schema.ApplyFunc(func(ctx context.Context, conn dialect.ExecQuerier, plan *migrate.Plan) error {
  4.             // Search the schema.Change that triggers the data migration.
  5.             hasC := func() bool {
  6.                 for _, c := range plan.Changes {
  7.                     m, ok := c.Source.(*schema.ModifyTable)
  8.                     if ok && m.T.Name == user.Table && schema.Changes(m.Changes).IndexModifyColumn(user.FieldName) != -1 {
  9.                         return true
  10.                     }
  11.                 }
  12.                 return false
  13.             }()
  14.             // Change was found, apply the data migration.
  15.             if hasC {
  16.                 // At this stage, there are three ways to UPDATE the NULL values to "Unknown".
  17.                 // Append a custom migrate.Change to migrate.Plan, execute an SQL statement
  18.                 // directly on the dialect.ExecQuerier, or use the generated ent.Client.
  20.                 // Create a temporary client from the migration connection.
  21.                 client := ent.NewClient(
  22.                     ent.Driver(sql.NewDriver(dbdialect, sql.Conn{ExecQuerier: conn.(*sql.Tx)})),
  23.                 )
  24.                 if err := client.User.
  25.                     Update().
  26.                     SetName("Unknown").
  27.                     Where(user.NameIsNil()).
  28.                     Exec(ctx); err != nil {
  29.                     return err
  30.                 }
  31.             }
  32.             return next.Apply(ctx, conn, plan)
  33.         })
  34.     }
  35. }

For more examples, check out the Apply Hook examples section.