sqlite3 and Address Book.app from the Command Line

The Mac OS X program, Address Book uses sqlite3 to store information.  The actual database is located in each users Library/Application Support/AddressBook directory and called AddressBook-v22.abcddb.  In order to interfaces with Address Book.app you can use the sqlite3 command followed by the path to the database itself.  For example, the following command will simply dump you into a sqlite interactive command line environment:

sqlite3 ~/Library/Application Support/AddressBook/AddressBook-v22.abcddb

Once in the environment you can view databases, manually work with the data, etc.  The basic information about a contact is stored in the ZABCDRECORD table.  You can view the contents of this table using the following command:

select * from ZABCDRECORD

If you type the following then you’ll list all of the contents of the ZABCDEMAILADDRESS table:

select * from ZABCDEMAILADDRESS

Notice that here you’ll see email addresses, but also a lot more information.  To figure out which column of your table that you want to look at to just see the email addresses, use the following command at the sqlite3 interactive prompt (noting that it’s case sensitive):

.header ON

Now that we know that we want to constrain our output to ZADDRESS, go ahead and use the following command to simply list the email addresses:

“select DISTINCT(ZADDRESS) from ZABCDEMAILADDRESS”

You can also run the above query from a single command, rather than using the interactive prompt:

sqlite3 ~/Library/Application Support/AddressBook/AddressBook-v22.abcddb “select DISTINCT(ZADDRESS) from ZABCDEMAILADDRESS”

sqlite3 -separator ‘t’ ~/Library/Application Support/AddressBook/AddressBook-v22.abcddb ‘select * from ZABCDEMAILADDRESS’