I went searching for a way to show my unread messages count inside of my new MacBook Pro’s Touch Bar and after an hour or two of Google-fu was unable to find a working solution out there. So I got my hands dirty and wrote something up myself. I am documenting this here on my blog so either I or anyone who finds this blog from Google can recreate the results I achieved.

AppleScript to the rescue! …or not

I first started by trying to write an AppleScript that could either parse the badge data from the dock or count up the messages but was unable to find any library available to do either natively. I spent quite a bit of time in AppleScript trying all kinds of ideas but was unable to produce the results I was seeking.

More defeat with Terminal

I then had an idea, Apple includes sqlite3 in macOS by default and could open the chat.db file and parse out the read messages count for me…or so I thought. I then launched up terminal and tried to open Message’s chat.db but was unable to open the file in Terminal.app.

cayces-mbp # sqlite3 ~/Library/Messages/chat.db
Error: unable to open database "/Users/caycehouse/Library/Messages/chat.db": unable to open database file

After doing some more Google-fu I found out that the reason Terminal.app was unable to open the chat.db file was because of new file protections in macOS Mojave. I then opened up System Preferences -> Security & Privacy -> Privacy -> Full Disk Access and added an exception for Terminal.app.

Adding exception for Terminal.App in System Preferences -> Security & Privacy -> Privacy -> Full Disk Access

Terminal to the rescue!

Now that we have privacy protections out of the way, let’s see what we can do.

cayces-mbp # sqlite3 ~/Library/Messages/chat.db
SQLite version 3.24.0 2018-06-04 14:10:15
Enter ".help" for usage hints.
sqlite>

Great, now we can make a connection to the database that holds message’s chat records. After some peeking around at the table structure inside I find that is_read is a column in the messages table. So a simple COUNT query on the records that are marked unread should give us our count right? Ha ha not really.

cayces-mbp # sqlite3 ~/Library/Messages/chat.db "SELECT COUNT(*) FROM message WHERE is_read=0"
436

After some more digging and testing I come up with a query that actually works! (Before I started writing this I only had three unread messages, not 436 like the last command would make you think).

cayces-mbp # sqlite3 ~/Library/Messages/chat.db "SELECT * FROM 'message' WHERE is_read = 0  AND text != 'NULL' AND is_from_me != 1"
3

Conclusion

I ended up packaging the shell script into a BetterTouchTool Touch Bar widget, and you can download it below. Don’t forget to give BetterTouchTool Full Disk Access or else the script will not work. If you like my work, or have any questions feel free to contact me using the form on this site.

BetterTouchTool JSON