Sqldiff: SQLite Database Difference Utility(sqlite.org)
sqlite.org
Sqldiff: SQLite Database Difference Utility
https://www.sqlite.org/sqldiff.html
24 comments
Mentioned in yesterday's discussion on declarative schema migrations for SQLite: https://news.ycombinator.com/item?id=31249823
I was debugging Postgres permissions the other day and was hoping for a tool where you can just diff the permissions of a table between databases and see why in one database a certain user has permissions (to insert, execute,...) and in the other one it doesn't.
That might be a naive ask and maybe that's not even possible but I'm wondering if such a thing exists?
That might be a naive ask and maybe that's not even possible but I'm wondering if such a thing exists?
Check out migra[1] - it covers most of that. One notable omission is column-level grants though - as far as I know changes to those aren’t detected
[1] https://github.com/djrobstep/migra
[1] https://github.com/djrobstep/migra
This looks interesting, thanks for sharing!
I'll give it a try. Do you by any chance know if it detects different search paths too between roles? (That was my actual issue in the end while debugging why role A could execute a function and role B couldn't).
I'll give it a try. Do you by any chance know if it detects different search paths too between roles? (That was my actual issue in the end while debugging why role A could execute a function and role B couldn't).
Thanks for mentioning migra. Unfortunately it looks like it's only for identical databases - ie using public schema - so it can't diff dba.user1_schema.table1 vs dbb.user2_schema.table1?
I don't have any production DBs that use public/not-instance/user-specific schemas :/
I don't have any production DBs that use public/not-instance/user-specific schemas :/
I am not very familiar with this tool, and the documentation doesn't mention this use case but it does look like you can pass specific schema(s) or exclude them:
https://github.com/djrobstep/migra/blob/master/migra/migra.p...
It looks like the schema do have to have the same name between the databases, which I think is sensible but again I could be wrong. This is a darker corner of the postgres tooling world that could definitely use some light, so good on the migra devs to trying this out. If you do get it working, consider sending them a PR with some documentation on your use case!
https://github.com/djrobstep/migra/blob/master/migra/migra.p...
It looks like the schema do have to have the same name between the databases, which I think is sensible but again I could be wrong. This is a darker corner of the postgres tooling world that could definitely use some light, so good on the migra devs to trying this out. If you do get it working, consider sending them a PR with some documentation on your use case!
Which {SQL,} databases do this as a native, online database feature; so you don't have to pull backups to sqldiff?
E.g. django-reversion and VDM do versioned domain model on top of SQL, but not with native temporal database features.
Many apps probably could or should be written as mostly-append-only - not necessarily blocking until the previous record is available to hash – but very few apps are written that way, so run sqldiff offline.
E.g. django-reversion and VDM do versioned domain model on top of SQL, but not with native temporal database features.
Many apps probably could or should be written as mostly-append-only - not necessarily blocking until the previous record is available to hash – but very few apps are written that way, so run sqldiff offline.
There is Dolt, which started out as git for tables but is turning into a MySQL compatible database with good versioning. (Including branches and merges.)
dolthub/dolt https://github.com/dolthub/dolt:
EthicalML/awesome-production-machine-learning#model-and-data-versioning: https://github.com/EthicalML/awesome-production-machine-lear...
dolt clone
dolt pull
dolt push
dolt checkout
dolt branch
dolt commit
dolt merge
dolt blame
dolt diff
EthicalML/awesome-production-machine-learning#model-and-data-versioning: https://github.com/EthicalML/awesome-production-machine-lear...
See also https://github.com/simonw/sqlite-diffable for output that's easier to machine process.
On mac, you can install with macports:
sudo port install sqlite3-tools
brew install sqldiffI’ve always wanted a diff for query results to know if anything changes when I’m optimizing a query.
We actually built a tool exactly for that: https://www.datafold.com/data-diff
The core use case is helping data engineers building analytical pipelines test the changes to their code prior to deploying, but can also work for any query. You can diff tables or just SQL queries directly.
This could be very useful for working on applications that store configuration in SQLite databases.
FYI FWIW: brew install sqldiff
Cool debugging tool when updating databases which pull data from various sources!
What is the relevance of this to SQLite's diff tool?
I don’t get the XKCD reference either. It’s the opposite. There are many ways to diff database schemas but, as I understand the doc, this utility actually compares the data stored in a database with the data stored in another database having the same schema. I’m not aware of any other “built-in” tools for comparing the content of databases.
There's a bunch of them out there and they mostly suck.
https://docs.microsoft.com/en-us/sql/ssdt/how-to-compare-and... https://www.red-gate.com/products/sql-development/sql-data-c... https://www.apexsql.com/sql-tools-datadiff.aspx
https://docs.microsoft.com/en-us/sql/ssdt/how-to-compare-and... https://www.red-gate.com/products/sql-development/sql-data-c... https://www.apexsql.com/sql-tools-datadiff.aspx
It actually also compares data when the two databases have different schemas! It's pretty nice for quickly comparing changes in datasets.
Oops commented on the wrong post! If a mode sees, please delete.