Steps to create sql diff of two database versions - RhetTbull/osxphotos GitHub Wiki

  • Install GNU diff (On current macOS versions, Apple ships FreeBSD though it used to ship GNU at least as 10.15)

brew install diffutils

then use /opt/homebrew/bin/diff as the diff command

  • Extract schema from SQLite database:

sqlite3 tests/Test-10.16.0.1.photoslibrary/database/Photos.sqlite ".schema" | sort > sql_11_0_1.sql

  • Do same for second database

  • Then format (prettify) in VSCode (need way to do this on command line)

  • Create the diff:

diff --new-line-format='+%L' --old-line-format='-%L' --unchanged-line-format=' %L' sql_11_0_1.sql 12_0_1.sql >sql_diff.txt