Warning: private macOS internals. Written on macOS 27.0.1 in November 2026. This isn't a supported API. Apple can change the file's location, layout, or permissions in any update, so check your version before trusting these queries. Editing or deleting the file can damage your message history. Everything here only reads it. Keep what you find on your own Mac.
Messages search doesn't always bring up the results I want. I wanted a way to easily query my messages and link them to calendar appointments, reminders and so on. Apple already suggests calendar events and reminders from messages, but not always where and when I need them. I can wait for Apple to build that the way I want, or I can do it myself with the workflows I already have.
So I busted out some 1970s tech to handle a modern problem: SQL, a language for asking databases questions. Everything old is new again.
The short version: every message on your Mac lives in one database file. You can ask it questions, like "how many days did we talk this week?", without reading a single message.
A few words first
- Database: a file of tables, like spreadsheets that point at each other.
- SQL: a language for asking a database questions. IBM developed it in the early 1970s, and it's still everywhere.
- SQLite: the small database engine Messages uses. Your Mac already has a
sqlite3command for it. - Handle: a phone number or email address. One person can have several.
- Read-only: opening a file so nothing can change it.
Open it without touching it
Why it matters: this file is your message history. One wrong command could damage it.
The file is ~/Library/Messages/chat.db. Give the Terminal app Full Disk Access (System Settings, Privacy & Security), then open the file read-only:
sqlite3 -readonly "$HOME/Library/Messages/chat.db"
A few things to know before you start:
- Changing the file doesn't send anything. The database is Messages' record of what already happened, not a remote control. Adding a row won't send a text, and deleting one won't unsend it: the other person's phone has its own copy. Sending goes through Messages itself. My morning check-in, for example, sends with the Shortcuts Send Message action and only reads the database afterward.
- An error means a permission problem, not an empty inbox.
- Don't copy just
chat.dbwhile Messages is running. The database uses a write-ahead log: new changes go into a side file,chat.db-wal, first, and get folded intochat.dblater. Think of a notepad next to a ledger. Until the notes are copied over, the newest messages live only on the notepad, so a copy ofchat.dbby itself can miss them. If you need a copy, take all three files:chat.db,chat.db-walandchat.db-shm. Opening it in place with-readonly, like above, already reads both.
Find the conversation, not just the person
Why it matters: a person isn't a conversation. The same friend can be in a one-on-one chat and several group chats.
- Person
handleOne phone number or email.The same person can have several. - Who's in which chat
chat_handle_joinA list of pairs: this person is in that conversation. - Conversation
chatA one-on-one chat or a group.This is the one you pick. - Which messages
chat_message_joinA list of pairs: this message belongs to that conversation. - Message
messageWhen it was sent, who sent it, and the text.Attachments hang off messages the same way:message_attachment_join, thenattachment.
JOIN in the queries below is one of these hops.This lists every conversation one phone number or email is in, newest first. The email here is made up; use the exact phone number or email, not a contact name.
SELECT c.ROWID AS chat_id,
c.chat_identifier,
c.display_name,
c.style,
datetime(MAX(m.date) / 1000000000 + 978307200, 'unixepoch') AS last_message_utc
FROM chat AS c
JOIN chat_handle_join AS chj ON chj.chat_id = c.ROWID
JOIN handle AS h ON h.ROWID = chj.handle_id
LEFT JOIN chat_message_join AS cmj ON cmj.chat_id = c.ROWID
LEFT JOIN message AS m ON m.ROWID = cmj.message_id
WHERE h.id = '[email protected]'
GROUP BY c.ROWID
ORDER BY MAX(m.date) DESC;
Pick the chat_id you mean. My scripts treat style 45 as a one-on-one chat. That's what I've seen, not something Apple promises.
Two traps in every message
Why it matters: both make a working query give wrong or empty answers, and nothing warns you.
- Time starts in 2001. Message dates count nanoseconds since January 1, 2001, not 1970 like most computers. That's why these queries divide by a billion and add 978,307,200 seconds.
- Text can hide. Some messages have an empty
textcolumn even though they have words. The words are packed into another column,attributedBody, in a format SQLite can't read on its own. My scripts use a rough unpacking shortcut, not a real decoder, so it can miss or garble text.
Count days without reading anything
Why it matters: this is the private part. You get a number, and no one's words leave the database.
For one conversation, this counts the days in the last week with any message, and the days with a message from the other side. Put your chat_id where the 42 is.
SELECT COUNT(DISTINCT date(m.date / 1000000000 + 978307200,
'unixepoch', 'localtime')) AS days_with_messages,
COUNT(DISTINCT CASE WHEN m.is_from_me = 0 THEN
date(m.date / 1000000000 + 978307200, 'unixepoch', 'localtime')
END) AS days_they_wrote
FROM message AS m
JOIN chat_message_join AS cmj ON cmj.message_id = m.ROWID
WHERE cmj.chat_id = 42
AND m.date > (CAST(strftime('%s', 'now', '-7 days') AS INTEGER)
- 978307200) * 1000000000;
The answer is just two numbers, like 5|3 (made up).
Read it for what it is:
- It counts days, not how good the conversation was.
- No texts doesn't mean no call or visit.
- "The last week" means the last seven days of timestamps, which can touch eight calendar dates.
Find where an attachment lives
Why it matters: photos and files aren't stored in the database. It records where each one was saved, which is hard to find any other way.
Finding where attachments live has been valuable to me. Each attachment record has the file's original name, its type, its size, and its path on disk, usually somewhere under ~/Library/Messages/Attachments/. For one conversation:
SELECT a.transfer_name AS original_name,
a.mime_type,
a.total_bytes,
a.filename AS path_on_disk
FROM attachment AS a
JOIN message_attachment_join AS maj ON maj.attachment_id = a.ROWID
JOIN chat_message_join AS cmj ON cmj.message_id = maj.message_id
WHERE cmj.chat_id = 42
ORDER BY a.ROWID DESC
LIMIT 20;
A path in the database isn't proof the file is still there. It may have been deleted, or kept only in iCloud. Opening the file is a separate step you do yourself.
What I've built on it
Why it matters: small tools that answer real questions, all from one file.
The two I'd miss:
- Summaries before a visit or call. A model running on my Mac (Ollama) reads recent messages and writes a short summary. The messages never go to a cloud model.
- A morning check-in. A Shortcut asks me a few morning questions, including sleep and stress, then texts the answers to my own number. A script finds that text in the database and logs it.
And one more:
- An agent I can text. A separate tool that only sees chats I've allowed. It's cool, but I don't use it much: the ergonomics of texting yourself are bad.
What I checked
The queries above ran on a made-up database with the same layout, not on my real messages. On my Mac, running macOS 27.0.1, I checked that the attachment columns the last query uses are all there.
Ask it your own questions
When Messages search doesn't bring up what I want, I ask the file directly. A few small questions, asked read-only, turned into tools I'd miss. The next step is the one I started with: linking messages to my calendar and reminders.
Whatever you build, keep the rules from the top: open it read-only, keep what you find on your own Mac, and let Messages manage the file.
This is the second Magic files post. The first was about where your Mac keeps Focus records.