Add support for Unix Path on Postgres databases for GCP Cloud SQL

[FrenchMajesty]

Why this feature is required (specific use-cases will be appreciated)?

At the moment, it is impossible to establish a connection to Cloud SQL instance on GCP without using socketPath. I have a backend API deployed on App Engine and a Postgres DB so this is an important feature needed to support. See the documentation.

The only means that Google Cloud provides for connecting a App Engine (standard) Node.js application to a Postgres DB is via Unix sockets.

Have you tried any other work arounds?

I've tried other connection protocols and they all fail. I've gone with the ridiculous work around of standing up an AWS db because going the route of a private IP adds a lot of DevOps overhead (from creating and managing network connections, to VPC, to registered IP ranges, ingress, etc...) and also adds a lot in terms of costs as well. Those VPC are not cheap at all.

Are you willing to work on it with little guidance?

Sure

[markgidman-rad]

Google has released the Cloud SQL Node.js connector which is now the preferred mechanism for connecting to Cloud SQL from NodeJS on App Engine and/or Cloud Run.

While the connector has been demonstrated to work with Knex, I don't think it can work with Lucid yet because the connector determines config options asynchronously at runtime and Lucid loads the database config synchronously. I've put in a feature request to support asynchronous database configs. https://github.com/adonisjs/core/discussions/4508

I'm hoping to avoid AWS as a workaround. I think rewriting everything in PHP might be preferable. :)

If you have any fresh insight on this, it would be appreciated.

[JannieT]

We are running an Adonis 6 app using "@adonisjs/lucid": "^20.1.0" fine on Google Cloud Run and connecting to Cloud SQL Postgres via a TCP/IP connection. Am happy to share specific settings if it will help someone.

[FrenchMajesty]

It turns the problem is not function since the config object is passed through to Knex.js, but rather a typing issue. This is what I ended up doing to get things working in my database.ts file:

    pg: {
      client: 'pg',
      connection: Env.get('PG_SOCKET_PATH')
        ? ({
            host: Env.get('PG_SOCKET_PATH'),
            user: Env.get('PG_USER'),
            password: Env.get('PG_PASSWORD', ''),
            database: Env.get('PG_DB_NAME'),
          } as any)
        : {
            host: Env.get('PG_HOST'),
            port: Env.get('PG_PORT'),
            user: Env.get('PG_USER'),
            password: Env.get('PG_PASSWORD', ''),
            database: Env.get('PG_DB_NAME'),
          },
    },

Ugly, but it works. Would be great if types supported a more native solution. Again, happy to help open a PR and would love some guidance on how to do it.

[thetutlage]

If I get it right, the port should be optional in this case?

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[JannieT]

What we did was to relax the environment variable validation in start/env.ts:

DB_HOST: Env.schema.string(),

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.