4 ms·

sqlite databases are binary blobs which don't have meaningful diffs if you check them into source control.

typon 7y ago
I wrote a SQLite <-> Json converter for this reason. Ended up coming up with a weird convention in the Json to represent relations. It might be cool to instead write a recfiles <-> SQLite converter. I want to use SQLite from within my app, but want to check in readable data to a source control system.
- svnpenn 7y ago
  https://sqlite.org/cli.html#csv_export https://sqlite.org/cli.html#csv_export
  - typon 7y ago
    The CSV format is really ugly - i barely consider it human readable.
brigandish 7y ago
Why would you check a database into source control? Not trying to be funny, genuinely wondering.
- lolc 7y ago
  Sometimes you want data under version-control. You want to track changes and maybe you want to be able to reverse them. An example is human-supervised data-imports. Example: we operate a pipline that pulls datasets from multiple government agencies and merges them. The sources are updated periodically and updates frequently introduce new inconsistences. To reconciliate, we use auxiliary datasets that record tweaks to each source, such as entries to add, mutate or delete. In our implementation of the pipeline the source datasets, the tweak tables, and the resulting merged dataset are kept in Sqlite for ease of processing. The pipeline writes out dumps of each table during processing, even for intermediate stages. When running the pipeline, I can scan the diff and decide whether the changes are reasonable or tweaks must be introduced or removed. Once I'm satisifed with the result, the dumps are then committed to record the current known-good state. When somebody wants to know why one record in the result is the way it is, I can determine how it changed in the source data, how it was tweaked, and what the result was before and after. It's really easy to produce diffs between revisions. Code-reviews are conducted over changes in pipeline code, validation logic, source and result all in one step.

I wrote a SQLite <-> Json converter for this reason. Ended up coming up with a weird convention in the Json to represent relations. It might be cool to instead write a recfiles <-> SQLite converter. I want to use SQLite from within my app, but want to check in readable data to a source control system.

svnpenn 7y ago
https://sqlite.org/cli.html#csv_export https://sqlite.org/cli.html#csv_export
- typon 7y ago
  The CSV format is really ugly - i barely consider it human readable.

brigandish 7y ago

Why would you check a database into source control? Not trying to be funny, genuinely wondering.

lolc 7y ago
Sometimes you want data under version-control. You want to track changes and maybe you want to be able to reverse them. An example is human-supervised data-imports. Example: we operate a pipline that pulls datasets from multiple government agencies and merges them. The sources are updated periodically and updates frequently introduce new inconsistences. To reconciliate, we use auxiliary datasets that record tweaks to each source, such as entries to add, mutate or delete. In our implementation of the pipeline the source datasets, the tweak tables, and the resulting merged dataset are kept in Sqlite for ease of processing. The pipeline writes out dumps of each table during processing, even for intermediate stages. When running the pipeline, I can scan the diff and decide whether the changes are reasonable or tweaks must be introduced or removed. Once I'm satisifed with the result, the dumps are then committed to record the current known-good state. When somebody wants to know why one record in the result is the way it is, I can determine how it changed in the source data, how it was tweaked, and what the result was before and after. It's really easy to produce diffs between revisions. Code-reviews are conducted over changes in pipeline code, validation logic, source and result all in one step.