How to get the pooled connection url?

[lohrm-stabl]

Hi there,

I wonder how to get the pooled connection url from the project. I want to replicate something similar like the neon integration does in vercel (adding one env var for unpooled and one for the pooled connection.

Currently, I do it like this (typescript via CDKTF):

const project = new Project(....)           

const region = Fn.element(Fn.split(".", project.databaseHost), 1);
const hostSuffix = `${region}.aws.neon.tech`;
const hostPrefix = Fn.element(Fn.split(".", project.databaseHost), 0);

new TerraformOutput(this, `sbc-${PortalBranch}-db-url`, {
    value: `postgresql://${role.name}:${role.password}@${hostPrefix}.${hostSuffix}/${bessdbDb.name}?sslmode=require`,
    sensitive: true,
});

new TerraformOutput(this, `sbc-${PortalBranch}-db-url-pooled`, {
    value: `postgresql://${role.name}:${role.password}@${hostPrefix}-pooler.${hostSuffix}/${bessdbDb.name}?sslmode=require`,
    sensitive: true,
});

There needs to be a better way surely?

[kislerdm]

@lohrm-stabl Hello Michael! Please check this issue, it illustrates how to output connection URI.

[lohrm-stabl]

Ok, so you are confirming the use of outputs/local variables to build the connection string manually. This works, but maybe it should be added to the docs.

In your comment on the other issue you propose to just use project.database_host, however, this does not work with pooled urls, since there is no project.database_host_pooled (which would make this much simpler). In order to get the pooled URL, you have to split up the database_host and add the -pooler suffix to the first element of the string split by ..

It would be great (and improve the DX and code readability so much) if the provider would have the capability to compose the connection strings for you.

[kislerdm]

@lohrm-stabl Hey Michael, thank you for the feedback. I'll consider adding such functionality as connection string. However, strictly speaking, all attributes required to establish connection to the database are present in the output attributes of respective resources 😉 The objective of the tf provider does not include strings concatenation on behalf of the client. If your database adapter requires specific format of database connection attributes, it shall be adapted on the client's side.

[kislerdm]

@lohrm-stabl hey Michael, I feel like I misunderstood the issue you raised. I somehow thought it concerned the database resource, while the problem is in the endpoint 🙃.

I verified that indeed the output attribute host of the resource neon_endpoint is not consistent with the input attribute pooler_enabled.

It will be fixed in the next release - thank you for your support! 🙏

[lohrm-stabl]

Honestly, I don't think you misunderstood me. I never used the neon_endpoint resource as I could get the host directly from the project resource - but maybe this is a misunderstanding from my side.

I thought that they were basically replicas (which is not something we currently use). I guess every project already has a default endpoint, so I'm also not sure how I would import that one? By using the default branch? But even the endpoint gets me only one host URL - as I understood it there are always two (if pooling is enabled): The normal one and then the one to the pooler.

However, I'm glad you discovered something else related to this!

[kislerdm]

@lohrm-stabl thanks for understanding! You are right, the project is provisioned with the default endpoint. However, the tf provider does not support pooling for default endpoint yet.

Regardless, the misbehavior of the endpoint resource was confirmed thanks to your issue - thank you!

[lohrm-stabl]

I never activated pooling via the web console. I just clicked this to give me a pooled connection string:

So it looks to me like pooling works out of the box for every project. You just need the correct connection string

[kislerdm]

@lohrm-stabl it appears so, AFAIK, Neon use pgbouncer as a proxy to db instances by default. It's up to the user, whether to use it; dns will be configured for both URIs. So how would you propose to implement it on the provider side? Though, please assume that custom tf functions will not be supported yet - I want to maintain BW compatibility with old versions of terraform which did not support custom functions.