DEV Community

Masatoshi Nishiguchi
Masatoshi Nishiguchi

Posted on

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,, 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
Enter fullscreen mode Exit fullscreen mode

Get started

Generate a new Phoenix application with mix

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 my_app --database sqlite3
Enter fullscreen mode Exit fullscreen mode

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},
      # ...
Enter fullscreen mode Exit fullscreen mode

Tweak Ecto.Repo settings

# lib/my_app/repo.ex

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

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
Enter fullscreen mode Exit fullscreen mode

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 my_app --database postgres and mix 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 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.

Top comments (0)