Searching Messages on Mac

Messages has some of the worst search functionality that I've ever seen. Rather than dawdle around in the terrible UI, it's easier and more powerful to just run raw SQL commands on the local database that Messages keeps by running sqlite3 ~/Library/Messages/chat.db in Terminal.

Some useful queries:

-- Select recent messages to see the columns
SELECT * FROM message WHERE is_from_me = 1 LIMIT 10;
-- Select with a certain amount of text
SELECT * FROM message WHERE text LIKE '%<specific_query>%';
-- If you need to search a specific group, first find a chat message in that thread and get the group ID from it...
SELECT cache_roomnames, text FROM message WHERE text LIKE '%<identifiable_text>%' and cache_roomnames IS NOT NULL;
-- ...then you can select all messages from that chat
SELECT text FROM message WHERE text LIKE '%<specific_query>%' and cache_roomnames = 'chat4768614642110776';
-- You can also select messages with context around it (here the surrounding 3 messages)
WITH chat
     AS (SELECT ROW_NUMBER()
                  OVER (
                    ORDER BY date) AS pos,
                text
         FROM   message
         WHERE  cache_roomnames = 'chat4768614642110776')
SELECT GROUP_CONCAT(c.text, Char(10))
       || Char(10)
       || Char(10)
FROM   chat h
       JOIN chat c
         ON c.pos BETWEEN h.pos - 3 AND h.pos + 3
WHERE  h.text LIKE '%new bit%'
GROUP  BY h.pos
ORDER  BY h.pos;