Apple Books

Query your local Apple Books library on macOS. Read-only access to books, highlights, notes, collections, and reading progress.

Requirements

• macOS only — Apple Books stores data in

  ~/Library/Containers/com.apple.iBooksX/

• Full Disk Access required for the process running queries (System Settings → Privacy & Security → Full Disk Access)
• sqlite3 (pre-installed on macOS)
• Apple Books must have been opened at least once (databases are created on first launch)

Database discovery

The database filenames are consistent across macOS installs, but always resolve them dynamically in case Apple changes them in future versions:

BKLIBRARY_DB="$(ls ~/Library/Containers/com.apple.iBooksX/Data/Documents/BKLibrary/*.sqlite 2>/dev/null | head -1)"
AEANNOTATION_DB="$(ls ~/Library/Containers/com.apple.iBooksX/Data/Documents/AEAnnotation/*.sqlite 2>/dev/null | head -1)"

If either variable is empty, Apple Books has not been set up on this Mac.

Important: These are read-only queries. Never INSERT, UPDATE, or DELETE rows — doing so may corrupt Apple Books data or cause iCloud sync issues.

List all books

BKLIBRARY_DB="$(ls ~/Library/Containers/com.apple.iBooksX/Data/Documents/BKLibrary/*.sqlite 2>/dev/null | head -1)"
sqlite3 "$BKLIBRARY_DB" \
  "SELECT ZTITLE, ZAUTHOR, ZGENRE, ZPAGECOUNT, ZREADINGPROGRESS, ZISFINISHED, ZASSETID
   FROM ZBKLIBRARYASSET
   WHERE ZTITLE IS NOT NULL
   ORDER BY ZLASTOPENDATE DESC;"

Search books by title or author

BKLIBRARY_DB="$(ls ~/Library/Containers/com.apple.iBooksX/Data/Documents/BKLibrary/*.sqlite 2>/dev/null | head -1)"
sqlite3 "$BKLIBRARY_DB" \
  "SELECT ZTITLE, ZAUTHOR, ZGENRE, ZREADINGPROGRESS, ZASSETID
   FROM ZBKLIBRARYASSET
   WHERE ZTITLE IS NOT NULL AND (ZTITLE LIKE '%SEARCH_TERM%' OR ZAUTHOR LIKE '%SEARCH_TERM%')
   ORDER BY ZLASTOPENDATE DESC;"

Replace

SEARCH_TERM

with the user's query.

Currently reading (in progress, not finished)

BKLIBRARY_DB="$(ls ~/Library/Containers/com.apple.iBooksX/Data/Documents/BKLibrary/*.sqlite 2>/dev/null | head -1)"
sqlite3 "$BKLIBRARY_DB" \
  "SELECT ZTITLE, ZAUTHOR, ZGENRE,
          printf('%.0f%%', ZREADINGPROGRESS * 100) AS progress,
          datetime(ZLASTOPENDATE + 978307200, 'unixepoch', 'localtime') AS last_opened
   FROM ZBKLIBRARYASSET
   WHERE ZTITLE IS NOT NULL
     AND ZREADINGPROGRESS > 0.0
     AND (ZISFINISHED IS NULL OR ZISFINISHED = 0)
   ORDER BY ZLASTOPENDATE DESC;"

Finished books

BKLIBRARY_DB="$(ls ~/Library/Containers/com.apple.iBooksX/Data/Documents/BKLibrary/*.sqlite 2>/dev/null | head -1)"
sqlite3 "$BKLIBRARY_DB" \
  "SELECT ZTITLE, ZAUTHOR, ZGENRE,
          datetime(ZDATEFINISHED + 978307200, 'unixepoch', 'localtime') AS finished_date
   FROM ZBKLIBRARYASSET
   WHERE ZISFINISHED = 1
   ORDER BY ZDATEFINISHED DESC;"

Get all highlights and notes for a specific book

AEANNOTATION_DB="$(ls ~/Library/Containers/com.apple.iBooksX/Data/Documents/AEAnnotation/*.sqlite 2>/dev/null | head -1)"
sqlite3 "$AEANNOTATION_DB" \
  "SELECT ZANNOTATIONSELECTEDTEXT, ZANNOTATIONNOTE, ZANNOTATIONSTYLE,
          datetime(ZANNOTATIONCREATIONDATE + 978307200, 'unixepoch', 'localtime') AS created
   FROM ZAEANNOTATION
   WHERE ZANNOTATIONDELETED = 0
     AND ZANNOTATIONASSETID = 'ASSET_ID'
     AND length(ZANNOTATIONSELECTEDTEXT) > 0
   ORDER BY ZPLLOCATIONRANGESTART ASC;"

