Using SQL to Look Through All of Your iMessage Text Messages

Seamless iMessage integration 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 smooth. Handoff is a nice feature to continue writing a message on another device after starting elsewhere.

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.

If you have iCloud Message sync turned on, you should be able to explore all messages you’ve sent with this Apple ID, provided your Mac is connected to the internet and fully synced.

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 might get a message like: ls: Messages: Operation not permitted. If your shell prints out the file path, then you’ve already granted full disk access to your terminal client, and you can skip this step. However, if you try and open the database in a SQL client, you’ll likely see an error.

Starting with macOS Catalina, additional security features have become standard. 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 first. Open the “Privacy & Security” item in System Preferences, and enable your SQL client and/or terminal.

TablePlus can not connect to chat.db before granting permission and restarting.
Without Full Disk Access, we get an error trying to open chat.db.

You must restart open applications when granting permission.
We must restart open applications when granting full disk access.

TablePlus can successfully connect after granting permission and restarting.
After granting permission and restarting, we have access to chat.db.

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.
  • If you want to reduce the risk of losing any message history, you should copy the chat.db to a new location: cp ~/Library/Messages/chat.db ~/Documents/chat.db and connect to the copy.

2. Exploring the Tables

When you open the database, you should see about fifteen tables. tables in chat.db 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

March 2024 Update

In a recent version of macOS (Ventura I think, I’m not exactly sure), the mechanism for reading the messages changed. Rather than storing messages in plain text in chat.db, messages are encoded as a hex blob in the attributedBody column. The following queries may not return any message data, unless you’re on an older version of macOS, but I’m leaving them in for posterity. I’m currently exploring ways to make it easier to read your messages database while parsing the attributedBody.

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;

4. Attachments

Not all attachments visible in chat.db will be accessible. However, the column filename points to the location of the attachment on your Mac. You could try to open an attachment a few ways. A quick way (my preferred option) is to copy the filename, and in a terminal just run the open command and paste the file path. Alternatively, you could open Finder, type CMD + SHIFT + G and paste the path in.

The filename is visible in the database, and points to a file on your Mac.
Copy this path to open an attachment.

 


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.

Conversation
  • Adrian says:

    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!

  • Sean Sun says:

    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!

  • Tim Sullivan says:

    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!

    • Jed Wood says:

      Tim- there’s a comma at the end of the line just before the “FROM” line. Remove that comma and it should work. Thanks Dan!

  • Michael P says:

    Hi Dan, thanks so much for this!

    I wanted to delete the all the many large attachments from a high-traffic group chat and was unable to do so on MacOS Big Sur using the chat’s Info > Photos > Show More > Shift + Click to Select All > Right click > Delete. So instead I ran the following (make sure you know what you are doing since this probably doesn’t play nicely with the usual 30-day “Recently deleted” flow):

    drop trigger before_delete_on_attachment; — refers to a UDF defined outside of the schema
    drop trigger after_delete_on_attachment; — refers to another UDF defined outside of the schema

    delete from message_attachment_join
    where message_id in (
    select maj.message_id
    from message_attachment_join maj
    inner join message m on m.ROWID = maj.message_id
    inner join chat_message_join cmj on m.ROWID = cmj.message_id
    inner join chat c on c.ROWID = cmj.chat_id
    where c.display_name == ‘Some high traffic group chat name’
    );

    CREATE TRIGGER before_delete_on_attachment BEFORE DELETE ON attachment BEGIN SELECT before_delete_attachment_path(OLD.ROWID, OLD.guid); END;
    CREATE TRIGGER after_delete_on_attachment AFTER DELETE ON attachment BEGIN SELECT delete_attachment_path(OLD.filename); END;

  • Andrew Dunn says:

    So, i want to view a specific conversation that is under “chat” and not “chat_message_join”. How would i program this in?

  • Alan Heartfield says:

    Messages can take up a lot of space on an iPhone. Use the Manage Storage function on the iPhone to find the messages that are taking up the most memory. Using a free SQL browser app on the AppStore called Ridill, it is easy to export the results of a query to a text file. By querying on handle_id all the text can be easily found from any given sender. This is the query: SELECT text FROM message WHERE handle_ID = 2. Change the handle_id to the appropriate number for the texts you want to export.
    Then use the Manage Storage function on the iPhone to delete the text messages for that individual.

  • Gab M says:

    What is a query to open the “deleted_messages”? I don’t know anything about code so anything simple helps!!!!!

    • paco perez says:

      I am trying to figure out the same, let me know if you do.

  • Comments are closed.