9 ms·

Atlas – Terraform but for Database Migrations

237 points by wg0 5y ago

TameAntelope 5y ago
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.
- danielrhodes 5y ago
  You might find something like Hasura interesting. https://hasura.io/ https://hasura.io/ It is a GraphQL server that sits on top of your Postgres DB and the schema reflects the table schema. It's quite powerful right out of the box. If you combine that on the front-end with Apollo and a Typescript types code generator, you end up with strong typing all the way from your database to the front-end.
  - TameAntelope 5y ago
    I went with Prisma + Nexus + Pal.js + Apollo, which was probably at least two layers too complex. I'm looking, still, for an all-in-one solution to this, though I do need more control over my queries/mutations than what Hasura would give me.
    - hellcow 5y ago
      When faced with this problem, I wound up using normal SQL migrations to set the DB state, sqlc to generate the SQL boilerplate (https://sqlc.dev/ https://sqlc.dev/), and I wrote a little tool to generate the HTTP handler boilerplate for these tables. Not all-in-one, but this approach has been really effective in a large codebase.
    - sramam 5y ago
      (Apologies for going off-topic) I am working on a solution to simplify this piece of the puzzle and seeking feedback for an early version of the solution. Please DM me (email in profile) and we can set something up.
  - gedw99 5y ago
    there is an open source golang project that does the same thing called GraphJin. has GUi tooling also. https://github.com/dosco/graphjin https://github.com/dosco/graphjin You give it the Postresql script and you will see the new GraphQL schema in he GUI, ready to test. This means that is only one change point ever - the sql. It also supports subscriptions. for CI you can just put the script into your git tree and it will pick them up at the start of a deploy update.
- RedCrowbar 5y ago
  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/ https://www.edgedb.com/ [2] https://www.edgedb.com/docs/guides/migrations/index https://www.edgedb.com/docs/guides/migrations/index [3] https://www.edgedb.com/docs/guides/introspection/index https://www.edgedb.com/docs/guides/introspection/index [4] https://www.edgedb.com/docs/graphql/graphql https://www.edgedb.com/docs/graphql/graphql
redact207 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. 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.
ZiiS 5y ago
When documenting a migration tool, show an example of renaming a column before working on the logo.
- jiggawatts 5y ago
  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 need most help automating.” “Err…” “Thank your for your presentation, we may or may not be in touch.”
  - ewuhic 5y ago
    Which identity management tools have you had in mind btw?
    - jiggawatts 5y ago
      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.
brycelarkin 5y ago
Is there a reason for creating your own DDL? I imagine it’d be possible to use SQL DDL, introspect the database, and then generate the appropriate DDL for the migration. Does the Atlas DDL include more info over sql DDL?
hankchinaski 5y ago
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 diff between the expected state and the current state. So not sure why the custom language is needed
- metadat 5y ago
  It will be interesting to see if this approach is defensible and better than other strategies, but I’m not holding my breath. Learning HCL for Terraform is one of the worst parts, IMO. It’s like Dropwizard config language made a baby with JSON-but-not-quite-JSON. For now, I’m going to hold back on this particular time investment. P.s. Is this really another project called “Atlas”? The number of times I’ve encountered projects sharing this name over the course of my career is just depressing. Too generic to be any degree of informative or unambiguous, definitely wish it was called Schemmaform instead, a way better name, and took me all of 5 seconds to come up with.
- a8m 5y ago
  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).
kabes 5y ago
How does this compare to liquibase?
atombender 5y ago
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 and leave behind an invalid index that must be manually deleted.) Whether this must be done with "CONCURRENTLY" or not isn't something the tool can know. In some cases, a change must done in multiple steps. For example, if you change a column from "NULL" to "NOT NULL", you have to provide a default value. Updating the database with a default value can in fact be a huge operation that might even have to be done in multiple stages to avoid locking rows for too long. There's no way to easily express this in a DSL. M Then there's the database support. A tooo like this needs to support a huge range of features. Postgres has extensions (CREATE INDEX ... USING), special index operator classes, functional indexes, partitioning, and so on. I've seen many ORMs or SQL adapters (ActiveRecord/ARel, Squirrel, Goqu, Sequelize) try to be smart with how they let you build SQL from high-level code, but they all fail to cover all cases. (Recently I needed to do "ORDER BY CASE ... END" and was using Goqu, which has support for case expressions, but did not support sorting on them.) 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. I'm a fan of non-magical tools that let me just write SQL migrations, like dbmate and Goose, because that gives me full control. Having a tool to magically figure out the diff isn't super helpful, because I really need to know the diff myself when writing the migration, in order to make it predictable. It's simply more convenient to specify the order yourself.
- manigandham 5y ago
  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.
  - 5y ago
