Is there any plan or intention to support SQLite?

[joeljuca]

Hello!

I just discovered Triplex and while I was exploring it I realized the SQLite-based multi-tenant implementation I'm working on is quite similar to what Triplex does for Postgres and MySQL. So, I'm wondering if there are plans, or perhaps intentions, to support SQLite.

[aseigo]

Hi! Did you complete your implementation, or would you still be interested in adding support to triplex?

It would probably be fairly easy as all the adapter-specific code is in lib/triplex.ex (aside from a couple small things in lib/mix).

I've recently forked the repo and merged all outstanding reops, cleaned up tests, etc. over at https://github.com/aseigo/triplex after years of non-activity here. I will be doing a release soon and would happily merge in such a feature.

[joeljuca]

@aseigo Hello Aaron! Good to see some movement in here. Nice work on pushing things forward over Triplex.

In regard to SQLite support, I can't put time on it for now, but I'm definitely interested in this feature. If you're forking Triplex into smt else I'd like to follow the development, and I'd jump in when I have the opportunity.

[aseigo]

Cool! Yes, as Triplex is apparently abandoned for all intents and purposes, and I (among others) use it in production, it's time to move it forward, even if it means forking it. I'd prefer to have a hand-over from the existing maintainer, so let's see if that happens. Either way, there'll be a new release in the next week or two.

FWIW: I'm also using SQLite with Elixir, though not in a multi-tenant situation ... it's a really nice idea, though, and one definitely worth implementing in this library IMHO :)

[joeljuca]

@aseigo cool! I'm building on top of SQLite as well, I implemented a SQLite-based multi-tenant system (one db per tenant) . It was a challenge, and there are things I'd like to implement, like automated point-in-time backups, and soft real-time replication streaming w/ Litestream, but the basic setup is ready.

My idea was to adapt this implementation to smt compatible with Triplex (or some other multi-tenant lib) at some point, make it fully Open Source, etc. – but again, I haven't had a chance to put the effort in it yet.

I also thought of forking Triplex as well, so I'm happy to see you taking the lead. Lemme know if you end up teaming up a bunch of Elixir hackers onto a Triplex fork, I'd like to help support it. I've made some research over the auto-backup feature and online streaming feature, so I could share my findings. A proper integration with Oban would definitely be a great addition to routine tasks.

[aseigo]

@joeljuca i absolutely wish to have multiple collaborators on the repository, as i really don't like "bus number of 1" in projects (c.f. Triplex) ... i'll keep you in the loop...

i am also very interested in seeing the solution you came up with for SQLite as well as the access model in the SQLite3 ecto library is fairly different from others .. it currently simply rejects anything that uses prefix: from what i gather! so i'm really interested in how you've approached that set of issues!

as for my own use of SQLite3 and Elixir, i have been using it paired with SpatialLite for GIS functionality as well as some data sync experiments ... nice to see usage of it growing

[joeljuca]

@aseigo IIRC, each SQLite database connection is treated as a process. So I start_link/2 a process for each SQLite database, and whenever I want to query them I have to call a fn that sets the connection PID in the current worker process (I think it's MyApp.Repo.put_dynamic_repo/1 or smt like that). I do run my queries, etc., then I reset this PID back to the main repo (MyApp.Repo) when I'm finished. I don't recall the specifics, but I know it's pretty much launching one process per db, then using the right process when I want to query data for a specific tenant.

What I'm mostly missing from my setup is to set up a "smarter" supervision tree that would sleep processes that haven't been used in a while, and will launch a new process if they're requested a PID for a sleeping database. And Oban jobs for db maintenance ops.