Switch between SQLite and PostgreSQL based on MIX_ENV in Elixir Phoenix
Published: 2021-10-17 16:08:10

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

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

[mix phx.new]: https://hexdocs.pm/phoenix/Mix.Tasks.Phx.New.html#module-options

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

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