Switch between SQLite and PostgreSQL based on MIX_ENV in Elixir Phoenix

I like the simplicity of SQLite for my local development; however when I deploy my app to a cloud platform like Gigalixir, Fly.io, etc, often PostgreSQL is preferred. I was wondering if I can switch between them based on MIX_ENV.

Elixir version etc

elixir          1.12.3-otp-24
erlang          24.1.1
phoenix         1.6.2

Get started

Generate a new Phoenix application with mix phx.new

We can specify what datbase we want to use when we generate a new Phoenix application. If we plan to use ecto_sqlite3 for local development, it would be easier to start by generating an application with this option --database sqlite3.

mix phx.new my_app --database sqlite3

Add both `ecto_sqlite3` and `postgrex` to our project dependencies

# mix.exs

  defp deps do
    [
      {:phoenix, "~> 1.6.2"},
      # ...
      {:ecto_sqlite3, ">= 0.0.0", only: [:dev, :test]},
      {:postgrex, ">= 0.0.0", only: :prod},
      # ...

Tweak `Ecto.Repo` settings

# lib/my_app/repo.ex

defmodule MyAPp.Repo do
  use Ecto.Repo,
    otp_app: :my_app,
    adapter:
      if(Mix.env() in [:dev, :test],
        do: Ecto.Adapters.SQLite3,
        else: Ecto.Adapters.Postgres
      )
end

Exclude db files from version control

ecto_sqlite3 generates database files within the project directory. We want to make sure those files are included in .gitignore

# .gitignore

# ...

# Database files
*.db
*.db-*

Configure database

We need to make sure that database configuration is done appropriately for each environment. Check these files:

config/dev.exs (configure for ecto_sqlite3)
config/test.exs (configure for ecto_sqlite3)
config/runtime.exs (configure for postgrex)

If our application is generated with the --database sqlite3 option, the database should already be configured for ecto_sqlite3 in development and test.

This Japanese article Phoenix1.6でDBにSQLite3を指定した際のコード差異を調べてみた by MzRyuKa compares two Phoenix-generated apps between mix phx.new my_app --database postgres and mix phx.new my_app --database sqlite3.

It is interesting that what is DATABASE_URL for postgrex is DATABASE_PATH for ecto_sqlite3, which is good to be aware of.

Wrap up

That's about it. It was quite easy to set up. Now we can use ecto_sqlite3 in development and test; postgrex in production. So far it is working well for me. I was able to deploy to Fly.io successfully.

I do not think this is good for "proper" applications where we want to fully utilize PostgreSQL functionalities, but I have a feeling most toy applications won't have any issue.