pgo.url_config doesn't work using an AWS (via Heroku) database url

[ejstembler]

  let assert Ok(database_url) = envoy.get("DATABASE_URL")
  let assert Ok(url_config) = pgo.url_config(database_url)

exception error: #{function => <<"main">>,line => 28,
                   message => <<"Assertion pattern match failed">>,
                   module => <<"pgo_test">>,
                   value => {error,connection_unavailable},
                   gleam_error => let_assert}
  in function  pgo_test:main/0 (~/Projects/pgo_test/build/dev/erlang/pgo_test/_gleam_artefacts/pgo_test.erl, line 45)% 

Here's a sanitized example:

postgres://user:password@ec2-nn-nnn-nnn-nn.compute-1.amazonaws.com:5432/databasename

[ejstembler]

I hard-coded the test from test/gleam/pgo_test.gleam#L8-L18. And it seems to be parsing the url fine. It must be a different issue...

[lpil]

Are you using ipv6? Are there any logs?

[ejstembler]

Are you using ipv6? Are there any logs?

I wasn't as far as I know, but I did try it.

let assert Ok(database_url) = envoy.get("DATABASE_URL")
let assert Ok(url_config) = pgo.url_config(database_url)
let config = pgo.Config(..url_config, ssl: True, ip_version: pgo.Ipv6, pool_size: 1)
let db = pgo.connect(config)
//...

It didn't seem to have any affect. I can see where the error is occuring in the generated erlang:

    _assert_subject@2 = gleam@pgo:execute(
        Sql,
        Db,
        [gleam_pgo_ffi:coerce(5)],
        Return_type
    ),
    {ok, Response} = case _assert_subject@2 of
        {ok, _} -> _assert_subject@2;
        _assert_fail@2 ->
            erlang:error(#{gleam_error => let_assert,
                        message => <<"Assertion pattern match failed"/utf8>>,
                        value => _assert_fail@2,
                        module => <<"pgo_test"/utf8>>,
                        function => <<"main"/utf8>>,
                        line => 46})

Does Gleam have a log file somewhere I can view?

[lpil]

You can enable compiler logging by setting GLEAM_LOG=trace but this is unrelated to compilation or even Gleam. This is some misconfiguration of PGO or your AWS infrastructure which preventing connection to the database.

Have you tested with a different client to verify that it is possible to connect to your database from your Heroku instance? For example: psql

[ejstembler]

Yes, I've tested in psql and Ruby.

dotenv -o -f ".env.heroku" psql $DATABASE_URL -c "SELECT id, author_id, slug, title FROM posts WHERE id = 5;"

require 'pg'

# Check if DATABASE_URL environment variable is set
unless ENV['DATABASE_URL']
  puts "Please set the DATABASE_URL environment variable."
  exit(1)
end

# Check if ARGV[0] is provided and is an integer
unless ARGV[0] && ARGV[0].match?(/^\d+$/)
  puts "Usage: ruby query_post.rb <post_id>"
  puts "Please provide a valid post ID as an integer."
  exit(1)
end

# Connect to the database
conn = PG.connect(ENV['DATABASE_URL'])

# Prepare SQL statement
sql = "SELECT id, author_id, slug, title FROM posts WHERE id = $1"

# Execute SQL statement
begin
  conn.prepare('get_post_by_id', sql)
  result = conn.exec_prepared('get_post_by_id', [ARGV[0]])

  # Print output data to stdout
  result.each do |row|
    puts "ID: #{row['id']}, Author ID: #{row['author_id']}, Slug: #{row['slug']}, Title: #{row['title']}"
  end
ensure
  conn.close if conn
end

[lpil]

Could you test it with pgo please? Thank you

[ejstembler]

Could you test it with pgo please? Thank you

It's been a while since I've written any erlang. I wasn't able to write a test in erlang with pgo. However, I was able to test it in a rebar3 shell:

$ rebar3 shell
1> application:ensure_all_started(pgo).
{ok,[backoff,opentelemetry_api,pg_types,pgo]}
2> application:ensure_all_started(ssl).
{ok,[]}
3> pgo:start_pool(default, #{pool_size => 5, host => "ec2-nn-nnn-nnn-nn.compute-1.amazonaws.com", database => "databasename", user => "user", password => "password"}).
{ok,<0.254.0>}
4> pgo:query("SELECT id, author_id, slug, title FROM posts WHERE id = 5").
{error,none_available}

Notes

• pgo doesn't have any functions to parse a DATABASE_URL, so I hard-coded the individual parts.
• I didn't know how to set query parameters, so I hard-coded it.
• I also tried ssl => true in pgo:start_pool; same {error,none_available} result though.

[lpil]

Thank you. Looks like this issue is within PGO rather than the bindings here so you'll need to take this over there. Sorry I couldn't be more helpful.

[ejstembler]

Thanks. I opened an issue on their end: https://github.com/erleans/pgo/issues/77

[ejstembler]

@lpil The erlang connection was figured out by @tsloughter in the other issue I opened up.

Any idea how to convert this erlang snippet to Gleam?

ssl_options => [{verify, verify_none}]

pgo.Config(..url_config, ssl: True, ip_version: pgo.Ipv6, pool_size: 1)

[tsloughter]

Looks like there is no ssl_options in https://github.com/lpil/pgo/blob/main/src/gleam/pgo.gleam#L16 so that'll need to be added.

[giovannibonetti]

Apparently this is being worked on at #22

[ghivert]

Author of #22 here.

Apart from setting the correct SSL parameters, I'd be curious on your use case to access the SSL options of pgo. Actually, all I needed was to set the correct CA certificates for SSL to work, and I'm rather happy with the result, meaning I'd prefer to not open the SSL options as-is, because it's not really gleamish (with lot of configurations, etc.). It would require more work from our side to have a configuration which makes sense, and disallow wrong configuration.

[tsloughter]

Thanks @giovannibonetti

@ghivert can you send that PR here instead?