choward 5y ago
I 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 beneficial here to me. Sure, using it to modify the schema to the desired state is fine from just the schema perspective. But there is also data you have to worry about. Like someone else stated, the rename example is probably the most trivial example. How would the tool know if I wanted to delete one column and add another or if I wanted to rename the column? Another more complicated example is what if I had a column called created_at that stored a string and I wanted it to store a date instead. How would atlas know that just by changing the type? I might want it deleted and recreated. But if not, how would it know what format I stored the string in and how to parse it? For the rename problem, each column could have it's own id so if the id doesn't change it's a rename. Or it could ask while running the migrate command and then store those answers somewhere to be used when deploying to production which seems like a pain. Something like that might work for changing the data type too. You have two columns with the same name and different ids. The new column could have something to specify to transform the data from the old column. (I know it's a terrible idea to change the data type of a column in production, but the same idea applies if you want to copy the data converted to a new type to a new column, get your production code using that new column, then delete the other column.) Either way, this doesn't seem to by much over the classic migration approach. You still have to think about how the data is going to move or be manipulated. And with the classic approach they generate a schema so you can see the final state of your database and make sure it's what you want. With the atlas approach you have to approved the planned migration which is basically the same thing as making sure the schema generated is correct using the classic imperative approach.
- evanelias 5y ago
  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/ 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.
nhoughto 5y ago
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 simple sql migrations via flyway or similar, but if you got this working in theory and built the features it could do a better job on average than non expert humans, it could enforce patterns / naming / conventions / indexes etc. Could do with sql migrations too but would be more difficult as it wouldn’t have the full declared model.
- tianzhou 5y ago
  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-migration https://bytebase.com/doc/error#backward-incompatible-migrati... and plan to add those enforcement gradually as you pointed.
PudgePacket 5y ago
Seems very similar to https://www.prisma.io/migrate https://www.prisma.io/migrate.
theginger 5y ago
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.
deleted 5y ago
[deleted]
smrtinsert 5y ago
Sold 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.
marcc 5y ago
I’m the creator of https://schemahero.io 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!
- rotemtam 5y ago
  Hey! Sure, we'd love that :-) Ping us on our Atlas discord? https://discord.com/invite/QhsmBAWzrC https://discord.com/invite/QhsmBAWzrC
