3 ms·

We could look into supporting another way to define migrations that is not yaml. What would you have in mind and why is yaml bad?

gregw2 2y ago
Why mot use comment-annotated .sql like liquibase or flyway database migration tools do? How does yaml help? As someone who did migrations wrapped in XML back in the day, those parsing wrappers around sql (xml, yaml, json) add complexity for the user and create opaque error messages at times due to the wrapper format. (quoting, escaping, indenting, allowed characters, etc) I get that as a certain fraction of your userbase may like yaml and I am not saying you shouldn't support it. And I get that the yaml parsing library can accelerate certain dev steps for you. But the impedence mismatch between yaml and sql and error handling will make your product usability worse. You are already building a domain specific language of sorts. You will build a better product if you keep your "language" as close to the problem domain (sql-centric migrations) as possible. Yaml just adds complexity. My two cents anyway.
sgarland 2y ago
YAML is bad, first and foremost, because the language is hideously awful. Anything that can interpret Norway’s country code as a bool, or certain numbers as sexagesimal, has not been well-thought out. As to what else, I continually fail to see what’s wrong with SQL. It’s incredibly easy to read, in plain English, and understand what is being done. ALTER TABLE foo ADD COLUMN bar INTEGER NOT NULL DEFAULT 0… hmm, wonder what that’s doing? For people who say you get too many .sql files doing it this way: a. Get your data models right the first time b. Build a tool to parse and combine statements, if you must.
- vortex_ape 2y ago
  I agree with your point about SQL's readability, however, I think there's value in having structured metadata around migrations, like dependencies between migrations, rollback instructions, and environment specific variations, beyond just the raw SQL. This could be done with SQL comments, but having it in a structured format makes it more reliable to parse and validate programmatically. I do see why YAML's quirks could become a problem in a tool that's meant to help you make sure your database is in order, we didn't run into issues like country codes or numbers being interpreted as sexagesimal (yet). Perhaps a middle ground would be to keep the actual migrations in .sql files for readability, while using a separate metadata file (in JSON or TOML) for the orchestration details. What do you think?
  - sgarland 2y ago
    Re: dependencies, SQL is declarative. While there might be benefit in keeping the last modification as its own entity, I think just dumping the output of SHOW CREATE TABLE or the equivalent is the best solution. It clearly shows the current state of the table. This of course requires that the migration has already occurred, or has been faked for display purposes. I guess my stance is that if you need some kind of config file to create the migration, then fine, but as a DBRE, it’s much easier for me to reason about the current state by seeing the declarative output than by mentally summing migration files. As a bonus, this lets you see quite easily if you’ve done something silly like creating an additional index on a column that has a UNIQUE constraint (though you’d still have to know why that’s unnecessary, I suppose).
    - vortex_ape 2y ago
      Yep that's the reason we generate a schema.sql at the end so that you know the current state of the database after the migrations have been applied (you don't need to mentally sum those migrations). Coupled with git, you get to see the diff of the schema.sql to see what changed which is super useful, like the same additional index example you mentioned.

gregw2 2y ago

Why mot use comment-annotated .sql like liquibase or flyway database migration tools do? How does yaml help? As someone who did migrations wrapped in XML back in the day, those parsing wrappers around sql (xml, yaml, json) add complexity for the user and create opaque error messages at times due to the wrapper format. (quoting, escaping, indenting, allowed characters, etc) I get that as a certain fraction of your userbase may like yaml and I am not saying you shouldn't support it. And I get that the yaml parsing library can accelerate certain dev steps for you. But the impedence mismatch between yaml and sql and error handling will make your product usability worse. You are already building a domain specific language of sorts. You will build a better product if you keep your "language" as close to the problem domain (sql-centric migrations) as possible. Yaml just adds complexity. My two cents anyway.

sgarland 2y ago

YAML is bad, first and foremost, because the language is hideously awful. Anything that can interpret Norway’s country code as a bool, or certain numbers as sexagesimal, has not been well-thought out. As to what else, I continually fail to see what’s wrong with SQL. It’s incredibly easy to read, in plain English, and understand what is being done. ALTER TABLE foo ADD COLUMN bar INTEGER NOT NULL DEFAULT 0… hmm, wonder what that’s doing? For people who say you get too many .sql files doing it this way: a. Get your data models right the first time b. Build a tool to parse and combine statements, if you must.

vortex_ape 2y ago
I agree with your point about SQL's readability, however, I think there's value in having structured metadata around migrations, like dependencies between migrations, rollback instructions, and environment specific variations, beyond just the raw SQL. This could be done with SQL comments, but having it in a structured format makes it more reliable to parse and validate programmatically. I do see why YAML's quirks could become a problem in a tool that's meant to help you make sure your database is in order, we didn't run into issues like country codes or numbers being interpreted as sexagesimal (yet). Perhaps a middle ground would be to keep the actual migrations in .sql files for readability, while using a separate metadata file (in JSON or TOML) for the orchestration details. What do you think?
- sgarland 2y ago
  Re: dependencies, SQL is declarative. While there might be benefit in keeping the last modification as its own entity, I think just dumping the output of SHOW CREATE TABLE or the equivalent is the best solution. It clearly shows the current state of the table. This of course requires that the migration has already occurred, or has been faked for display purposes. I guess my stance is that if you need some kind of config file to create the migration, then fine, but as a DBRE, it’s much easier for me to reason about the current state by seeing the declarative output than by mentally summing migration files. As a bonus, this lets you see quite easily if you’ve done something silly like creating an additional index on a column that has a UNIQUE constraint (though you’d still have to know why that’s unnecessary, I suppose).
  - vortex_ape 2y ago
    Yep that's the reason we generate a schema.sql at the end so that you know the current state of the database after the migrations have been applied (you don't need to mentally sum those migrations). Coupled with git, you get to see the diff of the schema.sql to see what changed which is super useful, like the same additional index example you mentioned.