#Sqlpro lite full
#Sqlpro lite mac

Inner join message m on m.ROWID = maj.message_id 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):ĭrop trigger before_delete_on_attachment - refers to a UDF defined outside of the schemaĭrop trigger after_delete_on_attachment - refers to another UDF defined outside of the schema 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. Let me know in the comments if you’ve written anything exciting for this database! But more than that, it’s fun to explore and write some interesting queries. These are just a few examples of what you can do when you dig into the data behind your messages.Īccessing the chat database can, of course, be useful for finding old messages. Sum(length(message.text)) / 3000 AS estimated_page_count, - not sure where I got this number, but it seems reasonableĬhat.chat_identifier = 'fill in identifier' Sum(length(message.text)) AS character_count,

To see some interesting information about messages between you and another person: To see the total count of messages across all of your chats:Ĭount(chat.chat_identifier) AS message_count To find a message that matches some text: JOIN message ON chat_message_ssage_id = message. To see all of your messages with a nicely formatted date:ĭatetime (message.date / 1000000000 + strftime ('%s', ''), 'unixepoch', 'localtime') AS message_date, With these, we can build some pretty cool queries. There are also join tables, which show the relationships between tables (handles to chats, chats to messages, messages to attachments, etc.).

message – all messages sent and received.

chat – a collection of your messages (both direct and group).

attachment – metadata and storage location.

When you open the database, you should see about fifteen tables. If you’re having trouble navigating to it, you can press CMD+SHIFT+G in a finder window and type that in the dropdown.

In a SQL client, connect to the database at ~/Library/Messages/chat.db.

In a terminal, run sqlite3 ~/Library/Messages/chat.db.

Otherwise, you should be able to access the message database: That should be it! You’ll need to restart any applications that were open while you granted them access. Make sure your SQL client and/or terminal are selected. Open System Preferences, find the “Security & Privacy” pane, click on the “Privacy” tab, and find the “Full Disk Access” item. Let’s get that out of the way right away.

#Sqlpro lite full

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. I’m doing this on macOS Catalina, which has added some additional security features (i.e., restrictions). Similarly, if you try and open the database in a SQL client, you’ll likely see an error. If you open a terminal and run ls ~/Library/Messages/chat.db, you’ll get a message like: ls: Messages: Operation not permitted. The database is located at ~/Library/Messages/chat.db. However, your mileage may vary depending on whether you are using a SQL client (I use TablePlus) or sqlite3 on the command line.

#Sqlpro lite mac

There aren’t any prerequisites to getting started (other than a Mac with iMessage). With a little setup, you can use SQL’s raw power to look through your old messages. If you use iMessage on your Mac, you can access the SQLite database where all of your messages are stored. iOS 13 helped with this quite a lot (while still leaving something to be desired), but searching on your computer is basically worthless.įortunately, there is a relatively easy fix for this. What frustrates me is the inability to efficiently search through old messages. I love being able to send messages from my computer, and the integration between the two devices is seamless.

It’s also one of my least favorite things. IMessage is one of my favorite things about having an iPhone and a Mac.