Nestjs agent with google sql connection

Help me!
1

Hello,

I am using the nest js agent provided by the package @forestadmin/agent by mounting it on the application :

  createAgent({
    authSecret: process.env.FOREST_AUTH_SECRET,
    agentUrl: process.env.FOREST_AGENT_URL,
    envSecret: process.env.FOREST_ENV_SECRET,
    isProduction: process.env.NODE_ENV === "production",
  })
    .addDataSource(createSqlDataSource(process.env.DATABASE_URL))
    .mountOnNestJs(app)
    .start();

The function createSqlDataSource requires a connection string, however we are google app engine with cloud sql for our infrastructure, and the correct way of connecting to the database from the app engine instance is with a socket - the connection does not seem to work with the public ip of the database instance. We try with no success with the following url :
postgres://<user>:<password>@/<databsename>?unix_socket=/cloudsql/<connexionName>
Any hints on how to solve this ?

2

Hello @Augustin_Barbe,

You are using a DB hosted on a Cloud SQL instance, am I right?

I haven’t heard about connection strings using sockets. I will try to find a solution with the team.

In the meanwhile basic connection string should look like this: (if it’s a Postgres one)

postgres://<user>:<password>@<host>:<port>/<DATABASE_NAME>

Looking at the Cloud SQL documentation you have several connection options.

We will investigate with the team to give you a better answer. :pray:

Kind regards,
Morgan

3

Thanks for the quick reply !
Indeed it’s a db hosted on cloud sql,
I referred to this method of connection : Connect from App Engine standard environment  |  Cloud SQL for PostgreSQL  |  Google Cloud

For the regular connection string, (with the public ip of the instance provided by google ), I get a connection refused (I suspect accessing to the db instance within a app engine instance is not possible through the public ip adress, but I am not entirely sure as my knowledge is limited :slight_smile:

Otherwise, don’t know if it’s the right place to put this, but massive kudos for releasing the agent for nestjs (and the overall Forest product) !!

2 Likes
4

Hey @Augustin_Barbe :wave:

Sorry for the super delayed response, GCloud was a bit on the slow side :slight_smile:

I was able to make it work using the following steps:

  • Allow my personnal network as an allowed IP on GCP (On console cloud, SQL > Select your cloud SQL instance > Connections > Networking > Add Network, then simply paste your current IP)
  • Once that was done, I set my connection string as postgres://<user(most likely "postgres")>:<password (The one entered during the instance creation)>@<host (The public IP address of the instance)>/<database name (visible in the database section of gcp)>?sslmode=disable

(Please note the sslmode disabled. It should be possible to connect via a secure connection)

Once all these were done, I was able to connect to my Cloud SQL instance.

Let me know if that helps :slight_smile:

1 Like
5

Thank you for the reply @jeffladiray !

Your solution works indeed if I run the application locally on my computer. However the aim here is to connect to the instance via App engine, hence it’s not possible to use the public IP address of the sql instance.
Before the nestjs agent, I used a “framework agnostic” method, and after digging a bit, I had to modify the generated code to make it work on an app engine environment ( I followed this method described here : node.js - Defining Sequelize on google cloud sql nodejs - Stack Overflow )
I digged a bit into createSqlDataSource function and figured it was also relying on Sequelize. I’ll try monkeypatch it to make it work on socket connections, but I was hoping to have a cleaner solution

6

Your solution works indeed if I run the application locally on my computer

Ooh, maybe I did not fully understand the issue here then :slight_smile:
You want to run your development agent in a GCP environment? If that’s the case, you may want to use a private cloud network, and allow it to both your app & your cloud SQL instance.
GCP would then give you a private network URL to connect your app to your cloud SQL instance with a simple connection string (It seems to be detailed in this section) without the need of a socket file to open the connection.

8

I managed to set up a private network access to connect through private ip :slight_smile:
It would be nice to be able to connect through socket in the future. I also noticed a small bug, but I don’t know if you have a repo to submit a PR !
Thanks for the help

2 Likes
9

Hey @Augustin_Barbe :wave:

The repository for this new agent is located here (And the beta branch should be the default one)

Don’t hesitate to open a PR or share the issue here :+1:

1 Like