ecto_sqlite3

ecto_sqlite3 copied to clipboard

Hi there

I have a few questions and possibly an interesting proposal.

First of all, amazing work you;ve put in this adapter and the exqlite version! I'm in love with both and have migrated from sqlitex.

Is there a way to use this adapter and run normal ecto adapters and for it to just generate the sql and possible pass it on to somewhere else? IE another server. Maybe a behaviour or something similar to switch between the default of running exqlite directly or passing it somewhere else. A simple abstraction would suffice. Such as another server which is running exqlite, or a local instance which handles the exqlite wrapped in (gen)servers (or something better) for concurrency. There is more to say about this yet I'm trying to be brief.

The rationale:

Last year I began working on a project similar to Actordb.com in pure Elixir. I've used sqlitex for it and mixed code from sqlitex, the mysql and postgresql ecto adapters. The idea is to have a server which you can use independently with many sqlite connections via the Actor model (since Elixir and Erlang support this greatly). Most of the apps I've built can deal with this actor modular approach quite well.

I;m thinking out loud now with an eye on using this instead of my own ecto adapter. As this will simplify things instead of wasting effort in duplicating and reimplementing the wheel from zero. Since Elixir's built in mind with modularity.

My current implementation prototype is located at: https://fossil.lba.im/glow/dir?ci=tip (WIP)

This is more of a question and invitation to share ideas and thoughts.

Some references and ideas: https://expensify16.rssing.com/chan-10870710/latest-article4-live.php - Scaling Sqlite to 4 million queries per second https://review.firstround.com/your-database-is-your-prison-heres-how-expensify-broke-free http://www.actordb.com/

Thanks for your understanding

This is actually something I was investigating for my own needs. Yes it is possible, but it is incredibly cumbersome with how ecto is structured.

If I have time, I'll extract out the stuff I did into a blog article or example to use, but it works. The only thing that is a pain in the ass is coercing the rows returned back into the ecto schemas themselves. You have to Enum.zip the columns with the rows and feed it into the ecto struct.

Basically long story short, forcing a square peg into a round hole. Sort of.

Oh yeah, the square peg in round hole is why I ditched the actordb ecto implementation I began in 2019. This is one of the reasons why I asked this question, It would be better if there where one central ecto SQL generator for sqlite instead of multiple implementations which all will hit the same issues over and over.

I'm currently investigating all possible methods and I'll be doing some Proof of Concepts. I'm hoping to have something that will require a maximum of 10 modified lines in the ecto_sqlite3 to support what I'm thinking off.

I'll come back with the results and possibly a pull request in the future.

The only thing that is a pain in the ass is coercing the rows returned back into the ecto schemas themselves. You have to Enum.zip the columns with the rows and feed it into the ecto struct.

It's a bit of boilerplate, but using Ecto.Repo.load should handle most of the actually complex things, doesn't it?

Yes, that does take a bit of the complexity out. I do remember using it but had a case where it wasn't possible. It has been a while since I last played with it. For those looking at this as well: https://hexdocs.pm/ecto/Ecto.Repo.html#c:load/2