timeliner VS go-sqlite3

Heya! I'm the author of PhotoStructure, and my Google Photos account (before I started working on PhotoStructure) is about that size, too.

I wrote up some tips here: https://photostructure.com/faq/takeout/

This is what I did:

1. First try to fetch all your Google Photos via Takeout in one archive. If it fails (like it did for me), try different-sized .tgz archives. I had to use the 10 Gb option (using 50gb caused an internal-to-google error).

If that fails to work, the last resort is to manually create by-year albums, shove all photos from that year into that album, and do a takeout of just that album. Repeat as necessary for every year.

2. Install an app on your phone to *directly* upload the original photos and videos from your phone to your NAS/home server. I have several recommended apps here: https://photostructure.com/faq/how-do-i-safely-store-files/#...

At this point, you can still use Google Photos (for viewing and as a last-ditch backup), but your originals are safe (without all the Google Photo downsampling and metadata shenanigans), and you're free to use whatever self-hosted software you want (like PhotoStructure, but there are a ton of alternatives, as well).

FWIW, I also tried this software: https://github.com/mholt/timeliner -- it does what it can, but the files you get via the API has a bunch of metadata stripped from it. I even had captured-at times get mangled with older photos.

This is why I'm building Timelinize [1]. It's a follow-up to my open source Timeliner project [2], which has the potential to download all your digital life onto your own computer locally, and projects it all onto a single timeline, across all data sources (text messages, social media sites, photos, location history, and more).

It's a little different from "self hosting" but it does have a similar effect of bringing all your data home and putting it in your control.

The backend and underlying processing engine is all functional and working very well; now I'm just getting the UI put together, so I hope to have something to share later this year.

[1]: https://twitter.com/timelinize (website coming eventually)

[2]: https://github.com/mholt/timeliner

Not a "big project/service" but a Go project that uses Sqlite is one of my own, Timeliner[1] and its successor, Timelinize[2] (still in development). Yeah the cgo dependency kinda sucks but you don't feel it in code, just compilation. And it easily manages Timeline databases of a million and more entries just fine.

[1]: https://github.com/mholt/timeliner

[2]: https://twitter.com/timelinize

This looks promising but might be a bit complicated for you: https://github.com/mholt/timeliner

I made Timeliner to download my Google Photos: https://github.com/mholt/timeliner -- requires some tech prowess for now, though.

SQLite encryption at-rest is a hot requested feature of both the “default” CGo driver [1] and the transpiled alternative driver [2]. So, this is a feature I wanted to bring to my own Wasm based Go driver/bindings [3].

Open-source SQLite encryption extensions have had a troubled last few years. For whatever reason, in 2020 the (undocumented) feature that made it easy to offer page-level encryption was removed [4]. Some solutions are stuck with SQLite 3.31.1, but Ulrich Telle stepped up with a VFS approach [5].

Still, their solution seemed harder than something I'd want to maintain, as it requires understanding the structure of what's being written to disk at the VFS layer. So, I looked at full disk encryption for something with less of an impedance mismatch.

Specifically, I'm using the Adiantum tweakable and length-preserving encryption (with 4K blocks, matching the default SQLite page size), and encrypting whole files (rather than page content).

I'm not a cryptographer, so I'd really appreciate some roasting before release.

There is nothing very Go specific about this (apart from the implementation) so if there are no obvious flaws, it may make sense to port it to C/Rust/etc and make it a loadable extension.

[1] https://github.com/mattn/go-sqlite3/pull/1109

for what it's worth, the two pool approach is suggested here by a collaborator to github.com/mattn/go-sqlite3: https://github.com/mattn/go-sqlite3/issues/1179#issuecomment...

SQLite is great. I've also recently settled on it as a key-value store, after considering a few purpose-built key-value solutions. Turns out that it's really easy to make SQLite work as a key-value store, but very difficult to make key-value stores relational.

Just be careful with `:memory:` databases. From the mattn/go-sqlite3 FAQ[1]:

> Each connection to ":memory:" opens a brand new in-memory sql database, so if the stdlib's sql engine happens to open another connection and you've only specified ":memory:", that connection will see a brand new database. A workaround is to use "file::memory:?cache=shared" (or "file:foobar?mode=memory&cache=shared"). Every connection to this string will point to the same in-memory database.

I noticed strange behaviors with just `:memory:` where tables would just disappear at random, and this workaround helped. Make sure to use a unique filename as the `file:` value, especially if using this in tests.

[1]: https://github.com/mattn/go-sqlite3#faq

github.com/mattn/go-sqlite3

For building my web server, I chose to use the Gin framework as the foundation of my app. It was incredibly easy to understand and work with, and I was pleasantly surprised by how seamlessly it integrated with writing unit tests for the server. To handle the database, I leveraged the power of go-sqlite and migrate for efficient SQL queries and migrations. These libraries proved to be both powerful and user-friendly, making the development process a breeze.

https://github.com/mattn/go-sqlite3/blob/master/_example/sim...

SQLite does have an optional "user authentication" extension, though I've not personally tried it out:

https://www.sqlite.org/src/doc/trunk/ext/userauth/user-auth....

The widely used Go SQLite library by mattn says it supports it, if that's useful:

https://github.com/mattn/go-sqlite3#user-authentication

I have an OSS project, sq which is a data-wrangling swiss-army knife for structured data. Think of it as jq for databases. It supports Postgres, SQLServer, MySQL and - relevantly - SQLite. It embeds SQLite via CGo and the mattn/go-sqlite3 driver.

IDE: use whatever make you productive. I personally use vscode. VCS: git, as golang communities use github heavily as base for many libraries. AFAIK Linter: use staticcheck for linting as it looks like mostly used linting tool in go, supported by many also. In Vscode it will be recommended once you install go plugin. Libraries/Framework: actually the standard libraries already included many things you need, decent enough for your day-to-day development cycles(e.g. `net/http`). But here are things for extra: - Struct fields validator: validator - Http server lib: chi router , httprouter , fasthttp (for non standard http implementations, but fast) - Web Framework: echo , gin , fiber , beego , etc - Http client lib: most already covered by stdlib(net/http), so you rarely need extra lib for this, but if you really need some are: resty - CLI: cobra - Config: godotenv , viper - DB Drivers: sqlx , postgre , sqlite , mysql - nosql: redis , mongodb , elasticsearch - ORM: gorm , entgo , sqlc(codegen) - JS Transpiler: gopherjs - GUI: fyne - grpc: grpc - logging: zerolog - test: testify , gomock , dockertest - and many others you can find here