Tools can be smart enough -- it just requires a lot of work and domain expertise. For example, Facebook has used declarative schema management company-wide for over a decade, to manage schema changes for what is likely the largest MySQL fleet in the world.
Then again, maybe this is an area where MySQL's DDL limitations actually makes tooling more possible: there's no transactional DDL in MySQL, so you can't get real clever with ordering anyway. And historically companies don't actually use ALTER TABLE with large MySQL tables; instead they use an external online schema change (OSC) tool which builds a shadow table and then swaps it. Since the OSC tool isn't actually running an ALTER directly on the original table, the exact ALTER is conceptually not necessary, if the OSC tool just takes the desired state as a CREATE instead (as fb-osc does).
That said, I agree with the sentiment in this subthread that it's a complex domain with a lot of idiosyncrasies. I tend to raise an eyebrow when DB tools use generic DSLs and try to handle a lot of different database vendors, as it's extremely rare (almost unheard of...) for a tool's authors to be deeply well-versed in large-scale production experience of many different DBMS. The various communities around Postgres, MySQL, SQL Server, and Oracle tend not to have a lot of overlap at the expert level.
Not so much for other companies.
Disclaimer: I don't work for Facebook, but I used to work at Google and build its Cloud SQL service.
However, subsequent to FB, I independently built the declarative schema management tool skeema.io which is used by several large well-known companies. So Atlas is a competitor, and I may inherently be biased in my skepticism of tools that use DSLs to generically handle multiple DBMS. (It sounds like we are in agreement there though, regarding your comment on FB's all-MySQL environment.)
Anyway, in my previous comment, my point was that it is certainly possible to build trustworthy declarative schema management tools. It's difficult and requires a lot of effort, but it is not impossible.
Facebook didn't limit schema structure too much, btw. One of my main projects there was building the in-house DBaaS interface, which was used for all sorts of things across the company, quite a wide variety of workloads and table designs. iirc the only major table structure limitations were: you must have a primary key; you must not use foreign key constraints; you must use InnoDB (or later MyRocks, but that was after my time). These are fairly common requirements among other large MySQL-backed companies though, definitely not specific to Facebook.
FWIW, google does take the same approach as skeema to describe the desired schema state with pure SQL. But it's the Google Spanner does the heavy lifting. And that adds more respect for your work on skeema, since skeema is doing the heavy lifting from the tooling side, which in my opinion is more challenge.
BTW, I also need to disclose that I am currently building bytebase.com which is also a schema migration tool.
Unless the schema is self-modifying. Then obviously the database schema definition is the only source of truth.
The overall topic in this subthread was whether or not declarative schema management is appropriate and safe for "real" applications, as compared to traditional migration (imperative) schema management, for popular relational database systems.
If you're referring to exclusively using stored procs to generate dynamic tables or something goofy like that, then sure -- in that case you can't use any external schema management at all, whether declarative or imperative. But that doesn't mean that declarative schema management isn't a safe or possible approach for everyone else.
I don't know about declarative vs imperative. I can see "versioned migrations" are "coming soon", so maybe that could work. Depending on what it will be.
Technically, there's nothing prevent them doing that. It's probably just priority conflicts. A bit sigh though since it's a worthwhile feature IMO.
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...
I thought about keeping it on the same GitHub repository (https://github.com/ariga/atlas), but as a separate Go module? WDYT?
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“.
If you want to chat about it, join us on our discord server? https://discord.com/invite/QhsmBAWzrC
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.
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;
(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.
It also saves time in learning and maintaining an entirely separate configuration system.
One of Atlas's creators here. Thanks for the feedback!
I completely understand your point here, and I would add that for many use cases the declarative approach isn't robust enough for schema migrations. The classic example being, how does a tool discern between a column drop/add and a rename, and how does the migration tool allow for backward-compatible schema evolution?
For this reason, as you can see on https://atlasgo.io, we are going to publish to the CLI a different style of migration which we call "versioned migrations", that support the process of "migration authoring". This already exists in the Go API so if you want to delve into it on your own you can, but it will be out as part of the CLI really soon.
Migration authoring means that you modify your desired schema, and the tool generates a possible migration for you. In cases where there is ambiguity (multiple ways to reach state B from A), the tool may interactively prompt you for decisions.
So hopefully with Atlas you can get the best of both ways. Have an intelligent engine help you author the migration for you, but ultimately you get an SQL file you can edit, review in CR, and use your existing migration tools with (Flyway, Liquibase, etc.)
Feel free to join our discord channel (https://discord.com/invite/QhsmBAWzrC) if you want to chat more :-)
R
Indeed it is. We founded Ariga (the company that's maintaining Atlas) to build this kind of infrastructure which we feel is missing from the data infra/platform engineering landscape. Aside from our existing love for open-source (we maintain https://entgo.io), we are building Atlas in the open because we understand the scope of the problem and it's long-tail characteristics and realize that it will have to be a community effort.
For example, with Postgres some indexes have to be performed with "CREATE INDEX CONCURRENTLY" or "DROP INDEX CONCURRENTLY", which cannot be done inside a transaction. (Also, a "CREATE INDEX" like this can fail halfway and leave behind an invalid index that must be manually deleted.) Whether this must be done with "CONCURRENTLY" or not isn't something the tool can know.
In some cases, a change must done in multiple steps. For example, if you change a column from "NULL" to "NOT NULL", you have to provide a default value. Updating the database with a default value can in fact be a huge operation that might even have to be done in multiple stages to avoid locking rows for too long. There's no way to easily express this in a DSL. M
Then there's the database support. A tooo like this needs to support a huge range of features. Postgres has extensions (CREATE INDEX ... USING), special index operator classes, functional indexes, partitioning, and so on. I've seen many ORMs or SQL adapters (ActiveRecord/ARel, Squirrel, Goqu, Sequelize) try to be smart with how they let you build SQL from high-level code, but they all fail to cover all cases. (Recently I needed to do "ORDER BY CASE ... END" and was using Goqu, which has support for case expressions, but did not support sorting on them.)
So while a tool like this might be good when you're just starting out, for a "real" app you want to avoid this sort of automation, because the tool is almost certainly not going to be smart enough.
I'm a fan of non-magical tools that let me just write SQL migrations, like dbmate and Goose, because that gives me full control. Having a tool to magically figure out the diff isn't super helpful, because I really need to know the diff myself when writing the migration, in order to make it predictable. It's simply more convenient to specify the order yourself.