Browsing Chrome history with fzf
2015. 04. 06.

This is yet another post on fzf, a command-line fuzzy finder. Recently I've been trying to integrate fzf with everything I can think of, and this time, it's the browsing history of Google Chrome.

Chrome database

So how do we access the browsing history? Turns out that Chrome stores the information in its SQLite database file locally stored on your disk drive. The location of the file depends on your operating system. As of now, it's

In the following, we'll assume OS X, but you shouldn't have any difficulty applying the same strategy on Linux.

Anyway, now we know the location of the database, we can access the information with "sqlite3" command. However, if you're running Chrome at the moment, the database file is locked by the process and you'll see Error: database is locked message. An easy workaround is to simply make a copy of the file.

cp -f ~/Library/Application\ Support/Google/Chrome/Default/History /tmp/h

Let's examine the database. First we see the list of the tables in it.

sqlite3 /tmp/h .tables

  downloads             meta                  urls
  downloads_url_chains  segment_usage         visit_source
  keyword_search_terms  segments              visits

Okay, looks like urls is the table we're looking for. Now we check the schema of the table with .schema command.

sqlite3 /tmp/h '.schema urls'

  CREATE TABLE urls(
    id              INTEGER PRIMARY KEY,
    url             LONGVARCHAR,
    title           LONGVARCHAR,
    visit_count     INTEGER DEFAULT 0 NOT NULL,
    typed_count     INTEGER DEFAULT 0 NOT NULL,
    last_visit_time INTEGER NOT     NULL,
    hidden          INTEGER DEFAULT 0 NOT NULL,
    favicon_id      INTEGER DEFAULT 0 NOT NULL);
  CREATE INDEX urls_url_index ON urls (url);

Notice that I reformatted the output so that it's easier to read. The names of the columns are very straightforward, and we're ready to run some SQLs.

sqlite3 /tmp/h 'select title, url from urls order by last_visit_time desc'

Formatting

You'll notice that two fields in each line of the output are delimited by a | character.

  Issues · junegunn/fzf|https://github.com/junegunn/fzf/issues
  junegunn/fzf|https://github.com/junegunn/fzf
  junegunn.kr|http://junegunn.kr/

This is the default format. However, the delimiter is not good enough as the character is often found in titles and URLs and SQLite does not do any escaping.

sqlite3 /tmp/h "select '|', '|'"

  |||

To avoid possible conflicts, we're going to change the delimiter with -separator option. Let's pick some obscure string that'll probably never appear in the text. Also, as our screen estate is limited, we'll going to truncate the title field using substr() function of SQLite.

sqlite3 -separator {::} /tmp/h \
  "select substr(title, 1, $(( COLUMNS / 3 ))), url
   from urls order by last_visit_time desc"

Instead of hard-coding the width, we used $COLUMNS whose value is automatically set to the width of the terminal.

Since we now know the maximum length of the first field, we can use printf of awk command to align the fields.

cols=$(( COLUMNS / 3 ))
sep={::}
sqlite3 -separator $sep /tmp/h \
  "select substr(title, 1, $cols), url
   from urls order by last_visit_time desc" |
awk -F $sep '{printf "%-'$cols's  %s\n", $1, $2}'

  Issues · junegunn/fzf  https://github.com/junegunn/fzf/issues
  junegunn/fzf           https://github.com/junegunn/fzf
  junegunn.kr            http://junegunn.kr/

Much better. Let's further decorate the output with colors. We put ANSI escape code for cyan foreground color before the second URL field.

# Print URLs in cyan color (36)
awk -F $sep '{printf "%-'$cols's  \x1b[36m%s\x1b[m\n", $1, $2}'

Putting it all together

Finally time for fzf integration. We feed what we've prepared into fzf for interactive filtering. Since the input has ANSI escape codes, we need to pass --ansi option to fzf so that it correctly interprets the codes. Also, we pass --multi option so that the user can select multiple entries with TAB or shift-TAB key and open them at once. Once fzf is completed with the selections, we strip the title part with sed command (strip everything before "http"), and use open command of OS X to open the selected URLs with the default browser.

# c - browse chrome history
c() {
  local cols sep
  cols=$(( COLUMNS / 3 ))
  sep='{::}'

  cp -f ~/Library/Application\ Support/Google/Chrome/Default/History /tmp/h

  sqlite3 -separator $sep /tmp/h \
    "select substr(title, 1, $cols), url
     from urls order by last_visit_time desc" |
  awk -F $sep '{printf "%-'$cols's  \x1b[36m%s\x1b[m\n", $1, $2}' |
  fzf --ansi --multi | sed 's#.*\(https*://\)#\1#' | xargs open
}

Now we can just type in c from terminal to browse the entire Chrome history, fuzzy-search for links, and open them in the default browser. Quite handy.

Although the code shown here is for Chrome on OS X, it won't be difficult to adapt it for FireFox which also has its own SQLite database, and for Linux, by replacing open with something like xdg-open.

» capture | close