I would like to compare Sqlite databases with each other, both schema and data. I know that a rather crude method exists by comparing the database dumps with each other. But are ther any other methods as well? Especially from a command line environment. I know several tools for comparing databases exist, and I would like to know what sort of methodology they use.
If you only need rough precision, the dump method is crude but does get the job done.
If you want a lot of precision, I have done a similar task using Python's SQLite module. (http://docs.python.org/library/sqlite3.html) I grabbed and directly compared alike rows in each table and wrote out changes to a file, with further post-comparison and UPDATEs done after all comparison was done. You can do similar comparisons with the schema for each "*.db" file.
I have always loved using Scooter Software - Beyond Compare (limited to 2 links) to visually compare between folder, files etc. Group of them or single.
This should help with an easy to use Visual Compare.
It uses simple export with command line to do compare SQLite DB.
I could not even post the Images / Screenshots. If someone can do so and add to this answer please do so.
RedGate offers a suite of tools to help with DB comparisons: http://www.red-gate.com/products/sql-development/sql-compare/
There is a tool to compare schemas from different DB's as well as a tool to compare the data from tables or from the result of a query. We use the data compare tool every time we update our database to ensure that queries often done by our product code return the same data in the same order, etc.
RedGate Sql Compare and Sql Data Compare do not work on SQLite unfortunately.
SQLite Compare Utility http://www.codeproject.com/Articles/220018/SQLite-Compare-Utility works decent for schema differences, but wasn't very helpful for data differences in my situation.
I would give my arm if Beyond Compare added support for Sql Server and SQLite comparison.
Try using SchemaCrawler. SchemaCrawler is a free and open-source command-line tool that produces text (text, JSON, CSV, HTML) output that is designed to be diff-ed, using standard diff tools. It can compare both structure and data.
Sualeh Fatehi, SchemaCrawler
There is a GUI tool for Windows, "KS DB Merge Tools for SQLite", allows to compare both schema and data. It has two-panel UI like most of text diff tools:
I am the author of this tool.
Free version available, but free version has no command line support mentioned in the question. Paid version can accept db paths from command line, that's allows to use it as diff viewer for source control tools like TortoiseSVN, but it has no console output or something like that.
UPD July 2022: I've made a free web version of my SQLite tool, also compares both schema and data. Comparing to the desctop version, it does not support merge, but works in the most of popular modern wasm-compatible web browsers, including Safari on Mac and Firefox and Chromium on Linux:
There is a diff tool by sqlite.org: