Alfred is one of my favourite productivity apps for the Mac. It’s a file indexer, a clipboard manager, a snippet expander - and that’s just scratching the surface really. I recently got a new machine without it installed and realised just how much I rely on Alfred, particularly its clipboard manager.

With the clipboard manager Alfred keeps a history of the text (and images, cool huh!) that I put on my clipboard, and I can recall any of them using a simple shortcut

alfred01

The clipboard itself is stored in a file by Alfred, and is just a SQLite database. Since it’s that, you can query it using SQL!

To find your clipboard history file go to Alfred’s preferences pane and Advanced. Click the Reveal in Finder in the bottom right of the window, and in the resulting folder there should be a Databases sub-folder and within that a clipboard.alfdb file.

Note
 This is not intended as a user-servicable file! Don’t blame me if opening it in SQLite knackers it, and definitely don’t try running `UPDATE`s against it…

With the file located, you can query it by opening it up with SQLite:

$ sqlite3 clipboard.alfdb

Here’s the clipboard history table’s schema:

.schema clipboard

CREATE TABLE clipboard(item, ts decimal, app, apppath, dataType integer, dataHash);
CREATE INDEX clipboard_items ON clipboard (item);
CREATE INDEX clipboard_ts ON clipboard (ts);
CREATE INDEX clipboard_app ON clipboard (app);
CREATE INDEX clipboard_datatype ON clipboard (dataType);
CREATE INDEX clipboard_datahash ON clipboard (dataHash);

You can query individual rows:

sqlite> .header on
sqlite> .mode column
sqlite> SELECT * FROM clipboard LIMIT 1;

item                                                                                 ts          app            apppath                          dataType    dataHash
-----------------------------------------------------------------------------------  ----------  -------------  -------------------------------  ----------  ----------
https://get.slack.help/hc/en-us/articles/202288908-Format-your-messages#code-blocks  610489734   Google Chrome  /Applications/Google Chrome.app  0

and you can run aggregations on it, here showing which application I most frequently copy content from:

sqlite> select app,count(*) from clipboard group by app order by 2 desc;
app            count(*)
-------------  ----------
Brave Browser  4080
Visual Studio  2996
iTerm          1473
Emacs          152
Telegram       105

or even how many times I’ve copied a link from my blog to send to someone :-)

sqlite> SELECT count(*) FROM clipboard WHERE item LIKE '%rmoff.net%';
99
Note
 This is not intended as a user-servicable file! Don’t blame me if opening it in SQLite knackers it, and definitely don’t try running `UPDATE`s against it…