Access Cloud SQL via VPC in CLoud Functions

[Tianzi]

Re-posting from a user:

> The documentation says that cloud functions can access Cloud SQL via VPC if they're in the same project. I'm working on a project now and that assertion doesn't hold, I've had to set a world readable net mask to call my Cloud SQL instance from Cloud Functions; additionally, there's sample code for MySQL adapters but nothing really great for MSSQL; and when I try to replicate the MySQL doco around using unix sockets; I get issues in the MSSQL nodejs library which requires a server rendering the whole instance via socket mute.

Has anyone else run into this and can provide some guidance to the user?

[jackwotherspoon]

Two main things I would suggest looking into to hopefully resolve this.

1) Please verify Serverless VPC Access is enabled and configured properly. This is required for Cloud Functions to properly connect to Cloud SQL via Private IP. Please refer to this documentation for the exact steps but to summarize, "For private IP paths, your application will connect directly to your instance through Serverless VPC Access". 

Key takeaways:

• Verify that a Serverless VPC Access Connector was successfully created at: https://console.cloud.google.com/networking/connectors
• When creating a Cloud Function, the Serverless VPC connector must be visible and selected in the "Runtime, build, connections and security settings" > "Connections" section under "VPC Connector".

2) For SQL Server Instances it is recommended to use TCP to connect and not Unix sockets. A sample with mssql for Node exists here: https://cloud.google.com/sql/docs/sqlserver/connect-functions#connecting_to which is a snippet from the full github example. You will just need to change the environment variables and make sure to have config.server set to the Private IP address of your SQL Server instance.

I hope this helps clear it up! Please let me know if there are any follow-up questions 🙂

[moonking]

I have the similar problem and solution with public IP for cloud postgres.

Then I changed to python code to use google.cloud.sql connector after adding a connection to DB in the service with code somthing like this:

from google.cloud.sql.connector import connector

conn = connector.connect(

            instance_connection_name='instance name',

            user='username',

            password='pwd',

            database='dbname'

But I get ModuleNotFoundError: No module named 'google.cloud.sql' error when deploying.  I also added google.cloud.sql to requirements.txt but I don't think that is required, right?

[jackwotherspoon]

@moonking Seems like you are attempting to use the Cloud SQL Python Connector package. You will need to pip install the package or add `cloud-sql-python-connector` to your requirements.txt file to properly have the package imported.

[moonking]

Thanks @jackwotherspoon . I was trying to figure out which package to include in requirements.txt.

I added cloud-sql-python-connector as per your suggestion but I get error "Build failed with status: FAILURE and message: ERROR: Invalid requirement: '`cloud-sql-python-connector' (from line 2 of requirements.txt)"

[jackwotherspoon]

Based on your error message it seems you may have included a quotation at the beginning of the line in your requirements.txt. There should be no quotations, just cloud-sql-python-connector on the line.

Is that what you have? If so does that resolve your problem?

[moonking]

Thank you, I feel so stupid ‌‌🙄

@jackwotherspoon based on python postgres example with without using sqlalchemy, i have the code as such

        # Set up the Postgres connection

        conn = Connector.connect(

            <connection-string>,

            "pg8000",

            user=<user>,

            password=<password>,

            database='postgres'

        )

but I get the error, even though driver is supplied:

"Failed to connect to postgres db: Connector.connect() missing 1 required positional argument: 'driver'"

[jackwotherspoon]

@moonking I'd recommend using SQLAlchemy to get the benefits of connection pooling. However, if you want to connect without it you still can, I would recommend following the notebook you linked as a guide. It seems your code has a few differences from the notebook.

The first one being I would recommend explicitly initializing a Connector object. The second one being that "database" is not the proper argument, it should be "db" instead of database.  

Proper Code:

connector = Connector()

conn = connector.connect(

        <connection-string>,

        "pg8000",

        user=<user>,

        password=<password>,

        db='postgres'

)

Hopefully this helps resolve your issue! 😀 If it does not please create a separate forum post or ask a question directly on the Python Connector so that we don't clutter this post as the discussion no longer focuses on Cloud Functions.

[moonking]

Thank you so much @jackwotherspoon 🙌

[DavoLinares]

I find a solution with this reply so first thank you!

I'm wondering, how do I test a google function that connects to a Cloud SQL postgres instance? I mean the code has the connector and the connection and everything, I put a a query inside but how I can test it in GCP console if the only way is to sent a json object to test it?

Thanks

[purna05]

Hi @Tianzi @jackwotherspoon 

I'm working on the same process where my cloud SQL MySql instance was created in projectA and I want to access MySql from projectB using cloud functions. 

ProjectA details:
ProjectID : migration-project-421317
CloudSQL Instance Name : (migration-project-421317:us-central1:testsql)
VPC Network: testsqlvpc
Service account : (added service account of projectB and cloudsql client role)

ProjectB details :
ProjectID : steel-math-354217
Cloud Function Name : vpc-function
Serverless VPC access : cloud-sl-vpc-connect (10.8.0.0/28 as IP range)
VPC network : vpcforcf
SA : default service account

created 2gen with HTTPS trigger checked Allow unauthenticated invocations.
 Runtime SA : default SA
Build SA : default SA
Ingress : Allow ALL
Egress : Network = Serverless VPC access : cloud-sl-vpc-connect

took runtime as python39 and prepared a basic script to add data into particular column in MySql Database. Test Function was green with 0 errors and successfully deployed with 0 errors. but when i try to Test function it executed with POST 200 status and I'm getting below error and no error logs

[jackwotherspoon]

The error is probably due to networking/connectivity to your Cloud SQL instance. As your Cloud SQL instance is in a different VPC network than your Cloud Function. You will probably want to take a look this connection as multiple VPC's can make things a bit tricky to setup. I would recommend looking at Private Service Connect as it helps make connecting across VPCs easier.