Can I access contact names in chat.db? 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!
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.


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&gt;

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.


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’.
In Terminal within sqlite3, run the following:

attach "/Users/yourusername/Library/Messages/chat.db" as cdb;
attach "&lt;paste your path&gt;" as adb;


Replace ‘yourusername’ in the first command and paste the path you copied in the second one.
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.
You should get an output resembling:

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