feat: sqlite storage built into the container

[sakgoyal]

Problem Statement

Is it possible to use a sqlite db directly built into the main container so the entire app is entirely self contained and does not depend on other running services?

Currently it needs a separate postgres container, but using a simple sqlite file means everything can live inside a single container.

This is dramatically simplify deployment and reduce complexity. This also means you can just use a single docker command instead of a docker compose setup.

[usekaneo]

ROA-832

[randoneering]

@sakgoyal I know the project originally used this method, but moved away from it. I can see this as a good use case for duckdb. I will take this feature add on.

[sakgoyal]

so this feature will be added back as duckdb instead of sqlite?

[randoneering]

I was considering it, but it would be the wrong tool for the job. It is more of an OLAP database and generally used for analytics. Sqlite would make the most sense, again, as it was originally the method used at the start of Kaneo.

Let me ask you this-is there a reason you wouldn't opt for using an external postgres database server? Say, maybe running something like a free tier Neon database? You could then just use a .env file and docker rather than docker compose.

[sakgoyal]

I want to reduce external dependencies and keep everything inside 1 container. it makes infrastructure far simpler to maintain when you can have hundreds of applications running on a machine and want to keep track of what is what, and what data is where.