Replace

ASSET_ID

with the book's

ZASSETID

from the library query.

Get all highlights across all books (with book titles)

BKLIBRARY_DB="$(ls ~/Library/Containers/com.apple.iBooksX/Data/Documents/BKLibrary/*.sqlite 2>/dev/null | head -1)"
AEANNOTATION_DB="$(ls ~/Library/Containers/com.apple.iBooksX/Data/Documents/AEAnnotation/*.sqlite 2>/dev/null | head -1)"
sqlite3 "$AEANNOTATION_DB" \
  "ATTACH DATABASE '$BKLIBRARY_DB' AS lib;
   SELECT lib.ZBKLIBRARYASSET.ZTITLE, lib.ZBKLIBRARYASSET.ZAUTHOR,
          ZAEANNOTATION.ZANNOTATIONSELECTEDTEXT, ZAEANNOTATION.ZANNOTATIONNOTE,
          datetime(ZAEANNOTATION.ZANNOTATIONCREATIONDATE + 978307200, 'unixepoch', 'localtime') AS created
   FROM ZAEANNOTATION
   JOIN lib.ZBKLIBRARYASSET ON ZAEANNOTATION.ZANNOTATIONASSETID = lib.ZBKLIBRARYASSET.ZASSETID
   WHERE ZAEANNOTATION.ZANNOTATIONDELETED = 0
     AND length(ZAEANNOTATION.ZANNOTATIONSELECTEDTEXT) > 0
   ORDER BY ZAEANNOTATION.ZANNOTATIONCREATIONDATE DESC
   LIMIT 50;"

List collections (shelves)

BKLIBRARY_DB="$(ls ~/Library/Containers/com.apple.iBooksX/Data/Documents/BKLibrary/*.sqlite 2>/dev/null | head -1)"
sqlite3 "$BKLIBRARY_DB" \
  "SELECT c.ZTITLE, c.ZCOLLECTIONID, COUNT(m.Z_PK) AS book_count
   FROM ZBKCOLLECTION c
   LEFT JOIN ZBKCOLLECTIONMEMBER m ON m.Z_PK IN (
     SELECT Z_PK FROM ZBKCOLLECTIONMEMBER
   )
   WHERE c.ZDELETEDFLAG = 0 AND c.ZTITLE IS NOT NULL
   GROUP BY c.ZCOLLECTIONID
   ORDER BY c.ZTITLE;"

Reading stats

BKLIBRARY_DB="$(ls ~/Library/Containers/com.apple.iBooksX/Data/Documents/BKLibrary/*.sqlite 2>/dev/null | head -1)"
sqlite3 "$BKLIBRARY_DB" \
  "SELECT
     COUNT(*) AS total_books,
     SUM(CASE WHEN ZISFINISHED = 1 THEN 1 ELSE 0 END) AS finished,
     SUM(CASE WHEN ZREADINGPROGRESS > 0 AND (ZISFINISHED IS NULL OR ZISFINISHED = 0) THEN 1 ELSE 0 END) AS in_progress,
     SUM(CASE WHEN ZREADINGPROGRESS = 0 OR ZREADINGPROGRESS IS NULL THEN 1 ELSE 0 END) AS not_started,
     printf('%.0f%%', AVG(ZREADINGPROGRESS) * 100) AS avg_progress
   FROM ZBKLIBRARYASSET
   WHERE ZTITLE IS NOT NULL;"

Annotation styles

Style value	Color
1	Green
2	Blue
3	Yellow
4	Pink
5	Purple

Annotation types

Type value	Meaning
2	Highlight
3	Bookmark

Date handling

Apple Books uses Core Data timestamps (seconds since 2001-01-01). To convert to human-readable:

datetime(TIMESTAMP_COLUMN + 978307200, 'unixepoch', 'localtime')

Troubleshooting

• "unable to open database file" — Grant Full Disk Access to the process (OpenClaw gateway / node) in System Settings → Privacy & Security → Full Disk Access
• Empty results — Open Apple Books at least once so macOS creates the databases
• Stale data — Apple Books may hold a write lock while open; queries still work in WAL mode but data may lag a few seconds behind the UI

Notes

• ZASSETID

  is the key that links books to their annotations

• ZREADINGPROGRESS

  is a float from 0.0 to 1.0

• ZISFINISHED

  is 1 when marked as finished, NULL or 0 otherwise
• The

  ZLASTOPENDATE

  field tracks when the book was last opened
• All queries are read-only — never modify these databases
• Audiobooks from Apple Books also appear in this database (

  ZISSTOREAUDIOBOOK = 1

  )