Direct access to the Evernote SQL database - mengwong

On OS X and probably other desktop platforms, one may address the Evernote.sql database directly through an sqlite3 client. SQLite Manager for Firefox is one option; the command line client is another.

The most important table is Zenattributedentity; it is denormalized; multiple object types are flattened into a single table, distinguished by their z_ent entity type column.

Z_ENT = 11: shared notebooks (zsharename)
Z_ENT = 12: note (ztitle)
Z_ENT = 13: notebook (zname)
Z_ENT = 14: attachment (zfilename)
Z_ENT = 15: saved search
Z_ENT = 17: tag


This SQL query finds all excel attachments.

SELECT "open $HOME/Library/Caches/Metadata/com.evernote.Evernote/p" || notes.z_pk || ".evernote" as open, attachments.zfilename, notes.ztitle, notebooks.zname from zenattributedentity attachments join zenattributedentity notes join zenattributedentity notebooks on attachments.znote = notes.z_pk and notes.znotebook1 = notebooks.z_pk where attachments.zfilename like "%xls%";


After using DBIx::Class::Schema::Loader's dbicdump to extract the schema,

dbicdump -o dump_directory=/Users/mengwong/lib/perl/ -o components='["InflateColumn::DateTime"]' Evernote::Schema dbi:SQLite:/Users/mengwong/Library/Application\ Support/Evernote/data/*/Evernote.sql '{ quote_char => "\"" }'

The following DBIx::Class code in Zenattributedentity.pm will create some of the appropriate relationships:

# the has_many through the Z12tag table won't work if the Z12tag table doesn't type its columns.
__PACKAGE__->has_many(tag2note => 'Evernote::Schema::Result::Z12tag', "z_17tags" );
__PACKAGE__->has_many(note2tag => 'Evernote::Schema::Result::Z12tag', "z_12notes" );
__PACKAGE__->has_many(notebook2note => 'Evernote::Schema::Result::Zenattributedentity', "znotebook1");
__PACKAGE__->has_many(attachments => 'Evernote::Schema::Result::Zenattributedentity', "znote");

Other relations like "a note belongs_to a notebook" are left as an exercise for the reader.


A separate table, Z12Tags, records note/tag relationships.

Please don't contact me with requests for assistance; try the Evernote forum or leave comments in this post.