Item — HN Reader

You have to be careful with this, as using .dump as your filter will not necessarily get you an equivalent database on restore. For example, .dump will not include database metadata such as application_id¹ or user_version²(perhaps other things too but these are the ones that have bitten me before).

The custom diff driver in the OP will not reflect changes to those values either obviously, but at least it won't lose them in the actual database file stored in the git repository.

¹ https://sqlite.org/pragma.html#pragma_application_id

² https://sqlite.org/pragma.html#pragma_user_version

1 comments

Is there no capability in SQLite to dump these things as well? Seems like a pretty big thing missing if that's the case, a thing I would expect SQLite to support pretty well.

Not as far as I'm aware. You can query much of that stuff using the CLI with the .dbinfo command, but support for that appears to be dependent on build options so may not be available. Another option might be to provide multiple commands("sqlite my.db 'PRAGMA user_version' .dump"), but you'd have to remember to update scripts if you suddenly started using other things in your app too.

I'll note that the metadata is handled correctly by the CLI's .backup command or you can "VACUUM INTO <somewhere>"¹, so if you're just attempting to use .dump for the sake of backup there are probably better options available anyway.

But yeah, I was surprised when I chased down a bug that was caused by a missing application_id following a restore from .dump output. I did a search at the time and noticed it had been reported to the sqlite folks on their forums a few times, so presumably I'm missing the reasoning behind not including it.

¹ https://www.sqlite.org/lang_vacuum.html

Not sure if this actually does more, but remember you can do:

    sqlite> select * from pragma_application_id();
    0
    sqlite> select * from pragma_user_version();
    0

Yeah, you could use that as in "sqlite3 t.db 'SELECT format(…) FROM pragma_…' .dump" to create a file that sets application_id/$others and then dumps the tables, which would make restoration from the dump file work in more cases.

It is just another thing to be aware of if you're trying to store your database in your git repository with a filter, and want it to roundtrip correctly.