Yeah, I found that repo after some more searching once I posted the question.
That’s also WIP so it could be a good project to contribute.
1 Like
@outlog @lambrospetrou Thanks for the mention. I have a “help wanted” section in the README in which I describe what work I think remains to bring sqlite_ecto2 from its current prerelease state to something we could legitimately call a 2.0.0 release.
PRs welcome!
@sotojuan I agree (which is why I started this project 
). One caveat that isn’t (yet) in the README: You really should avoid using SQLite in any context where you are deploying as a cluster (unless there is a single database node in the cluster). Since SQLite works with its database stored as a local file rather than communicating over-the-wire with an independent database server, each machine in the cluster would wind up operating on independent versions of the data. Certain doom awaits.
3 Likes
SQLite is a good choice for the IOT (such as the raspberry pi) devices that don’t need a full blown database.
2 Likes
Very true! Indeed @ConnorRigby who is the current maintainer of https://github.com/elixir-sqlite/sqlite_ecto2 often uses it on Nerves based systems running on devices like the Raspberry PI https://nerves-project.org/
SQLite is an A+ option for Nerves in my opinion. Unfortunately the adapter needs a bit of love for Ecto2 as well as work for Ecto3. That said i use it in production today and it’s a great library once you understand what it’s use cases are.
3 Likes
ecto_sqlite3 didn’t end up causing a dependency conflict on the day that is today, 2021-08-06.
SQLite really makes it easier to get started in development, whether it’s a hobby project or a serious one. 9/10 times I don’t need PostgreSQL in place until I’m actually ready to go live anyway, and it’s extra work to set up a PG database (including users, permissions, etc) in development as well test compared to a SQLite database. For a team project, every member would have to set up two PG databases each (dev and test), compared to zero setup for SQLite. mix ecto.migrate just works without any additional configuration. Ideally PostgreSQL would be the default for production, since, as @chrismccord points out, PG is superior for production, and SQLite would be used for dev and test (something you can do in Rails).
Unfortunately, Elixir appears to use a single database adapter for all environments (see generated repo.ex in application). So that would would be a feature request, to allow different repo’s (and/or different DB adapters) for different environments. It’s a minor thing, but it would reduce a lot of friction for people evaluating Phoenix and also for setting up dev and test environments.
As an aside, you could also do in-memory SQLite databases for testing, which gives you a brand new test DB every time (no migrations needed).
This is not considered a good practice in general, you want every environment to be equal to production. It’s simple engineering 101. Although if it’s your own little project don’t let this stop you.
And lots of projects out there rely on database specific features.
7 Likes
I don’t think I’ve ever NOT run into db specific feature conflicts—at least fulltext search always seems to be a requirement and I’m not installing a dedicated db for that. I also ran into some other surprising differences when I converted a client’s db from pg to sqlite. I think Rails allows (allowed?) different dbs in different envs because historically they pushed the idea of doing everything in the application—they were even against things like fkeys back in the day.
5 Likes
Even worse, the problem could be extremely subtle. SQLite is SERIALIZABLE by default (literally serialized, in fact), while Postgres is READ COMMITTED which is much weaker. You could write code which presents with all sorts of awful anomalies on Postgres and yet is perfectly safe in SQLite, and you would be none the wiser when you test it locally.
Even if you ran Potsgres in SERIALIZABLE you would have aborts due to how Postgres’s SSI handles conflicts, whereas SQLite is literally serialized so there will be no aborts.
It’s just dangerous tbh.
2 Likes
For development environment there should be no difficulties in setting a postgres database. You shouldn’t need any special users or permissions and using the default postgres/postgres as username and password is universal across most phoenix applications. To make the setup even easier to manage, you can create a docker-compose file and use the dockerized version of postgres. That one is even easier to manage as you can automate all the setup process and reuse it across different projects.
I already dug into this topic some time ago when I wanted to use the same repo for both sqlite and postgres (mind you, my aim was to allow the user to deploy a liveview application with either sqlite or postgres). You can read about what I found after my investigation here: What is the reasoning behind baking in the adatper at compile-time
The transaction cancellation that is used by postgres currently is faster (and allows concurrency) than using a in-memory database. The basic idea behind testing with a real database is that you want to isolate and rollback any data insertions and transactions rollback are the perfect feature for that. SQLite not only doesn’t allow multiple writes at the same time (which means no async tests 
), but you will have to run migrations everytime you create a new in-memory database, which is slower compared to postgres running migrations only once when tests start.
1 Like
LOL, I never thought about any of this. I need to educate myself a lil better here. Of course it’ll all be vibes in a few months, so maybe I’ll just play video games instead.
2 Likes
Of course even if you’re using Postgres it’s still quite likely that you will miss the race conditions during development unless you test aggressively. Which is why I am so against weak consistency guarantees. I actually have a pretty good idea of how databases work and yet I still constantly find myself confused as to whether something is safe in Postgres because so many of the “guarantees” are actually confusing implementation details about how locks are taken and such.
Strict-serializable is wonderful because you don’t have to know anything about the implementation to have a good mental model of what’s safe. Unfortunately the SQLite tradeoff is that there’s no concurrency control at all, but it’s still fast enough for plenty of stuff!
1 Like
