Phoenix + SQLite Deployment tips

phx_sqlite_fly_launch.md

Deploying to Fly.io with SQLite

Deploying a Phoenix app to Fly.io is a breeze...is what everyone kept telling me. In fairness, I imagine the process would have been breezier had I just used postgres, but all the sqlite and litestream talk has been far too intriguing to ignore. "Wait", you say. "It is just a flat file. How much harder can it be?"

It is easy to make something harder than it should be. It is hard to take something complex and make it truly simple. flyctl launch does an amazing job at providing a simple interface to the utterly complex task of generating deployment resources, especially now that we are living in a containerd (erm, firecracker) world.

This gist is for anyone who, like me, thinks they know better than to read all of the documentation and therefore necessarily spends a long time trying to make something work that everyone claimed was simple. Therein lies the rub: it is simple once you know how it works. So without further adieu, please enjoy all the things I discovered when converting from postgres to sqlite and deploying to Fly.io.

Cheating Before We Start

Earlier I made a remark about reading all of the docs. However if you follow the Phoenix Framework v1.6.6 "Deploying to Fly" guide faithfully, you will have to do more work than necessary. In the future, you will know you have the updated guide when it is titled, "Deploying to Fly.io". In the meantime, this is the way:

$ fly launch

Say no when it asks about postgres. Say no again when it asks if you want to deploy.

Failure to Launch

This title is misleading. I said yes to deploy. Deploy was the problem. Ultimately "Failure to Launch" is funnier than "Failure to Deploy" though so it stays.

The fly launch command generates a Dockerfile, a fly.toml configuration and some release files into your Phoenix app. It will even set SECRET_KEY_BASE for you. It is almost perfect.

Problem 1: DATABASE_PATH

The output showed something like the following:

	 Preparing to run: `/app/bin/migrate` as nobody

	 ERROR! Config provider Config.Reader failed with:
	 ** (RuntimeError) environment variable DATABASE_PATH is missing.
	 For example: /etc/lighter/lighter.db

Error release command failed, deployment aborted

This makes sense. I suspect that DATABASE_URL would have been set for me if I had chosen Postgres. But alas, I trudged on towards fly.toml:

# fly.toml

[env]
+ DATABASE_PATH = ???????
  PHX_HOST = "spicy-burrito-2702.fly.dev"
  PORT = "8080"

Question: What should we use for DATABASE_PATH?

Certainly there are rules that I must follow for this to work correctly. One of my motivations for trying SQLite was, funnily enough, Fly.io's announcement of Free Postgres Databases. Slyly they mention they're really just giving away 3GB Volumes, so I created one:

$ fly volumes create myapp_data --size 1

With my new volume in hand, I consulted the Using Volumes section of the docs and thus added the following to my fly.toml:

# fly.toml

+[mounts]
+ source = "myapp_data"
+ destination = "/data"

[env]
+ DATABASE_PATH = /data/my_app_prod.db
  PHX_HOST = "spicy-burrito-2702.fly.dev"
  PORT = "8080"

After running fly deploy I saw several errors related to Exqlite:

failed to connect: ** (Exqlite.Error) got :eacces while retrieving Exception.message/1 for %Exqlite.Error{message: :eacces, statement: nil} (expected a string)

Fatality: I have messed up so badly that my errors have errors.

The difference between the correct configuation and the one above is so subtle as to be almost imperceptible, but it became clearer when I read one extra line of documentation (emphasis mine):

This would make myapp_data appear under the /data directory of the application.

In other words, within the [mounts] configuration source is the name of the volume and the name of a directory. The directory will be mounted under the destination path.

The following represents the proper changes for DATABASE_PATH:

# fly.toml

+[mounts]
+ source = "myapp_data"
+ destination = "/data"

[env]
+ DATABASE_PATH = "/data/myapp_data/my_app_prod.db"
  PHX_HOST = "spicy-burrito-2702.fly.dev"
  PORT = "8080"

But wait, there's more!

Problem 2: release_command

Another round of fly deploy and another set of errors, but I did appear to make some progress:

==> Release command detected: /app/bin/migrate

