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”

Or, if you would rather work with tab delimited text (and we will want to for a future article) and pull all of the information for these users (which would need to be cross referenced against their unique ID such as ZSERIALNUMBER for other pertinent information btw):
sqlite3 -separator ‘t’ ~/Library/Application Support/AddressBook/AddressBook-v22.abcddb ‘select * from ZABCDEMAILADDRESS’
Next we’re going to simply dump the contents of our file out to a text file called alladdys.txt, stored in /Scripts/alladdys.txt:

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

Now we’re going to go ahead and limit the output to addresses including krypted@mac.com and dump that into a file in the same folder called specificaddy.txt, which allows us to check Address Book to see if an email address is there (might be useful later):

sqlite3 -separator ‘t’ ~/Library/Application Support/AddressBook/AddressBook-v22.abcddb ‘select DISTINCT(ZADDRESS) from ZABCDEMAILADDRESS’ | grep krypted@mac.com > /Scripts/specificaddy.txt

While we were specifically looking for Address Book information, it’s worth noting that sqlite is fairly prolific in Mac OS X.  It is used with iCal, which stores databases in ~/Library/Calendars/Calendar Cache.  It is also used for some things in Mail.app, which stores information in ~/Library/Mail/Envelope Index.  Safari also stores information about RSS feeds in ~/Library/Syndication/Database3.

3 Comments

  • jjoreskog
    October 7, 2009 - 5:44 pm | Permalink

    Remember to finish each command line with a semicolon.

  • December 16, 2009 - 1:55 am | Permalink

    sudo port install contacts

  • LoB
    January 22, 2011 - 6:41 am | Permalink

    For errors such as this one:
    http://discussions.apple.com/thread.jspa?messageID=10984659&tstart=0

    How can I identify groups / contacts bearing missing ‘distribution list’ information. I am sure this could be done using sqlite command lines but how?

  • Comments are closed.