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

81–90 of 92 posts

Re: Atlas – Terraform but for Database Migrations

#81

Post by evanelias »

1500100900 wrote: post #54
evanelias wrote: post #43

Earlier quoted context omitted.

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 tool…

> 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. Unless the schema is self-modifying. Then obviously the database schema definition is the only source of truth.

Can you provide an example of what you're referring to?

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.

Re: Atlas – Terraform but for Database Migrations

#82

Post by grogers »

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…

You might like liquibase. It's basically exactly what you describe - each changeset has a forward and rollback, written in SQL. It stores which changesets have been run in a special table in the DB itself.

Re: Atlas – Terraform but for Database Migrations

#83

Post by atombender »

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…

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.

Re: Atlas – Terraform but for Database Migrations

#84

Post by 1500100900 »

evanelias wrote: post #81
1500100900 wrote: post #54

Earlier quoted context omitted.

> 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. Unless the schema is self-modifying. Then obviously the database schema definition is the only source of truth.

Can you provide an example of what you're referring to? 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…

Yes, something goofy like that.

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.

Re: Atlas – Terraform but for Database Migrations

#85

Post by jen20 »

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…

> The database schema and the application should have a schema version that they expect, and rules for translating between versions.

That… is migrations. The rules for translating between versions are literally what migrations do.

Re: Atlas – Terraform but for Database Migrations

#86

Post by LittlePeter »

majkinetor wrote: post #75
LittlePeter wrote: post #72

I use migra [ https://databaseci.com/docs/migra ] for PostgreSQL. I only put schema.sql into git, no migration scripts. To make changes, I update schema.sql and load it up into temporary db. Then I run migra prod_db temp_db and it spits out the SQL statements that take me from the old schema.sql (which was in production) to the new. I eyeball the statements and if they look good, I apply them to prod_db. Is Atlas abl…

You eyeball the statements? Thats 100% unreliable, I'll just wait for your sugar to go down while you do that. Migration that is not automatic is not an option.

Yes, I eyeball the migration code. You could call it code review, whatever. There is no way I am running migration code automatically. In fact I am not sure what you mean by "automatically"? As in a non-human triggers the migration? But do you review the code?

First of all, migrations do not happen in my case that often that I need them to run unattended/automatically.

Second, I don't trust any migration system to spit out flawless code. Maybe I have trust issues.

Re: Atlas – Terraform but for Database Migrations

#87

Post by majkinetor »

LittlePeter wrote: post #86
majkinetor wrote: post #75

Earlier quoted context omitted.

You eyeball the statements? Thats 100% unreliable, I'll just wait for your sugar to go down while you do that. Migration that is not automatic is not an option.

Yes, I eyeball the migration code. You could call it code review, whatever. There is no way I am running migration code automatically. In fact I am not sure what you mean by "automatically"? As in a non-human triggers the migration? But do you review the code? First of all, migrations do not happen in my case that often that I need them to run unattended/automatically. Second, I don't trust any migration system to sp…

If migrations are rare, you are supporting old system.

Migrations are there every few days on my projects. Non automatic migration is IMO not a migration but database intervention, just as non-automatic tests are not really tests.

Re: Atlas – Terraform but for Database Migrations

#88

Post by hinkley »

nijave wrote: post #79
hinkley wrote: post #32

Earlier quoted context omitted.

It doesn't even have to delete anything. What happens when it gets 53% of the way through whatever it's doing at it errors out? Now wtf do I do?

>What happens when it gets 53% of the way through whatever it's doing at it errors out? That tends to be the norm with Terraform, too :) If it's like Terraform, you still need to know exactly what the tool is doing and how the services behave under the hood.

God yes. I confidently pushed 'apply' in production a couple of times before I ever encountered these problems. Luckily it blew chunks in the new data center we were trying to set up. Yes, an app is more likely to start cleanly when all of its dependencies are up and running, but still.

So much for Ops setting expectations.

Re: Atlas – Terraform but for Database Migrations

#89

Post by smrtinsert »

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…

I have never seen a db rollback. Once you get into prod you're capturing data. A roll ack could lose data. I've only seen forward migrations to fix issues.

Re: Atlas – Terraform but for Database Migrations

#90

Post by mvc »

majkinetor wrote: post #87
LittlePeter wrote: post #86

Earlier quoted context omitted.

Yes, I eyeball the migration code. You could call it code review, whatever. There is no way I am running migration code automatically. In fact I am not sure what you mean by "automatically"? As in a non-human triggers the migration? But do you review the code? First of all, migrations do not happen in my case that often that I need them to run unattended/automatically. Second, I don't trust any migration system to sp…

If migrations are rare, you are supporting old system. Migrations are there every few days on my projects. Non automatic migration is IMO not a migration but database intervention, just as non-automatic tests are not really tests.

This is the first I'd heard of migra but I can definitely imagine a solution involving piping the schema-diff between two databases (e.g. uat and local-dev) into some file that eventually gets applied in prod.

Seems easier than manually creating diffs which is what existing automatated migration tools seem to want you to do.

I love that it's just the diff. Makes it easier to integrate into cloud-native pipelines.

Post reply on HN