apple-books

# 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:

```bash
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

```bash
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

```bash
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)

```bash
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

```bash
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

```bash
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)

```bash
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)

```bash
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

```bash
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:
```sql
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`)