# 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`].

[SQLite]: https://www.sqlite.org/index.html
[Gigalixir]: https://www.gigalixir.com/
[Fly.io]: https://fly.io/
[PostgreSQL]: https://www.postgresql.org/
[`MIX_ENV`]: https://hexdocs.pm/mix/1.12/Mix.html#module-environments

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

```elixir
# mix.exs

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

[`ecto_sqlite3`]: https://hexdocs.pm/ecto_sqlite3/Ecto.Adapters.SQLite3.html
[`postgrex`]: https://hexdocs.pm/postgrex/readme.html

### Tweak `Ecto.Repo` settings

```elixir
# 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
```

[`Ecto.Repo`]: https://hexdocs.pm/ecto/Ecto.Repo.html

### 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`

```bash
# .gitignore

# ...

# Database files
*.db
*.db-*
```

### Differences in configurations

This Japanese article [Phoenix1.6でDBにSQLite3を指定した際のコード差異を調べてみた by MzRyuKa](https://qiita.com/MzRyuKa/items/8d28b4e59df4dcbe30c0) 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.