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
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
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-*
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.
Top comments (1)
ありがとう
Thank you. That is helpful