5

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.

6
  • Show hidden files with SHIFT-COMMAND-PERIOD. I think that fie IS a sqllite database so that should work. Jun 1 at 20:26
  • So, thanks for the quick response. But that doesn't really do anything different than the CLI approach. And you're correct in that this is a SQLite database.
    – hrokr
    Jun 1 at 20:30
  • It is a SQLite database and you can open it with a SQLite application. There are many in the App Store. But you need someone to supply a "query" which will present the data in a meaningful way. My skills are a bit rusty (and were never good) otherwise I would make an answer with a suitable query. Maybe someone... And ALWAYS make copy of the database before messing about with it!
    – Gilby
    Jun 1 at 23:57
  • You can also open SQLite databases in Terminal. Just use the command sqlite3 chat.db to get you started. I repeat, ALWAYS make a copy of the database before messing with it.
    – Gilby
    Jun 2 at 0:00
  • I found a query and so have made an answer.
    – Gilby
    Jun 2 at 0:53
5

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

2
  • That works as described and I think I can go from here. Thanks!
    – hrokr
    Jun 3 at 0:33
  • @hrokr Great. Sorting it out was good for me too!
    – Gilby
    Jun 3 at 3:08

You must log in to answer this question.

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