gmail-to-sqlite: putting a Gmail archive into a database you can query

Why put mail in SQLite at all

gmail-to-sqlite is described as a robust Python application that syncs Gmail messages to a local SQLite database for analysis and archival purposes. The description names the two reasons, analysis and archival, and both are different from what Gmail itself is built for.

Gmail's own search is a fast index over a live mailbox, and it is good at finding one message. It is not good at the questions that come up when you want to look at a decade of correspondence as data. How many messages did this person send me, and how large were they? Which senders are responsible for the most unread mail? What was my total email volume per year? How many messages did I send to myself?

Those are all aggregate questions, and the README answers each of them with a SQL query. That is the pitch: once the mail is in a relational store, the questions become queries you can write, and the answers are limited by your SQL rather than by an interface designed for finding a single email.

The project is MIT licensed, the primary language is Python, and the last push to the repository was on 2026-10-11. The package version is 0.2.0, there are no GitHub releases, and the install is from source rather than from a published index.

The interesting architectural decision is that the schema stores structured values as JSON rather than in separate tables. The sender column is JSON holding the name and email, the recipients column is JSON keyed by type with to, cc, and bcc, and the labels column is a JSON array of Gmail labels.

The schema, and why the columns are JSON

The database schema is a single messages table, and the interesting part is the type of each column.

The message_id is TEXT and holds the unique Gmail message ID, and the thread_id is TEXT holding the Gmail thread ID. The sender is JSON with the sender information, the name and the email. The recipients are JSON, with recipients by type, being to, cc, and bcc. The labels are JSON, an array of Gmail labels. Then there are the plain columns: the subject as TEXT, the body as TEXT holding the plain text body, the size as INTEGER in bytes, the timestamp as DATETIME, the is_read and is_outgoing and is_deleted as BOOLEAN, and the last_indexed as DATETIME holding the last sync timestamp.

The JSON choice is what makes the example queries work, and it is a deliberate trade. A normalised schema with a senders table and a message_senders join table would be more conventional and would let you index the email address directly. Storing the sender as a JSON document means one row per message, no joins, and a much simpler sync, because a message's sender and recipients arrive as a unit and never change independently.

The cost is that you need SQLite's JSON functions to reach inside, which the README does throughout with the -> and ->> operators. sender->>'$.email' extracts the email as text, and json_each is used to iterate over the recipients array in the self-sent query.

The is_deleted column is the one that makes the archive honest. A full sync can detect deletions, and a message removed from Gmail is marked rather than dropped, so the row remains for the record with a flag on it. The query for finding all deleted messages is a select on is_deleted=1 ordered by timestamp.

The commands, and the difference between the two sync modes

There are three commands, and the CLI argument list is short enough to hold in your head.

# Incremental sync (default)
python main.py sync --data-dir ./data
# or: uv run main.py sync --data-dir ./data

# Full sync with deletion detection
python main.py sync --data-dir ./data --full-sync

# Sync a specific message
python main.py sync-message --data-dir ./data --message-id MESSAGE_ID

# Detect and mark deleted messages only
python main.py sync-deleted-messages --data-dir ./data

# Use custom number of worker threads
python main.py sync --data-dir ./data --workers 8

# Get help
python main.py --help
python main.py sync --help

The command is required and is one of sync, sync-message, or sync-deleted-messages. The data directory is required and is where the SQLite database is stored. The full-sync flag forces a complete sync of all messages. The message id is required for the single-message command. The worker count is optional and defaults to the number of CPU cores. The help flag covers both the top level and the individual commands.

The distinction between the two sync modes is the important part. Incremental sync is the default and only downloads new messages, which is the mode you run on a schedule. Full sync downloads all messages and detects deletions, which is the mode you run when you suspect something was removed on the Gmail side, and it is also the mode that costs the most.

The third command, sync-deleted-messages, does the deletion detection on its own without downloading anything, which is a middle ground for a periodic check on a large mailbox.

The features behind these commands are the ones worth knowing about. Parallel processing uses multi-threaded message fetching for improved performance, and the worker count is the knob. Robust error handling does automatic retries with exponential backoff, which matters because the Gmail API will throttle you. Graceful shutdown handles interruption signals cleanly. And the type safety claim is backed by comprehensive type hints throughout the codebase.

