Switching to SQLite with Phoenix

Tagged: databases elixir

This morning I switched Galleyº over to SQLite from Postgres. This turned out to be a surprisingly easy thing to do! After some searching around, I found a gist written by a contributor on the phoenix core team that details most of the work one needs to do (and bonus - it goes over deployment options on fly.io, which I will consider using when it comes time to deploy.)

A bit of cleanup

I ran into a few small problems that were fairly easy to fix by making a few educated guesses. I learned that sqlite does not have an ilike functionality, and after just changing ilike -> like... it just... worked. So, I checked the hex docs and found the following:

like(string, search)

Searches for search in string.

from p in Post, where: like(p.body, "Chapter%")

Translates to the underlying SQL LIKE query, therefore its behaviour is dependent on the database. In particular, PostgreSQL will do a case-sensitive operation, while the majority of other databases will be case-insensitive. For performing a case-insensitive like in PostgreSQL, see ilike/2.

You should be very careful when allowing user sent data to be used as part of LIKE query, since they allow to perform LIKE-injections.

The above made me appreciate how much work has to go into building adapters for a database (let alone multiple databases). When I first tried phoenix some time in 2018 SQLite was not supported.

N.B: Looks like I'll have to read up on like injections in the meantime...

I learned that sqlite can't support distinct and so I moved back to using a group_by call. I also learned (and by learned I mean became vaguely aware) of something called "Extensions" in Postgres as the auth generator for phoenix adds the following line to the migration for adding user accounts:

 def change do
    execute "CREATE EXTENSION IF NOT EXISTS citext", ""

    create table(:users) do
      add :email, :citext, null: false
      add :hashed_password, :string, null: false


I learned that citext stands for case insensitive text. After removing that execute call (which does not work with SQLite) I was able to run all my migrations properly. Looks like another case of differences between databases (I haven't actually tested that the :citext datatype works out of the box yet). We'll see!

Why SQLite?

I really don't need Postgres for this application. I have no intention of scaling this application. I'll be happy when it's deployed and working for myself and a few friends. Galley will be a closed application, invite only, and I currently have no intentions of extending its use except to friends and friends of friends (if they should find it useful).

It's satisfying to replace a sledgehammer with a more precise tool. I'm not a database expert by any means - even just having the ability to cp my entire production database and not having to learn database backups, etc. makes me happy (that's not the intent of this project, after all).

Aside, SQLite is making quite a few rounds on hackernews these days too, and I'm keen to learn more about litestream... but I don't think I even need to think about implementing something like that yet. The joys of side-projects!

Thanks for reading,