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

Thanks for sharing this.

Thank you very much.

Thanks for sharing this 👍

This was super useful - thanks for writing this up!

Super helpful, thanks! Especially

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

This is exactly what I was looking for in the world. Thank you

Thank you for saving me the time of figuring this out on my own. <3

This is great!

Thanks, was struggling for a few hours on this topic.

Exactly what I was looking for, starred

Thank you so much!

Did you run into anything concerning (Exqlite.Error) attempt to write a readonly database?

I tried to copy a seeded database file using fly sftp shell as per the Fly.io doc page that was inspired by this gist of yours.

@carlgleisner Have you checked the filesystem permissions on the remote db file(s)? The file transfer may have retained some local settings.

@carlgleisner Have you checked the filesystem permissions on the remote db file(s)? The file transfer may have retained some local settings.

I had to change the permissions of the folder to 770 ✅

Many thanks for your response!

Did you try using to distribute SQLite using LiteFS?

My attempt below. Lots of moving parts, so plenty of reasons to fail. It works, more or less: if I use a VPN to relocate in some other node, and start a connection, then the database is not initialised. Only when I click the database is updated. When I run this on my local host (on different ports), it works, so I am not sure about the settings if seomthing is proxied or not.

# stop when you have to deploy
fly launch
# remove release_command from fly.toml 
fly consul attach
fly deploy

#env.sh.eex
ip=$(grep fly-local-6pn /etc/hosts | cut -f 1)
export RELEASE_DISTRIBUTION="name"
export RELEASE_NODE=$FLY_APP_NAME@$ip

#Dockerfile - Debian based.
{RUNNER}
RUN apt-get install ca-certificates fuse3...
COPY --from=flyio/litefs:0.5 /usr/local/bin/litefs /usr/local/bin/litefs
COPY litefs.yml /etc/litefs.yml
COPY --from=builder --chown=nobody:root /app/_build/${MIX_ENV}/rel/liveview_counter ./
# USER nobody
ENV ECTO_IPV6 true
ENV ERL_AFLAGS "-proto_dist inet6_tcp"
ENTRYPOINT litefs mount

def start(_type, _args) do
    MyApp.Release.migrate()
    children = [ {DNSCluster, query: System.get_env("DNS_CLUSTER_QUERY") || :ignore},...]

#config/runtime.exs
#  config :my_app, dns_cluster_query: System.get_env("DNS_CLUSTER_QUERY")

#litejs.yml
fuse:
  dir: "/mnt/mydata"

data:
  dir: "/mnt/litefs"

proxy:
  addr: ":8081"
  target: "localhost:8080"
  db: "my_app_prod.db"
  passthrough: 
    - "*.ico"
    - "*.png"

exec:
  - cmd: "/app/bin/server -addr :8080 -dsn /mnt/mydata" 

lease:
  type: "consul"
  advertise-url: "http://${HOSTNAME}.vm.${FLY_APP_NAME}.internal:20202"
  candidate: ${FLY_REGION == PRIMARY_REGION}
  promote: true

  consul:
    url: "${FLY_CONSUL_URL}"
    key: "litefs/${FLY_APP_NAME}"

#fly.toml
primary_region="cdg"
#[deploy] release_command = "/app/bin/migrate"

[mounts]
source="mydata"
destination="/mnt"
process=["app"]

[env]
PHX_HOST = "my-app.fly.dev"
DNS_CLUSTER_QUERY="my-app.internal"
DATABASE_PATH="/mnt/mydata/my_app_prod.db"
PORT="8080"

Thanks! @ndrean hope you write more about Litefs

Well, thanks @bartonhammond , but it has been a horrible experience, really, only trial n error process. Not even sure if this really works as it doesn't seem to pick-up the db when I connect to a replica node via a VPN.