Guide on connecting via Ecto

Framework Integrations
1

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!

13 Likes
2

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.

2 Likes
3

Thx for this. I also ran into the same problem of figuring out how to get this to work with neon.

4

We added a new Elixir guide. Please let us know if you have any feedback.

Is anyone using Ecto Migrate to manage schema migrations between Neon database branches? If so, we would be interested in hearing about your setup. Thank you!

3 Likes
5

@mattste, @Daniel, thanks!

I finally got around to trying to connect to Neon, just from my local dev so far. In NixOS, I’ve set cacertfile to /etc/ssl/certs/ca-certificates.crt.

A couple of gotchas I found:

  • If a PGHOST environment variable is set, as it is in my project’s Devbox environment, that will be used over what’s in Ecto’s Repo config. So I ran mix ecto commands with:
    PGHOST="" mix ecto.migrations
    (Of course, the environment wouldn’t usually have PGHOST set to something other than Neon if it’s Neon that’s in use, but I just wanted to temporarily experiment.)
  • If Neon has suspended compute after a few minutes of inactivity, the connection for the mix ecto command results in ** (DBConnection.ConnectionError) connection not available and request was dropped from queue after 2987ms. Simply running the command again works for me.
6

Thank you for the feedback, David. We will update the guide with this information.

7

@Daniel I have a question.
If Ecto keeps a connection pool and query nothing with Neon, would this be treated as active time in Neon and incur costs? If so, are there any recommended methods to circumvent this? (for example, use dynamic repo)

8

I followed the instructions in the document, but I cannot connect to Neon.

Erlang: 26.0.2
Elixir: 1.15.4-otp-26

config :my_app, MyApp.Repo,
  url: <url>,
  ssl: true,
  ssl_opts: [
    server_name_indication: ~c"ep-billowing-sun-767748.us-west-2.aws.neon.tech",
    verify: :verify_none
  ]

$ mix ecto.setup

22:08:41.251 [notice] TLS :client: In state :hello received SERVER ALERT: Fatal - Access Denied


22:08:41.257 [error] Postgrex.Protocol (#PID<0.370.0>) failed to connect: ** (DBConnection.ConnectionError) ssl connect: TLS client: In state hello received SERVER ALERT: Fatal - Access Denied
 - {:tls_alert, {:access_denied, ~c"TLS client: In state hello received SERVER ALERT: Fatal - Access Denied\n"}}
9

Hi @reflow

Did you use your own Neon compute endpoint hostname? This is the hostname example used in the docs:

ep-billowing-sun-767748.us-west-2.aws.neon.tech

Your hostname will differ. You can find your hostname on the Neon Dashboard. This topic describes where to find it: Connect from any application - Neon Docs

If you did use your own hostname, let me know and I will take a closer look at our example.

Regarding your earlier question, I am not familiar with Ecto’s connection pool, but if you are not querying Neon, and you are on the Free Tier, your compute should transition to an idle state after 5 minutes of inactivity. You can monitor this on the Branches page in the console:

image
image1751×733 54.1 KB

1 Like
10

Thank you for your response.

Yes I use my own Neon compute endpoint hostname and it’s online. I can connect it with TablePlus, but not with Ecto.

I think Neon can be a top-of-mind database for Elixir users. I really want to use it.

11

Hi @reflow,

We’ve tested the instructions with the following Elixir/Erlang versions:

elixir --version

Erlang/OTP 26 [erts-14.1] [source] [64-bit] [smp:2:2] [ds:2:2:10] [async-threads:1] [jit:ns]
Elixir 1.15.6 (compiled with Erlang/OTP 26)

This is what our config.ex file looks like:

image
image1067×441 45.2 KB

We also added a repo, where you can view the configuration for the documented example:

Let us know how you make out.

1 Like
12

Oh… I missed to change server_name_indication to my host.
It works now. Thank you!