[a8m]

Hey, I'm one of the atlas's creator. Thanks for the feedback.

I'm actually familiar with all the things you mentioned here (I worked at FB too ;)), and some of them are the reasons why we decided to create atlas and OSS it.

First, atm, we support HCL and Go (with fluent API) for describing schemas, but in the next versions, we'll add support for SQL DDLs (e.g. "CREATE TABLE", "CREATE INDEX", etc). Can't promise time estimation because it's in development, but that means we plan to OSS with it an SQL-parser infrastructure for the supported databases (can elaborate on that if you want).

Before I continue to migration authoring, I want to mention the reason we chose HCL (or Go). In next versions, we plan to support attaching "annotations" to schemas, like in k8s or in ent [1]. We these annotations, you'll be able to define privacy policy, or create integration to other tool. More details in the near future.

Now, migration authoring. The CLI does not expose all functionalities that are covered by the core engine atm, but when you run this tool (apply/plan) the output is a list of SQL statements. The core engine already knows to generate the "reverse" command for each statement (if it is reversible), and also a summary that indicates if the migration is "transactional" and "reversible" (see example [2]). Next version of atlas is going to support "migration authoring" - that means, instead of generate you list of statements and execute them (after approve), we'll let you the option to generate them to a directory, edit them, and integrate them with tools like flyway, go-migrate, etc.

In addition to that, the engine is also going to suggest you to break a migration plan to multiple steps (like a DBA) in order to make it transactional or reversible if it is not.

[1]: https://entgo.io

[2]: https://github.com/ariga/atlas/blob/master/sql/postgres/migr...

[newusertoday]

I have unrelated request since you are planning to add sql parser to your project. Would it be possible to have sql parser as seperate library? I am in need of sql parser and so far i have only been able to get parsers for specific dialects like pingcap parser for mysql. I think sql parser that can support multiple different sql dialects would be a great addition to golang ecosystem.

[a8m]

I agree with that as well. The idea is to create an infrastructure for SQL parsers. Base parser will hold all standard structure and dialects can register custom clauses/statements. At the moment, I generate PEG files for each dialect, but this creates too much duplicate code, and does not allow sharing same types/objects between different dialects.

I thought about keeping it on the same GitHub repository (https://github.com/ariga/atlas), but as a separate Go module? WDYT?

[newusertoday]

+1, separate go module looks good to me.

[tpetry]

That would be really usefull! And when the parser does not understand a language construct (e.g. a new swl fwature) let it fall back to some dumb parsing for that part.

If it does not under „LIMIT 5 WITH TIES“ let it parse „LIMIT 5“ in it‘s usefull abstraction and just provide two suffix keywords „WITH“ and „TIES“.

[rotemtam]

hey, Yes, indeed that's part of our plan!

If you want to chat about it, join us on our discord server? https://discord.com/invite/QhsmBAWzrC

[lf-non]

This sounds amazing. Thanks for creating this.

We have been using liquibase quite successfully (which provides automatic rollback support for most common operations) but have often wondered what it would be like to just define an entity model and have the migrations generated from the diff of that.

We used something that did this for JPA in past, but had to settle for yaml migrations for our node.js services. Its cool that this utility is language agnostic and uses HCL for its DSL (which is as easy to parse).

Looks the entity model would also be a good candidate for generating domain model classes (for a majority of tables anyways). Currently we use tbls to generate a yaml dump of the database schema after running migrations and use that for codegen. It works fairly well, but every now and then someone ends up using generated code for tables that were created through migrations in another branch, and that wastes time when things break.

[atombender]

To be clear, I'm not saying it cannot be done — just that it's hard, especially if it's a one-size-fits-all solution that needs to support many databases and SQL dialects, and that a tool like this is going to continually be fighting the various disparities that exist between databases.

I ran into an interesting challenge recently where it was necessary to replace the primary key. The only way to do this (with Postgres) on a live production database is drop the key and add it again in the same transaction, with a USING INDEX; so:

    CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS foo_new_pkey
      ON foo(bar);

    ALTER TABLE foo
      DROP CONSTRAINT foo_pkey CASCADE,
      ADD CONSTRAINT foo_pkey PRIMARY KEY
        USING INDEX foo_new_pkey;

There are some challenges to making a tool be able to do this seamlessly:

(1) The tool must understand that a modified primary key will need an existing index.

(2) It must understand that it has to be done in a single transaction.

(3) It must understand that this modifies the underlying catalogue: Postgres will rename the new "foo_new_pkey" index to "foo_pkey" and drop the old index.

And that's just Postgres. I bet other databases are different. Most databases don't even allow transactional DDL (e.g. Oracle and Microsoft SQL Server).

Does the amount of work required actually justify the end result? As I said earlier, developers need to understand migrations and their ordering in order to be able to plan their rollout. If a system cannot craft and "orchestrate" migrations perfectly — and I argue that this is infeasible without tons of work — then this means engineers have to run the tool and examine the output and understand it before rolling it out anyway. So now you have a smart, complex tool to learn that doesn't even do the whole job. And in the case of complex migrations it might not even do all of it, requiring the suggested SQL output to be tweaked before it can be run. You might as well just write migrations by hand, then.

To be clear, I think it's good to be ambitious, I'm just generally skeptical for the above reasons.

Last point: Having an SQL parser in Go would be great, so kudos if you manage to build this. Again, I think you will be fighting here to stay up to date with all the dialects, but it's a worthy goal.

[derekperkins]

You might coordinate with the Vitess team to see if there's any interest in sharing code on the parser.