18

Like everyone else who uses Apple's Messages app, I have a chat.db file. I'd like to read the file but nothing seems to yeild the expected results.

Here is what I've tried so far:

  • The CLI approach of cd to ~/Libray/Messages followed by open chat.db was amongst the best in that something was found and there was an attempt to open the file. Unfortunately, it didn't work as hoped.

    enter image description here

  • Since I now had the db browser for SQLite open, I tried to open the file via that app's gui but because the folder is hidden one, it didn't show.

    enter image description here

Has anyone had any success with this? I certainly can't be the first person to have tried and I've seen elsewhere the schema may also be an issue.

0

4 Answers 4

14

The file you have found is a SQLite database and you need an application which can read SQLite databases. You have many choices - search in the Mac App Store for SQLite (or similar) or use the Terminal command sqlite3.

But a SQLite database is intimidating to the first time user, even though it is amongst the simplest of relational databases. A "relational database" organises data into one or more tables (or "relations") of rows and columns. The power (and complexity) of relational databases comes with connecting data in two or more tables via relationships.

To make sense of a SQLite database, you need:

  1. An application - I use "SQLPro for SQLite" from the Apple App Store. Its web page is SQLitePro.
  2. A query to extract the data you want. I have "cheated" and found one here SQL query for chat.

Steps to read chat.db:

  1. Make a copy of the database - I copied mine to the Desktop.
  2. Run your app and open the chat.db.
  3. Choose the query tab.
  4. Enter this query:

Copy and paste

select
 m.rowid
,coalesce(m.cache_roomnames, h.id) ThreadId
,m.is_from_me IsFromMe
,case when m.is_from_me = 1 then m.account
 else h.id end as FromPhoneNumber
,case when m.is_from_me = 0 then m.account
 else coalesce(h2.id, h.id) end as ToPhoneNumber
,m.service Service

/*,datetime(m.date + 978307200, 'unixepoch', 'localtime') as TextDate -- date stored as ticks since 2001-01-01 */
,datetime((m.date / 1000000000) + 978307200, 'unixepoch', 'localtime') as TextDate /* after iOS11 date needs to be / 1000000000 */

,m.text MessageText

,c.display_name RoomName

from
message as m
left join handle as h on m.handle_id = h.rowid
left join chat as c on m.cache_roomnames = c.room_name /* note: chat.room_name is not unique, this may cause one-to-many join */
left join chat_handle_join as ch on c.rowid = ch.chat_id
left join handle as h2 on ch.handle_id = h2.rowid

where
-- try to eliminate duplicates due to non-unique message.cache_roomnames/chat.room_name
(h2.service is null or m.service = h2.service)

order by m.date desc;
  1. Execute the query.

I can then see all my messages (I have squashed up some of the columns to partially hide my content):

Chat database

You can use different applications - I have used dbHarbour (free, I think).

Or you can do it in Terminal with a few incredibly detailed and finicky / unforgiving-of-even-a-single-typo commands:

gilby@Beth/Users/gilby/Desktop% sqlite3 chat.db
SQLite version 3.32.3 2020-06-18 14:16:19
Enter ".help" for usage hints.
sqlite> .mode csv
sqlite> .output chat.csv
sqlite> select   <<<< Just copy and paste the query from above
   ...>  m.rowid
   ...> ,coalesce(m.cache_roomnames, h.id) ThreadId
   ...> ,m.is_from_me IsFromMe
   ...> ,case when m.is_from_me = 1 then m.account
   ...>  else h.id end as FromPhoneNumber
   ...> ,case when m.is_from_me = 0 then m.account
   ...>  else coalesce(h2.id, h.id) end as ToPhoneNumber
   ...> ,m.service Service
   ...>
   ...> /*,datetime(m.date + 978307200, 'unixepoch', 'localtime') as TextDate -- date stored as ticks since 2001-01-01 */
   ...> ,datetime((m.date / 1000000000) + 978307200, 'unixepoch', 'localtime') as TextDate /* after iOS11 date needs to be / 1000000000 */
   ...>
   ...> ,m.text MessageText
   ...>
   ...> ,c.display_name RoomName
   ...>
   ...> from
   ...> message as m
   ...> left join handle as h on m.handle_id = h.rowid
   ...> left join chat as c on m.cache_roomnames = c.room_name /* note: chat.room_name is not unique, this may cause one-to-many join */
   ...> left join chat_handle_join as ch on c.rowid = ch.chat_id
   ...> left join handle as h2 on ch.handle_id = h2.rowid
   ...>
   ...> where
   ...> -- try to eliminate duplicates due to non-unique message.cache_roomnames/chat.room_name
   ...> (h2.service is null or m.service = h2.service)
   ...>
   ...> order by m.date desc;      <<<< Press Return
sqlite>

The mode and output commands make sure that the output goes to a CSV file which you can open in a spreadsheet. The query is exactly as before.

That is a rather deep plunge into SQLite. Best of luck.

IMPORTANT EDIT:

For iOS16/macOS13, there are some changes to chat.db to support new features of Messages. Whilst the above SQL executes correctly, it does not show the message text for new messages.

