Gmail to SQLite

Original Article ↗ Hacker News Discussion ↗

Tools for visualizing and analyzing email

  • Several people mention similar projects: visual mail explorers (like disk-usage treemaps), mail→DB loaders, and Postgres-backed IMAP/archive systems.
  • SQLite is viewed as a strong archival and analysis format: easy to query from any language, amenable to FTS5 full‑text search, and even recognized for preservation use.
  • Some note that WhatsApp and other platforms already store data in SQLite, and that schemas for these are known in forensics circles.

Gmail-specific vs generic IMAP / Takeout

  • Question raised: why “Gmail to SQLite” rather than generic “IMAP to SQLite”?
  • Defenders say Gmail’s API + OAuth is more reliable and faster than IMAP, which can be slow, flaky, and hit Google bandwidth or “cold storage” issues.
  • Experiences with Google Takeout vary widely: some report 20–60 minutes for large mailboxes; others report multi‑day delays or frequent failures.
  • Takeout is seen as good for periodic snapshots but not for continuous backup.

Schema design and SQLite techniques

  • Discussion on whether to break out specific headers vs store a single headers JSON blob.
  • Suggested pattern: keep all headers as JSON, then use generated columns or expression indexes (json_extract) for fields you want to query or index.
  • Views and schema migration tools are suggested to avoid constantly altering base tables.
  • Some caution that adding indexes for one‑off queries may be overkill; others argue it’s a flexible way to evolve analyses over time.

Backup tools and workflows

  • Multiple tools referenced: IMAP syncers, Gmail backup utilities, and GUI archivers with local search.
  • Continuous, resumable backup is a key desire, especially to protect against sudden account lockouts.
  • Some people just use a desktop IMAP client in full‑offline mode as a rolling local backup.

Privacy, de‑Googling, and alternatives

  • Strong thread arguing to leave Gmail due to data collection, profiling, ad targeting, political influence, and government access.
  • Counter‑considerations: free providers usually monetize data; genuinely privacy‑respecting service often means paying.
  • Common recommendations: use your own domain to allow switching providers; consider paid providers or privacy‑focused hosts; some self‑host successfully, others prefer reputable MX hosts to avoid deliverability issues.
  • Note that even if you leave Gmail, correspondence with Gmail users still flows through Google.

OAuth, app passwords, and API friction

  • Many complain that Google has buried or removed app‑specific passwords in favor of OAuth, making simple IMAP access hard.
  • OAuth is criticized as complex and provider‑specific, especially when trying to access one’s own data; some build proxies to hide OAuth from clients.
  • Developers integrating Gmail APIs describe a “maze” of verification: publishing apps, organization settings, detailed justifications, videos, and long review times.
  • Some argue the strictness is justified because email access is extremely sensitive and users will approve scammy apps; others see it as overkill.

Feature requests and practical concerns for this tool

  • Requests include: full‑text search integration, attachment metadata and extraction, unsubscribe link detection, and mbox/Takeout support.
  • One user questions whether a single‑table DB is worth it vs CSV/dataframes; others respond that indexing, FTS, and tooling make SQLite clearly superior for large mailboxes.
  • Performance concerns: initial sync observed as slow; async fetching suggested.
  • Bandwidth cost for very large accounts (e.g., 40GB+) is raised but not clearly answered; some suggest Takeout + parsing as a cost‑free alternative.