Switch between SQLite and PostgreSQL based on MIX

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

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-*

Differences in configurations

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. 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.

Switch between SQLite and PostgreSQL based on MIX_ENV in Elixir Phoenix