Feature: SQLite for self hosting

[henrikbjorn]

Feature Overview

Would be great if there was an option to use SQLite for hosting (especially selfhost). It does not seem that sqlite package nor gem is part of the Gemfile or Dockerfile.

Requirements

If there is a missing / incorrect requirement, please leave a comment before starting work on this.

• Possible to use SQLite

Implementation Suggestions

Use the gem from Fractaledmind https://fractaledmind.github.io/2024/04/15/sqlite-on-rails-the-how-and-why-of-optimal-performance/ and maybe tune something a bit based on his post

[brtwrst]

yes sqlite support would be very welcome

[danwetherald]

+1

Looks like the first big change would be the schema - currently the entire schema is using gen_random_uuid() as its primary keys.

[zachgoll]

@henrikbjorn @brtwrst @danwetherald what are the primary reasons you'd prefer Sqlite over Postgres? I'd like to understand specific use cases in greater detail.

[22Solo]

I prefer SQLite over PostgreSQL for its simplicity, lightweight nature, ease of setup for local storage, and suitability for single-user applications or prototypes where advanced features and concurrency are not required.

[danwetherald]

@zachgoll - Self hosting is much simpler using SQLite - If deployed to fly.io (for example) - this eliminated an entire second app for the DB which needs 3 machines running 24/7.

When self hosting for usage of 1-2 people with occasional usage, it would be nice to scale machines / servers to zero - this is a bit tougher with Postgres.

Also, you are already using good job, should also think about moving to solid queue powered by SQLite. (as this is the default in Rails 8)

[danwetherald]

I prefer a single source of truth with access from anywhere.

[zachgoll]

Noted, thanks!

[kalicki]

I think that currently litestack (https://github.com/oldmoe/litestack) is the most complete way to integrate with sqlite with little effort.

[danwetherald]

@kalicki very interesting - I would rather see this head more towards a vanilla Rails 8 implementation with gems maintained by the rails core team, but this is certainly interesting.

[zachgoll]

Yep, in general, we're trying to make this project as "Vanilla" and unsurprising to new contributors as possible due to the OSS nature of the project.

[brtwrst]

I host things locally on tiny/small servers, think raspberry pi. Basically everything is single user.
I chose sqlite wherever I can.
No second container, never a need to migrate the db to a newer version, super easy backup.
Only downside to sqlite is the "concurrent write lock", which should not matter at all at such small scales.
I mean I run a Nextcloud instance on sqlite that syncs to multiple machines with no issues.

[danwetherald]

Maybe take a look at this.

https://highperformancesqlite.com/watch/locking

[zachgoll]

@brtwrst Awesome, thanks for the info here! Very helpful.

[dixpac]

If we look at this project only through the lens of self-hosting then SQLite makes sense. But, not sure what are the plans with the "hosted by Maybe" option?

[zachgoll]

@dixpac the "hosted" version will be running on Postgres. Because of this, it is unlikely that we'll introduce SQLite in the "core" code for this repo and support it internally.

That said, very open to all proposals as I'm sure there's a way to introduce it as some sort of "add-on" / "plugin" down the road

[johnpitchko]

I was tinkering with this today. As @danwetherald pointed out, the first issue I encountered was using UUIDs for keys, but I saw that blog post that showed some steps for adding the necessary gems and extensions to support ULIDs in SQLite.

The other issue I encountered was that SQLite does not support a JSONB field type, but I think it supports JSON?

[johnpitchko]

Thought about this some more. I think one big issue is that the migrations currently use UUIDs for the primary keys. AFAIK SQLite does not support UUID data types. So out of the box, none of the migrations can run.

Now one could write a Rake task to parse all the migrations and swap the primary and foreign key column types to integer (or even ULIDs) which should allow the migrations to run at a point in time. However, an issue of future migrations will remain. That is, whenever the team creates/modifies tables with UUIDs, apps using SQLite won't be able to run those migrations out of the box unless the UUID columns are replaced.

Maybe there is a mechanism where one could hook into the Rails migrate task so that it first swaps column types before executing the migrations? I have no idea...