Improve database handling

[Hyodar]

At the very beginning of development, we were using an embedded KV database - it was performant, simple and good enough for our uses. After we started getting on with more complex queries, we decided to move on to SQLite, since it's a good alternative for an SQL database that doesn't require much of a setup and holds pretty good performance. It's still pretty limited in terms of datatypes or general resources though. Still, now we're seeing issues such as #209 which brings the question of which tooling we should use. With gorm, we're flexible enough to switch to some other SQL database without much of an issue. We can also explore SQLite settings and see if there's anything that is good to set there, e.g. WAL mode, but I'm afraid the lack of concurrency may lead to issues down the road. We can also go back to KV databases if there's a good alternative for our use-case, though in this case it'd be very preferable if we can have some flexibility on querying (not only strictly KV) since when we need to do something more complex than strict KV we can just do it easily.

Making this issue so we can discuss the alternatives and pros and cons here.

[emlautarom1]

I agree on keeping an SQL interface rather than using plain KV since, as you mention, this allows us to perform complex queries if needed. At the same time - if possible - I would prefer to not spin up an additional server (ex. Postgres) since it adds additional maintenance cost down the road. A couple of questions right now are:

• Do we see performance degradation by limiting SQLite to a single connection?
• If there is, does it have anything to do with the ORM we're using?
• If we want concurrent access, then WAL (https://www.sqlite.org/wal.html) sounds like a well supported feature since it's been available since 2010.

[Hyodar]

Let's keep this open for a while. I don't think our use-case should be problematic with a single connection, but if it comes to that this discussion should be brought. Main reason we should think carefully about this is migrations are always painful, so if we have a convenient alternative that might make it less likely for it to happen, it'd be nice.

If we want concurrent access, then WAL (https://www.sqlite.org/wal.html) sounds like a well supported feature since it's been available since 2010.

Yup. Though IIRC I think WAL does not make SQLite totally concurrency-friendly - from the page you linked: "Writers merely append new content to the end of the WAL file. Because writers do nothing that would interfere with the actions of readers, writers and readers can run at the same time. However, since there is only one WAL file, there can only be one writer at a time."