What is recommended way to manage versioned migration with atlas?

[wangmir]

I've been looking for the doc for days to study atlas features. And there are quite a lot. But, because of that, I'm a little bit confused about what should I do with this.

I'm trying to establish versioned migration with atlas. We already have existing database, and we also have SQL script to initialize which can generate same schema as existing database.

And, It seems even when I use versioned migration (https://atlasgo.io/versioned/diff), Versioned Migration Authoring, which is based on the hcl schema and generate versioned migration script with tracking diff between former hcl & modified hcl, is recommended way to make the database as the declarative way in atlas.

So, these are my concerns,

1. With existing database

First of all, when we start from an existing database, how can I generate a "declarative schema" from this?
From this link I was able to generate baseline migration script at migrations/ directory.

But, how can we generate the declarative schema hcl file?

Is there any method to generate a schema ddl file (HCL) from the migration directory?

OR, should I use atlas schema inspect again to the database server to generate hcl file, and use that file for the declarative state of the database? (I think this sequence is a little bit awkward)

So, this is what I thought for the initial setup of atlas migration with an existing database.

1. Generate baseline migration script using existing database
2. Generate ddl schema using the migration directory
3. There is no diff between ddl and the migration directory for now.

But, in this case, 2 seem to be missing.

2. Who is responsible for generating the migration script?

Because doc mentioned about reviewability of versioned migration, I wanna make it clear about the sequence.

When I make a change on DDL file, who is responsible for generating the corresponding migration script?

1. The developer who requests a pull request to the github?
2. Github actions for CI which can check modification on the ddl file, and automatically generate migration script based on that?
3. Github actions for CD which will automatically apply modified schema. (I think it's not)

It seems 1, but in that case, if the developer accidentally misses the migration script for that, we need to compare ddl and migration directory to check the migration directory is properly set. But, when I see this section (https://atlasgo.io/declarative/diff), the only impossible comparison is between migration directory vs. ddl schema.

So, this is what I thought for the sequence of migration handling,

1. Developer changes ddl schema
2. Developer generate migration script using atlas migrate diff
3. Developer make PR
4. Github actions checks the PR for that ddl has no diff with the migration directory.

• If there are diff, then lint fail, if not, then pass

5. Pull request merged --> migration will be done to the server (when the corresponding server is actually bumped up)

But I'm not sure this is the right direction when I use atlas.

3. How can we synchronize declarative schema with manual migration script?

I saw this section (https://atlasgo.io/versioned/new), and it seems atlas migrate new cannot be managed with versioned migration authoring because it cannot be synced with ddl schema. Is it right?
If not, I think we still need (1) generating ddl file from migration directory & (2) compare btw. migration directory vs. ddl schema.

So, what I thought was,

1. generate manual migration script
2. generate new ddl schema based on the migration directory
3. make PR

but yes, I can't find a function to generate ddl schema from migration directory.

4. Difference between atlas schema apply & atlas migrate apply

What is difference between atlas schema apply & atlas migrate apply?? I think the functionality of two is quite similar but I can't find any comparison between two. atlas schema apply also can receive migration directory as input argument, so I'm confusing.

[rotemtam]

Hey @wangmir

Thanks for the great questions!

I'll try to answer them this evening.

[rotemtam]

First of all, when we start from an existing database, how can I generate a "declarative schema" from this?

The best way is to use atlas schema inspect -u <URL> to get the HCL representation.
If you prefer using a UI for this, you can use https://gh.atlasgo.cloud/explore:

• Paste the SQL representing your schema (be sure to select the correct database dialect)
• Click "Visualize"
• Use the select box to choose HCL
• Atlas Cloud will translate your SQL to HCL.

As this is a one time operation, I think both are good options.
Btw, you can also use an SQL file as the desired state of your database, if you prefer.

Who is responsible for generating the migration script?

At our company, the developer is responsible for generating migrations as part of the PR for the change their making.
We ensure there is no drift between the desired state by running atlas migrate diff followed by git status --porcelain to detect if something was forgotten, the full script looks like this:

atlas migrate diff check \
  --dir "file://${{ inputs.migration-dir }}" \
  --to "ent://${{ inputs.ent-schema }}" \
  --dev-url "${{ inputs.dev-database }}"
status=$(git status --porcelain)
if [ -n "$status" ]; then
  echo "missing migrations."
  echo "you need to run 'atlas migrate diff' and commit the changes"
  echo "$status"
  git --no-pager diff
  exit 1
fi

and it seems atlas migrate new cannot be managed with versioned migration authoring because it cannot be synced with ddl schema. Is it right?

atlas migrate new is a utility for creating new manual migration scripts (my favorite way of using it is with the --edit flag because it also re calculates the atlas.sum file when i save).

there is no automatic way of updating your desired state based on new scripts in the migration dir - the process is designed to work the other way around. however, this can be easily achieved by spinning up an empty database container, running atlas migrate apply on it and then atlas schema inspect.

notice that manual migrations are mostly useful for features not yet supported by Atlas (e.g views, data migrations etc).

What is difference between atlas schema apply & atlas migrate apply?? I

atlas schema apply is the declarative way of applying migrations. it does a diff between the desired state and the target database and suggests a plan. if that desired state is a migration directory, it generates a plan from the current state of the target database to the sum of all migrations in the directory, which may be very different from applying the directory from scratch.

atlas migrate apply is the versioned way - it will only execute in one order, and takes into account the revision table on the target database.

[wangmir]

Thanks for the detailed answer!! Now I feel I understand atlas much more evident.

[wangmir]

So, because I need to use views, and I also want to make SSOT (single source of truth) for database schema, it seems I need to maintain declarative schema with SQL rather than HCL because it does not support views yet. Right?

[wangmir]

And it seems, atlas schema inspect -u URL only supports inspection from actual database, not the SQL script. right?

But, in case of https://gh.atlasgo.cloud/explore there are multiple syntax error around that with PostgreSQL script. (e.g. OWNER)