Graceful shutdown, and the second CTRL+C

The shutdown behaviour is documented as a four step sequence, and it is the kind of detail that tells you what the author was worried about.

When you press CTRL+C, the application stops accepting new tasks, waits for currently running tasks to complete, saves the progress of completed work, and exits cleanly. Pressing CTRL+C a second time forces an immediate exit.

This matters for a sync tool because the work is incremental. If the process is killed halfway through a batch of a hundred thousand messages, the question is what state the database is in. The answer here is that the progress of the completed work is saved, so the next run picks up from there rather than starting over. That is a resume, and it is what makes a long first sync survivable.

The second CTRL+C is the escape hatch. The first one asks for a graceful stop, and the second one says you are not waiting. That design is common in tools that do long network-bound work, and it is the difference between a tool you can interrupt and a tool you leave running.

The last_indexed column in the schema is the visible trace of this. Every row records the last sync timestamp, so the database itself carries the information about how fresh it is, rather than the tool having to keep a separate state file that could drift out of step with the data.

Getting the OAuth credentials, which is the real setup work

The prerequisites are Python 3.8 or higher, a Google Cloud Project with the Gmail API enabled, and an OAuth 2.0 credentials file called credentials.json. The package metadata is slightly stricter, with requires-python set to >=3.9.

The setup sequence is a clone, a dependency install, and the credentials. The dependency install uses uv:

# Using uv
uv sync

The credentials step is the one that takes real effort. You go to the Google Cloud Console, create a new project or select an existing one, enable the Gmail API, create OAuth 2.0 credentials of the Desktop application type, then download the credentials file and save it as credentials.json in the project root.

The Desktop application type is the right choice for a local script, and it produces the flow where a browser opens, you sign in, you grant access, and the token is stored locally. The Makefile has a run target that makes the requirement explicit, since it notes the application requires credentials.json.

The one thing to be careful about is the scope of access. A tool that reads your whole mailbox is being trusted with your entire correspondence, and the desktop credential flow is a browser sign-in rather than a service account, so you can see exactly which account is being authorised. It is worth checking that the authorised account is the one you intend, because the database it writes is a complete copy.

The tooling, and the entry points

The project is a uv-managed project with a Makefile that covers the whole development loop, and the Makefile is worth reading because it names the tools.

The install target is uv sync and install-dev is uv sync --dev. Tests run with uv run pytest tests/ -v, and the coverage variant adds pytest-cov with both an HTML and a terminal report. Linting runs flake8 over gmail_to_sqlite and tests, then mypy over gmail_to_sqlite. Formatting is black, and there is a separate format-check target. Build is uv build and upload is uv publish.

The mypy configuration in pyproject.toml is strict, with disallow_untyped_defs, disallow_incomplete_defs, check_untyped_defs, disallow_untyped_decorators, no_implicit_optional, warn_return_any, warn_unreachable, and strict_equality all enabled. That matches the README's claim of comprehensive type hints throughout the codebase, and it is the level of strictness that catches a mistake in a threaded sync before it becomes a corrupted row.

There are two ways to run the application, and the Makefile shows both. The run target is uv run python -m gmail_to_sqlite, and run-cli is uv run gmail-to-sqlite, using the console script entry point declared as gmail-to-sqlite = gmail_to_sqlite.main:main.

The dependencies explain the design choices. google-api-python-client and the google-auth family handle the API and the OAuth dance, peewee is the ORM and the database layer, and beautifulsoup4 is there for parsing message bodies into plain text, which is what the body column holds. There is also a setup.py in the repository, but its own docstring says it is for backward compatibility and development installs, and that the actual package configuration is in pyproject.toml, with hatchling as the build backend.

Editorial conclusion

Adopt gmail-to-sqlite if you want a local, queryable archive of your own mail, particularly for analysis, and if you are comfortable granting a desktop OAuth client access to the account. Do not adopt it as a mail client, since it is a one-way sync with no send path. Verify first that the Gmail API quota on your account can absorb a full sync, because that is the one operation whose cost depends on the size of your mailbox.

Frequently asked questions

Official sources

1. Issues
2. License: MIT
3. marcboeker/gmail-to-sqlite on GitHub
4. README