Can I access contact names in chat.db?

Question

I've been playing around with the chat.db file in Library/Messages to look at my messages from a sql perspective. I'm wondering if it's possible to see contact names in this db, specifically somehow joining on the message table. My poking around hasn't been helpful, and neither has googling.

Any insights appreciated!

Answer 1

You can join the handle table to the messages table.

select date, id, text
from message
left join handle
on message.handle_id = handle.ROWID

However, this only provides the phone number or Apple ID email address. This is because chat.db does not store contact names — this is looked up on-the-fly by Messages.

You can attach the address book database and perform a query joining tables from the two databases to get the information you need.

1. Start a sqlite3 session. Open Terminal and run

   sqlite3
   

   SQLite version 3.19.3 2017-06-27 16:48:08
   Enter ".help" for usage hints.
   Connected to a transient in-memory database.
   Use ".open FILENAME" to reopen on a persistent database.
   sqlite>
   

2. Determine which Contacts database to use. Contacts can come from multiple sources. In my case, all my contacts are on iCloud, so it's easy to choose the largest database. Go to

   ~/Library/Application Support/AddressBook/Sources
   

   and find the folder with the largest size, or otherwise have a look through each folder's Metadata folder using QuickLook to find contacts within the database.

   

3. Once you've found the folder in Sources containing the contacts you wish, get the path to the database. Right-click on AddressBook-v22.abcddb within the folder with your contacts, hold ⌥ and select ‘Copy “AddressBook-v22.abcddb” as Pathname’.

4. In Terminal within sqlite3, run the following:

   attach "/Users/yourusername/Library/Messages/chat.db" as cdb;
   attach "<paste your path>" as adb;
   

   Replace ‘yourusername’ in the first command and paste the path you copied in the second one.

5. Run this SQL. Copy and paste all these lines in.

   select date, id, ZFIRSTNAME || ' ' || ZLASTNAME, text
   from cdb.message
   left join cdb.handle
   on message.handle_id = handle.ROWID
   left join adb.ZABCDPHONENUMBER
   on replace(substr(handle.id, 4), ' ', '')
   like '%' || substr(replace(ZABCDPHONENUMBER.ZFULLNUMBER, ' ', ''), 2)
   left join adb.ZABCDRECORD
   on ZABCDPHONENUMBER.ZOWNER = ZABCDRECORD.Z_PK;
   

   Note that this SQL contains some magic numbers.

   • substr(handle.id, 4) presumes phone numbers from Messages start with a country code in the format of a plus followed by 2 digits.
   • substr(replace(…), 2) presumes phone numbers from Contacts start with a 0.

   This is the standard international format. This will need to be adjusted for US.

6. You should get an output resembling:

   datetimestamp|+441234567890|First Last|Lorem ipsum dolor sit amet