Export Highlights from Apple Books

fetchHighlightsFromAppleBooks.js

const fs = require("fs");
const sqlite3 = require("sqlite3");
const glob = require("glob");
const os = require("os");
const { open } = require("sqlite");

const username = os.userInfo().username;
const ANNOTATION_DB_PATH = `/users/${username}/Library/Containers/com.apple.iBooksX/Data/Documents/AEAnnotation/`;
const BOOK_DB_PATH = `/users/${username}/Library/Containers/com.apple.iBooksX/Data/Documents/BKLibrary/`;
const annotationsFiles = glob.sync(`${ANNOTATION_DB_PATH}/*.sqlite`);
const booksFiles = glob.sync(`${BOOK_DB_PATH}/*.sqlite`);

const APPLE_EPOCH_START = new Date("2001-01-01").getTime();

const SELECT_ALL_ANNOTATIONS_QUERY = `
select 
  ZANNOTATIONASSETID as assetId,
  ZANNOTATIONSELECTEDTEXT as quote,
  ZANNOTATIONNOTE as comment,
  ZFUTUREPROOFING5 as chapter,
  ZANNOTATIONSTYLE as colorCode,
  ZANNOTATIONMODIFICATIONDATE as modifiedAt,
  ZANNOTATIONCREATIONDATE as createdAt
from ZAEANNOTATION
where ZANNOTATIONDELETED = 0 
  and ZANNOTATIONSELECTEDTEXT is not null 
  and ZANNOTATIONSELECTEDTEXT <> ''
order by ZANNOTATIONASSETID, ZPLLOCATIONRANGESTART;`;
const SELECT_ALL_BOOKS_QUERY = `select ZASSETID as id, ZTITLE as title, ZAUTHOR as author from ZBKLIBRARYASSET`;

function convertAppleTime(appleTime) {
  return new Date(APPLE_EPOCH_START + appleTime * 1000).getTime();
}

async function createDB(filename) {
  return await open({
    filename: filename,
    driver: sqlite3.Database,
  });
}

async function getBooksFromDBFile(filename) {
  const db = await createDB(filename);
  return await db.all(SELECT_ALL_BOOKS_QUERY);
}

async function getBooks() {
  const books = await Promise.all(booksFiles.map(getBooksFromDBFile));
  return books.flat();
}

async function getAnnotationsFromDBFile(filename) {
  const db = await createDB(filename);
  return await db.all(SELECT_ALL_ANNOTATIONS_QUERY);
}

async function getAnnotations() {
  const annotations = await Promise.all(
    annotationsFiles.map(getAnnotationsFromDBFile)
  );
  return annotations.flat();
}

(async function main() {
  const books = await getBooks();
  const annotations = await getAnnotations();
  const booksByAssetId = {};
  const output = annotations.map(({ assetId, ...r }) => {
    if (booksByAssetId[assetId] === undefined) {
      booksByAssetId[assetId] = books.find((b) => b.id === assetId);
    }
    const book = booksByAssetId[assetId];
    return {
      ...r,
      modifiedAt: convertAppleTime(r.modifiedAt),
      createdAt: convertAppleTime(r.createdAt),
      author: book.author ?? "Unknown Author",
      title: book.title ?? "Unknown Title",
    };
  });
  fs.writeFileSync("output.json", JSON.stringify(output));
  console.log("Exported", output.length, "items");
})();