Skip to content

Migrate JSONL data to SQLite

Use this guide when you are upgrading an existing ZettelForge installation from v2.1.x (JSONL storage) to v2.2.0 or newer (SQLite default) and you want to carry your notes, knowledge graph, and entity index forward.

Fresh installs do not need this. ZettelForge 2.2.0 and later create an empty SQLite database on first use.

Prerequisites

  • ZettelForge v2.2.0 or newer installed (pip install -U zettelforge)
  • Read and write access to your data directory (~/.amem by default, or whatever AMEM_DATA_DIR / storage.data_dir points to)
  • Free disk space for a full backup alongside the new database — expect roughly 1.5x the original JSONL size during migration

What the script does

scripts/migrate_jsonl_to_sqlite.py runs five steps:

  1. Copies notes.jsonl, kg_nodes.jsonl, kg_edges.jsonl, and entity_index.json into <data_dir>/backup_pre_sqlite/.
  2. Opens or creates <data_dir>/zettelforge.db via SQLiteBackend (WAL journal mode, thread-safe via RLock).
  3. Writes every note using INSERT OR REPLACE.
  4. Upserts knowledge-graph nodes and edges.
  5. Adds entity mappings using INSERT OR IGNORE.

The script is idempotent — running it a second time produces the same database state. The original JSONL files are never deleted.

The --dry-run flag does not prevent writes

Despite the flag name, --dry-run creates the SQLite database and runs the full migration — it only adds a [DRY RUN] label to the console output. This is a known source-level behavior. Because the migration is idempotent, a first run is always safe on an empty or pre-existing database.

Run the migration

python scripts/migrate_jsonl_to_sqlite.py --data-dir ~/.amem

Output format (record counts will match your data):

ZettelForge JSONL -> SQLite Migration
Data directory: /home/you/.amem

[1/5] Backing up source files...
  Backed up notes.jsonl
  Backed up kg_nodes.jsonl
  Backed up kg_edges.jsonl
  Backed up entity_index.json
  Backup directory: /home/you/.amem/backup_pre_sqlite

[2/5] Migrating notes...
  Migrated <N> notes

[3/5] Migrating KG nodes...
  Migrated <N> nodes

[4/5] Migrating KG edges...
  Migrated <N> edges

[5/5] Migrating entity index...
  Migrated <N> entity mappings

==================================================
Migration complete!
  Notes:           <N>
  KG nodes:        <N>
  KG edges:        <N>
  Entity mappings: <N>
  Database:        /home/you/.amem/zettelforge.db

JSONL files have NOT been deleted.
Backup saved to: /home/you/.amem/backup_pre_sqlite

The database file is written with 0600 permissions (owner read/write only).

If a file is absent from your data directory the script warns and skips it — this is normal if you never used the TypeDB graph or the entity index.

Verify

After the migration, confirm your notes are reachable:

from zettelforge import MemoryManager

mm = MemoryManager()           # reads ~/.amem by default
stats = mm.get_stats()
print(stats["total_notes"])    # matches the migration log
print(mm.recall("APT28", k=3))

You can also inspect the database directly:

sqlite3 ~/.amem/zettelforge.db "SELECT COUNT(*) FROM notes;"
sqlite3 ~/.amem/zettelforge.db "SELECT COUNT(*) FROM kg_edges;"

Roll back

If anything looks wrong, delete the database and re-migrate from the original JSONL files (which were not deleted):

ls ~/.amem/backup_pre_sqlite/
# notes.jsonl  kg_nodes.jsonl  kg_edges.jsonl  entity_index.json
rm ~/.amem/zettelforge.db
python scripts/migrate_jsonl_to_sqlite.py --data-dir ~/.amem

Clean up (optional)

Once you are confident the SQLite store is healthy, move the backup out of your data directory:

mv ~/.amem/backup_pre_sqlite ~/zettelforge-jsonl-backup-$(date +%Y-%m-%d)

Delete the backup only after a full release cycle with no regressions.