bdcravens 5y ago
Since they created their own JSONish DDL, why not build atop of DBML? (https://www.dbml.org/ https://www.dbml.org/)
uyaij 5y ago
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/ https://docs.djangoproject.com/en/4.0/topics/migrations/ [2] https://alembic.sqlalchemy.org/en/latest/autogenerate.html https://alembic.sqlalchemy.org/en/latest/autogenerate.html
pjungwir 5y ago
What I'd really like is Database Migrations but for Terraform: a way to maintain a history of imperatively-expressed changes with up/down functions for each one. The hard part is transactional "DDL". CloudFormation has this sort of, but it seems to break a lot.
Ultimatt 5y ago
This seems terrible. What about managing views, or stored procedures? AKA the actually complex stuff to manage migrations of.
- a8m 5y ago
  Views are going to be supported in the SQL-DDL version (see my comments below). I don't have any info about stored-procedures/triggers/events atm.
rossmohax 5y ago
Migration tool capable of handling background migrations will be a miracle we all have been waiting for. Example of background migrations is nicely explained in the gitlab handbook https://docs.gitlab.com/ee/development/post_deployment_migrations.html https://docs.gitlab.com/ee/development/post_deployment_migra...
daenney 5y ago
Though I understand where you're going with "Terraform for X", as someone who gets to deal with terraform on almost daily basis to manage infrastructure this makes me instantly want to head for the hills. Terraform is certainly useful and when it works it works. The problem is that when it doesn't, things have a tendency to go horribly wrong. The amount of hours saved by terraform are barely offset by the amount of time spent recovering broken production infrastructure or resolving state issues because it threw in the towel halfway through some operation. That's without even digging into plan and apply discrepancies. So, declarative schema migrations, yay, and market yourself that way. But I would be careful with being "terraform for X". There's a lot of terraform trauma among people who run production systems.
- rotemtam 5y ago
  Hi daenney (One of Atlas's creators here) Thanks a lot for the feedback. Frankly, we didn't make the Terraform analogy. We didn't even start this thread, just woke up to this wonderful discussion this morning :-) With regards to our marketing material, we only state that our DDL is terraform like because it is based on HCL.. I completely hear you on Terraform and it's pains and I've had my fair share of problems with it myself! This is why our approach to building Atlas is to offer two styles of migrations: declarative migrations (which is what you're seeing now) and a more advanced workflow (which I believe most mature projects will end up using) which we call migration authoring or versioned migrations. This second approach is similar to existing migration tools, with versioned SQL files, up/down direction, etc. In fact, it will work great out of the box with most existing migration tools (such as Flyway, Liquibase etc.) The main difference is that users will be able to use Atlas's fairly advanced migration planner and more advanced features that will be available later this year. So indeed, we're not a terraform for X, though i'm sure that title by whoever chose it brought this topic quite a bit of attention :-)
LittlePeter 5y ago
I use migra [https://databaseci.com/docs/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 able to handle foreign key definitions with ON UPDATE RESTRICT ON DELETE RESTRICT clause? Can it handle ID columns auto generate default always? Does it allow for constraints like check(airport_code = upper(airport_code))? The screenshots show rather simple SQL stuff. How
- majkinetor 5y ago
  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.
  - LittlePeter 5y ago
    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.
    - majkinetor 5y ago
      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.
      - mvc 5y ago
        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.
majkinetor 5y ago
It takes few days for one to create migration tool that uses SQL files using shell only (for example PoweShell being a serious language is awesome for this). I did that on number of projects and it works like a charm. I can create any type of ad hoc workflow developer needs. For example most of them want functions/types/sprocs recreated each time on each migration which is trivial to do in shell. Some of the devs had really wired but useful wishes that I was able to trivially easy do. Afterall, its just executing sql scripts 1 by 1, optionally with transaction started before that. Nobody ever used down migrations so not sure why people still do that. Shell is mandatory because I may want to do any number of things around migrations that require feedback from it (for example installing DLL files on Sql Server server or forcing backup just before migration).

237 points by wg0 5y ago

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.

danielrhodes 5y ago
You might find something like Hasura interesting. https://hasura.io/ https://hasura.io/ It is a GraphQL server that sits on top of your Postgres DB and the schema reflects the table schema. It's quite powerful right out of the box. If you combine that on the front-end with Apollo and a Typescript types code generator, you end up with strong typing all the way from your database to the front-end.
- TameAntelope 5y ago
  I went with Prisma + Nexus + Pal.js + Apollo, which was probably at least two layers too complex. I'm looking, still, for an all-in-one solution to this, though I do need more control over my queries/mutations than what Hasura would give me.
  - hellcow 5y ago
    When faced with this problem, I wound up using normal SQL migrations to set the DB state, sqlc to generate the SQL boilerplate (https://sqlc.dev/ https://sqlc.dev/), and I wrote a little tool to generate the HTTP handler boilerplate for these tables. Not all-in-one, but this approach has been really effective in a large codebase.
  - sramam 5y ago
    (Apologies for going off-topic) I am working on a solution to simplify this piece of the puzzle and seeking feedback for an early version of the solution. Please DM me (email in profile) and we can set something up.
- gedw99 5y ago
  there is an open source golang project that does the same thing called GraphJin. has GUi tooling also. https://github.com/dosco/graphjin https://github.com/dosco/graphjin You give it the Postresql script and you will see the new GraphQL schema in he GUI, ready to test. This means that is only one change point ever - the sql. It also supports subscriptions. for CI you can just put the script into your git tree and it will pick them up at the start of a deploy update.
RedCrowbar 5y ago
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/ https://www.edgedb.com/ [2] https://www.edgedb.com/docs/guides/migrations/index https://www.edgedb.com/docs/guides/migrations/index [3] https://www.edgedb.com/docs/guides/introspection/index https://www.edgedb.com/docs/guides/introspection/index [4] https://www.edgedb.com/docs/graphql/graphql https://www.edgedb.com/docs/graphql/graphql

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

ZiiS 5y ago

When documenting a migration tool, show an example of renaming a column before working on the logo.

jiggawatts 5y ago
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 need most help automating.” “Err…” “Thank your for your presentation, we may or may not be in touch.”
- ewuhic 5y ago
  Which identity management tools have you had in mind btw?
  - jiggawatts 5y ago
    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.

brycelarkin 5y ago

Is there a reason for creating your own DDL? I imagine it’d be possible to use SQL DDL, introspect the database, and then generate the appropriate DDL for the migration. Does the Atlas DDL include more info over sql DDL?

hankchinaski 5y ago

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 diff between the expected state and the current state. So not sure why the custom language is needed

metadat 5y ago
It will be interesting to see if this approach is defensible and better than other strategies, but I’m not holding my breath. Learning HCL for Terraform is one of the worst parts, IMO. It’s like Dropwizard config language made a baby with JSON-but-not-quite-JSON. For now, I’m going to hold back on this particular time investment. P.s. Is this really another project called “Atlas”? The number of times I’ve encountered projects sharing this name over the course of my career is just depressing. Too generic to be any degree of informative or unambiguous, definitely wish it was called Schemmaform instead, a way better name, and took me all of 5 seconds to come up with.
a8m 5y ago
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).

kabes 5y ago

How does this compare to liquibase?

atombender 5y ago

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 and leave behind an invalid index that must be manually deleted.) Whether this must be done with "CONCURRENTLY" or not isn't something the tool can know. In some cases, a change must done in multiple steps. For example, if you change a column from "NULL" to "NOT NULL", you have to provide a default value. Updating the database with a default value can in fact be a huge operation that might even have to be done in multiple stages to avoid locking rows for too long. There's no way to easily express this in a DSL. M Then there's the database support. A tooo like this needs to support a huge range of features. Postgres has extensions (CREATE INDEX ... USING), special index operator classes, functional indexes, partitioning, and so on. I've seen many ORMs or SQL adapters (ActiveRecord/ARel, Squirrel, Goqu, Sequelize) try to be smart with how they let you build SQL from high-level code, but they all fail to cover all cases. (Recently I needed to do "ORDER BY CASE ... END" and was using Goqu, which has support for case expressions, but did not support sorting on them.) 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. I'm a fan of non-magical tools that let me just write SQL migrations, like dbmate and Goose, because that gives me full control. Having a tool to magically figure out the diff isn't super helpful, because I really need to know the diff myself when writing the migration, in order to make it predictable. It's simply more convenient to specify the order yourself.

manigandham 5y ago
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.
- 5y ago

choward 5y ago

I 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 beneficial here to me. Sure, using it to modify the schema to the desired state is fine from just the schema perspective. But there is also data you have to worry about. Like someone else stated, the rename example is probably the most trivial example. How would the tool know if I wanted to delete one column and add another or if I wanted to rename the column? Another more complicated example is what if I had a column called created_at that stored a string and I wanted it to store a date instead. How would atlas know that just by changing the type? I might want it deleted and recreated. But if not, how would it know what format I stored the string in and how to parse it? For the rename problem, each column could have it's own id so if the id doesn't change it's a rename. Or it could ask while running the migrate command and then store those answers somewhere to be used when deploying to production which seems like a pain. Something like that might work for changing the data type too. You have two columns with the same name and different ids. The new column could have something to specify to transform the data from the old column. (I know it's a terrible idea to change the data type of a column in production, but the same idea applies if you want to copy the data converted to a new type to a new column, get your production code using that new column, then delete the other column.) Either way, this doesn't seem to by much over the classic migration approach. You still have to think about how the data is going to move or be manipulated. And with the classic approach they generate a schema so you can see the final state of your database and make sure it's what you want. With the atlas approach you have to approved the planned migration which is basically the same thing as making sure the schema generated is correct using the classic imperative approach.

