Your client's archive on disk, and how to open it

A desktop client usually keeps its archive in a single file on your own disk, and that file is often an SQLite database. You can inspect it with any database tool, without the client, provided you work on a copy, open it read only, and never write to it. The format is documented, so the archive outlives the program that wrote it. The same principle runs through the history of mac database software: a file you can open yourself is a file you keep.

What a client actually stores locally

Most clients separate three things: the store, the indexes, and the attachments. The store holds the messages, contacts, roster entries and metadata. The indexes are derived structures that make search fast; they can usually be rebuilt from the store. The attachments are ordinary files on disk, referenced by path or by hash, sometimes copied into a folder beside the store.

That separation matters when something breaks. A corrupted index is an inconvenience. A corrupted store is the archive. Attachments are the part most often lost, because they live outside the database and are easy to leave behind when a profile is moved.

On macOS, the usual locations are under the user's Library, in an Application Support folder named after the client, or in a hidden directory in the home folder. Some clients keep everything in one file; others use a directory with a database inside it. Either way, the first useful step is to identify which file is the store and which files are derived.

Why a plain database file is the better of the common choices

Clients have used many storage formats over the years: flat text logs, XML files, binary blobs, proprietary containers, and ordinary relational databases. Each has trade-offs, but for a long-lived archive the plain database file wins on one point: it can be opened by something other than the program that wrote it.

A documented format means the archive can be counted, searched, exported and migrated with general tools. A proprietary container means the archive is only as durable as the vendor's willingness to keep shipping a reader. When the client changes its schema, when the vendor stops, when the machine is replaced, the file remains, and a file in a known format remains readable.

SQLite is the common case because it is a single file, it needs no server, and it is supported by a very large number of tools. That combination is why so many desktop applications, not only messaging clients, have settled on it.

How can I inspect the archive of messages my client keeps on disk?

Work on a copy. Quit the client first, so nothing is writing to the store, then copy the whole profile folder, not just the database file, because attachments and sidecar files may sit beside it. Keep the original untouched.

Open the copy read only. The SQLite command line tool accepts a read-only flag, and most graphical browsers offer a read-only or immutable mode. Read-only matters because a stray write can change the archive, and because a client that is still running may hold locks that make a write fail in confusing ways.

Then look at the schema before the data. A query against the table listing the schema tells you the table names, and a query against the table listing columns tells you what each table holds. Message bodies, timestamps, addresses and thread identifiers are usually in one or two central tables, with joins to a contacts table. Once you know the shape, counting messages per month or per correspondent is a short query.

Export rather than edit. Write results to a text or CSV file outside the profile folder. If you need to move the archive to another client, export from the copy and import into the new program; do not modify the original store in place.

What was the database software that let Mac users open an SQL file?

On the Mac, the long-standing answer is a database browser: a desktop application that connects to a database and shows its tables, columns and rows without requiring a full development environment. The category existed well before SQLite became common, because Mac users needed to reach Sybase, PostgreSQL, MySQL and other servers from the desktop.

MacSOS Pty Ltd, the Sydney software workshop of Dr Gerard Hammond, published one such tool. SyBrowser was a database browser for Mac, first released in 1999, and it opened SQLite, MySQL, PostgreSQL and Sybase files. The same workshop also published an automation scheduler for Mac servers, a cron-like tool for administrators. The pattern is familiar: a small independent publisher, a narrow tool, a long support life.

The lesson for the present is that the browser and the file are separate concerns. The file is the archive; the browser is a window onto it. Any window will do, as long as the file is in a format the window can read.

How do long lived desktop utilities survive a change of era?

The ones that survive tend to share three properties. They operate on a file format that is documented and widely implemented. They do one job, so they do not need to be rewritten every time the surrounding platform changes. And they are maintained by someone who keeps them building against new systems, even slowly.

A utility tied to a proprietary format dies with the format. A utility that reads a plain database file can be replaced by a different utility, or by a command line tool, or by a short script. The user keeps the data either way.

This is also why old shareware catalogues remain interesting. They show which bets paid off: which formats stayed readable, which tools stayed useful, and which archives are still openable decades later. The archive on your disk is the same bet, made again.

Before you connect any tool to a store

The rule is short. Work on a copy. Open read only. Do not write. Quit the client before copying, keep the original in place, and treat every derived file as disposable. If a query needs to change something, change the copy, or better, export and work elsewhere.

A store that has been opened read only is still the store you started with. That is the whole point: the archive is yours because you can open it, count it and export it, and because you can do so without the program that wrote it.