Preparing the production environment for deployment in GCP with Cloud SQL database

xgeek116

Hello,

Elixir version = 1.13.1
Phoenix version = 1.6.6

I created a REST API in Elixir/Phoenix and everything works fine with the PostgreSQL database (hosted in Google Cloud SQL), but I have some issues when configuring the deployment env, I was following this tutorial : https://shyr.io/blog/deploy-phoenix-google-app-engine

But in my project structure I didn’t find the file config/prod.secret.exs, so I created one and compiled and now I’m having error about missing : DATABASE_URL variable

In the config/dev.exs file I was configuring the database like this :

config :test_api, TestApi.Repo,
  socket_dir: "/cloudsql/CONNEXTION_NAME_CLOUD_SQL",
  username: "testUser",
  password: "testPassword",
  database: "testDB",
  pool_size: 10

But in config/runtime.exs the format required is :
For example: ecto://USER:PASS@HOST/DATABASE

How can I inject the socket dir (cloudsql) inside ?

Best regards

4 comments

/phoenix #ecto #databases #production #deployment

0 2406 4

2024-02-02 09:05:35 UTC

Showing Posts 1 to 4

Show Best Posts
Show All (oldest first)
Show All (newest first)

xgeek116

Hello,

I tried to set the DATABASE_URL env variable like :
DATABASE_URL: “ecto://user:password@/database?host=/cloudsql/DATABASE_CONNECTION_NAME”

But I get the following error :

    ** (EXIT) an exception was raised:
        ** (Ecto.InvalidURLError) invalid url ecto://user:password@/database?host=/cloudsql/DATABASE_CONNECTION_NAME, host is not present. The parsed URL is: %URI{authority: "user:password@", fragment: nil, host: nil, path: "/database", port: nil, query: "host=/cloudsql/DATABASE_CONNECTION_NAME", scheme: "ecto", userinfo: "user:password"}

Post #1

benwilson512

Author of Craft GraphQL APIs in Elixir with Absinthe

This is the format that is provided by default in runtime.exs, but this is not required. You can pass all of the same options that are passed to config/dev.exs, you probably just want to substitute the username and password options with System.get_env calls.

Post #2

mstibbard

I trust you’ve solved this issue by now (or given up!!).

For anyone else that finds this, I’ve written up the complete steps to deploy to Google Cloud Run + Cloud SQL here: https://www.stibbard.io/elixir-phoenix-deploy-google-cloud-run-and-cloud-sql

The answer to your specific question:

What was previously contained in config/prod.secret.exs was moved into config/runtime.exs
The default Ecto connection in a fresh mix phx.new project_name is in the form of ecto://USER:PASS@HOST/DATABASE however Cloud SQL expects a socket connection, not a domain (e.g., HOST) so you need to update your config/runtime.exs. Refer to Ecto.Adapters.Postgres — Ecto SQL v3.14.0 – the one you want is :socket. An example updated config could be like below, which includes moving the values into environment variables/secrets.

# do this for each secret. e.g., socket, db, user, pass
database_password =
  System.get_env("DATABASE_PASSWORD") ||
    raise """
    environment variable DATABASE_PASSWORD is missing.
    """

config :insight, Insight.Repo,
  socket: database_socket,
  database: database_name,
  username: database_user,
  password: database_password,
  pool_size: String.to_integer(System.get_env("POOL_SIZE") || "10"),
  socket_options: maybe_ipv6

In your deployment you will need to populate those environment variables. In my writeup I did this in Google Secrets Manager
You will also need to sort IAM permissions to allow your server to talk to Cloud SQL, and this will vary depending on what service you deployed your app to AND what Service Account you used. In my writeup I created a new Service Account, and we deployed to Google Cloud Run

Post #3

mstibbard

I can’t update my post above but there’s an even easier way – using postgres environment variables (e.g., PGHOST, PGUSER, etc) which is what :postgrex defaults to when your code does not specify database connection details. I’ve updated my write-up accordingly: https://www.stibbard.io/elixir-phoenix-deploy-google-cloud-run-and-cloud-sql

Post #4

— All posts loaded —