GCP deploy db connection error: Can't create a connection to host

Question

I am trying to deploy my flask app to GCP. However my db connection is not working.

I already try different methods, but anything worked. I also setup db connection locally and I was able to write information on the Google SQL, but when I deploy the app the string doesn't worked at all.

This is part of my code:

from flask import Flask, send_from_directory, render_template, request, redirect, url_for
from sqlalchemy import cast, String
import psycopg2
from model import db, Contacts, Testimonials
from forms import ContactForm, SearchContactForm
from secrets import token_hex
from werkzeug.utils import secure_filename, escape, unescape
from datetime import datetime
import os
import dateutil.parser


root_dir = os.path.abspath(os.path.dirname(__file__))

app = Flask(__name__)
db.init_app(app)

debug_mode = False

if debug_mode:
    # Dev environment
    app.config['SEND_FILE_MAX_AGE_DEFAULT'] = 0
    app.debug = True
    # Define the database
    app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql://postgres:password@public_ip_address/dbname'
else:
    # Production environment
    app.debug = False
    # Define the database
    app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql+pg8000://postgres:password@public_ip_address/dbname'

To deploy I am using command gcloud app deploy from cloud shell, and works perfect. Actually, I can render the parts of the website not related with db connection.

The error I am getting is:

pg8000.exceptions.InterfaceError: Can't create a connection to host {public_ip_address} and port 5432 (timeout is None and source_address is None).

Answer 1

I'd recommend using the Cloud SQL Python Connector to manage your connections and take care of the connection string for you. It supports the pg8000 driver and should help resolve your troubles as it will work both locally and in GCP (Cloud Functions, Cloud Run etc.)

Here is example code showing how to use the connector to access a GCP Cloud SQL database.

from google.cloud.sql.connector import connector
import sqlalchemy

# configure Cloud SQL Python Connector properties
def getconn() ->:
    conn = connector.connect(
        "project:region:instance",
        "pg8000",
        user="YOUR_USER",
        password="YOUR_PASSWORD",
        db="YOUR_DB"
    )
    return conn

# create connection pool to re-use connections
pool = sqlalchemy.create_engine(
    "postgresql+pg8000://",
    creator=getconn,
)

# query or insert into Cloud SQL database
with pool.connect() as db_conn:
    # query database
    result = db_conn.execute("SELECT * from my_table").fetchall()

    # Do something with the results
    for row in result:
        print(row)

For more detailed examples refer to the README of the repository.