14 ms·
Gmail to SQLite
- terhechte 1y agoI build something to visualize huge amounts of email (such as from Gmail) some years ago: https://github.com/terhechte/postsack https://github.com/terhechte/postsack
- andai 1y agoHey this is really neat! It's like those disk usage visualizers, except that it seems to focus on the total volume of the mail rather than the disk usage. Is there a size option too? To see which senders are using most of my storage. (Also your website's SSL certificate has expired.)
- terhechte 1y agoNo currently not. It would be easy to add though. I haven't updated the tool in a while (after using it to clean up my Gmail inbox). Thanks for pointing out the certificate!
- mywacaday 1y agoLooks interesting, the link to gmvault in your readme is now a dead end, is this it https://github.com/gaubert/gmvault https://github.com/gaubert/gmvault. Thanks!
- sgbeal 1y ago> the link to gmvault in your readme is now a dead end, is this it https://github.com/gaubert/gmvault https://github.com/gaubert/gmvault. Funnily enough, the gmvault.org domain _that_ page points to is simply a parked GoDaddy placeholder. It's also not been updated in 10+ years except for two non-source files.
- nijave 1y agoThis looks interesting. I've DIY'd something similar with qdirstat before but you need to arrange your emails a certain way like dated folders and can't re-slice with different criteria. On the other hand, qdirstat "cache" files are really easy to generate so can be used for visualizing a bunch of file-like things
- TekMol 1y agoShouldn't this be "imap to sqlite" or something? Why tie it to one specific email provider?
- isaachinman 1y agoBecause _it is_ specific to Gmail. It's using OAuth and presumable API access. IMAP is much harder, and much slower, and is bound by Google's bandwidth limits.
- pastage 1y agoDoing a mbox export with Google Takeout from gmail is pretty fast.
- remram 1y agoWhat? You have to schedule it, they literally wait 3 days before they start it, and then it can take most of a day to get it ready for download. It is not fast.
- kilroy123 1y agoI've never had to wait that long. I usually can download within 20 minutes and it's 15 GB of data.
- crazygringo 1y agoI've never had that experience. You don't need to schedule anything, and it takes maybe part of an hour to be ready to download? Maybe there have been times when it was broken or under high demand though?
- phh 1y agoFWIW, for several years I've tried backuping my gmail account with imap (including some stuff made specifically for gmail): It never succeeded. The best syncer were running for one month, and after one month it hit some mails that it simply couldn't retrieve? Like I guess it was in too cold storage and timeout-ed? I don't know. So I can understand why using Google's proprietary API might work better (or not, I don't know) Anyway, as a sibling says, nowadays Google Takeout includes mbox and work properly (and is pretty fast, like half a day), but doesn't allow continuous update. And I migrated to another mail provider (infomaniak), and I've thanked myself for using my own mail domain name years earlier.
- yread 1y agoWould be nice to enable fulltext search as well
- padjo 1y agoYes! I find gmail’s full text search surprisingly bad given it’s run by a search company.
- isaachinman 1y agoAgreed! One of the reasons we started working on Marco. https://marcoapp.io https://marcoapp.io
- porker 1y agoBut not as bad as Outlook 365's search...
- jbverschoor 1y agoBoot as bad as the Mail.app from iOS and macOS
- pastage 1y agoOutlook must be the worst email client there is. Something about least common denominator.
- NelsonMinar 1y agoIt got a lot worse recently when they added bad AI to it. Now it does dumb synonyms. Like I search for "doctorate" and it starts highlighting every instance of D alone, like the word "he'd". (Presumably trying to pick up Ph.D.?) For awhile searches for "A" would have it highlighting "the", too.
- jgalt212 1y agoIt's the least bad search--better than Yahoo, and Thunderbird desktop in my direct experience. However, I don't download the full message into Thunderbird out of fear of blasting through gmail bandwidth limits.
- unsnap_biceps 1y agoI'm curious as to why you choose to break out specific headers in the schema. For example, you have recipients, subject, and sender as JSON fields, when you could have just a headers field with all of them, and even add the rest of the headers in the message. If it's performance related, you can still have headers as a single json blob and then use generated columns for the specific fields. For example CREATE TABLE IF NOT EXISTS "messages" ( "id" INTEGER NOT NULL PRIMARY KEY, -- internal id "message_id" TEXT NOT NULL, -- Gmail message id "thread_id" TEXT NOT NULL, -- Gmail thread id "headers" JSON NOT NULL, -- JSON object of { "header": value }, "subject" TEXT GENERATED ALWAYS AS (json_extract("headers", '$.Subject')) VIRTUAL NOT NULL) ... ); CREATE INDEX subjectidx on messages(subject); I've found this model really powerful, as it allows users to just alter table to add indexed generated columns as they need for their specific queries. For example, if I wanted to query dkim status, it's as simple as ALTER TABLE messages ADD dkim TEXT GENERATED ALWAYS AS (json_extract("headers", '$."Dkim-Signature"')) VIRTUAL NOT NULL); CREATE INDEX dkimidx on messages(dkim); SELECT dkim, COUNT(0) FROM messages GROUP BY dkim; or whatever you want.
- xearl 1y agoTIL, thanks a lot!
- formerly_proven 1y agoYou can also create indices directly on expressions, including json_extract etc.
- dotancohen 1y agoI see that you defined the `dkim` column as NOT NULL. So what happens when an email message does not contain the Dkim-Signature header?
- hun3 1y agoProbably something like Error: stepping, NOT NULL constraint failed: messages.dkim (19) because, unlike MySQL, SQLite apparently returns SQL NULL for JSON null value.
- jokoon 1y agoI would have preferred a script that parses the mail backup Google sends you. I think it's a big eml file.
- ukuina 1y agoGoogle Takeout regularly fails to complete for me. Syncing via the API seems like a reasonable alternative.
- einpoklum 1y agoLet us stop using GMail: * Google collects vast amounts of personal data, specifically through receiving all of your email and analyizing it. * It builds elaborate user profiles and uses them to target you with ads designed to better influence you. * Its hold on information (from different sources) has made it excessively powerful economically, and thus also politically. * Google/Alphabet has long started to affect legislation, including through direct registered lobbying: ~15 Million USD in 2024 (opensecrets.org). * It has been known to pass, and likely still passes, the information it collects - including copies of your email correspondence - on to the US government (Edward Snowden leaks). and finally: * There are multiple email providers, many of them quite good - both for pay and gratis. Naturally most of the gratis ones have their own interests, but nothing like Google.
- evertedsphere 1y agohttps://mako.cc/copyrighteous/google-has-most-of-my-email-because-it-has-all-of-yours https://mako.cc/copyrighteous/google-has-most-of-my-email-be...
- pastage 1y agoThis should be seen as a encouraging to switch to something else rather than defeatist. Many of my communications do not touch Google services, professionally it has been judged as too risky, personally I keep a google account but also others. Edit: You can create groups of people that are not affected by Google/Apple/Facebook, this should be seen as a goal.
- devrandoom 1y agoThis could be an interesting take, GDPR wise. Google handling personal emails of people that have no contract or business relationship with Google.
- mediumsmart 1y agoI was with you from day one and never started using gmail.
- phantompeace 1y ago
- the_mitsuhiko 1y agoI really lament that you cannot sign in even with an application specific password any more and you need to get an oauth client and go through an oauth flow. It’s my email, but Google takes away an open standard even for myself to access it.
- kasey_junk 1y agoSorry, why do you consider app specific passwords an open standard but oauth not?
- simonw 1y agoPOP3/IMAP work with any client that supports those protocols. OAuth really doesn't. Every OAuth integration I've ever built always feels like it needs a tiny bit of custom development. Also the OAuth flow is usually absolutely horrible for when you're trying to get a token for accessing your own data. I've had to spin up a temporary web app to handle a hunch of redirects just to get my own token!
- sir 1y agoI built a proxy a while ago to make this easier - it lets you stick with IMAP/POP/SMTP as-is. No need for your client to even know that OAuth exists. See here: https://github.com/simonrob/email-oauth2-proxy https://github.com/simonrob/email-oauth2-proxy
- the_mitsuhiko 1y ago> No need for your client to even know that OAuth exists Yes, you can do that, however the problem is getting a client_id/client_secret in the first place. You need to register yourself for one, you need to (nowadays) whitelist every single account or go through a google verification process. At one point you could apply for a client_id that allowed anyone to use it, but that ship has sailed.
- kasey_junk 1y ago
- pdyc 1y agothis is great if only there was a tool for whatsapp to sqlite it would make my data so much more useful
- philipwhiuk 1y agohttps://medium.com/@Med1um1/extracting-whatsapp-messages-from-backups-with-code-examples-49186de94ab4 https://medium.com/@Med1um1/extracting-whatsapp-messages-fro...
- flas9sd 1y agoin both iOS and Android it's all stored in sqlite already. Table schemas circulate forensics blogs and QA sites and how to obtain the unencrypted db
- pdimitar 1y agoWould love a comparison to gbackup-rs[0]. To me having to install a tool through Python is a show-stopper. [0] https://github.com/djipko/gbackup-rs https://github.com/djipko/gbackup-rs
- simonw 1y agoDoes that tool still work? It's not had any updates in 3 years and it looks like it uses IMAP, which may not be available for new Gmail accounts now that they're moving away from even per-app passwords.
- pdimitar 1y agoMy account is very old and the tool works on it. Figured it will probably stop working at some point, thanks for the refresh. I have that tool activating once every 24h still, to this day.
- oulipo 1y agoWhat's the best open-source GMail backup software that exists? Someone has setup something like that? (also archiving attachments, etc)
- sbarre 1y agoThis isn't exactly what you're asking for, but Google offers a service called Takeout that lets you request and download backups of all your data from their services, including Gmail. I have a reminder to trigger this every few months and update my local backup. If I recall it comes as a gzipped mbox file.
- oulipo 1y agoyes but I'd rather do it "continuously", the issue I want to back against is that Google locks me out of my account for some random reason
- Leftium 1y agohttps://github.com/GAM-team/got-your-back https://github.com/GAM-team/got-your-back - Open source - Resume (so backups/restores will eventually complete) Honorable mention: https://www.mailstore.com/en/products/mailstore-home/ https://www.mailstore.com/en/products/mailstore-home/ - Not open source - GUI with index: nice for searching mail locally - Resume only for backup (so large restores generally fail)
- crazygringo 1y agoI've been using: https://github.com/gaubert/gmvault https://github.com/gaubert/gmvault For a long time and it's worked great. But it seems like GYB is actively maintained, so maybe I should switch.
- karteum 1y agoFWIW, I used gmvault a long time ago (N.B. I typically deactivated individual .eml.gz compression in favor of a more global compression with a squashfs archive of the gmvault backup). Since I found not very practical to search through that archive, I developed https://github.com/karteum/gmvaultdb https://github.com/karteum/gmvaultdb in order to convert it to a local sqlite DB (I made it for personal use and chose to keep the schema simple to have simple queries for my case. If the DB was to grow bigger I'd probably use a different schema e.g. to place the contacts/from/to/cc in separate tables... I also chose to extract attachments directly on the filesystem rather than put them in the DB - which enable direct access and deduplication) (N.B. it can also process the mbox produced by https://takeout.google.com/ https://takeout.google.com/ with the caveat that in some case Takeout permanently losses some information because of a bug with older encodings so I'd always prefer a backup using gmvault or imap)
- flas9sd 1y agohaving sqlite exporters for platforms is great help for archiving, but also general questions: I used https://github.com/ltdangle/mail2db https://github.com/ltdangle/mail2db to see how much mail volume I still receive monthly on a mail account that I want to move away from. A top10 of senders directed my un- and resubscribe actions.
- hamburglar 1y agoThis looks great and simple. I’ll likely try it out. Any chance you’re working on including attachment metadata (and/or broken out access to the attachments themselves) in the future?
- curtisszmania 1y ago[dead]
- noer 1y agoThis is just a single table DB though? At that point, why not just export to a csv or dataframe or whatever and leverage analysis packages to analyze whatever you wanted to. I admittedly might just not have or understand the use case nor have I thought about how large a Gmail account actually is so feel free to ignore if I'm missing something!
- hiAndrewQuinn 1y agoA couple of reasons which pop to mind: - Searching a plain text data file is O(n). Searching a SQLite database that has been properly indexed, which is very easy to do nowadays with FTS5, is O(log n) worst case scenario and O(1) in the best case. This doesn't explain why SQLite over a dataframe or anything, but it definitely justifies it over plain text for large email collections. - SQLite is really easy to write custom views and programs around. Virtually every major programming language can work with it without issue. See also: simonw's wonderful https://datasette.io/ https://datasette.io/ . - SQLite is an accepted archival format by the Library of Congress, if you ever want to go down the rabbit hole of digital preservation.
- gitroom 1y agoman, the whole gmail backup mess reminds me why i avoid locking myself into someone else's sandbox. figuring out what actually keeps me sticking with a platform even when i know all the downsides - is it just laziness or something deeper?
- EvanAnderson 1y agoI am reminded a little of the Postgres-backed IMAP server Archiveopteryx: https://github.com/aox/aox https://github.com/aox/aox The schema from AOX always looked really good to me, but I never have gotten to really giving it a try. I wanted to use it, primarily, to get analytics about my mail and for search (not a daily-driver IMAP server).
- natmaka 1y agoI'm reminded of Manitou-Mail, a daily-driver powerful PostgreSQL-based dedicated mail client, quite robust and powerful. https://www.manitou-mail.org/ https://www.manitou-mail.org/
- EvanAnderson 1y agoThanks for the note about that. That's an interesting-looking take on the problem.
- ThinkBeat 1y agoWhat is the cost for bandwidth here? As someone with a 40GB+ Gmail account, will I get billed for the transfer using this tool? It is easy to fix though, since I can get Google Take Out (is that the name?) which I think is free and then parse file files once downloaded. Still using this tool would be faster from a get it going perspective.
- alimbada 1y agoI did something similar using Got Your Back and some C# hacked together in LinqPad to help me analyse my emails.
- renegat0x0 1y agoI recently tried to integrate Gmail in my app [0], and I poured too much time on it. I decided it is not worth to support Gmail. Gmail to SQLite describes 6 steps to get credentials working, but it is not true for me. After 6 steps: - that Google said that my app was not published, so I published it - Google said that app cannot be internal, because I am not a workspace user - for external apps - then it said I cannot use the app until it is verified - in verification they wanted to know domain, address, other details - they wanted to have my justification for scopes - they wanted to have video explaining how the app is going to be used - they will take some time to verify the data I provided them It all looks like a maze of settings, where requiring any of users to go above the hoops required by Google is simply too much. Links: [0] https://github.com/rumca-js/Django-link-archive https://github.com/rumca-js/Django-link-archive
- bradgessler 1y agoThe steps Google makes people jump through just for API keys are absolutely insane. Does anybody have insight as to why it’s so bad?
- IMTDb 1y agoBecause otherwise tons of people anonymously create api keys with extremely wide scopes for small / low quality apps. When those inevitably get used for nefarious purposes; Google image suffers as a result.
- victorbjorklund 1y agoProbably because if you get API access to someones email account it is game over. And people are stupid so some of them are going to click yes to some scammy app. And then they will blame Google for not protecting them.
- isaachinman 1y agoUse regular old IMAP with an app password. Don't jump through their hoops.
- bytter 1y agoFunny... I did the same thing yesterday, just because I wanted to list my recipient emails by domain. Code is awful, but here it is: https://github.com/hugoferreira/gmail-sqlite-db https://github.com/hugoferreira/gmail-sqlite-db
- alimbada 1y agoYep. I did the same to group by domain and sender.
- vladgur 1y agoAwesome! Feature request: parse email content to extract unsubscribe links and allow me to unsubscribe from most frequent senders easily
- frshOffTheBoat 1y ago[dead]
- shinryuu 1y agoWould have been nice if you supported google takeout with mbox instead.
- 1vuio0pswjnm7 1y agoWasn't there a period where one could get an XML feed of their Gmail, many years ago.
- flashblaze 1y agoIs there anything similar written in TypeScript?
- rantingdemon 1y agoInteresting tool. I'm trying it out now. I had to jump through some hoops in Google's admin panel that probably had me creating some OAuth org for my personal account... It is now syncing my messages, but very slowly. Some Async magic could probably be cool :)