Ecto_sqlite3 - an Ecto3 SQLite3 adapter

Awesome! You guys beat me to it but I’ll be happy to steal your tests for my library.

Thanks for the hard work!

Heh, @dimitarvp don’t be shy, I actually used your repo for a little while. I was trying to figure out how I was going to implement the adapter.

Something worth noting, the ecto_sqlite3 ecto adapter doesn’t necessarily depend on the exqlite library. It is entirely possible that we could flip implementations in and out. As long as the driver library implements db_connection it should in theory just be a shoe in replacement. This could also mean we make a behaviour library that we just implement for the drivers and ecto_sqlite3 can be completely agnostic to what backend would be preferrable.

That’s what I was thinking.

Exactly.

Thanks, man. For the kind words as well.

Hi,

this is marvelous! I am going to develop an application for an embedded device with limited memory, so sqlite is ideal here.

Many thanks for your work!

This is absolutely wonderful! Thanks so much for doing this. I’ve been wishing for this for quite some time.

Wow! This is amazing - Only a few weeks back I really wanted something like this!

Now at risk of coming across as greedy… Can I introduce you to “DuckDB”… It can be compiled against the sqlite C API, so seems like there is some chance of out of the box chance of getting something functioning. That said, I believe it’s SQL dialect is postgresql, so I suspect it would take work to make ecto integration work

My use case (probably) doesn’t require ecto integration though, I just have an urgent need to store some time series data in an embedded appliance where disk space is very minimal. I really just want sqlite with columnar storage for efficiency…

I would be willing to sponsor some work on this if someone wanted to pick this up?

I’m actually going to take a look at what is necessary to get a separate driver for DuckDB built. It’s a data store that also interests me greatly. Originally what started me down the sqlite path was using an ORC file to store timeseries data, but it was a massive pain to have to keep rewriting the file everytime I wanted to append data.

At minimum, I’ll probably build a really simple interface similar to Exqlite where you can build prepared statements and run those statements against the database.

As for getting an ecto adapter in there, that may prove a little difficult. But I believe we could shim in the ability to specify the driver for the adapter as well via a quick config.

use Ecto.Repo,
  otp_app: :my_timeseries_app,
  adapter: Ecto.Adapters.SQLite3,
  driver: DuckDB

Although, I am unsure if DuckDB provides the ability to use the RETURNING clause that SQLite3 just added.

Anyways, at minimum a driver in elixir is really easy to do.

Note, that SQLite is also very resilient. If your hardware does not break and you don’t do anything stupid, your data is safe. This is very important for embedded devices, because normally you have to expect a power failure any time.

Hi Sebb, Thanks for your thoughts. Yes, sqlite is an incredible database! This is an amazing integration, thanks to both of these projects. I will definitely be looking to use ecto+sqlite on an upcoming project

WRT embedded, there are lots of challenges here. Another is that storage is not infinite and usually quite slow (and ram in short supply). A typical sql database is going to store something like: timestamp (4 bytes), tag (umpteen bytes), value (say 8 bytes)

In contrast RRD will just store basically the value… However, they have trouble with non square datasets, ie varying tags

Dbs like influxdb can store the values as increments from the previous value and handle varying tags. So data usage can be only a few bits per sample sometimes.

So notwithstanding your point about data security, storage requirements for the data are also quite important. This is a difficult problem. I have pondered a simple DETS or cubdb db. However, I am fairly sure that I will reverse into writing a whole timeseries db, hence looking for a real one!

The requirements depend on the data you are storing.
It may be OK to lose some samples in a time series, but you can’t lose vital config data.
DETS will not help you in case of a power failure at the wrong time.