8 ms·

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 p

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 work, rather than some tool that might decide to drop and create a new table. I can also continue to use SQL and not have to learn HCl for databases. This is useful when I want to control how updates are done, if I want index updates to be done concurrently Vs locking the table.

jrockway 5y ago
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. This gives you 3 things: 1) PR reviewers can see what your migration does to the database in an easy-to-read diff. (A tiny bit of regexing needs to be done to make PostgreSQL dumps compatible between machines; it puts the OS name and the build time in there.) 2) You have the full database schema in an easy-to-read form. I open the SQL dump all the time to remind myself of what fields are named, what the default values are, etc. 3) Unit tests against the database are faster. Instead of creating a new database and applying 100s of migrations, you just apply one dump file. Takes milliseconds. 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. That way, you could upgrade your code to something that reads and writes "version 2", but understands "version 3", and then apply the database migration at your leisure, and update the code to start writing "version 3" records. But, nobody does this. They just cross their fingers and hope they don't have to roll back the migration. And honestly, I don't think I've ever had to roll back a migration, because they're so scary that you test it a billion times before it ever makes it to production. But, that testing-a-billion-times comes at the cost of writing new features, and the team that only tests it 999 million times no doubt has a horror story or two.
- throwdbaaway 5y ago
  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. https://github.com/fabianlindfors/reshape https://github.com/fabianlindfors/reshape, on HN front page a couple weeks ago, has some nice tricks to help with the incompatible cases.
  - jrockway 5y ago
    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.
    - throwdbaaway 5y ago
      Right, I did have to learn this the hard way back in 2014.
    - jokethrowaway 5y ago
      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
  - kodah 5y ago
    You can get by on that "wisdom" for a while, but eventually your DB will garner a significant size and performance impact as a result. That said, even on very active products that takes a while, so there's probably something to blending the idea of one large migration every so often and only making schema compatible changes along the way.
- tianzhou 5y ago
  >> 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.
parhamn 5y ago
> 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.
- sverhagen 5y ago
  That's why we try to set our databases to deletion_protection=true, and make it hard to delete them. But your point still largely stands!
  - paulgb 5y ago
    Interestingly, that's the default for GCP but not AWS, even though both providers are developed by Hashicorp. I was pleasantly surprised by how difficult it was to (even intentionally) delete my GCP database when I was starting to use terraform.
    - nijave 5y ago
      Terraform usually use cloud provider defaults if they're applicable. There's also 2 deletion protection mechanisms for RDS (and maybe GCP DBs). AWS has built in deletion protection which is an attribute on the resource and Terraform has lifecycle protection which is a meta attribute you can put on any resource type. The former disables the delete API (returns an error) on the AWS side and the latter prevents Terraform from running the destroy event (which includes replaces)
- rotemtam 5y ago
  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).
hinkley 5y ago
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?
- capableweb 5y ago
  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 :)
- nijave 5y ago
  >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.
  - hinkley 5y ago
    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.
jayd16 5y ago
Its not that bullet proof. It still runs into issues with version control type conflicts made by multiple developers/branches. Something like FlywayDB can at least spot these problems but not prevent them. Not sure if any of the migration tooling can handle multiple branches or unordered changes.
rotemtam 5y ago
hey redact207 Thanks for the feedback! (one of Atlas's creators here). First of all, I completely understand you. Good ol' SQL has been with us for decades and isn't going anywhere. In the very near future you will be able to work with Atlas in pure SQL in a few ways: 1. Use a command like `atlas schema diff` (API not final) to generate the diff SQL for you, that you can then edit and use with your favorite tools. 2. Use a CREATE TABLE statement instead of HCL for your desired schema. 3. Use Atlas with a workflow that we call "migration authoring" to author for your the migrations into your <favorite migration tool> directory format. Second, I'd say that as much as I'd like it to be ubiquitous knowledge, many product teams don't have anyone on board with expertise like you probably have in planning migrations well, and so they can benefit greatly from a tool that will embed well tested and researched "DBA" knowledge into migration planning. The amount of outages I've heard about that are related to migrations that were ill planned is enormous. Many have commented on this thread that it's a huge topic with a lot of subtleties and that it will be very hard to build a tool that does this well. I agree with that sentiment, but we built a strong team here at Ariga, and I hope together with the OSS community we will build something remarkable.
grogers 5y ago
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.

jrockway 5y ago

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. This gives you 3 things: 1) PR reviewers can see what your migration does to the database in an easy-to-read diff. (A tiny bit of regexing needs to be done to make PostgreSQL dumps compatible between machines; it puts the OS name and the build time in there.) 2) You have the full database schema in an easy-to-read form. I open the SQL dump all the time to remind myself of what fields are named, what the default values are, etc. 3) Unit tests against the database are faster. Instead of creating a new database and applying 100s of migrations, you just apply one dump file. Takes milliseconds. 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. That way, you could upgrade your code to something that reads and writes "version 2", but understands "version 3", and then apply the database migration at your leisure, and update the code to start writing "version 3" records. But, nobody does this. They just cross their fingers and hope they don't have to roll back the migration. And honestly, I don't think I've ever had to roll back a migration, because they're so scary that you test it a billion times before it ever makes it to production. But, that testing-a-billion-times comes at the cost of writing new features, and the team that only tests it 999 million times no doubt has a horror story or two.

