How to Restore Your Logseq DB SQLite Database from a Previous Git Version

Questions & Help Documentation
,
1
  • Here is a step-by-step how-to article tailored for Logseq users who want to restore their SQLite database (db.sqlite) from a previous Git commit. This is applicable to Logseq Desktop version.
  • If you use Logseq with Git version control, you can restore your db.sqlite file to a previous state by using Git commands. This is useful if something goes wrong, or you want to revert to an earlier version of your knowledge graph.

  • Prerequisites

    • You have Git installed on your system.
    • You are comfortable using the command line.
    • Your Logseq graph is tracked in a Git repository and the db.sqlite file is committed to Git.
    • (Optional but recommended) You have sqldiff installed to compare database versions.
  • Step 1: Open Your Logseq backup Repository Folder

    • Open a command-line terminal and navigate to your Logseq graph directory (the root of your Git repository).

      ```shell
cd ~/.logseq/git/_Users_myUsee_logseq_graphs_MyGraph-001
```
  • Step 2: View Commit History for db.sqlite

    • To see the history of changes for your db.sqlite file, run:

      ```shell
git log --pretty=format:'%h %cd %s' --date=iso-strict -- db.sqlite
```
    • This will show a list of commits affecting db.sqlite with their short hash, date, and message.
  • Step 3: Identify the Commit to Restore
    • Review the output and copy the commit hash (<commit-hash>) for the version you want to restore.

      • Example output:

        ```shell
a1b2c3d4 2024-07-03T10:27:15+00:00 Updated db.sqlite after sync
e5f6g7h8 2024-07-01T08:11:42+00:00 Added notes
...
```
  • Step 4: Restore the db.sqlite File

    • Run the following command, replacing <commit-hash> with the hash you copied:

      ```shell
git checkout <commit-hash> -- db.sqlite
```
    • This restores db.sqlite to its state in that commit.
  • Step 5: (Optional) Commit the Restored Version
    • If you want to keep this restored version as the new current version and share it with collaborators, commit the change:
    • git add db.sqlite
git commit -m "Restore db.sqlite from commit <commit-hash>"
  • Step 6: (Optional) Compare Two Versions

    • If you want to compare two versions before restoring, you can extract them and use sqldiff:

      ```shell
git show <older-commit-hash>:db.sqlite > old.db.sqlite
git show <newer-commit-hash>:db.sqlite > new.db.sqlite

sqldiff old.db.sqlite new.db.sqlite
```
      • This will show you the differences between the two database files.

  • Troubleshooting

    • File not found: Make sure you are in the correct repository and that db.sqlite is tracked by Git.
    • Accidentally overwritten: You can always repeat the above process with a different commit hash.

  • Summary

    • Use git log to find the commit you want.
    • Use git checkout <commit-hash> -- db.sqlite to restore that version.
    • Optionally, commit the restoration.
    • Use sqldiff to compare database versions if needed.
    • By following these steps, you can safely restore your Logseq SQLite database to any previous version tracked in your Git repository.
2 Likes