Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Export Highlights from Apple Books
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");
})();
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment