[FIXED] How can I connect to a Cloud PostgreSQL database from dart code?

Issue

I have a PostgreSQL database deployed in Google Cloud that I am trying to connect to from a Cloud Run instance. I have tried the following two packages, both of them eventually leading to the same exception:

• https://pub.dev/packages/postgres
• https://pub.dev/packages/database_adapter_postgre

The exception I am getting is: SocketException: Failed host lookup: '/cloudsql/{INSTANCE_CONNECTION_NAME}' (OS Error: Name or service not known, errno = -2)

I get here both times when trying to establish the connection, so in the case of the first package:

connection = new PostgreSQLConnection(
        '/cloudsql/{INSTANCE_CONNECTION_NAME}',
        5432,
        'postgres',
        username: 'username',
        password: 'password');
await connection.open(); // <-- exception thrown here

I have tried changing the host string to /cloudsql/INSTANCE_CONNECTION_NAME}/.s.PGSQL.5432, but that did not work. My first thought were permissions, the service account the Cloud Run instance is using (xxx-compute@developer.gserviceaccount.com) has the Cloud SQL Editor role (tried Client and Admin too).

Running the same database code locally from a dart console app, I can connect to my database via its public IP address as the host with both packages, so the database itself is up and running.

Can someone point me in the right direction with this exception/have an example code for any of the packages above to show how to connect it to a Cloud SQL instance from a Cloud Run?

Edit: I tried setting up a proxy locally to test out if the connection is wrong like so:

.\cloud_sql_proxy.exe -instances={INSTANCE_CONNECTION_NAME}=tcp:5433 psql

Then changing the connection host value in the code to localhost, and the port to 5433. To my surprise it works - so from locally I am seemingly able to connect to the DB using that connection string. It still doesn't work when I use it from a Cloud Run instance though. Any help is appreciated!

Solution

It seems dart doesn't support connection through unix socket, you need to configure a IP (public or private, as you need).

Alternatively you can use pg which support unix socket connection

Hope this helps.

Answered By - Daniele Ricci

Answer Checked By - David Goodson (PHPFixing Volunteer)