Flask SQLalchemy can't connect to Google Cloud Postgresql database with Unix socket

Question

I am using Flask SQLalchemy in my google app engine standard environment project to try and connect to my GCP Postgresql database.. According to google docs, the url can be created in this format

# postgres+pg8000://<db_user>:<db_pass>@/<db_name>?unix_socket=/cloudsql/<cloud_sql_instance_name>

and below is my code

from flask import Flask, request, jsonify
import constants

app = Flask(__name__)

# Database configuration from GCP postgres+pg8000
DB_URL = 'postgres+pg8000://{user}:{pw}@/{db}?unix_socket=/cloudsql/{instance_name}'.format(user=user,pw=password,db=dbname, instance_name=instance_name)

app.config['SQLALCHEMY_DATABASE_URI'] = DB_URL
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False  # silence the 
deprecation warning
sqldb = SQLAlchemy(app)

This is the error i keep getting:

File "/env/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 412, in connect return self.dbapi.connect(*cargs, **cparams) TypeError: connect() got an unexpected keyword argument 'unix_socket'

kurtisvg · Answer 1 · 2019-10-16T20:06:32.703

7

The argument to specify a unix socket varies depending on what driver you use. According to the pg8000 docs, you need to use unix_sock instead of unix_socket.

To see this in the context of an application, you can take a look at this sample application.

edited Oct 16 '19 at 20:06

answered Dec 14 '18 at 17:00

kurtisvg

3,412
1
8
24

1

Thanks @kurtisvg, I tried changing to unix_sock but i started getting this error instead line 1184, in __init__ raise InterfaceError("communication error", e) sqlalchemy.exc.InterfaceError: (pg8000.core.InterfaceError) ('communication error', ConnectionRefusedError(111, 'Connection refused')) (Background on this error at: http://sqlalche.me/e/rvf5) – Isaac Dec 16 '18 at 05:49
3

I was able to fix it by adding /cloudsql/INSTANCE_CONNECTION_NAME/.s.PGSQL.5432 to the url – Isaac Dec 16 '18 at 08:30

score 2 · Answer 2 · answered Jun 24 '20 at 20:25

2

It's been more than 1.5 years and no one has posted the solution yet :)

Anyway, just use the below URI

postgres+psycopg2://<db_user>:<db_pass>@<public_ip>/<db_name>?host=/cloudsql/<cloud_sql_instance_name>

And yes, don't forget to add your systems public IP address to the authorized network.

answered Jun 24 '20 at 20:25

Debdut Goswami

1,301
12
28

1

using flask_sqlalchemy version 2.4.1 i wound up getting an error - "Can't load plugin: sqlalchemy.dialects:postgres.psycopg2" with that code. change "postgres+psycopg2" to "postgresql" – buzz11 Apr 19 '21 at 22:50
@buzz11 https://stackoverflow.com/questions/33794074/nosuchmoduleerror-cant-load-plugin-sqlalchemy-dialectsredshift-psycopg2/37281686 this should solve your problem. – Debdut Goswami Apr 20 '21 at 06:01
Connecting via cloud sql proxy does not require adding any authorized networks, only a public IP. There's even an org policy that disallows adding authorized networks, `sql.restrictAuthorizedNetworks`, so that the public IP can be used _only_ with the proxy. – Michał Łazowik Mar 14 '22 at 01:04

score 0 · Answer 3 · edited Aug 06 '21 at 15:16

Example of docs

As you can read in the gcloud guides, an examplary connection string is

postgres+pg8000://<db_user>:<db_pass>@/<db_name>?unix_sock=<socket_path>/<cloud_sql_instance_name>/.s.PGSQL.5432

Varying engine and socket part

Be aware that the engine part postgres+pg8000 varies depending on your database and used driver. Also, depending on your database client library, the socket part ?unix_sock=<socket_path>/<cloud_sql_instance_name>/.s.PGSQL.5432 may be needed or can be omitted, as per:

Note: The PostgreSQL standard requires a .s.PGSQL.5432 suffix in the socket path. Some libraries apply this suffix automatically, but others require you to specify the socket path as follows: /cloudsql/INSTANCE_CONNECTION_NAME/.s.PGSQL.5432.

PostgreSQL and flask_sqlalchemy

For instance, I am using PostgreSQL with flask_sqlalchemy as database client and pg8000 as driver and my working connection string is only postgres+pg8000://<db_user>:<db_pass>@/<db_name>.

Flask SQLalchemy can't connect to Google Cloud Postgresql database with Unix socket

3 Answers3

Example of docs

Varying engine and socket part

PostgreSQL and flask_sqlalchemy