5 ms·

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 exi

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.
- rotemtam 5y ago
  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 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 https://discord.com/invite/QhsmBAWzrC) if you want to chat more :-) R
nhoughto 5y ago
Yep is a big long tail to think about solving for, decades of features / idiosyncrasies in a complex domain.
- rotemtam 5y ago
  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 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.
evanelias 5y ago
> 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.
- tianzhou 5y ago
  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.
  - evanelias 5y ago
    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.
a8m 5y ago
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 https://entgo.io [2]: https://github.com/ariga/atlas/blob/master/sql/postgres/migrate_test.go#L169-L189 https://github.com/ariga/atlas/blob/master/sql/postgres/migr...
- newusertoday 5y ago
  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.
  - rotemtam 5y ago

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.

rotemtam 5y ago
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 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 https://discord.com/invite/QhsmBAWzrC) if you want to chat more :-) R

nhoughto 5y ago

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

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

evanelias 5y ago

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

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

a8m 5y ago

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 https://entgo.io [2]: https://github.com/ariga/atlas/blob/master/sql/postgres/migrate_test.go#L169-L189 https://github.com/ariga/atlas/blob/master/sql/postgres/migr...

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