Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Atlas – Terraform but for Database Migrations

atlasgo.io

Post reply on HN
Print view View original

41–50 of 92 posts

Re: Atlas – Terraform but for Database Migrations

#41

Post by tianzhou »

jrockway wrote: post #12
redact207 wrote: post #5

I'll never be comfortable with any tool for that automatically generates schema changes, as I'm just never sure at what point it decides to delete parts of my prod db. All of my migrations are dumb DDL statements rolled up into a version. I know exactly what the final state is as it gets run and used when integration testing, staging etc. It's boring but pretty bulletproof. I can rename a table and be confident it'll…

I feel the same way. I'd prefer to program an SQL database in SQL. What I personally do these days is to write migrations as normal (sql files for the "up" and "down" steps), and then have a `go generate` step that creates a randomly-named database, applies each migrations, and dumps the schema into a file that gets checked in. A test assures that the generated content is up to date with the rest of the repository. T…

>> In general, I think that database migrations are fundamentally flawed. The database schema and the application should have a schema version that they expect, and rules for translating between versions.

I have the same feeling, that could be a very desirable native feature for DB Engine, but no vendor seems to be interested in addressing that.

Re: Atlas – Terraform but for Database Migrations

#42

Post by tianzhou »

nhoughto wrote: post #16

Agree with general sentiment that creating a DSL/HCL like thing to essentially represent DDL commands seems less than ideal I wouldn’t want to be the author on the hook for never ending sql functions etc, you’d want a good escape hatch, same pain as hcl. But for a tool like this to add value (just like terraform) it’s got to understand the relationship graph, properties of resources etc. I’m a big believer in manual…

You may take a look at Bytebase.com. It's using plain SQL and has a web-based UI to do schema migration for team collaboration. Disclaimer: I am the author of it.

>> it could enforce patterns / naming / conventions / indexes etc.

That resonates with us as well. We already have backward-compatibility check https://bytebase.com/doc/error#backward-incompatible-migrati... and plan to add those enforcement gradually as you pointed.

Re: Atlas – Terraform but for Database Migrations

#43

Post by evanelias »

tianzhou wrote: post #40
evanelias wrote: post #35

Earlier quoted context omitted.

> 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. 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 t…

Facebook can do that because they have a homogeneous environment using only MySQL. And they have a strict engineering discipline and strong DBA team to limit the allowed schema structure. 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.

I probably should have disclosed in my previous comment that I'm a former member of Facebook's MySQL infra/automation team, although I didn't work on schema management there specifically.

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.

Re: Atlas – Terraform but for Database Migrations

#44

Post by evanelias »

satyrnein wrote: post #38
evanelias wrote: post #22

Earlier quoted context omitted.

Declarative schema management provides some nice properties that aren't present in imperative/migration-based tools. I'm the creator of a widely-used declarative tool for MySQL/MariaDB called Skeema, and I wrote a blog post summarizing some advantages a few years back: https://www.skeema.io/blog/2019/01/18/declarative/ You are correct that renames are problematic with declarative tools, but in production renames are…

I find Skeema pretty compelling, but I couldn't figure out one aspect: Say I'm adding a gender column to my employees table. I see how Skeema would be an alternative to running DDL in a migration. But I would end up still needing a migration to backfill the data. So I could use Skeema, but I still need migrations (we also occasionally fix data due to bugs, etc). At that point, we were less motivated to add an additio…

Currently, Skeema doesn't interact with DML or provide anything to help here. But that means you're free to use whatever solution you'd like, and Skeema won't get in your way, even if you store your DML in the same repo or even same subdirectories.

Part of the reason for this is that some of Skeema's users are quite large, and larger MySQL users already have in-house solutions for row data migrations. When your tables are huge and/or sharded, a data migration consists of a lot more complexity than just putting an UPDATE statement into a .sql file :)

I do agree it would be good to have some options in Skeema for DML at various scales, and it's something I plan to start approaching in the future, hopefully later this year. Overall my approach with Skeema's roadmap has been to first get DDL right for tables, then get DDL right for other types of objects (finally complete), and only then move on to considering automation for other areas (whether that be DML, or something like managing users/grants, database global variables, etc).

Re: Atlas – Terraform but for Database Migrations

#45

Post by uyaij »

Seems similar to Django[1] and SQLAlchemy automatic migrations[2], where you can modify your model definitions and derive the necessary SQL to emit.

Although I guess this tries to diff against the actual database state rather than previous known model state.

[1] https://docs.djangoproject.com/en/4.0/topics/migrations/ [2] https://alembic.sqlalchemy.org/en/latest/autogenerate.html

Re: Atlas – Terraform but for Database Migrations

#46

Post by RedCrowbar »

TameAntelope wrote: post #2

The dream for any kind of tool like this is to only have to define your data structures once, and use the tool to "generate" related code rather than try to keep two different artifacts in sync (e.g. a GraphQL schema and this DDL). How far along that path has this project progressed? I can't really tell from the main page.

Starting with an expressive declarative schema and building everything around it is exactly the approach we take in EdgeDB [1]. One of the key ideas is that you should be able to define almost anything not just statically, but as a result of some computation. Think views, functions, and computed columns, but without the traditional limitations and change-rigidity of their implementation in Postgres, because schema migrations are treated as a single logical change unit [2] rather than a bunch of independent DDL statements, so dependencies between schema objects and their changes are understood. The richness of the schema coupled with thorough introspection [3] then enables GraphQL schema derivation [4] and type-safe client generation without any loss in fidelity.

(disclaimer: I work on EdgeDB)

[1] https://www.edgedb.com/ [2] https://www.edgedb.com/docs/guides/migrations/index [3] https://www.edgedb.com/docs/guides/introspection/index [4] https://www.edgedb.com/docs/graphql/graphql

Re: Atlas – Terraform but for Database Migrations

#47

Post by a8m »

atombender wrote: post #13

In my experience, this sort of thing breaks down when you have a large production database that requires carefully crafted migrations to avoid affecting the existing system and taking too many locks. 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 an…

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...

Re: Atlas – Terraform but for Database Migrations

#48

Post by a8m »

hankchinaski wrote: post #9

Is it just me or I hate to learn new configuration languages that may disappear in no time after having invested time and effort learning them? Not just the syntax but all the nuances and the configuration options that come with it. If it was a migration tool using ANSI SQL as a configuration language I would have been happy to try it out. You can probably achieve the same declarative style by using SQL and running a…

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

At the moment, you can define schemas using Go (with a fluent API) or with HCL. The reason we decided to use HCL is because it can be easily extended, and our plans are to allow attaching metadata/annotations to schema objects (like k8s annotations) - more details in future versions.

Having said that, we understand we can't cover all features of every database in HCL, and that's why we work on allowing users to define their schemas using SQL DDLs (e.g. "CREATE TABLE", "CREATE INDEX", etc).

The apply/plan output is SQL, and we don't have plans to change it. However, the core engine is already smart enough to generate you a "reverse" command and tell you if a migration is "reversible" and "transactional". The next (minor) version is going to introduce "migration authoring". A way to generate a migration output to a directory and integrate it with tools like flyway or go-migrate, and also gives you suggestions to break migration to multiple steps if it's not transactional or reversible (like a DBA).

Re: Atlas – Terraform but for Database Migrations

#50

Post by newusertoday »

a8m wrote: post #47
atombender wrote: post #13

In my experience, this sort of thing breaks down when you have a large production database that requires carefully crafted migrations to avoid affecting the existing system and taking too many locks. 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 an…

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…

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.
Post reply on HN