DEV Community

Masatoshi Nishiguchi
Masatoshi Nishiguchi

Posted on

6 1

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

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
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,
    adapter:
      if(Mix.env() in [:dev, :test],
        do: Ecto.Adapters.SQLite3,
        else: Ecto.Adapters.Postgres
      )
end
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
*.db
*.db-*
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 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.

Heroku

This site is built on Heroku

Join the ranks of developers at Salesforce, Airbase, DEV, and more who deploy their mission critical applications on Heroku. Sign up today and launch your first app!

Get Started

Top comments (1)

Collapse
 
gevera profile image
Denis Donici

ありがとう

Thank you. That is helpful

A Workflow Copilot. Tailored to You.

Pieces.app image

Our desktop app, with its intelligent copilot, streamlines coding by generating snippets, extracting code from screenshots, and accelerating problem-solving.

Read the docs