16 ms·

Show HN: Version Control for Databases

224 points by akie 7y ago

rhyst 7y ago
Looks very interesting. In the README it says: > The text mentions connection type phpmyadmin, but it doesn't actually work. Don't use it. Will phpmyadmin support work in future? My use case is making backups of a DB where I don't have SSH access but do have a phpmyadmin account.
- akie 7y ago
  Yes, that's why I originally added it. After a day of hacking I had it (mostly) working on one particular instance of phpmyadmin, but when I tried it on another one it completely broke. Not sure why that was, but since I didn't have the need myself anymore I stopped developing that part.
akie 7y ago
I made this, and have been using it in production and for local development for the past year or two. It's been immensely helpful to quickly save or easily restore the database to a previously know 'good point'. I have also been using it to quickly load staging databases into development and vice versa. So far it's MySQL only, but the connection points for other databases such as PostgreSQL are in place. It's main goal is to simplify development - it's not intended as a replacement for writing database migrations or to work with production databases. I would be grateful for any feedback, preferably in the form of pull requests ;-)
- westoque 7y ago
  I like this pattern of being able to restore snapshots in your development environment. For the uninitiated, docker actually has all these functions. For example, if you create a container for postgres and mount a volume, you can create a snapshot of the volume for distribution for your team so they can mount/unmount anytime.
  - oauea 7y ago
    > you can create a snapshot of the volume how? note that docker commit/export does not include volumes
    - remmargorp64 7y ago
      This functionality for Docker was proposed by NetApp (https://netapp.io/2017/06/22/snapshots-clones-docker-volume-paradigm/ https://netapp.io/2017/06/22/snapshots-clones-docker-volume-...) There was also a github issue with numerous people voting for it (https://github.com/moby/moby/issues/33782 https://github.com/moby/moby/issues/33782) But for whatever reason, the docker team apparently decided not to build it. We still don't have a properly easy way to snapshot or share docker volumes. So I came up with my own method. Basically, you have to mount the volume and then tar the parts of it that you want snapshotted (typically your data directories). You can do this with any linux machine image. Here's an example (where "foo" is the docker container you want to snapshot): mkdir -p docker-volume-snapshots && docker run --rm --volumes-from foo -v $(shell pwd)/docker-volume-snapshots:/docker-volume-snapshots debian:stretch-slim tar -czvf /docker-volume-snapshots/snapshot.tar.gz /var/lib/my_data_folder Then, to restore the snapshot, here's what you do: docker run --rm --volumes-from mysql8.0 -v $(shell pwd)/docker-volume-snapshots:/docker-volume-snapshots:delegated debian:stretch-slim /bin/sh -c "cd / && tar -xvf /docker-volume-snapshots/snapshot.tar.gz" (Edit: Don't forget to start up/initialize and then pause/stop the running "foo" docker container before you run these commands to snapshot the volume)
      - oauea 7y ago
        Looks interesting but I doubt that'll be a consistent snapshit, right? I guess you could combine it with `docker pause`
        remmargorp64 7y ago
        oh, yeah, I forgot to mention that I stop the running docker container before I do this
      - 7y ago
- silvester23 7y ago
  Very interesting, my go-to workflow so far has been creating backup copies in phpMyAdmin but this seems much more convenient and reliable. I'll give it a try soon!
- anonymousjunior 7y ago
  Interesting utility. I can see a few really exciting use cases for this. If I had one wish it'd be native support for syncing generated snapshots to S3 or Google Storage for easy transfers between authenticated systems and users. Maybe I'll take a look at building out a PR for that over the weekend if that's okay
  - akie 7y ago
    Yes, by all means please do. Simplicity of use is very important to me, so make sure it's generally applicable and easily understandable, but other than that - go for it! Awesome.
llagerlof 7y ago
Nice idea to speed up the proccess of development and testing. Does the snapshot generated include the views, triggers and stored procedures?
kaiby 7y ago
Which databases can this be used for? Based on the usage of phpmyadmin and WordPress in the examples, I take it only works for MySQL?
- akie 7y ago
  Yes, so far this is MySQL only. I don't think adding Postgres support would be that far fetched, have a look at https://github.com/infostreams/db/tree/master/bin/drivers/mysql/connectors/direct https://github.com/infostreams/db/tree/master/bin/drivers/my... for example to see the meat of the load & save operations. You'd have to implement it twice though, once for local servers, and once for remote servers (via ssh).
  - ianwalter 7y ago
    This should be on your README (ignore if it already is and I just missed it).
- k_ 7y ago
  I was a little annoyed at having to hunt for this information too, but scrolling the README.md a little, I found this [0]: > At the moment, only mysql is supported. Seems like I won't be able to test this for work since we are on SQL Server. Too bad. [0]: https://github.com/infostreams/db/blob/master/readme.md#syntax https://github.com/infostreams/db/blob/master/readme.md#synt...
dmidnight 7y ago
If you were to take the hash and place it in a bitcoin transaction on the blockchain, you could "notarize" each hash with your private key.
egyptiankarim 7y ago
Is this approach materially different/more scalable than using the active record, ORM, and seed file patterns supported by popular MVC frameworks (e.g., like how Rails does it https://edgeguides.rubyonrails.org/active_record_migrations.html#migrations-and-seed-data https://edgeguides.rubyonrails.org/active_record_migrations....)? Isn't the crux of all that to be able to source control and easily distribute schemas and data in a database agnostic way?
- abhchand 7y ago
  This was my same thought. Really impressive as an exercise to develop this yourself, but this is core functionality of a lot of ORMS. Rails' ActiveRecord is kind of the shining example of this, with it's `schema.rb` versioning file
  - matsemann 7y ago
    I think this is for data too, not just schemas.
    - egyptiankarim 7y ago
      But for data we can use the seed files and associated patterns, right? Since it's just text in those files, they're trivially checked into source control, diffed, distributed etc.
  - akie 7y ago
    This is more meant as something that supplements migrations, as a “time machine” of your database so to speak. You would run it before you do any kind of potentially destructive operation on your database, such as running or rolling back a migration. Not all migrations are reversible (for example, migrations that drop columns), so you cannot easily go back to before you ran that migration. It’s also convenient when you check out an older version of your code - you can just restore the database as it was at that point in time. You could run a command in a git hook to enforce saving a new version when you tag a new release for example. In any case, it makes your database less of a black box, and it basically becomes ok to destroy it - because you can so easily make a new snapshot and go back to how it was before.
oauea 7y ago
So this just automates myqldump and import, right?
- MuffinFlavored 7y ago
  I can't tell if it is for just the schema, or the schema + data inside the tables. If it is just the schema, I feel like there are better software ways to define + manage database migrations (aka make them part of the repo with up/down methods to roll forward / roll back)
llcoolv 7y ago
Looks nice, but I don't see what advantages it has compared to liquibase or flyway.
- PrimeDirective 7y ago
  It's not just structure, it's data, too
  - llcoolv 7y ago
    Well, most of the flyway migration schemes I have seen so far also cover data (both initial refdata and migrating existing when the structure changes).
k-godwin 7y ago
I'm not sure what advantage this has over putting MySQL dumps into git (or manually versioning them with filenames)?
simbakhadder 7y ago
Seems useful as a tool for integration tests. Makes it a bit cleaner to update the state of a database and test against the application level code
PaulKeeble 7y ago
The way I have been doing database versioning and rollout for years is with Liquibase (https://www.liquibase.org/ https://www.liquibase.org/). It is based on the Database refactoring book that came out some years ago and provides forwards and backwards progress based on a table that tracks the checksums of the changes allowing them to be rolled out where ever the file goes. You can share with teammates, propagate to environments and have seperate setup for structure, data and test data setups. Restoring snapshots isn't really what you want with databases. The data in production is only in production so you need to refactor it. The issue in today's 365/24/7 always online is that refactoring database structure in that way with a lot of data causes downtime so these sorts of tools require a lot of orchestration around them. Liquibase can also capture an existing structure and data set with an export as well from just about any database engine out there, so you can capture your initial point as you are and then move forward with updates being individual changes so it is relatively easy to adopt.
- jph 7y ago
  Liquibase "Hello World": https://github.com/joelparkerhenderson/demo_liquibase_hello_world https://github.com/joelparkerhenderson/demo_liquibase_hello_... --liquibase formatted sql --changeset alice@example.com:1 create table person ( id int not null primary key, name varchar(100) );
- djrobstep 7y ago
  I don't know why people are stuck on this model of a long chain/log of successive schema changes when you can simply make a diff. I lay out this approach here: https://djrobstep.com/talks/your-migrations-are-bad-and-you-should-feel-bad https://djrobstep.com/talks/your-migrations-are-bad-and-you-...
  - javagram 7y ago
    Hibernate has this model built in as well, I believe. https://stackoverflow.com/a/221422 https://stackoverflow.com/a/221422 hbm2ddl.auto=update But it isn’t recommended for production because the automatic diff-based approach can lead to unpleasant surprises. With individual migration files, you always know exactly what they are going to do and can think and plan for how a rollback might be necessary if needed. It’s fair to say that you don’t necessarily need to keep 100 individual files around once they’ve been deployed to every environment for a while and there’s no chance you’d rollback. But I think people typically do this approach because it is simpler and easier than attempting to export the database structure at #100 or whatever and setting a new baseline every few months.
ericschrock 7y ago
I'm working on a project looking to solve a similar problem, but using a very different mechanism to provide git-like semantics for any database that runs in a container (https://titan-data.io https://titan-data.io). We support local docker containers today and will be adding k8s support in the coming weeks to run in any Kubernetes cluster using k8s snapshots (e.g. push data state from CI/CD k8s cluster to S3 bucket, and clone locally later for forensics). Each automation approach (SQL, dumpfiles, or storage) carries different tradeoffs, but we believe there's a real problem facing developers when it comes to managing data within their workflows. Would love to learn more about what some of the killer use cases are, and how these approaches can complement each other.
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/
MadWombat 7y ago
There are multiple tools that attempt to do this, some specific to a framework some not. To name a few, - flyway - liquibase - dbmate - alembic for python/sqlalchemy - django migrations for python/django - diesel migrations for rust/diesel - echo migrations for elixir/echo - persistent migrations for haskell/persistent All of these, in one way or another, provide an ability to create sequential database schema changes that can be stored in version control and applied/rolled back. What is your system doing that is different from all of these?
- jaredklewis 7y ago
  For elixir, it’s ecto, not echo. If we are listing migration libraries, the migrations in rails’ activerecord are nice too.
- onlyrealcuzzo 7y ago
  Tangential, but is there anything that does this for spreadsheets? I haven't tried to maintain one in Git before, but I've been curious about this functionality.
  - jmathai 7y ago
    FWIW, Google Sheets has version history (including at the cell level). I'm not sure if this is available in their API but it's pretty easy to ingest via the web.
  - zachmu 7y ago
    Dolt does this: https://github.com/liquidata-inc/dolt https://github.com/liquidata-inc/dolt. You can import from either CSV or directly from XLS.
- akie 7y ago
  This is more meant as something that supplements migrations, as a “time machine” of your database so to speak. You would run it before you do any kind of potentially destructive operation on your database, such as running or rolling back a migration. Not all migrations are reversible (for example, migrations that drop columns), so you cannot easily go back to before you ran that migration. It’s also convenient when you check out an older version of your code - you can just restore the database as it was at that point in time. You could run a command in a git hook to enforce saving a new version when you tag a new release for example. In any case, it makes your database less of a black box, and it basically becomes ok to destroy it - because you can so easily make a new snapshot and go back to how it was before.
timsehn 7y ago
We built a Git-style version controlled SQL database called Dolt (https://github.com/liquidata-inc/dolt https://github.com/liquidata-inc/dolt) mentioned deep in this thread. It allows diffs and merges across schema and data based on commits. We are focused on the data distribution/open data use case (https://www.liquidata.co/blog/2019-10-09-where-is-the-data-catalog/ https://www.liquidata.co/blog/2019-10-09-where-is-the-data-c..., https://www.liquidata.co/blog/2019-12-06-the-history-of-data-exchange/ https://www.liquidata.co/blog/2019-12-06-the-history-of-data...) but I think a number of use cases mentioned in this thread are covered by Dolt. You do have to move a copy of your data to Dolt, it does not sit on top or beside of your current database. So, it is an extra step in your data modification workflow, similar to versioning code. You had to add a version control step into your code modification workflow.
valw 7y ago
cough Datomic cough: https://www.infoq.com/presentations/Datomic-Database-Value/ https://www.infoq.com/presentations/Datomic-Database-Value/
lichtenberger 7y ago
Pretty nice tool :-) But what if the data store would do all of that already? - retaining the history of your data: - free deduplication through copy on write and sharing unchanged structures - further store space savings through page-level versioning (not all records in a page have to be written: just the changed/inserted/deleted ones plus depending on the algorithm a few more to fast track it's reconstruction) - read and write performance is balanced using a sliding snapshot algorithm: This avoids having to read long chains of incremental page changes and it avoids write peaks from intermittant full page snapshots. Instead they get trailed over a few (usually three or four revisions). - the sorage manager stores timestamps for all revision in a RevisionRootPage, which itself is indexed - Optional commit author and message - Reverting to and committing a past "good" revision is just adding a few bytes to index a new RevisionRootPage which references the past revision data (simple pointer) - You can diff taking rolling hashes into account - Currently I'm working on a diffing JSON format and a web front-end to display these in special space filling approaches - Sophisticated time travel queries - typed, user-defined and versioned secondary index structures (based on AVL trees) - a path summary kept up to date always - a lot of metadata stored and kept up to date for each record/node It's already implemented in https://sirix.io https://sirix.io / https://github.com/sirixdb/sirix https://github.com/sirixdb/sirix I'm working on getting it stable, but help from you, the Open Source community is always needed :-)
kuczmama 7y ago
This is a really cool idea. Thanks for sharing this. I will definitely give it a go if you have postgres.
husui 7y ago
Hi, thanks for making this. I tried to give it a shot with a remote MySQL database, but I'm not sure how to specify the connection port (I submitted an issue about it). Sorry if it's obvious and I missed it.
deepakputhraya 7y ago
I think dotmesh does something similar - https://dotmesh.com/ https://dotmesh.com/ Github link - https://github.com/dotmesh-io/dotmesh https://github.com/dotmesh-io/dotmesh

224 points by akie 7y ago

Looks very interesting. In the README it says: > The text mentions connection type phpmyadmin, but it doesn't actually work. Don't use it. Will phpmyadmin support work in future? My use case is making backups of a DB where I don't have SSH access but do have a phpmyadmin account.

akie 7y ago
Yes, that's why I originally added it. After a day of hacking I had it (mostly) working on one particular instance of phpmyadmin, but when I tried it on another one it completely broke. Not sure why that was, but since I didn't have the need myself anymore I stopped developing that part.

akie 7y ago

I made this, and have been using it in production and for local development for the past year or two. It's been immensely helpful to quickly save or easily restore the database to a previously know 'good point'. I have also been using it to quickly load staging databases into development and vice versa. So far it's MySQL only, but the connection points for other databases such as PostgreSQL are in place. It's main goal is to simplify development - it's not intended as a replacement for writing database migrations or to work with production databases. I would be grateful for any feedback, preferably in the form of pull requests ;-)

westoque 7y ago
I like this pattern of being able to restore snapshots in your development environment. For the uninitiated, docker actually has all these functions. For example, if you create a container for postgres and mount a volume, you can create a snapshot of the volume for distribution for your team so they can mount/unmount anytime.
- oauea 7y ago
  > you can create a snapshot of the volume how? note that docker commit/export does not include volumes
  - remmargorp64 7y ago
    This functionality for Docker was proposed by NetApp (https://netapp.io/2017/06/22/snapshots-clones-docker-volume-paradigm/ https://netapp.io/2017/06/22/snapshots-clones-docker-volume-...) There was also a github issue with numerous people voting for it (https://github.com/moby/moby/issues/33782 https://github.com/moby/moby/issues/33782) But for whatever reason, the docker team apparently decided not to build it. We still don't have a properly easy way to snapshot or share docker volumes. So I came up with my own method. Basically, you have to mount the volume and then tar the parts of it that you want snapshotted (typically your data directories). You can do this with any linux machine image. Here's an example (where "foo" is the docker container you want to snapshot): mkdir -p docker-volume-snapshots && docker run --rm --volumes-from foo -v $(shell pwd)/docker-volume-snapshots:/docker-volume-snapshots debian:stretch-slim tar -czvf /docker-volume-snapshots/snapshot.tar.gz /var/lib/my_data_folder Then, to restore the snapshot, here's what you do: docker run --rm --volumes-from mysql8.0 -v $(shell pwd)/docker-volume-snapshots:/docker-volume-snapshots:delegated debian:stretch-slim /bin/sh -c "cd / && tar -xvf /docker-volume-snapshots/snapshot.tar.gz" (Edit: Don't forget to start up/initialize and then pause/stop the running "foo" docker container before you run these commands to snapshot the volume)
    - oauea 7y ago
      Looks interesting but I doubt that'll be a consistent snapshit, right? I guess you could combine it with `docker pause`
      - remmargorp64 7y ago
        oh, yeah, I forgot to mention that I stop the running docker container before I do this
    - 7y ago
silvester23 7y ago
Very interesting, my go-to workflow so far has been creating backup copies in phpMyAdmin but this seems much more convenient and reliable. I'll give it a try soon!
anonymousjunior 7y ago
Interesting utility. I can see a few really exciting use cases for this. If I had one wish it'd be native support for syncing generated snapshots to S3 or Google Storage for easy transfers between authenticated systems and users. Maybe I'll take a look at building out a PR for that over the weekend if that's okay
- akie 7y ago
  Yes, by all means please do. Simplicity of use is very important to me, so make sure it's generally applicable and easily understandable, but other than that - go for it! Awesome.

llagerlof 7y ago

Nice idea to speed up the proccess of development and testing. Does the snapshot generated include the views, triggers and stored procedures?

kaiby 7y ago

Which databases can this be used for? Based on the usage of phpmyadmin and WordPress in the examples, I take it only works for MySQL?

akie 7y ago
Yes, so far this is MySQL only. I don't think adding Postgres support would be that far fetched, have a look at https://github.com/infostreams/db/tree/master/bin/drivers/mysql/connectors/direct https://github.com/infostreams/db/tree/master/bin/drivers/my... for example to see the meat of the load & save operations. You'd have to implement it twice though, once for local servers, and once for remote servers (via ssh).
- ianwalter 7y ago
  This should be on your README (ignore if it already is and I just missed it).
k_ 7y ago
I was a little annoyed at having to hunt for this information too, but scrolling the README.md a little, I found this [0]: > At the moment, only mysql is supported. Seems like I won't be able to test this for work since we are on SQL Server. Too bad. [0]: https://github.com/infostreams/db/blob/master/readme.md#syntax https://github.com/infostreams/db/blob/master/readme.md#synt...

dmidnight 7y ago

If you were to take the hash and place it in a bitcoin transaction on the blockchain, you could "notarize" each hash with your private key.

egyptiankarim 7y ago

Is this approach materially different/more scalable than using the active record, ORM, and seed file patterns supported by popular MVC frameworks (e.g., like how Rails does it https://edgeguides.rubyonrails.org/active_record_migrations.html#migrations-and-seed-data https://edgeguides.rubyonrails.org/active_record_migrations....)? Isn't the crux of all that to be able to source control and easily distribute schemas and data in a database agnostic way?

abhchand 7y ago
This was my same thought. Really impressive as an exercise to develop this yourself, but this is core functionality of a lot of ORMS. Rails' ActiveRecord is kind of the shining example of this, with it's `schema.rb` versioning file
- matsemann 7y ago
  I think this is for data too, not just schemas.
  - egyptiankarim 7y ago
    But for data we can use the seed files and associated patterns, right? Since it's just text in those files, they're trivially checked into source control, diffed, distributed etc.
- akie 7y ago
  This is more meant as something that supplements migrations, as a “time machine” of your database so to speak. You would run it before you do any kind of potentially destructive operation on your database, such as running or rolling back a migration. Not all migrations are reversible (for example, migrations that drop columns), so you cannot easily go back to before you ran that migration. It’s also convenient when you check out an older version of your code - you can just restore the database as it was at that point in time. You could run a command in a git hook to enforce saving a new version when you tag a new release for example. In any case, it makes your database less of a black box, and it basically becomes ok to destroy it - because you can so easily make a new snapshot and go back to how it was before.

oauea 7y ago

So this just automates myqldump and import, right?

MuffinFlavored 7y ago
I can't tell if it is for just the schema, or the schema + data inside the tables. If it is just the schema, I feel like there are better software ways to define + manage database migrations (aka make them part of the repo with up/down methods to roll forward / roll back)

llcoolv 7y ago

Looks nice, but I don't see what advantages it has compared to liquibase or flyway.

PrimeDirective 7y ago
It's not just structure, it's data, too
- llcoolv 7y ago
  Well, most of the flyway migration schemes I have seen so far also cover data (both initial refdata and migrating existing when the structure changes).

k-godwin 7y ago

I'm not sure what advantage this has over putting MySQL dumps into git (or manually versioning them with filenames)?

simbakhadder 7y ago

Seems useful as a tool for integration tests. Makes it a bit cleaner to update the state of a database and test against the application level code

PaulKeeble 7y ago

The way I have been doing database versioning and rollout for years is with Liquibase (https://www.liquibase.org/ https://www.liquibase.org/). It is based on the Database refactoring book that came out some years ago and provides forwards and backwards progress based on a table that tracks the checksums of the changes allowing them to be rolled out where ever the file goes. You can share with teammates, propagate to environments and have seperate setup for structure, data and test data setups. Restoring snapshots isn't really what you want with databases. The data in production is only in production so you need to refactor it. The issue in today's 365/24/7 always online is that refactoring database structure in that way with a lot of data causes downtime so these sorts of tools require a lot of orchestration around them. Liquibase can also capture an existing structure and data set with an export as well from just about any database engine out there, so you can capture your initial point as you are and then move forward with updates being individual changes so it is relatively easy to adopt.

jph 7y ago
Liquibase "Hello World": https://github.com/joelparkerhenderson/demo_liquibase_hello_world https://github.com/joelparkerhenderson/demo_liquibase_hello_... --liquibase formatted sql --changeset alice@example.com:1 create table person ( id int not null primary key, name varchar(100) );
djrobstep 7y ago
I don't know why people are stuck on this model of a long chain/log of successive schema changes when you can simply make a diff. I lay out this approach here: https://djrobstep.com/talks/your-migrations-are-bad-and-you-should-feel-bad https://djrobstep.com/talks/your-migrations-are-bad-and-you-...
- javagram 7y ago
  Hibernate has this model built in as well, I believe. https://stackoverflow.com/a/221422 https://stackoverflow.com/a/221422 hbm2ddl.auto=update But it isn’t recommended for production because the automatic diff-based approach can lead to unpleasant surprises. With individual migration files, you always know exactly what they are going to do and can think and plan for how a rollback might be necessary if needed. It’s fair to say that you don’t necessarily need to keep 100 individual files around once they’ve been deployed to every environment for a while and there’s no chance you’d rollback. But I think people typically do this approach because it is simpler and easier than attempting to export the database structure at #100 or whatever and setting a new baseline every few months.

ericschrock 7y ago

I'm working on a project looking to solve a similar problem, but using a very different mechanism to provide git-like semantics for any database that runs in a container (https://titan-data.io https://titan-data.io). We support local docker containers today and will be adding k8s support in the coming weeks to run in any Kubernetes cluster using k8s snapshots (e.g. push data state from CI/CD k8s cluster to S3 bucket, and clone locally later for forensics). Each automation approach (SQL, dumpfiles, or storage) carries different tradeoffs, but we believe there's a real problem facing developers when it comes to managing data within their workflows. Would love to learn more about what some of the killer use cases are, and how these approaches can complement each other.

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/

MadWombat 7y ago

There are multiple tools that attempt to do this, some specific to a framework some not. To name a few, - flyway - liquibase - dbmate - alembic for python/sqlalchemy - django migrations for python/django - diesel migrations for rust/diesel - echo migrations for elixir/echo - persistent migrations for haskell/persistent All of these, in one way or another, provide an ability to create sequential database schema changes that can be stored in version control and applied/rolled back. What is your system doing that is different from all of these?

jaredklewis 7y ago
For elixir, it’s ecto, not echo. If we are listing migration libraries, the migrations in rails’ activerecord are nice too.
onlyrealcuzzo 7y ago
Tangential, but is there anything that does this for spreadsheets? I haven't tried to maintain one in Git before, but I've been curious about this functionality.
- jmathai 7y ago
  FWIW, Google Sheets has version history (including at the cell level). I'm not sure if this is available in their API but it's pretty easy to ingest via the web.
- zachmu 7y ago
  Dolt does this: https://github.com/liquidata-inc/dolt https://github.com/liquidata-inc/dolt. You can import from either CSV or directly from XLS.
akie 7y ago
This is more meant as something that supplements migrations, as a “time machine” of your database so to speak. You would run it before you do any kind of potentially destructive operation on your database, such as running or rolling back a migration. Not all migrations are reversible (for example, migrations that drop columns), so you cannot easily go back to before you ran that migration. It’s also convenient when you check out an older version of your code - you can just restore the database as it was at that point in time. You could run a command in a git hook to enforce saving a new version when you tag a new release for example. In any case, it makes your database less of a black box, and it basically becomes ok to destroy it - because you can so easily make a new snapshot and go back to how it was before.

timsehn 7y ago

We built a Git-style version controlled SQL database called Dolt (https://github.com/liquidata-inc/dolt https://github.com/liquidata-inc/dolt) mentioned deep in this thread. It allows diffs and merges across schema and data based on commits. We are focused on the data distribution/open data use case (https://www.liquidata.co/blog/2019-10-09-where-is-the-data-catalog/ https://www.liquidata.co/blog/2019-10-09-where-is-the-data-c..., https://www.liquidata.co/blog/2019-12-06-the-history-of-data-exchange/ https://www.liquidata.co/blog/2019-12-06-the-history-of-data...) but I think a number of use cases mentioned in this thread are covered by Dolt. You do have to move a copy of your data to Dolt, it does not sit on top or beside of your current database. So, it is an extra step in your data modification workflow, similar to versioning code. You had to add a version control step into your code modification workflow.

valw 7y ago

cough Datomic cough: https://www.infoq.com/presentations/Datomic-Database-Value/ https://www.infoq.com/presentations/Datomic-Database-Value/

lichtenberger 7y ago

Pretty nice tool :-) But what if the data store would do all of that already? - retaining the history of your data: - free deduplication through copy on write and sharing unchanged structures - further store space savings through page-level versioning (not all records in a page have to be written: just the changed/inserted/deleted ones plus depending on the algorithm a few more to fast track it's reconstruction) - read and write performance is balanced using a sliding snapshot algorithm: This avoids having to read long chains of incremental page changes and it avoids write peaks from intermittant full page snapshots. Instead they get trailed over a few (usually three or four revisions). - the sorage manager stores timestamps for all revision in a RevisionRootPage, which itself is indexed - Optional commit author and message - Reverting to and committing a past "good" revision is just adding a few bytes to index a new RevisionRootPage which references the past revision data (simple pointer) - You can diff taking rolling hashes into account - Currently I'm working on a diffing JSON format and a web front-end to display these in special space filling approaches - Sophisticated time travel queries - typed, user-defined and versioned secondary index structures (based on AVL trees) - a path summary kept up to date always - a lot of metadata stored and kept up to date for each record/node It's already implemented in https://sirix.io https://sirix.io / https://github.com/sirixdb/sirix https://github.com/sirixdb/sirix I'm working on getting it stable, but help from you, the Open Source community is always needed :-)

kuczmama 7y ago

This is a really cool idea. Thanks for sharing this. I will definitely give it a go if you have postgres.

husui 7y ago

Hi, thanks for making this. I tried to give it a shot with a remote MySQL database, but I'm not sure how to specify the connection port (I submitted an issue about it). Sorry if it's obvious and I missed it.

deepakputhraya 7y ago

I think dotmesh does something similar - https://dotmesh.com/ https://dotmesh.com/ Github link - https://github.com/dotmesh-io/dotmesh https://github.com/dotmesh-io/dotmesh