Read: https://stackoverflow.com/questions/74579463/how-to-decode-message-summary-info-in-macos-messages-chat-db and https://stackoverflow.com/questions/72646565/in-the-new-macos-13-chat-db-where-is-the-text-from-edited-messages-stored which tell us that message text is now in message_summary_info.

Here is a explanation from the point of view of a forensics specialist: What Digital Investigators Need to Know

At present the required changes are outside my skill set! If anyone has a solution, please append to this answer.

3
  • Does this still work for you? It used to work for me but now I notice the message.text field is blank for messages sent by me but still populated for messages that I received from someone else. Commented Jan 16, 2023 at 4:04
  • @JessePangburn No it doesn't!! For me, all new messages the message.text field is blank. I have appending an edit to my answer with what I have discovered. No solution.
    – Gilby
    Commented Jan 16, 2023 at 8:22
  • Bummer. I was hoping it was just me! I had made a chatbot for scammers texting me, and this change messed it up. Commented Jan 19, 2023 at 17:51
2

OR - you could Eff all this noise and go use this

https://github.com/ReagentX/imessage-exporter/blob/develop/docs/features.md

Or reverse-engineer the coder's solution to handling the typed-stream data.

0
1

The attributedBody field is stored in an Apple-proprietary typedstream format, which I've found is actually quite parseable via the pytypedstream package for Python (use the typedstream.stream.TypedStreamReader function).

Assuming you are using pandas, here is a full example to retrieve all messages across all conversations. I leave it to you to filter the data how you please.

import os
import os.path
import sqlite3

import pandas as pd
from typedstream.stream import TypedStreamReader


# The textual contents of some messages are encoded in a special attributedBody
# column on the message row; this attributedBody value is in Apple's proprietary
# typedstream format, but can be parsed with the pytypedstream package
# (<https://pypi.org/project/pytypedstream/>)
def decode_message_attributedbody(data):
    if not data:
        return None
    for event in TypedStreamReader.from_data(data):
        # The first bytes object is the one we want
        if type(event) is bytes:
            return event.decode("utf-8")


def main():
    db_path = os.path.expanduser("~/Library/Messages/chat.db")
    with sqlite3.connect(db_path) as connection:
        messages_df = pd.read_sql_query(
            sql="SELECT text, attributedBody FROM message ORDER BY date DESC",
            con=connection,
            parse_dates={"datetime": "ISO8601"},
        )
        # Decode any attributedBody values and merge them into the 'text' column
        messages_df["text"] = messages_df["text"].fillna(
            messages_df["attributedBody"].apply(decode_message_attributedbody)
        )
        print(messages_df["text"])


if __name__ == "__main__":
    main()
0

I was able to solve / workaround the blanks on new message texts. The data is in that attributedBody field. You have choices. What I've done is just read them in DB Browser for SQLite.

I also read the ~/Library/AddressBook sqlite files and compiled a list of phone numbers and names.

Here's my new SQL query for ya

 m.rowid
,coalesce(m.cache_roomnames, h.id) ThreadId
,a.field2 Person
,m.is_from_me IsFromMe
,m.text MessageText
,m.attributedBody attributedBody
,datetime((m.date / 1000000000) + 978307200, 'unixepoch', 'localtime') as TextDate
,case when m.is_from_me = 1 then m.account
 else h.id end as FromPhoneNumber
,case when m.is_from_me = 0 then m.account
 else coalesce(h2.id, h.id) end as ToPhoneNumber
,m.service Service

,c.display_name RoomName

from
message as m
left join handle as h on m.handle_id = h.rowid
left join chat as c on m.cache_roomnames = c.room_name /* note: chat.room_name is not unique, this may cause one-to-many join */
left join chat_handle_join as ch on c.rowid = ch.chat_id
left join handle as h2 on ch.handle_id = h2.rowid
left join MasterAddressBook_Filtered as a on h.id = a.field1 /* import table from CSV */

where
(h2.service is null or m.service = h2.service)

order by m.date DESC;````
6
  • your other option is that you can work with the python / nodejs packages for apple's typestreams. I've not quite yet gotten the code to work where it will read the BLOB in attributedBody and decode it to perform an update the messageText. But i'm working on it; Commented Feb 7, 2023 at 19:21
  • alright now, this is HACKY like a sack but it worked. I wish i could do it all in nodejs but i'm not getting how to read the pipeline to get out the strings. Commented Feb 9, 2023 at 19:29
  • The script is too long so I made a GIST gist.github.com/johnnyspire/b9cc2a649edfcec97ed0e0b1175d7caa Commented Feb 9, 2023 at 19:34
  • In your GIST, you write the attributedBody field to a file, then use an executable called pytypedstream.exe to decode it. However, I did a search and found no such known executable. Maybe you can comment on how you actually decode that field? Commented Mar 21, 2023 at 20:29
  • 1
    @JessePangburn thanks for catching that - the python lib I used and how to configure it is here - github.com/dgelessus/python-typedstream - I tried like hell to make this work in Node but it was eating up time so I just used what worked. Sometimes "good enough" has to be good enough. If someone else is better at this, I'd love to see it in all NodeJs or something. Commented Mar 23, 2023 at 18:15

You must log in to answer this question.

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