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

51–60 of 92 posts

Re: Atlas – Terraform but for Database Migrations

#51

Post by rotemtam »

manigandham wrote: post #28
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…

Agree completely. Using tools to compare and catch all the changes between dev and live databases is fine, but experience has shown that having a handwritten SQL script is the best way to ensure the correct order of events, naming, transactions, features, etc. It also saves time in learning and maintaining an entirely separate configuration system.

Hi manigandham!

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

Re: Atlas – Terraform but for Database Migrations

#52

Post by rotemtam »

nhoughto wrote: post #31
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…

Yep is a big long tail to think about solving for, decades of features / idiosyncrasies in a complex domain.

Hi ngouto, One of Atlas's creators here.

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.

Re: Atlas – Terraform but for Database Migrations

#53

Post by capableweb »

hinkley wrote: post #32
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…

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?

You either create a new migration that migrates 53% back, or write a new migration for the rest.

The biggest error I see people do is coupling their DB migrations to their code deployments. Easiest way of handling this is to make each code version compatible with the DB schema before and after, and clean up the code after the migration was completed. Then you don't really care if the DB migration takes days to complete, nor if there are errors in the migration (unless you loose data obviously, then you're fucked). If the migration was wrong somehow, you can easily rollback the code as well and everything should still work, no need to rollback the migration just yet.

So most people seem to do migrations this way:

- Write migration file, commit to SCM

- When deploying the project, automatically run migration before starting application

- Wait for migration to finish, deploy code

What you could do to avoid issues like you mentioned:

- Write migration file to separate project

- Write code that works both with the version before applying the migration, and after

- Deploy new application code

- Apply migration when it suits you, application shouldn't care

- When confirmed it's working, clean up the code and deploy it again

This is only about the only way you can handle migrations that touches a lot of data and needs days to complete. But if you haven't reached that scale yet, your migrations are probably still coupled to your code deployments, which is probably fine in most scenarios, but can always be better :)

Re: Atlas – Terraform but for Database Migrations

#54

Post by 1500100900 »

evanelias wrote: post #43
tianzhou wrote: post #40

Earlier quoted context omitted.

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

Re: Atlas – Terraform but for Database Migrations

#55

Post by rotemtam »

parhamn wrote: post #24
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'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. To be fair, they analogized themselves to Terraform, which can go as far as deleting the database itself (and all the other infra that goes with it). As with anything a good dry-mode and a good process around review is how you minimize the risk.

Hi parhamn,

One of Atlas's creators here.

To be precise, we never analogized Atlas to Terraform :-) We said the existing HCL DDL is terraform like (which it is).

As far as I know, there are no heavily used terraform plugins for handling database migrations - and not because it's not possible.

The CLI currently exposes a declarative workflow (atlas schema apply), but we our analysis of the problem is that that declarative is not robust enough for many projects. For this reason, the Go API already support "versioned migrations" or "migration authoring" which means Atlas will generate migration files (SQL) and maintain the directory for you in the format that you like (Flyway, go-migrate etc).

In the very near future we will publish the migration authoring functionality to the CLI (you can already play with it via the Go package if you like).

Re: Atlas – Terraform but for Database Migrations

#56

Post by rotemtam »

marcc wrote: post #33

I’m the creator of https://schemahero.io , another utility that does something similar. I love seeing innovation in this space and congrats on building this. Atlas looks really cool, I’m going to check it out. Maybe we can chat about the challenges of building automated database schema migrations sometime!

Hey!

Sure, we'd love that :-)

Ping us on our Atlas discord?

https://discord.com/invite/QhsmBAWzrC

Re: Atlas – Terraform but for Database Migrations

#57

Post by rotemtam »

newusertoday wrote: post #50
a8m wrote: post #47

Earlier quoted context omitted.

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.

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

Re: Atlas – Terraform but for Database Migrations

#58

Post by lf-non »

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…

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.

Re: Atlas – Terraform but for Database Migrations

#59

Post by a8m »

newusertoday wrote: post #50
a8m wrote: post #47

Earlier quoted context omitted.

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.

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?

Re: Atlas – Terraform but for Database Migrations

#60

Post by jokethrowaway »

jrockway wrote: post #23
throwdbaaway wrote: post #19

Earlier quoted context omitted.

I thought the established wisdom is to make schema migration compatible with both the old app and the new app, whenever it is possible? E.g. you can safely add a nullable column, and it shouldn't trip up the old app nor the new app, unless you are using some crappy ORM that does "SELECT *", or you are on an older MySQL version that may take hours/days/weeks to rewrite the whole table just to add a nullable column. ht…

I think that's the established common wisdom, but it's not well-enforced by anything, and it's not innate. Everyone learns this the hard way once.

I would say a fair share of companies bent on following good practices do that. It's not all of them or not even the majority but I haven't seen cowboys migrations in 10+ years
Post reply on HN