You can use SQL to search your old iMessage data – and learn some surprising facts about the people you text along the way.
Join the DZone community and get the full member experience. SQLite is an often overlooked flavor of SQL engines. Some have suggested it is the most prolific SQL engine in existence due to its highly flexible nature and ability to run on almost any platform with limited resources. Unlike other SQL engines like MySQL, PostgreSQL, MSSQL, or Oracle, SQLite runs without a server. SQLite does not rely on a data directory or a constantly running daemon: a database is encapsulated in a single file. iMessage is one of the most popular messaging platforms today, largely because it is built into iOS and Mac devices. Since its release, it has evolved significantly. But, at its core, it is simply an instant messaging platform. iMessage uses SQLite in the background to store relational data about messages, conversations, and their participants. As a long-time Apple user, I have backed up and transferred my iPhone data since my first time using an iPhone, which was November 10,2009. Because I have been digitally hoarding my text data for so long, my iMessage database is nearly 1GB in size. Until a few years ago, the built-in search feature for iMessage was very limited and buggy. Although it has recently improved significantly, it is, like nearly any end-user tool, very limited in how you can query it. Those of us who frequently work with data that is trapped behind a limited front-end often wish we could get direct access to the SQL database. Fortunately, the iMessage database is not inaccessible – in fact, it is very easy to access. If you have iMessage enabled on your Mac as well as your iPhone, you have 2 different databases from which to choose. The database on your Mac is very easy to find, as it is simply under ~/Library/Messages/chat.db. If you do not use your Mac for iMessage, or, as in my case, your Mac iMessages do not go as far back, you can extract your iPhone’s database by performing a backup to your Mac. Follow these instructions to extract your iPhone’s iMessage database: Unlike most SQL servers, you do not need a connection string, host, or username to connect to an SQLite database. All you need to do is point your SQL client to the database file. More detailed instructions can be found in the Arctype Docs. From a UNIX terminal, type sqlite3 [filename]. One of my favorite parts about Arctype is how easy it is to analyze database schema. I’m a long-time user of command-line tools and old-school editors, but sometimes having a more visually interactive tool is a lifesaver. Let’s dig into the schema Apple has created for iMessage. Today we will focus on the chat, message, and handle tables, as well as a few, join tables to connect related records.