--> This release will not be available until the release command succeeds.
	 Starting instance
	 Configuring virtual machine
	 Pulling container image
	 Unpacking image
	 Preparing kernel init

	 21:31:00.825 [error] Exqlite.Connection (#PID<0.128.0>) failed to connect: ** (Exqlite.Error) got :enoent while retrieving Exception.message/1 for %Exqlite.Error{message: :enoent, statement: nil} (expected a string)
	 21:31:00.825 [error] Exqlite.Connection (#PID<0.127.0>) failed to connect: ** (Exqlite.Error) got :enoent while retrieving Exception.message/1 for %Exqlite.Error{message: :enoent, statement: nil} (expected a string)
	 21:31:03.037 [error] Exqlite.Connection (#PID<0.127.0>) failed to connect: ** (Exqlite.Error) got :enoent while retrieving Exception.message/1 for %Exqlite.Error{message: :enoent, statement: nil} (expected a string)

For better or worse, I was not alone in my troubles. I found someone else for whom Migration in sqlite3 on volume fails (community.fly.io).

Now the problem is stated plainly:

The release_command won’t work with sqlite. — @mrkurt

...so I remove the offending command:

# fly.toml

-[deploy]
-  release_command = "/app/bin/migrate"

Now, I can see the future:

$ fly deploy

...omitted...

--> release v1 created

--> You can detach the terminal anytime without stopping the deployment
==> Monitoring deployment

 1 desired, 1 placed, 1 healthy, 0 unhealthy [health checks: 1 total, 1 passing]
--> v1 deployed successfully

So how to run migrations? I invoke the release migrate function from Application.start/2 on the sage advice of @chrismccord:

# lib/my_app/application.ex

  def start(_type, _args) do
    # Run migrations
    MyApp.Release.migrate()

    children = [
      #children...
    ]

    #Supervisor...
  end

This is all. It is simple, and delightfully so. You just have to know how it works, and then you can see what all the fuss is about :) Thanks for reading!

BONUS: Converting to SQLite

Note this bonus section is about converting an existing app from using :postgrex to using :ecto_sqlite3.

If you want to generate a new app using SQLite, run the following:

$ mix phx.new my_app --database sqlite3

The switch to SQLite gave me the least amount of trouble, probably because I am experienced with Phoenix Framework. :)

These instructions should simplify making the switch. If anything can be more clear, leave a comment!

Add *.db files to your .gitignore:

# .gitignore

# Database files
*.db
*.db-*

Add :ecto_sqlite3 to your mix deps. You can also remove :postgrex if you are not going to use it:

# mix.exs

def deps do
  [
    {:ecto_sqlite3, ">= 0.0.0"},
    # deps...
   ]
end

Update Repo configuration for dev:

#config/dev.exs

config :my_app, MyApp.Repo,
  database: Path.expand("../my_app_dev.db", Path.dirname(__ENV__.file)),
  pool_size: 5,
  show_sensitive_data_on_connection_error: true

...and for test:

# config/test.exs

config, :my_app, MyApp.Repo,
  database: Path.expand("../my_app_test.db", Path.dirname(__ENV__.file)),
  pool_size: 5,
  pool: Ecto.Adapters.SQL.Sandbox

Then, update the runtime configuration for prod. Replace database_url with database_path similar to the following:

# config/runtime.exs

if config_env() == :prod do
  database_path =
    System.get_env("DATABASE_PATH") ||
      raise """
      environment variable DATABASE_PATH is missing.
      For example: /etc/my_app/my_app.db
      """

  config :my_app, MyApp.Repo,
    database: database_path,
    pool_size: String.to_integer(System.get_env("POOL_SIZE") || "5")

Update the adapter in your Repo module (usually at lib/my_app/repo.ex):

# lib/my_app/repo.ex

defmodule MyApp.Repo do
  use Ecto.Repo,
    otp_app: :my_app,
    adapter: Ecto.Adapters.SQLite3
end

Hi! Just wanted to say thanks for this - it was really helpful.

Hi! Just wanted to say thanks for this - it was really helpful.

Np 😉 , forked it.

oh, ha! Didn't notice that - I better forward my thanks to the original author too.