4

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!

2
  • 1
    There was a 3rd party app designed to let you browse all of your iChat/Messages.app logs easily and efficiently. But I cannot find it. It sounds like it's just what you need though.
    – l008com
    Commented Apr 4, 2018 at 21:55
  • Thanks@l008com! That does sound related, I'd love to be able to access them natively. Do you think this is a better question for stack overlflow? Commented Apr 4, 2018 at 21:57

1 Answer 1

10

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.

    Finder Sources folder

  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
    
5
  • Thanks a lot @grgarside. Is there a way that you're aware of doing this on-the-fly lookup? I suppose I could make a new table but that seems pretty arduous. Is the contacts->phone table something that exists? Commented Apr 5, 2018 at 17:51
  • @Peter I’m away from my computer at the moment and will look into it more later then, but just to add some more info: by ‘on-the-fly’ I mean Messages stores no information about the contacts itself anywhere in chat.db, instead it looks up this information through the Contacts app (framework) and these APIs do whatever they need to do to get the information, whether that be from a database like chat.db but for contacts or something else I don’t know, I will look into it. This is why when your iPhone or Mac is logged out the contact name is not filled into messages notifications.
    – grg
    Commented Apr 5, 2018 at 18:17
  • Ahh, interesting. Appreciate all the help! Don't go too much out of your way, but any insight would be much appreciated :) Interesting about the logout. Thanks again, let me know what you find! Commented Apr 5, 2018 at 18:23
  • @Peter A couple of hours of interesting research through some databases lead me to the edit I've made to my answer, let me know how it goes!
    – grg
    Commented Apr 6, 2018 at 22:40
  • Wow, truly above and beyond on this answer @grgarside. Your passion and work are really appreciated! Thanks so much! Commented Apr 6, 2018 at 22:41

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .