## Deploying to Fly.io with SQLite Deploying a Phoenix app to [Fly.io](https://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](https://fly.io/blog/free-postgres/#so-we-re-really-giving-you-free-volumes) and [litestream](https://github.com/benbjohnson/litestream/issues/323) [talk](https://twitter.com/mrkurt/status/1494380016238481415) 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: ```sh $ 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: ```sh 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`: ```diff # 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](https://fly.io/blog/free-postgres/). Slyly they mention they're really just giving away [3GB Volumes](https://fly.io/docs/reference/volumes/), so I created one: ```sh $ fly volumes create myapp_data --size 1 ``` With my new volume in hand, I consulted the [Using Volumes](https://fly.io/docs/reference/volumes/#using-volumes) section of the docs and thus added the following to my `fly.toml`: ```diff # 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`: ```sh 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](https://fly.io/docs/reference/volumes/#using-volumes) (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`: ```diff # 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: ```sh ==> 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)](https://community.fly.io/t/migration-in-sqlite3-on-volume-fails/3818). Now the problem is stated plainly: > The release_command won’t work with sqlite. — @mrkurt ...so I remove the offending command: ```diff # fly.toml -[deploy] - release_command = "/app/bin/migrate" ``` Now, I can see the future: ```sh $ 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: ```elixir # 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: ```sh $ 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](https://github.com/sponsors/mcrumm). :) These instructions should simplify making the switch. If anything can be more clear, leave a comment! Add `*.db` files to your `.gitignore`: ```.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: ```elixir # mix.exs def deps do [ {:ecto_sqlite3, ">= 0.0.0"}, # deps... ] end ``` Update `Repo` configuration for dev: ```elixir #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: ```elixir # 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: ```elixir # 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`): ```elixir # lib/my_app/repo.ex defmodule MyApp.Repo do use Ecto.Repo, otp_app: :my_app, adapter: Ecto.Adapters.SQLite3 end ```