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:
- An application - I use "SQLPro for SQLite" from the Apple App Store. Its web page is SQLitePro.
- A query to extract the data you want. I have "cheated" and found one here SQL query for chat.
Steps to read chat.db:
- Make a copy of the database - I copied mine to the Desktop.
- Run your app and open the chat.db.
- Choose the query tab.
- 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;
- Execute the query.
I can then see all my messages (I have squashed up some of the columns to partially hide my content):
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.
