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

Phoenix Forum Questions / Help
, , , ,
1

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

2

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"}
3

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.

4

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: Deploy a Phoenix application to Google Cloud Run and Cloud SQL · stibbard.io

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.11.1 – 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
5

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: Automated deployments of a Phoenix application to Google Cloud Run and Cloud SQL · stibbard.io