SQLite usage

[Arteneko]

For small (and single-user) instances, it would alleviate the maintenance burden to allow running an instance using SQLite as RDBMS.

Would you consider it something possible for this project?

[tsmethurst]

In theory it's definitely possible, since we abstracted the database connection into an interface that should work with dependency injection (so you can connect it to any kind of DB that can fulfill the interface).

In practice, we still need to tidy up the existing database functionality a bit, and work on basic features before we can put this on the roadmap.

So........... maybe! But not immediately.

[Arteneko]

I guess i would try to implement myself then, since I've been meaning to use it on my own host.

[decentral1se]

Just to chime in, I think this would be a wonderful addition to GtS 🚀

[5HT2]

To add to this, once this is possible it would be nice to add something to the docs that explains how to connect it to an existing masto / flavor database.

[charlag]

I considered the same for my own project but decided against it:

1. SQLite is single-threaded so it will limit how fast you can do certain things (but it might be fine still)
2. Need to re-do all JSON operations because JSON subsystems are pretty different between pg and sqlite (but idk how it is here)

so it's something to keep in mind!

[tsmethurst]

To add to this, once this is possible it would be nice to add something to the docs that explains how to connect it to an existing masto / flavor database.

@L1ving I made an issue for this here :) #128

[aschrijver]

Just some FYI's. Sqlite is getting a lot of attention on HN these days, and will probably suffice in production for small-scale instances. Here's some One process programming notes (with Go and SQLite). In this HN comment in a larger thread about Litestream (written in Go) some good tips on production-use. And if you wanna go wild, then use a cluster with rqlite (also written in Go).

[decentral1se]

There is a pure go implementation now!? sqlite 🤯

[tsmethurst]

There is a pure go implementation now!? sqlite exploding_head

this would be the implementation i suggest we go for, since i'd rather avoid gcc dependencies

[tsmethurst]

I mentioned this issue in the above PR, because the database interface tidying up and reshuffling will affect how this is implemented (hopefully making it easier overall).

[tsmethurst]

So, right now we use Go-PG for postgres connections and ORM. Looks like go-pg is now in maintenance mode, and the devs recommend migrating to Bun.

And bun (guess what!) also works with SQLite, and is apparently a bit faster than go-pg.

So the quickest step to getting sqlite working with gotosocial might just be to migrate from go-pg => bun

https://bun.uptrace.dev/guide/pg-migration.html#new-features

[tsmethurst]

Now that the migration to bun is complete, the starting point for adding an SQLite implementation is here: https://github.com/superseriousbusiness/gotosocial/blob/main/internal/db/bundb/bundb.go#L89-L91

Since sqlite doesn't use the same sort of connection options as postgres, this will also probably necessitate some changes to the database configuration in the following locations (at least)

https://github.com/superseriousbusiness/gotosocial/blob/main/internal/config/db.go
https://github.com/superseriousbusiness/gotosocial/blob/main/internal/config/config.go
https://github.com/superseriousbusiness/gotosocial/blob/main/cmd/gotosocial/databaseflags.go

[NyaaaWhatsUpDoc]

Currently being tackled in #154