Ecto_sqlite3 - an Ecto3 SQLite3 adapter

kevinlang

Hey all,

We have made an Ecto3 Adapter for SQLite3, ecto_sqlite3!

We have successfully on-boarded the full suite of integration tests (320+) that ecto and ecto_sql provide, as well as writing a good amount of our own integration tests and functional tests for the query-generation logic, giving us a great degree of confidence that this adapter is stable and ready for everyday usage. Due to its newness, it hasn’t seen much production use, but we are eager to have people start trying it out and let us know any issues they hit.

This adapter is mainly possible thanks to the exqlite SQLite3 driver, which is an Elixir-written successor to the Erlang SQLite3 NIF esqlite. It leverages Dirty NIF functionality to make the code easier to reason about and maintain.

Lastly, we have begun work on adding a --database sqlite3 option to Phoenix to make this adapter easy to use for new projects. Of course, even without those changes, switching from e.g., Postgres to SQLite3 is usually only a couple of lines of config changes

Feel free to file any issues you encounter in the Github repo.

cc @warmwaffles , who lead most of the development

52 comments

#ecto #library #ecto_sql #ecto_sqlite3

55 8696 52

2021-08-27 01:49:43 UTC

First Post!

Show Best Posts
Show All (oldest first)
Show All (newest first)

dimitarvp

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

Thanks for the hard work!

Post #1

Most Liked

kevinlang

I’m happy to report that phx.new support for the sqlite3 adapter is now merged into Phoenix!

Starting with the next Phoenix release, you will now be able to generate a sqlite3 repo for your Phoenix application by specifying the --database sqlite3 flag in your call to phx.new.

Post #23

warmwaffles

@wanton7 and @tj0 I recent went through and fixed the memory leak issue, or rather the perceived leak issue.

I could not reproduce the leak, but, after reading through mailing lists and what not, the issue seemed to stem from the garbage collector not destructing the prepared statements in a timely manner. So instead, when ecto closes the connection we handle it by also releasing the underlying resource (the prepared statement) and then the destructor will run later and free the last remaining bytes that was a pointer to the prepared statement.

Overall this should suffice and would love to see if anyone who was having issues with memory growing like crazy during high load to give the latest release a try.

https://github.com/elixir-sqlite/exqlite

Post #52

dimitarvp

My upcoming library will hide that from you because it will do pooling on the Rust side – the pool size will be configurable either as a project-wide config or when opening the DB.

I’ve done extensive tests (although I am not sure they are very scientific just yet). Opening 50 separate sqlite3 databases each with 20 “connections” (as we know, they are not really connections but file descriptors) and the test program barely consumed a single digit MBs of RAM.

The main challenge for such scenarios would be environments with severely reduced file descriptor limit (part of all Linux installs and a good chunk of the container cloud hosting platforms) so one has to be very judicious in picking a pool size with sqlite3. I suggest you put a pool size equaling the average expected amount of parallel tasks that will read from your DB. But honestly, for most projects a size of 2-3 should be quite fine – sqlite3 is extremely fast so taking a “connection” from the pool even if all “connections” are busy should be a matter of just a few milliseconds.

(Sorry that I haven’t pinged you to help me with the library yet; I’ll be at a lot of doctors in the next 1-2 months so I am keeping quiet since I barely have any free time.)

It’s quite interesting and I’ve used it once – it works just fine (which is something I find myself automatically taking for granted in the Elixir libraries! ).

Post #18