evanelias 5y ago
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/ 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.

nhoughto 5y ago

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 simple sql migrations via flyway or similar, but if you got this working in theory and built the features it could do a better job on average than non expert humans, it could enforce patterns / naming / conventions / indexes etc. Could do with sql migrations too but would be more difficult as it wouldn’t have the full declared model.

tianzhou 5y ago
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-migration https://bytebase.com/doc/error#backward-incompatible-migrati... and plan to add those enforcement gradually as you pointed.

PudgePacket 5y ago

Seems very similar to https://www.prisma.io/migrate https://www.prisma.io/migrate.

theginger 5y ago

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.

deleted 5y ago

[deleted]

smrtinsert 5y ago

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

marcc 5y ago

I’m the creator of https://schemahero.io 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!

rotemtam 5y ago
Hey! Sure, we'd love that :-) Ping us on our Atlas discord? https://discord.com/invite/QhsmBAWzrC https://discord.com/invite/QhsmBAWzrC

bdcravens 5y ago

Since they created their own JSONish DDL, why not build atop of DBML? (https://www.dbml.org/ https://www.dbml.org/)

uyaij 5y ago

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/ https://docs.djangoproject.com/en/4.0/topics/migrations/ [2] https://alembic.sqlalchemy.org/en/latest/autogenerate.html https://alembic.sqlalchemy.org/en/latest/autogenerate.html

pjungwir 5y ago

What I'd really like is Database Migrations but for Terraform: a way to maintain a history of imperatively-expressed changes with up/down functions for each one. The hard part is transactional "DDL". CloudFormation has this sort of, but it seems to break a lot.

Ultimatt 5y ago

This seems terrible. What about managing views, or stored procedures? AKA the actually complex stuff to manage migrations of.

a8m 5y ago
Views are going to be supported in the SQL-DDL version (see my comments below). I don't have any info about stored-procedures/triggers/events atm.

rossmohax 5y ago

Migration tool capable of handling background migrations will be a miracle we all have been waiting for. Example of background migrations is nicely explained in the gitlab handbook https://docs.gitlab.com/ee/development/post_deployment_migrations.html https://docs.gitlab.com/ee/development/post_deployment_migra...

daenney 5y ago

Though I understand where you're going with "Terraform for X", as someone who gets to deal with terraform on almost daily basis to manage infrastructure this makes me instantly want to head for the hills. Terraform is certainly useful and when it works it works. The problem is that when it doesn't, things have a tendency to go horribly wrong. The amount of hours saved by terraform are barely offset by the amount of time spent recovering broken production infrastructure or resolving state issues because it threw in the towel halfway through some operation. That's without even digging into plan and apply discrepancies. So, declarative schema migrations, yay, and market yourself that way. But I would be careful with being "terraform for X". There's a lot of terraform trauma among people who run production systems.

rotemtam 5y ago
Hi daenney (One of Atlas's creators here) Thanks a lot for the feedback. Frankly, we didn't make the Terraform analogy. We didn't even start this thread, just woke up to this wonderful discussion this morning :-) With regards to our marketing material, we only state that our DDL is terraform like because it is based on HCL.. I completely hear you on Terraform and it's pains and I've had my fair share of problems with it myself! This is why our approach to building Atlas is to offer two styles of migrations: declarative migrations (which is what you're seeing now) and a more advanced workflow (which I believe most mature projects will end up using) which we call migration authoring or versioned migrations. This second approach is similar to existing migration tools, with versioned SQL files, up/down direction, etc. In fact, it will work great out of the box with most existing migration tools (such as Flyway, Liquibase etc.) The main difference is that users will be able to use Atlas's fairly advanced migration planner and more advanced features that will be available later this year. So indeed, we're not a terraform for X, though i'm sure that title by whoever chose it brought this topic quite a bit of attention :-)

LittlePeter 5y ago

I use migra [https://databaseci.com/docs/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 able to handle foreign key definitions with ON UPDATE RESTRICT ON DELETE RESTRICT clause? Can it handle ID columns auto generate default always? Does it allow for constraints like check(airport_code = upper(airport_code))? The screenshots show rather simple SQL stuff. How

majkinetor 5y ago
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.
- LittlePeter 5y ago
  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.
  - majkinetor 5y ago
    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.
    - mvc 5y ago
      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.

majkinetor 5y ago

It takes few days for one to create migration tool that uses SQL files using shell only (for example PoweShell being a serious language is awesome for this). I did that on number of projects and it works like a charm. I can create any type of ad hoc workflow developer needs. For example most of them want functions/types/sprocs recreated each time on each migration which is trivial to do in shell. Some of the devs had really wired but useful wishes that I was able to trivially easy do. Afterall, its just executing sql scripts 1 by 1, optionally with transaction started before that. Nobody ever used down migrations so not sure why people still do that. Shell is mandatory because I may want to do any number of things around migrations that require feedback from it (for example installing DLL files on Sql Server server or forcing backup just before migration).