I feel like the name atlas is going to get into a bit of conflict with mongoDB who's hosted db products are called atlas.
Atlas – Terraform but for Database Migrations
21–30 of 92 posts
Re: Atlas – Terraform but for Database Migrations
#22I wish people would explain why they created their project and what their pain points were with existing alternatives. All I can find is "Contrary to existing tools, Atlas intelligently plans schema migrations for you, based on your desired state." What's so bad about writing an explicit migrations using something like Flyway? I'm a fan of declarative configuration for the most part but it doesn't seem that beneficia…
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 problematic in general because of deploy-order concerns. Best practice with schema changes is always for applications to be able to work fine with both the old and new schema, and renames typically break this, as most ORMs / database interface layers don't support this.
So renames already require a special process at companies that even allow renames in production (in my experience, many do not). Skeema treats them as an out-of-band activity; it gets out of your way and you handle the rename outside of the tool, and then can use Skeema to update your repo afterwards. If you accidentally try to do a rename inside the tool, it will treat it as a drop-and-create, but it prevents the push from proceeding since it detects it is destructive.
Re: Atlas – Terraform but for Database Migrations
#23Earlier quoted context omitted.
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 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.
Re: Atlas – Terraform but for Database Migrations
#24I'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.
Re: Atlas – Terraform but for Database Migrations
#25Earlier 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.
Right, I did have to learn this the hard way back in 2014.
Re: Atlas – Terraform but for Database Migrations
#26Earlier quoted context omitted.
This is always my only question when the salesperson is finished pitching some new provisioning or identity management tool. “Can I rename users when they change their name?” “Does that happen that often?” “Half the staff here are female and may change their surname when they get married.” “Err.. umm… I think that’s a work in progress.” “It’s fundamentally impossible with your architecture and it’s the thing that we…
Which identity management tools have you had in mind btw?
Over the years I've seen a lot of presentations from a lot of companies for all sorts of automation tools, especially identity management. Think "new user onboarding" for large enterprise.
To be honest, I can't remember the names of any of the products. If it wasn't obvious from my rant, we didn't call any of them back and I promptly forgot about the specific vendors and their products.
Most such products are utterly worthless, typically with net negative value. They can't handle even moderately complex cases like name changes or users moving from one department from another. The more complex cases like staff holding multiple positions at once, or filling in temporarily for someone are just out of the question.
Conversely, they're power tools and can wipe out your entire staff directory if you make the tiniest mistake. Few such tools have common sense "safety nets" built in by default.
Last but not least, all such tools these days carefully block any form of end-user extensibility. That way they can charge for product-specific plugins. Most such plugins are trivial to write, so you could do it yourself. Hence the vendors have to block all APIs or script-based interfaces lest you take their profit margin away from them by spending an afternoon whipping up some PowerShell scripts.
Re: Atlas – Terraform but for Database Migrations
#27[deleted]
Re: Atlas – Terraform but for Database Migrations
#28In 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.
Re: Atlas – Terraform but for Database Migrations
#29I'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.
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!
Re: Atlas – Terraform but for Database Migrations
#30Sold on the concept immediately. Migrations have always been a ridiculous concept to me. Let me describe the state I want and you write the annoying scripts that are essentially ephemeral.