How (specificially) do I read a chat.db file?

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.

  

• 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.

  

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.

Solution 1:

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 not simple. It is a series of interrelated tables. Connections between the tables define relationships and hence the term "relational database". SQLite are perhaps the simplest of SQL relational databases, but even so they are intimidating to the first time user.

To make sense of a SQLite datebase 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;

4. 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 simple 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.