Referencing nested migration files

[malliaridis]

Hey there (again), I have one more question about migration files and organizing those.

I was trying yesterday to find a way to configure atlas.hcl (under Windows) in a way that the following /migrations file structure could be found without adding the individual files:

/atlas.hcl
/migrations
├─ v0
│  ├─ 00_create_users_table.sql
│  ├─ 01_create_posts_table.sql
│  └─ ...
├─ v1
│  ├─ ...
│  └─ 17_update_indexes.sql
└─ atlas.sum

Below are the configurations I tried in atlas.hcl without success (validated everything with atlas migrate hash --env local and the output in atlas.sum):

• Setting env.src and/or env.migration.dir to file://migrations/*
• Setting env.src and/or env.migration.dir to ["file://migrations/v0", "file://migrations/v1"]
• Setting env.src and/or env.migration.dir to absolute path (Windows) file:///C:/Users/PATH/TO/PROJECT/migrations/v0 (only one directory)
• Setting env.src and/or env.migration.dir to relative path (Windows) ./migrations/v0 (only one directory)

I assume that for migrations, the env.migration.dir is the property I want to override, but I tried env.src too just for the sake of completeness.

What works is when I place the files in /migrations without sub-directories and execute atlas migrate hash. However, the env configurations are completely ignored, even if I provide --env local.

At the end, I was wondering if the properties were used, so I tried an empty string, but no error occurred (so they were ignored completely I guess?). Am I missing something here?

Here is the structure / content of the `atlas.hcl` file I used

env "local" {
  src = <the values mentioned above>

  // ...

  migration {
    dir = <the values mentioned above>
  }
}

[masseelch]

Hey @malliaridis, thank you for your questions, I will try to answer them 😊

Currently, Atlas does not support nested / recursive migration directories. Only *.sql files in the given path are considered, without scanning sub-directories. However, if you can share more details about your use case, we can consider adding support for this. I can think of either providing multiple paths like in your second try, or maybe support recursive scanning or using hcl functions to specify the glob pattern to use for file selection. For now I suggest you use a prefix for your migration files, e.g.:

/migrations
├─ v1.00_create_users_table.sql
├─ v1.01_create_posts_table.sql
├─ ...
└─ v2.17_update_indexes.sql

I assume that for migrations, the env.migration.dir* is the property I want to override, but I tried env.src` too just for the sake of completeness.

Your assumption is correct. src is used to specify the path (or multiple paths) to Atlas schema files (*.hcl).

However, the env configurations are completely ignored, even if I provide --env local.

If Atlas CLI does not find the configred env, it returns an error:

# atlas --env local migrate hash
Error: project file "atlas.hcl" was not found

# atlas --env locals migrate hash
Error: env "locals" not defined in project file

If this error does occur, but your configuration still does not work, you might have found a bug. Currently, we have no integration testing for windows set up (shame on us 🤦), so it might be a windows issue as well. If you find the time, can you send me a reproducible example repo, where I can check why the values of the configuration file are not used?

[malliaridis]

tl;dr It looks to be a bug under Windows when providing custom directories for migration files.

The background of my request is to be able to organize migrations scripts inside the migrations directory. As a software develops the requirements might change and so does the database. I was doing some courses from Liquibase to learn some basic concepts and get inspired for how to organize multiple migration files without loosing the overview over time.

Liquibase allows a master changelog.yml where you can put all your sub-directories like below:

# changelog.yml which is next to migrations/
databaseChangeLog:
  - includeAll:
      path: ./migrations/v0
  - includeAll:
      path: ./migrations/v1

This also takes into account all the supported formats (YAML, JSON, XML and SQL). I haven't tried to include a parent directory with sub-directories yet, but this might also be supported (not sure though).

If Atlas CLI does not find the configred env, it returns an error

That's correct, if the environment couldn't be found it throws an error, and that is the case for me too (under Windows and Mac).

I made the following observations under Windows with the Git Bash:

• Executing atlas migrate hash --env local without a migrations directory the following error message is displayed:

  Error: sql/migrate: CreateFile migrations: The system cannot find the file specified.

  Error message might be more accurate or the directory /migrations could theoretically be automatically created?

• Providing an empty /migrations directory atlas uses the default configuration and successfully creates the atlas.sum file

Additionally, what seems to be a bug is: No matter what is written inside env.migration.dir, atlas ignores every directory and uses the default /migrations, examples are

• file://migrations/v0
• file://C/ABSOLUTE/PATH/TO/PROJECT/migrations/v0
• file://C:/ABSOLUTE/PATH/TO/PROJECT/migrations/v0
• file:///C/ABSOLUTE/PATH/TO/PROJECT/migrations/v0
• file:///C:/ABSOLUTE/PATH/TO/PROJECT/migrations/v0
• file://~/migrations/v0
• file://C\\ABSOLUTE\\PATH\\WITH\\ESCAPED\\BACKSLASHES\\migrations\\v0
• file://C:\\ABSOLUTE\\PATH\\WITH\\ESCAPED\\BACKSLASHES\\migrations\\v0

atlas.hcl looks like follow:

env "local" {
  // ...
  migration {
    dir = "<directories from above>"
  }
}

I also tried migration and migrations, but no different result (nor an error message for invalid property).

It looks like the configuration is ignored under Windows, no matter which terminal I use (tested with cmd, powershell and git bash). Configuration with file://migrations/v0 works fine on Mac and fails when there is no directory found (like stated above), even if the v0 directory is missing. So it is very likely Windows-specific that the configuration is ignored.