Atlas – Terraform but for Database Migrations(atlasgo.io)
atlasgo.io
Atlas – Terraform but for Database Migrations
https://atlasgo.io/
91 comments
> 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.
Tools can be smart enough -- it just requires a lot of work and domain expertise. For example, Facebook has used declarative schema management company-wide for over a decade, to manage schema changes for what is likely the largest MySQL fleet in the world.
Then again, maybe this is an area where MySQL's DDL limitations actually makes tooling more possible: there's no transactional DDL in MySQL, so you can't get real clever with ordering anyway. And historically companies don't actually use ALTER TABLE with large MySQL tables; instead they use an external online schema change (OSC) tool which builds a shadow table and then swaps it. Since the OSC tool isn't actually running an ALTER directly on the original table, the exact ALTER is conceptually not necessary, if the OSC tool just takes the desired state as a CREATE instead (as fb-osc does).
That said, I agree with the sentiment in this subthread that it's a complex domain with a lot of idiosyncrasies. I tend to raise an eyebrow when DB tools use generic DSLs and try to handle a lot of different database vendors, as it's extremely rare (almost unheard of...) for a tool's authors to be deeply well-versed in large-scale production experience of many different DBMS. The various communities around Postgres, MySQL, SQL Server, and Oracle tend not to have a lot of overlap at the expert level.
Tools can be smart enough -- it just requires a lot of work and domain expertise. For example, Facebook has used declarative schema management company-wide for over a decade, to manage schema changes for what is likely the largest MySQL fleet in the world.
Then again, maybe this is an area where MySQL's DDL limitations actually makes tooling more possible: there's no transactional DDL in MySQL, so you can't get real clever with ordering anyway. And historically companies don't actually use ALTER TABLE with large MySQL tables; instead they use an external online schema change (OSC) tool which builds a shadow table and then swaps it. Since the OSC tool isn't actually running an ALTER directly on the original table, the exact ALTER is conceptually not necessary, if the OSC tool just takes the desired state as a CREATE instead (as fb-osc does).
That said, I agree with the sentiment in this subthread that it's a complex domain with a lot of idiosyncrasies. I tend to raise an eyebrow when DB tools use generic DSLs and try to handle a lot of different database vendors, as it's extremely rare (almost unheard of...) for a tool's authors to be deeply well-versed in large-scale production experience of many different DBMS. The various communities around Postgres, MySQL, SQL Server, and Oracle tend not to have a lot of overlap at the expert level.
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.
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 tools that use DSLs to generically handle multiple DBMS. (It sounds like we are in agreement there though, regarding your comment on FB's all-MySQL environment.)
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.
Facebook didn't limit schema structure too much, btw. One of my main projects there was building the in-house DBaaS interface, which was used for all sorts of things across the company, quite a wide variety of workloads and table designs. iirc the only major table structure limitations were: you must have a primary key; you must not use foreign key constraints; you must use InnoDB (or later MyRocks, but that was after my time). These are fairly common requirements among other large MySQL-backed companies though, definitely not specific to Facebook.
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 tools that use DSLs to generically handle multiple DBMS. (It sounds like we are in agreement there though, regarding your comment on FB's all-MySQL environment.)
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.
Facebook didn't limit schema structure too much, btw. One of my main projects there was building the in-house DBaaS interface, which was used for all sorts of things across the company, quite a wide variety of workloads and table designs. iirc the only major table structure limitations were: you must have a primary key; you must not use foreign key constraints; you must use InnoDB (or later MyRocks, but that was after my time). These are fairly common requirements among other large MySQL-backed companies though, definitely not specific to Facebook.
You are right we are actually from the same clan which prefers pure SQL over another DSL.
FWIW, google does take the same approach as skeema to describe the desired schema state with pure SQL. But it's the Google Spanner does the heavy lifting. And that adds more respect for your work on skeema, since skeema is doing the heavy lifting from the tooling side, which in my opinion is more challenge.
BTW, I also need to disclose that I am currently building bytebase.com which is also a schema migration tool.
FWIW, google does take the same approach as skeema to describe the desired schema state with pure SQL. But it's the Google Spanner does the heavy lifting. And that adds more respect for your work on skeema, since skeema is doing the heavy lifting from the tooling side, which in my opinion is more challenge.
BTW, I also need to disclose that I am currently building bytebase.com which is also a schema migration 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.
Unless the schema is self-modifying. Then obviously the database schema definition is the only source of truth.
Can you provide an example of what you're referring to?
The overall topic in this subthread was whether or not declarative schema management is appropriate and safe for "real" applications, as compared to traditional migration (imperative) schema management, for popular relational database systems.
If you're referring to exclusively using stored procs to generate dynamic tables or something goofy like that, then sure -- in that case you can't use any external schema management at all, whether declarative or imperative. But that doesn't mean that declarative schema management isn't a safe or possible approach for everyone else.
The overall topic in this subthread was whether or not declarative schema management is appropriate and safe for "real" applications, as compared to traditional migration (imperative) schema management, for popular relational database systems.
If you're referring to exclusively using stored procs to generate dynamic tables or something goofy like that, then sure -- in that case you can't use any external schema management at all, whether declarative or imperative. But that doesn't mean that declarative schema management isn't a safe or possible approach for everyone else.
Yes, something goofy like that.
I don't know about declarative vs imperative. I can see "versioned migrations" are "coming soon", so maybe that could work. Depending on what it will be.
I don't know about declarative vs imperative. I can see "versioned migrations" are "coming soon", so maybe that could work. Depending on what it will be.
Maybe you can explain why there is still no in place upgrade for CloudSQL PostgreSQL?
Actually I was the one leading to build their initial PostgreSQL offering. But I left the team later.
Technically, there's nothing prevent them doing that. It's probably just priority conflicts. A bit sigh though since it's a worthwhile feature IMO.
Technically, there's nothing prevent them doing that. It's probably just priority conflicts. A bit sigh though since it's a worthwhile feature IMO.
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 estimation because it's in development, but that means we plan to OSS with it an SQL-parser infrastructure for the supported databases (can elaborate on that if you want).
Before I continue to migration authoring, I want to mention the reason we chose HCL (or Go). In next versions, we plan to support attaching "annotations" to schemas, like in k8s or in ent [1]. We these annotations, you'll be able to define privacy policy, or create integration to other tool. More details in the near future.
Now, migration authoring. The CLI does not expose all functionalities that are covered by the core engine atm, but when you run this tool (apply/plan) the output is a list of SQL statements. The core engine already knows to generate the "reverse" command for each statement (if it is reversible), and also a summary that indicates if the migration is "transactional" and "reversible" (see example [2]). Next version of atlas is going to support "migration authoring" - that means, instead of generate you list of statements and execute them (after approve), we'll let you the option to generate them to a directory, edit them, and integrate them with tools like flyway, go-migrate, etc.
In addition to that, the engine is also going to suggest you to break a migration plan to multiple steps (like a DBA) in order to make it transactional or reversible if it is not.
[1]: https://entgo.io
[2]: https://github.com/ariga/atlas/blob/master/sql/postgres/migr...
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 estimation because it's in development, but that means we plan to OSS with it an SQL-parser infrastructure for the supported databases (can elaborate on that if you want).
Before I continue to migration authoring, I want to mention the reason we chose HCL (or Go). In next versions, we plan to support attaching "annotations" to schemas, like in k8s or in ent [1]. We these annotations, you'll be able to define privacy policy, or create integration to other tool. More details in the near future.
Now, migration authoring. The CLI does not expose all functionalities that are covered by the core engine atm, but when you run this tool (apply/plan) the output is a list of SQL statements. The core engine already knows to generate the "reverse" command for each statement (if it is reversible), and also a summary that indicates if the migration is "transactional" and "reversible" (see example [2]). Next version of atlas is going to support "migration authoring" - that means, instead of generate you list of statements and execute them (after approve), we'll let you the option to generate them to a directory, edit them, and integrate them with tools like flyway, go-migrate, etc.
In addition to that, the engine is also going to suggest you to break a migration plan to multiple steps (like a DBA) in order to make it transactional or reversible if it is not.
[1]: https://entgo.io
[2]: https://github.com/ariga/atlas/blob/master/sql/postgres/migr...
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?
I thought about keeping it on the same GitHub repository (https://github.com/ariga/atlas), but as a separate Go module? WDYT?
+1, separate go module looks good to me.
That would be really usefull! And when the parser does not understand a language construct (e.g. a new swl fwature) let it fall back to some dumb parsing for that part.
If it does not under „LIMIT 5 WITH TIES“ let it parse „LIMIT 5“ in it‘s usefull abstraction and just provide two suffix keywords „WITH“ and „TIES“.
If it does not under „LIMIT 5 WITH TIES“ let it parse „LIMIT 5“ in it‘s usefull abstraction and just provide two suffix keywords „WITH“ and „TIES“.
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
If you want to chat about it, join us on our discord server? https://discord.com/invite/QhsmBAWzrC
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.
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.
To be clear, I'm not saying it cannot be done — just that it's hard, especially if it's a one-size-fits-all solution that needs to support many databases and SQL dialects, and that a tool like this is going to continually be fighting the various disparities that exist between databases.
I ran into an interesting challenge recently where it was necessary to replace the primary key. The only way to do this (with Postgres) on a live production database is drop the key and add it again in the same transaction, with a USING INDEX; so:
(1) The tool must understand that a modified primary key will need an existing index.
(2) It must understand that it has to be done in a single transaction.
(3) It must understand that this modifies the underlying catalogue: Postgres will rename the new "foo_new_pkey" index to "foo_pkey" and drop the old index.
And that's just Postgres. I bet other databases are different. Most databases don't even allow transactional DDL (e.g. Oracle and Microsoft SQL Server).
Does the amount of work required actually justify the end result? As I said earlier, developers need to understand migrations and their ordering in order to be able to plan their rollout. If a system cannot craft and "orchestrate" migrations perfectly — and I argue that this is infeasible without tons of work — then this means engineers have to run the tool and examine the output and understand it before rolling it out anyway. So now you have a smart, complex tool to learn that doesn't even do the whole job. And in the case of complex migrations it might not even do all of it, requiring the suggested SQL output to be tweaked before it can be run. You might as well just write migrations by hand, then.
To be clear, I think it's good to be ambitious, I'm just generally skeptical for the above reasons.
Last point: Having an SQL parser in Go would be great, so kudos if you manage to build this. Again, I think you will be fighting here to stay up to date with all the dialects, but it's a worthy goal.
I ran into an interesting challenge recently where it was necessary to replace the primary key. The only way to do this (with Postgres) on a live production database is drop the key and add it again in the same transaction, with a USING INDEX; so:
CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS foo_new_pkey
ON foo(bar);
ALTER TABLE foo
DROP CONSTRAINT foo_pkey CASCADE,
ADD CONSTRAINT foo_pkey PRIMARY KEY
USING INDEX foo_new_pkey;
(1) The tool must understand that a modified primary key will need an existing index.
(2) It must understand that it has to be done in a single transaction.
(3) It must understand that this modifies the underlying catalogue: Postgres will rename the new "foo_new_pkey" index to "foo_pkey" and drop the old index.
And that's just Postgres. I bet other databases are different. Most databases don't even allow transactional DDL (e.g. Oracle and Microsoft SQL Server).
Does the amount of work required actually justify the end result? As I said earlier, developers need to understand migrations and their ordering in order to be able to plan their rollout. If a system cannot craft and "orchestrate" migrations perfectly — and I argue that this is infeasible without tons of work — then this means engineers have to run the tool and examine the output and understand it before rolling it out anyway. So now you have a smart, complex tool to learn that doesn't even do the whole job. And in the case of complex migrations it might not even do all of it, requiring the suggested SQL output to be tweaked before it can be run. You might as well just write migrations by hand, then.
To be clear, I think it's good to be ambitious, I'm just generally skeptical for the above reasons.
Last point: Having an SQL parser in Go would be great, so kudos if you manage to build this. Again, I think you will be fighting here to stay up to date with all the dialects, but it's a worthy goal.
You might coordinate with the Vitess team to see if there's any interest in sharing code on the parser.
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.
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
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
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.
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.
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.
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.
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.
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.
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, on HN front page a couple weeks ago, has some nice tricks to help with the incompatible cases.
https://github.com/fabianlindfors/reshape, on HN front page a couple weeks ago, has some nice tricks to help with the incompatible cases.
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
Right, I did have to learn this the hard way back in 2014.
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.
>> 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.
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.
> The database schema and the application should have a schema version that they expect, and rules for translating between versions.
That… is migrations. The rules for translating between versions are literally what migrations do.
That… is migrations. The rules for translating between versions are literally what migrations do.
I have never seen a db rollback. Once you get into prod you're capturing data. A roll ack could lose data. I've only seen forward migrations to fix issues.
It doesn't even have to delete anything.
What happens when it gets 53% of the way through whatever it's doing at it errors out? Now wtf do I do?
What happens when it gets 53% of the way through whatever it's doing at it errors out? 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 :)
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 :)
>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.
That tends to be the norm with Terraform, too :)
If it's like Terraform, you still need to know exactly what the tool is doing and how the services behave under the hood.
God yes. I confidently pushed 'apply' in production a couple of times before I ever encountered these problems. Luckily it blew chunks in the new data center we were trying to set up. Yes, an app is more likely to start cleanly when all of its dependencies are up and running, but still.
So much for Ops setting expectations.
So much for Ops setting expectations.
> 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.
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).
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).
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!
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.
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)
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)
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.
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.
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.
Not sure if any of the migration tooling can handle multiple branches or unordered changes.
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.
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.
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.
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.
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.
I find Skeema pretty compelling, but I couldn't figure out one aspect:
Say I'm adding a gender column to my employees table. I see how Skeema would be an alternative to running DDL in a migration. But I would end up still needing a migration to backfill the data.
So I could use Skeema, but I still need migrations (we also occasionally fix data due to bugs, etc). At that point, we were less motivated to add an additional process.
Is that what Skeema users do, or is there some other approach, or maybe this isn't what I should be doing in the first place?
Say I'm adding a gender column to my employees table. I see how Skeema would be an alternative to running DDL in a migration. But I would end up still needing a migration to backfill the data.
So I could use Skeema, but I still need migrations (we also occasionally fix data due to bugs, etc). At that point, we were less motivated to add an additional process.
Is that what Skeema users do, or is there some other approach, or maybe this isn't what I should be doing in the first place?
Currently, Skeema doesn't interact with DML or provide anything to help here. But that means you're free to use whatever solution you'd like, and Skeema won't get in your way, even if you store your DML in the same repo or even same subdirectories.
Part of the reason for this is that some of Skeema's users are quite large, and larger MySQL users already have in-house solutions for row data migrations. When your tables are huge and/or sharded, a data migration consists of a lot more complexity than just putting an UPDATE statement into a .sql file :)
I do agree it would be good to have some options in Skeema for DML at various scales, and it's something I plan to start approaching in the future, hopefully later this year. Overall my approach with Skeema's roadmap has been to first get DDL right for tables, then get DDL right for other types of objects (finally complete), and only then move on to considering automation for other areas (whether that be DML, or something like managing users/grants, database global variables, etc).
Part of the reason for this is that some of Skeema's users are quite large, and larger MySQL users already have in-house solutions for row data migrations. When your tables are huge and/or sharded, a data migration consists of a lot more complexity than just putting an UPDATE statement into a .sql file :)
I do agree it would be good to have some options in Skeema for DML at various scales, and it's something I plan to start approaching in the future, hopefully later this year. Overall my approach with Skeema's roadmap has been to first get DDL right for tables, then get DDL right for other types of objects (finally complete), and only then move on to considering automation for other areas (whether that be DML, or something like managing users/grants, database global variables, etc).
At my previous company there were several attempts to build declarative migration systems, one of which was more or less successful. They were all a lot more modest in scope and goals than Atlas.
The motivation is very straight forward: if you work in data management with different schemas, you often see similar migration patterns, so it makes sense to try to build abstractions representing these patterns.
If you couple this with the usual problem of the migration / schema duality, and some people who want to have a replica of the current state of the schema in the codebase in some way ("desired state"), you can see how we end up with schema-diff migration tools.
Whether these tools work well or not I think depends on the rate of schema changes vs size of the data store. The larger the dataset, the more specific you need to be with the migration strategies, so if you're not making schema changes very often, using a "clever", schema-diff migration tool adds risk for little benefit, and you might need to bypass the tool on a regular basis.
I don't work in a scenario in which such a tool would bring benefits, but there are projects that have frequent schema changes and data sizes small enough that you don't need to do anything special to maintain availability during migration.
The motivation is very straight forward: if you work in data management with different schemas, you often see similar migration patterns, so it makes sense to try to build abstractions representing these patterns.
If you couple this with the usual problem of the migration / schema duality, and some people who want to have a replica of the current state of the schema in the codebase in some way ("desired state"), you can see how we end up with schema-diff migration tools.
Whether these tools work well or not I think depends on the rate of schema changes vs size of the data store. The larger the dataset, the more specific you need to be with the migration strategies, so if you're not making schema changes very often, using a "clever", schema-diff migration tool adds risk for little benefit, and you might need to bypass the tool on a regular basis.
I don't work in a scenario in which such a tool would bring benefits, but there are projects that have frequent schema changes and data sizes small enough that you don't need to do anything special to maintain availability during migration.
I used to work on a monolithic Rails app and migrations would build up over time. We'd "collapse" migrations by doing a schema dump every couple weeks.
Without it, they'd accumulate thousands of migrations which would take quite a while locally and in CI to rebuild the database
It's also much easier to programmatically inspect. In ActiveRecord, the model attributes are dynamically determined from the DB (for better or worse) so you need to also take any migrations into account to know what fields a model has. From an inspection standpoint, it's nice to have the desired representation in code
Without it, they'd accumulate thousands of migrations which would take quite a while locally and in CI to rebuild the database
It's also much easier to programmatically inspect. In ActiveRecord, the model attributes are dynamically determined from the DB (for better or worse) so you need to also take any migrations into account to know what fields a model has. From an inspection standpoint, it's nice to have the desired representation in code
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
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.
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.
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).
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).
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?
Does the Atlas DDL include more info over sql DDL?
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.
I use migra [https://databaseci.com/docs/migra] for PostgreSQL.
I only put schema.sql into git, no migration scripts.
To make changes, I update schema.sql and load it up into temporary db. Then I run migra prod_db temp_db and it spits out the SQL statements that take me from the old schema.sql (which was in production) to the new. I eyeball the statements and if they look good, I apply them to prod_db.
Is Atlas 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
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
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.
Migration that is not automatic is not an option.
Yes, I eyeball the migration code. You could call it code review, whatever. There is no way I am running migration code automatically. In fact I am not sure what you mean by "automatically"? As in a non-human triggers the migration? But do you review the code?
First of all, migrations do not happen in my case that often that I need them to run unattended/automatically.
Second, I don't trust any migration system to spit out flawless code. Maybe I have trust issues.
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.
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.
Migrations are there every few days on my projects. Non automatic migration is IMO not a migration but database intervention, just as non-automatic tests are not really tests.
This is the first I'd heard of migra but I can definitely imagine a solution involving piping the schema-diff between two databases (e.g. uat and local-dev) into some file that eventually gets applied in prod.
Seems easier than manually creating diffs which is what existing automatated migration tools seem to want you to do.
I love that it's just the diff. Makes it easier to integrate into cloud-native pipelines.
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.
When documenting a migration tool, show an example of renaming a column before working on the logo.
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.”
“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.”
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.
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.
Luckily these days, this type of product is completely obsolete thanks to oauth and a central id provider.
Now the standard question to ask before buying any tool is. Do you support single sign on or do we need $complicatedprovisioningtool from your friends across the street? Ok. Bye.
Oauth isn’t perfect either, especially when it comes to propagating changes to a user. But it sure is a lot better than all these unique identity management tools doing even bigger mistakes over the rest api.
Now the standard question to ask before buying any tool is. Do you support single sign on or do we need $complicatedprovisioningtool from your friends across the street? Ok. Bye.
Oauth isn’t perfect either, especially when it comes to propagating changes to a user. But it sure is a lot better than all these unique identity management tools doing even bigger mistakes over the rest api.
Yeah, IAM software suck quite a lot. The bigger the company, the worst it is.
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.
Seems very similar to https://www.prisma.io/migrate.
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!
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
Sure, we'd love that :-)
Ping us on our Atlas discord?
https://discord.com/invite/QhsmBAWzrC
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).
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).
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.
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.
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 :-)
(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 :-)
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.
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.
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.
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-migrati... and plan to add those enforcement gradually as you pointed.
>> 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-migrati... and plan to add those enforcement gradually as you pointed.
How does this compare to liquibase?
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/ [2] https://alembic.sqlalchemy.org/en/latest/autogenerate.html
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/ [2] https://alembic.sqlalchemy.org/en/latest/autogenerate.html
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_migra...
Example of background migrations is nicely explained in the gitlab handbook https://docs.gitlab.com/ee/development/post_deployment_migra...
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.
How far along that path has this project progressed? I can't really tell from the main page.
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/ [2] https://www.edgedb.com/docs/guides/migrations/index [3] https://www.edgedb.com/docs/guides/introspection/index [4] https://www.edgedb.com/docs/graphql/graphql
(disclaimer: I work on EdgeDB)
[1] https://www.edgedb.com/ [2] https://www.edgedb.com/docs/guides/migrations/index [3] https://www.edgedb.com/docs/guides/introspection/index [4] https://www.edgedb.com/docs/graphql/graphql
You might find something like Hasura interesting.
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.
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.
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.
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.
(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.
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.
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/), 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.
Not all-in-one, but this approach has been really effective in a large codebase.
there is an open source golang project that does the same thing called GraphJin.
has GUi tooling also.
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.
has GUi tooling also.
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.
Since they created their own JSONish DDL, why not build atop of DBML? (https://www.dbml.org/)
[deleted]
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.