Connecting to Cloud SQL with Cloud Functions using CLI

Connect to Cloud SQL instance with Cloud Function using Cloud Shell(only) from scratch.

Open Cloud Shell

Enter the following command:

gcloud init

Click on Authorize. After this step:

• create/update your configurations
• set your account
• choose/create a project
• choose a default Compute Region (optional)

Create Cloud SQL instance

Enter the following command:

gcloud sql instances create <YOUR_INSTANCE_NAME> \
--database-version=MYSQL_5_7 \
--cpu=2 --memory=4GB \
--root-password=admin1234 \
--assign-ip \
--zone=us-central1-a\
--availability-type=zonal \
--no-backup

This command specifies the type of database we want(MySQL in this case), CPU and RAM specifications, the zone in which our instance needs to be created and makes our instance public(so that we can reach it from the Cloud Function directly (firewall rules apply)) and disables backup.

For detailed documentation, visit here

After successful SQL instance creation, you should be able to see something like this:

The Connection Name of our instance will be:

<projectName>:<regionName>:<instanceName>

In our case:

training-freshers:us-central1:sql-instance-1

Create a database in our instance using the following command:

gcloud sql databases create <databaseName> --instance=<instanceName>, -i <instanceName>

Database named demo-db created

To create a table in this database, we first need to connect to our instance using:

gcloud sql connect <instanceName> --user=root

Enter the password we set while creating our instance(admin1234) when prompted

Connected to Cloud SQL instance

To create a table, enter the following commands in order:

use <databaseName>;
CREATE TABLE `info` (
`id` INT NOT NULL AUTO_INCREMENT,
`firstname` VARCHAR(20),
`lastname` VARCHAR(20),
`age` VARCHAR(3),
`collegename` VARCHAR(150),
PRIMARY KEY (`id`)
);

This will create a table named ‘info’ in our database ‘demo-db’

Write Cloud Function code using nano editor

Create a file named requirements.txt in the current directory using:

sudo nano requirements.txt

Paste the following code:

SQLAlchemy==1.3.12
PyMySQL==0.9.3

Press Ctrl+O to write to the file, press Enter on the File name to write prompt, finally click Ctrl+X to exit the nano editor.

You should be able to see the following on typing cat requirements.txt:

Create a file named main.py in the same directory using:

sudo nano main.py

Make required changes to the following code and paste it:

import sqlalchemy
#connection name we noted earlier
connection_name = "training-freshers:us-central1:sql-instance-1"
#database name
db_name = "demo-db"
db_user = "root"
db_password = "admin1234"
driver_name = 'mysql+pymysql'
query_string = dict({"unix_socket": "/cloudsql/{}".format(connection_name)})

def writeToSql(request):
   #You can change this to match your personal details
   stmt = sqlalchemy.text("INSERT INTO info ( firstname, lastname, age, collegename) values ('Sagadevan', 'Kounder', '21', 'XYZ College')")

   db = sqlalchemy.create_engine(
   sqlalchemy.engine.url.URL(
   drivername=driver_name,
   username=db_user,
   password=db_password,
   database=db_name,
   query=query_string,
   ),
   pool_size=5,
   max_overflow=2,
   pool_timeout=30,
   pool_recycle=1800
   )
   try:
      with db.connect() as conn:
      conn.execute(stmt)
      print("Insert successful")
   except Exception as e:
      print ("Some exception occured" + e)
      return 'Error: {}'.format(str(e))
   return 'ok'

Make sure that the above code is properly indented

Deploy this function using (add service account which has permissions to access Cloud SQL):

gcloud functions deploy writeToSql \
--entry-point writeToSql \
--runtime python37 --trigger-http \
--allow-unauthenticated \
--service-account=<SERVICE_ACCOUNT> \
--source .

For detailed documentation, visit here

After successful deployment, you should see something like this:

Note down the httpsTrigger url, hitting this url will invoke our cloud function

Before hitting the url:

After hitting the url:

You should get an ‘ok’ response in your browser on hitting the url

Data has been inserted into our table successfully

We can confirm the same using Cloud Function Logs(only time we are using GCP console)

References:

Connecting to Cloud SQL with Cloud Functions | Google Codelabs

Connecting to CloudSQL from a service running in a GCP serverless environnement (ex: Cloud…