3 ms·

I'm the author of an open-source schema management tool, Skeema [1], which uses a declarative infrastructure-as-code approach: you track your table definitions

I'm the author of an open-source schema management tool, Skeema [1], which uses a declarative infrastructure-as-code approach: you track your table definitions (and procs, funcs, etc) in a Git repo of *.sql files, typically one CREATE statement per file. Instead of writing migrations, developers simply add / remove / modify these CREATE statements, going through the same pull request and code review process as for code. The tool knows how to diff the desired state (expressed in the repo) vs the actual state of any database environment (prod / stage / dev, etc) to generate the appropriate DDL. It's a bit of a paradigm shift relative to traditional mysql and postgres migration tools, but it's an approach that has been used successfully by Facebook internally for nearly a decade. It's also a common approach in the SQL Server world. I've written a blog post [2] describing some of the advantages of the declarative approach. Skeema currently only supports MySQL and MariaDB, but some declarative tools for Postgres include sqldef [3] and migra [4]. [1] https://www.skeema.io https://www.skeema.io [2] https://www.skeema.io/blog/2019/01/18/declarative/ https://www.skeema.io/blog/2019/01/18/declarative/ [3] https://github.com/k0kubun/sqldef/ https://github.com/k0kubun/sqldef/ [4] https://github.com/djrobstep/migra https://github.com/djrobstep/migra

lstamour 7y ago
I was going to ask after rename support, but lack of it is documented over here along with a couple other things. https://github.com/skeema/skeema/blob/master/doc/requirements.md#responsibilities-for-the-user https://github.com/skeema/skeema/blob/master/doc/requirement... Interesting ideas though. Microsoft does something similar with a DACPAC where they record the schema and diff, but they also record a list of rename actions etc. and keep a table to track those “migrations” in addition to the diff process. https://docs.microsoft.com/en-us/sql/relational-databases/data-tier-applications/data-tier-applications?view=sql-server-ver15 https://docs.microsoft.com/en-us/sql/relational-databases/da... and/or https://docs.microsoft.com/en-us/sql/ssdt/extract-publish-and-register-dacpac-files?view=sql-server-ver15 https://docs.microsoft.com/en-us/sql/ssdt/extract-publish-an... — for rename and other SSDT details, see https://docs.google.com/presentation/d/1DvC2gzCucjHFbGiBLa0R5H8WZm48z2pDciY0aRcykBo/view?usp=sharing https://docs.google.com/presentation/d/1DvC2gzCucjHFbGiBLa0R... (it’s a “RefactorLog” if searching) Having seen all that, the Rails ActiveRecord approach strikes a decent balance between specify migrations as imperative actions and keeping a declarative schema checked in. A comparison between the two approaches is at https://blog.raph.ws/2019/01/migrations-vs-dacpacs/ https://blog.raph.ws/2019/01/migrations-vs-dacpacs/ but I find DACPAC to be useful but over-complicated for developers and not well-enough supported on non-windows hosts yet. (It basically requires VS for Windows or SSDT right now...) Also, it’s likely a migration system needs some kind of code review checks and automation around which actions you’d allow in your migrations if you don’t have that already... The next question after “how do you migrate?” is probably “how do you backup and how long would it take to restore after a bad migration and/or bad rollback?” These days your answer is probably either “no time at all” for smaller apps with SSDs or “it’s all outsourced to the cloud,” for the new cloud distributed data stores or for the smart DIY ones, “we only deploy one server at a time and can either afford the downtime or have copies in multiple AZs, perhaps eventual consistency...”

lstamour 7y ago

I was going to ask after rename support, but lack of it is documented over here along with a couple other things. https://github.com/skeema/skeema/blob/master/doc/requirements.md#responsibilities-for-the-user https://github.com/skeema/skeema/blob/master/doc/requirement... Interesting ideas though. Microsoft does something similar with a DACPAC where they record the schema and diff, but they also record a list of rename actions etc. and keep a table to track those “migrations” in addition to the diff process. https://docs.microsoft.com/en-us/sql/relational-databases/data-tier-applications/data-tier-applications?view=sql-server-ver15 https://docs.microsoft.com/en-us/sql/relational-databases/da... and/or https://docs.microsoft.com/en-us/sql/ssdt/extract-publish-and-register-dacpac-files?view=sql-server-ver15 https://docs.microsoft.com/en-us/sql/ssdt/extract-publish-an... — for rename and other SSDT details, see https://docs.google.com/presentation/d/1DvC2gzCucjHFbGiBLa0R5H8WZm48z2pDciY0aRcykBo/view?usp=sharing https://docs.google.com/presentation/d/1DvC2gzCucjHFbGiBLa0R... (it’s a “RefactorLog” if searching) Having seen all that, the Rails ActiveRecord approach strikes a decent balance between specify migrations as imperative actions and keeping a declarative schema checked in. A comparison between the two approaches is at https://blog.raph.ws/2019/01/migrations-vs-dacpacs/ https://blog.raph.ws/2019/01/migrations-vs-dacpacs/ but I find DACPAC to be useful but over-complicated for developers and not well-enough supported on non-windows hosts yet. (It basically requires VS for Windows or SSDT right now...) Also, it’s likely a migration system needs some kind of code review checks and automation around which actions you’d allow in your migrations if you don’t have that already... The next question after “how do you migrate?” is probably “how do you backup and how long would it take to restore after a bad migration and/or bad rollback?” These days your answer is probably either “no time at all” for smaller apps with SSDs or “it’s all outsourced to the cloud,” for the new cloud distributed data stores or for the smart DIY ones, “we only deploy one server at a time and can either afford the downtime or have copies in multiple AZs, perhaps eventual consistency...”