#Using SQLite as the Main Database

20 messages · Page 1 of 1 (latest)

reef umbra
#

I just wanted to get people’s thoughts on this as I have been thinking about this for a few days already.

I saw the recent Rails 8 keynote and noticed a sudden interest in SQLite as the primary database backend. I get why people gravitate towards the idea as eliminating that network call to the database in another segment is really priceless. However, am I misunderstanding things though? This will only work on a single-server deployment, right? If you have a highly available, distributed system with redundant microservices, is SQLite still a viable option? I wanted to get people’s thoughts because I was wondering how to actually get this to work. I only came up with two options and I didn’t think either was sustainable.

Let me know what you guys think. I appreciate your feedback and ideas.

The first option I thought of was directing all the write operations to one node and then all reads to the other nodes. Effectively, I am just replicating the traditional database setup with one writable master and a bunch of read-only slaves.

The other option I thought about was implementing an active-active setup where a client can write to all available nodes and each node would handle the conflict resolution through vector clocks.

I don’t know. Not sold with either option. Is there another way to use SQLite and still make the environment highly available? I like the idea of using SQLite because of the raw speed I get but I feel like the complexity that comes with it is not worth it. I feel like I am better off using Postgres. Or is that the problem? Am I trying to solve this problem using traditional concepts?

Thanks in advance for your feedback. Very much appreciated. You guys have a good one! 👍

quasi brook
#

sqlite works just fine on small monolithic systems. i've used it for this purpose. it's fine for this. if you plan on having distributed systems, you probably want something else.

foggy spindle
#

if you think you will need a distributed system then yes you would likely not use an embedded db

quasi brook
#

for example, I am currently building a small headless CMS, not a lot of functionality, it uses sqlite for storing CMS data. for this purpose sqlite is just fine

#

I can embed everything into a single binary and deploy it anywhere quite easily

#

a traditional database with a server means I would need to mess with that stuff when deploying, with sqlite I can just embed a file/create a database and run migrations right there with no server setup

reef umbra
#

Thanks so much for the quick feedback @quasi brook and @foggy spindle ! Much appreciated. gopherhappy

#

First time posting here. Really amazed at how fast the community responds. gopherblushing

foggy spindle
#

postgres and mysql are good choices for non local db, if you go that route

quasi brook
#

id say postgres is the only good choice sunturtle if we go for a generic distributed application

#

it can do all that replication and reading stuff you talked about

#

I mean both can do it, but postgres is just objectively a better database

reef umbra
foggy spindle
#

not to turn this into tab vs space, vi vs emacs, etc … but what makes it “objectively better”?

quasi brook
#

and objectively speaking, it has worse performance

#

that's both a subjective point and an objective point

#

also... oracle.

foggy spindle
#

re oracle: use mariadb (and I doubt perf on tuned instance and workload and storage unit is worse, but I haven’t looked at that in years)