Support SQLite

[tmaier]

I checked the schema.rb. I see it enables the plpgsql extension.
I don't see where any PL/pgSQL Stored Procedures are being used here.

I think this can be removed and thus, enable the support for other databases, like SQLite.

Why supporting SQLite?
This would increase the ability to self-host, e.g., on a NAS

[krschacht]

Good catch. There are no stored procedures being used. However, we are using an advanced feature of Postgres which enables solid queue to work. I forget what it is, it’s a special query type, but that would have to be solved for to enable SQLite. Or if you must do SQLite + redis then I’m not sure it’s any simpler than just Postgres.

anyway, in support of SQLite just sharing that consideration.

(Postgres also powers action cable but I don’t think any special features there)

[tmaier]

Thanks for your quick reply. Solid Queue supports MySQL, PostgreSQL and SQLite.

Solid Queue can be used with SQL databases such as MySQL, PostgreSQL or SQLite
Source: https://github.com/rails/solid_queue/

[tmaier]

The real blocker is ActionCable, since it indeed just works with Redis and Postgres, according to https://guides.rubyonrails.org/action_cable_overview.html.

There is a blog post by fly.io how to get all of Rails running on SQLite: https://fly.io/ruby-dispatch/sqlite-and-rails-in-production/
In this case, they mention the litestack gem

For Rails 8, DHH plans to support SQLite directly in Action Cable and he also stated, that they implemented an Active Record-based cable adapter, which supports SQLite. (Source: rails/rails#50480)

[krschacht]

@tmaier I’ve been watching some of those Rails discussions too. They’re doing such good things with that. If we zoom out a little more, there are some related considerations.

I would really like someone to implement a Kamal based deployment. And I would also like to configure a Dockerfile that can run this app which has postgres and puma running on the same server. For personal use or just a handful of people, this would definitely be doable and then it would enable us to stay within the free tier of Fly in addition to potentially simplifying the Kamal deploy to services like Hetzner or AWS since you’d just need a single machine to start.

Both of these should be pretty easy to do, probably even easier than adding SQlite support, and would bring some of the same advantages. That way we could drag our feet until Rails 8 finishes their Action Cable work.

[tmaier]

Agreed. Waiting for Rails 8 is the most sensible way forward for SQLite.

For the other things you mentioned, like Kamal or to run Solid Queue within Puma:

• Kamal could make sense for some users. My critique of Kamal (unrelated to hostedgpt) is, that it oversimplifies the setup of a Postgres database. This may work for a PoC, but no one should ever run like this a production database, since backup/recovery etc. is not covered at all. And I personally consider servers in most cases as ephermical ("cattles instead of pets")
• Dockerfile. I think the current Dockerfile could be simplified. The differences between fly and render do not seem too big to really matter. Besides this, with a more generic production target in the Dockerfile, one could provide a "production" docker-compose file. NAS like Synology support to run docker compose. This one would suffer from the same issue about DB backup/recovery like Kamal for now. However, with SQLite support (with Rails 8) backup and restore of the database can be solved easily.
• Puma with Solid Queue: I think this could make sense as a default setting, since anyone should first out-grow this, before resorting to something more complex.

[krschacht]

Yes, agreed on all fronts! And regarding the Dockerfile: I got Render working and then as I was getting Fly working I wanted to completely accept their generated Dockerfile for easy maintenance going forward, and I didn’t want to break Render so I knew I was duplicating a lot. But now that they’re both working I think Fly’s generated Dockerfile is pretty smart — it includes JIIT and other ruby optimizations which are great on any host. I bet this would nearly work as-is for the other two environments, greatly consolidating it.

Let me know if you want to take the lead on anything! You know the best way to be volunteer is to start sharing good ideas. ;)