4 ms·

This doesn't tackle the primary issue I have with databases and source control - diff. A proper source control system for databases should handle both migratio

by beart 7y ago

This doesn't tackle the primary issue I have with databases and source control - diff. A proper source control system for databases should handle both migrations and diffing across migrations. I should be able to compare any two commits and view exactly what is different between them.

grey-area 7y ago
In the case of a large db that's not really practical for data changes, though it could be done easily if you just mean schema diffs, but then that's of limited extra use if you can see the migrations themselves.
- awshepard 7y ago
  I agree a data diff would be challenging, especially at scale, but one tool I think is lacking is schema diffs. For sure one can see the sequence of migrations that were applied, but if all you have is a series of sql files that add/remove/update column definitions, by the end of one or more diffs, you may not actually know or remember what's IN the table you're trying to understand. And if you don't have prod access to show create table (or equivalent), you're left with tracing the diff operations and reconstructing the table schema yourself. Have you seen a tool that can do that?
  - grey-area 7y ago
    I run migrations locally and on dev on sanitised snapshots of live data, and have easy access to those, so I just use the db to view the schema if required. Regular snapshots of the data are useful too. If migrations are kept small there isn't usually much confusion over what changed (see migration), or what exists (see db).
  - evanelias 7y ago
    I'm the author of a schema management tool, Skeema [1], designed to solve this problem for MySQL and MariaDB. There are a number of other existing tools that can compare/diff schemas on 2 live databases, but Skeema is designed to also actually manage your database structure through a declarative repo of CREATE statements. It works at any scale (natively supports sharding and external OSC tools) and is trusted by several large users, including GitHub [2] and Twilio SendGrid [3]. [1] https://www.skeema.io https://www.skeema.io [2] https://fosdem.org/2020/schedule/event/mysql_github_schema/ https://fosdem.org/2020/schedule/event/mysql_github_schema/ [3] https://sendgrid.com/blog/schema-management-with-skeema/ https://sendgrid.com/blog/schema-management-with-skeema/
  - lichtenberger 7y ago
    For sure you'd have to add rolling hashes, but that's probably more natural in a tree based data store. I've added these to https://sirix.io https://sirix.io, which fastens diffing considerably, especially with deep trees. Otherwise indexing changes could be done :-)
  - knutwannheden 7y ago
    jOOQ is an SQL library, which in addition to an internal SQL DSL for Java also includes other goodies like an SQL parser and since very recently (still under active development) also a schema diff tool (also available as CLI): https://www.jooq.org/diff/ https://www.jooq.org/diff/. One thing which sets jOOQ apart from most other tools out there is the fact that it supports many different SQL dialects. Thus the schema diff tool can for instance also parse DDL in one dialect and render the diff in another SQL dialect. For certain applications this could be of interest. Disclaimer: I am an active committer on the jOOQ project.
ericschrock 7y ago
There are purpose-built databases for this purpose such as Dolt (https://www.dolthub.com/ https://www.dolthub.com/), itself purportedly derived from noms (https://github.com/attic-labs/noms https://github.com/attic-labs/noms). Because it's baked into the database, you can do complex things like diffs and merges, but it requires that you run everything on that RDBMS vs. something standard. Generic diffing from two arbitrary data states of any database is pretty challenging, even if you have all the transaction history.
- zachmu 7y ago
  Yeah, Dolt is built for distribution / collaboration of data sets, rather than for actually hosting live data in production (although it can do that). The workflow you would use to diff your database versions would be very similar to OPs: periodically take a snapshot dump of prod, then import that snapshot into Dolt. The reason this is better than just diffing the snapshot directly (i.e. textual diff where you see which INSERT statements are present in one version but not the other) is that 1) you get git-like version control semantics where each revision is associated with a person and a commit message, and 2) you can run SQL queries to inspect what's different between two revisions, rather than needing to write some analysis program.

by beart 7y ago

grey-area 7y ago

In the case of a large db that's not really practical for data changes, though it could be done easily if you just mean schema diffs, but then that's of limited extra use if you can see the migrations themselves.

awshepard 7y ago
I agree a data diff would be challenging, especially at scale, but one tool I think is lacking is schema diffs. For sure one can see the sequence of migrations that were applied, but if all you have is a series of sql files that add/remove/update column definitions, by the end of one or more diffs, you may not actually know or remember what's IN the table you're trying to understand. And if you don't have prod access to show create table (or equivalent), you're left with tracing the diff operations and reconstructing the table schema yourself. Have you seen a tool that can do that?
- grey-area 7y ago
  I run migrations locally and on dev on sanitised snapshots of live data, and have easy access to those, so I just use the db to view the schema if required. Regular snapshots of the data are useful too. If migrations are kept small there isn't usually much confusion over what changed (see migration), or what exists (see db).
- evanelias 7y ago
  I'm the author of a schema management tool, Skeema [1], designed to solve this problem for MySQL and MariaDB. There are a number of other existing tools that can compare/diff schemas on 2 live databases, but Skeema is designed to also actually manage your database structure through a declarative repo of CREATE statements. It works at any scale (natively supports sharding and external OSC tools) and is trusted by several large users, including GitHub [2] and Twilio SendGrid [3]. [1] https://www.skeema.io https://www.skeema.io [2] https://fosdem.org/2020/schedule/event/mysql_github_schema/ https://fosdem.org/2020/schedule/event/mysql_github_schema/ [3] https://sendgrid.com/blog/schema-management-with-skeema/ https://sendgrid.com/blog/schema-management-with-skeema/
- lichtenberger 7y ago
  For sure you'd have to add rolling hashes, but that's probably more natural in a tree based data store. I've added these to https://sirix.io https://sirix.io, which fastens diffing considerably, especially with deep trees. Otherwise indexing changes could be done :-)
- knutwannheden 7y ago
  jOOQ is an SQL library, which in addition to an internal SQL DSL for Java also includes other goodies like an SQL parser and since very recently (still under active development) also a schema diff tool (also available as CLI): https://www.jooq.org/diff/ https://www.jooq.org/diff/. One thing which sets jOOQ apart from most other tools out there is the fact that it supports many different SQL dialects. Thus the schema diff tool can for instance also parse DDL in one dialect and render the diff in another SQL dialect. For certain applications this could be of interest. Disclaimer: I am an active committer on the jOOQ project.

ericschrock 7y ago

There are purpose-built databases for this purpose such as Dolt (https://www.dolthub.com/ https://www.dolthub.com/), itself purportedly derived from noms (https://github.com/attic-labs/noms https://github.com/attic-labs/noms). Because it's baked into the database, you can do complex things like diffs and merges, but it requires that you run everything on that RDBMS vs. something standard. Generic diffing from two arbitrary data states of any database is pretty challenging, even if you have all the transaction history.

zachmu 7y ago
Yeah, Dolt is built for distribution / collaboration of data sets, rather than for actually hosting live data in production (although it can do that). The workflow you would use to diff your database versions would be very similar to OPs: periodically take a snapshot dump of prod, then import that snapshot into Dolt. The reason this is better than just diffing the snapshot directly (i.e. textual diff where you see which INSERT statements are present in one version but not the other) is that 1) you get git-like version control semantics where each revision is associated with a person and a commit message, and 2) you can run SQL queries to inspect what's different between two revisions, rather than needing to write some analysis program.