throwdbaaway 5y ago
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. https://github.com/fabianlindfors/reshape https://github.com/fabianlindfors/reshape, on HN front page a couple weeks ago, has some nice tricks to help with the incompatible cases.
- jrockway 5y ago
  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.
  - throwdbaaway 5y ago
    Right, I did have to learn this the hard way back in 2014.
  - jokethrowaway 5y ago
    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
- kodah 5y ago
  You can get by on that "wisdom" for a while, but eventually your DB will garner a significant size and performance impact as a result. That said, even on very active products that takes a while, so there's probably something to blending the idea of one large migration every so often and only making schema compatible changes along the way.
tianzhou 5y ago
>> 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.

parhamn 5y ago

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

sverhagen 5y ago
That's why we try to set our databases to deletion_protection=true, and make it hard to delete them. But your point still largely stands!
- paulgb 5y ago
  Interestingly, that's the default for GCP but not AWS, even though both providers are developed by Hashicorp. I was pleasantly surprised by how difficult it was to (even intentionally) delete my GCP database when I was starting to use terraform.
  - nijave 5y ago
    Terraform usually use cloud provider defaults if they're applicable. There's also 2 deletion protection mechanisms for RDS (and maybe GCP DBs). AWS has built in deletion protection which is an attribute on the resource and Terraform has lifecycle protection which is a meta attribute you can put on any resource type. The former disables the delete API (returns an error) on the AWS side and the latter prevents Terraform from running the destroy event (which includes replaces)
rotemtam 5y ago
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).

hinkley 5y ago

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?

capableweb 5y ago
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 :)
nijave 5y ago
>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.
- hinkley 5y ago
  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.

jayd16 5y ago

Its not that bullet proof. It still runs into issues with version control type conflicts made by multiple developers/branches. Something like FlywayDB can at least spot these problems but not prevent them. Not sure if any of the migration tooling can handle multiple branches or unordered changes.

rotemtam 5y ago

hey redact207 Thanks for the feedback! (one of Atlas's creators here). First of all, I completely understand you. Good ol' SQL has been with us for decades and isn't going anywhere. In the very near future you will be able to work with Atlas in pure SQL in a few ways: 1. Use a command like `atlas schema diff` (API not final) to generate the diff SQL for you, that you can then edit and use with your favorite tools. 2. Use a CREATE TABLE statement instead of HCL for your desired schema. 3. Use Atlas with a workflow that we call "migration authoring" to author for your the migrations into your <favorite migration tool> directory format. Second, I'd say that as much as I'd like it to be ubiquitous knowledge, many product teams don't have anyone on board with expertise like you probably have in planning migrations well, and so they can benefit greatly from a tool that will embed well tested and researched "DBA" knowledge into migration planning. The amount of outages I've heard about that are related to migrations that were ill planned is enormous. Many have commented on this thread that it's a huge topic with a lot of subtleties and that it will be very hard to build a tool that does this well. I agree with that sentiment, but we built a strong team here at Ariga, and I hope together with the OSS community we will build something remarkable.

grogers 5y ago

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.