Cloud SQL

Cloud SQL Proxy

Google Documentation

Installation

Install MAC m1:

  curl -o cloud-sql-proxy https://storage.googleapis.com/cloud-sql-connectors/cloud-sql-proxy/v2.14.1/cloud-sql-proxy.darwin.arm64

  chmod +x cloud-sql-proxy

Crete Service Account with right permissions (cloud-sql)

Using a service account a role that includes the cloudsql.instances.connect permission. The predefined Cloud SQL roles that include this permission are:

Cloud SQL Client
Cloud SQL Editor
Cloud SQL Admin

Run cloud-sql-proxy

dev: INSTANCE_CONNECTION_NAME=dingoo-dev:europe-west1:postgres-dev

prod:

Default authentication

cloud-sql-proxy --address 0.0.0.0 --port 1234 dingoo-dev:europe-west1:postgres-dev

Using a Service Account

cloud-sql-proxy --address 0.0.0.0 --port 5432 $INSTANCE_CONNECTION_NAME --credentials-file ~/development/dingoo/credentials/dingoo-dev-cloud-sql.json

Connecting to Cloud SQL Postgres

psql "host=127.0.0.1 sslmode=disable dbname=dingoo_app user=dingoo"

Connecting to Cloud SQL from Python

Python to connect to PostGres database

def connect_tcp_socket() -> sqlalchemy.engine.base.Engine:
    """Initializes a TCP connection pool for a Cloud SQL instance of Postgres."""

    db_host = os.environ["DATABASE_HOST"]  # e.g. '127.0.0.1'
    print(db_host)
    db_user = os.environ["DATABASE_USERNAME"]  # e.g. 'my-db-user'
    db_pass = os.environ["DATABASE_PASSWORD"]  # e.g. 'my-db-password'
    db_name = os.environ["DATABASE_NAME"]  # e.g. 'my-database'
    db_port = os.environ["DATABASE_PORT"]  # e.g. 5432

    pool = sqlalchemy.create_engine(
        # Equivalent URL:
        # postgresql+pg8000://<db_user>:<db_pass>@<db_host>:<db_port>/<db_name>
        sqlalchemy.engine.url.URL.create(
            drivername="postgresql+pg8000",
            username=db_user,
            password=db_pass,
            host=db_host,
            port=db_port,
            database=db_name,
        ),
    )
    return pool

Python Connect and SQL Read

    conn = connect_tcp_socket()  # This is your Engine object

    with conn.connect() as connection:  # Get a Connection from the Engine
        result = connection.execute(
            text("SELECT * FROM points LIMIT 10;"))  # Use text()

        for row in result:
            print(row)