Sqldiff: SQLite Database Difference Utility | Hacker News Reader

Sqldiff: SQLite Database Difference Utility

sqlite.org

210 pointsthunderbong24 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?

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

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).

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 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!

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.

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:

  dolt clone
  dolt pull
  dolt push
  dolt checkout
  dolt branch
  dolt commit
  dolt merge

  dolt blame
  dolt diff

mgramin/awesome-db-tools > schema > changes: https://github.com/mgramin/awesome-db-tools#changes

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

If you use brew (and you shouldn't), then

  brew install sqldiff

Can you please explain your disdain for brew?

why not install it with homebrew?

I’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!

https://xkcd.com/927/

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

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.

Reply on news.ycombinator.com