iMessage is one of my favorite things about having an iPhone and a Mac. It’s also one of my least favorite things.
I love being able to send messages from my computer, and the integration between the two devices is seamless. What frustrates me is the inability to efficiently search through old messages. iOS 13 helped with this quite a lot (while still leaving something to be desired), but searching on your computer is basically worthless.
Fortunately, there is a relatively easy fix for this. If you use iMessage on your Mac, you can access the SQLite database where all of your messages are stored. With a little setup, you can use SQL’s raw power to look through your old messages.
1. Initial Setup
There aren’t any prerequisites to getting started (other than a Mac with iMessage). However, your mileage may vary depending on whether you are using a SQL client (I use TablePlus) or sqlite3 on the command line. The setup for both is similar.
The database is located at ~/Library/Messages/chat.db
. If you open a terminal and run ls ~/Library/Messages/chat.db
, you’ll get a message like: ls: Messages: Operation not permitted
. Similarly, if you try and open the database in a SQL client, you’ll likely see an error. We need to grant access first.
I’m doing this on macOS Catalina, which has added some additional security features (i.e., restrictions). Even so, it’s actually quite easy to get access to the database, as long as you don’t mind granting full disk access to some applications. Let’s get that out of the way right away.
Open System Preferences, find the “Security & Privacy” pane, click on the “Privacy” tab, and find the “Full Disk Access” item. Make sure your SQL client and/or terminal are selected.
That should be it! You’ll need to restart any applications that were open while you granted them access. Otherwise, you should be able to access the message database:
- In a terminal, run
sqlite3 ~/Library/Messages/chat.db
- In a SQL client, connect to the database at
~/Library/Messages/chat.db
. If you’re having trouble navigating to it, you can press CMD+SHIFT+G in a finder window and type that in the dropdown.
2. Exploring the Tables
When you open the database, you should see about fifteen tables.
A few notable tables:
- attachment – metadata and storage location
- chat – a collection of your messages (both direct and group)
- handle – metadata about chats
- message – all messages sent and received
There are also join tables, which show the relationships between tables (handles to chats, chats to messages, messages to attachments, etc.). With these, we can build some pretty cool queries.
3. Queries
To see all of your messages with a nicely formatted date:
SELECT
datetime (message.date / 1000000000 + strftime ("%s", "2001-01-01"), "unixepoch", "localtime") AS message_date,
message.text,
message.is_from_me,
chat.chat_identifier
FROM
chat
JOIN chat_message_join ON chat. "ROWID" = chat_message_join.chat_id
JOIN message ON chat_message_join.message_id = message. "ROWID"
ORDER BY
message_date ASC;
To find a message that matches some text:
SELECT
datetime (message.date / 1000000000 + strftime ("%s", "2001-01-01"), "unixepoch", "localtime") AS message_date,
message.text,
message.is_from_me,
chat.chat_identifier
FROM
chat
JOIN chat_message_join ON chat. "ROWID" = chat_message_join.chat_id
JOIN message ON chat_message_join.message_id = message. "ROWID"
WHERE
message.text like '%😂%'
ORDER BY
message_date ASC;
To see the total count of messages across all of your chats:
SELECT
chat.chat_identifier,
count(chat.chat_identifier) AS message_count
FROM
chat
JOIN chat_message_join ON chat. "ROWID" = chat_message_join.chat_id
JOIN message ON chat_message_join.message_id = message. "ROWID"
GROUP BY
chat.chat_identifier
ORDER BY
message_count DESC;
To see some interesting information about messages between you and another person:
SELECT
count(*) AS message_count,
sum(length(message.text)) AS character_count,
sum(length(message.text)) / 3000 AS estimated_page_count, -- not sure where I got this number, but it seems reasonable
message.is_from_me,
FROM
chat
JOIN chat_message_join ON chat. "ROWID" = chat_message_join.chat_id
JOIN message ON chat_message_join.message_id = message. "ROWID"
WHERE
chat.chat_identifier = 'fill in identifier'
GROUP BY
message.is_from_me;
These are just a few examples of what you can do when you dig into the data behind your messages.
Accessing the chat database can, of course, be useful for finding old messages. But more than that, it’s fun to explore and write some interesting queries. Let me know in the comments if you’ve written anything exciting for this database!
Hi Dan, thanks for the interesting info! Can this be used to access images in the chat.db files? I can see my conversations but the images in those files are just file names that I can’t access. Thanks!
Thanks Dan, I faced the same problem for searching content in old conversations. I have accessed the chat.db but I was mad for the table structure. Thanks for your queries, it helps a lot!
Hi Dan,
Very helpful post in getting to digging into the imessage chat database. I’m hitting a snag I don’t understand (it looks right to my untrained eyes):
Query 1 ERROR: near “FROM”: syntax error
In the query (your last example):
FROM
chat
JOIN chat_message_join ON chat. “ROWID” = chat_message_join.chat_id
JOIN message ON chat_message_join.message_id = message. “ROWID”
What am I missing here?
Thanks!