Guide on connecting via Ecto

After a nightmare day of debugging how to connect to Neon from my Elixir Phoenix app, I finally have a working set-up with Ecto.

# runtime.exs

database_url =
    System.get_env("DATABASE_URL") ||
      raise """
      environment variable DATABASE_URL is missing.
      For example: ecto://USER:PASS@HOST/DATABASE
      """

  %URI{host: database_host} = URI.parse(database_url)

  # Location of root certificates to verify database SSL connection.
  # For example: /opt/homebrew/etc/openssl@3/cert.pem
  database_ca_cert_filepath = System.get_env("DATABASE_CA_CERT_FILEPATH") || "/etc/ssl/certs/ca-certificates.crt"

  maybe_ipv6 = if System.get_env("ECTO_IPV6"), do: [:inet6], else: []

  config :overwatch, Overwatch.Repo,
    url: database_url,
    # Our production Neon database requires SSL to be enabled to connect. This enables verifying the Postgres server has a valid certificate.
    ssl: true,
    ssl_opts: [
      verify: :verify_peer,
      cacertfile: database_ca_cert_filepath,
      # see https://pspdfkit.com/blog/2022/using-ssl-postgresql-connections-elixir/
      server_name_indication: to_charlist(database_host),
      customize_hostname_check: [
        # Our hosting provider uses a wildcard certificate. By default, Erlang does not support wildcard certificates. This function supports validating wildcard hosts
        match_fun: :public_key.pkix_verify_hostname_match_fun(:https)
      ]
    ],
    pool_size: String.to_integer(System.get_env("POOL_SIZE") || "10"),
    socket_options: maybe_ipv6

Hopefully this can be added to the docs to help people in the future!

8 Likes

When I was running this in fly.io one thing that I needed to debug for too long time was that the ipv6 socket_options didn’t work with Neon and fly.io enables the ECTO_IPV6=true environmental variable by default in the Dockerfile.

Removing this env fixed my problem.

1 Like