Google Cloud SQL is a fully managed database service that makes it easy to set up, maintain, manage, and administer relational databases on the Google Cloud Platform. With Cloud SQL, you can connect to your database from anywhere in the world using a secure connection.
In this article, we will show you how to connect to Google Cloud SQL using Python, with best practices about security and maintainability in mind.
Requirements
Depending on the setup of your Cloud SQL instance, and your networking security and access management requirements, you have to make a choice on what way of connecting to the instance is enabled, which will determine how exactly your Python application will get connected to the instance.
You can have both a public and private connection enabled on your instance, though in reality you probably only want to worry about managing one of the two.
Having Public enabled means that the Cloud SQL instance gets a Public IP address with a block-by-default IP whitelist. You can either whitelist a specific IP address, or an IP range. It is also possible to not whitelist any particular IP address and instead use the Cloud SQL Auth proxy.
Pros
Cons
Pros
Cons
Note: This approach will only provide a secure direct tunnel between your application and the Cloud SQL instance, and the database connection itself is still handled by the programmer.
Note: You do not need to perform these steps if you are using some Google Cloud services, such as Cloud Run, App Engine, and Cloud Functions, as there's an option within these services to facilitate this without a more manual setup.
In your project, create a Service Account. You can call it whatever you want, and you have to give it the "Cloud SQL Client" role.
To achieve this, follow these steps:
You’ll need to download the credentials once you create the service account. To do this, take the following steps:
Treat this file with as you would any other sensitive information: Do not share it with anyone who shouldn't be allowed to access the project, and store it securely.
Choose one of the links in the Google Cloud documentation to get the actual binary for your platform. You also have to follow the step to make the binary executable, which on Unix-like systems is the chmod +x cloud-sql-proxy command.
If you are going to be using this for local development, just make sure the cloud-sql-proxy program is running whenever you're in the development zone.
If you are going to be using this on a more serious platform, such as a server, consider using your system's daemon manager to daemonize the proxy.
(Advanced) For Systemd on Linux, first move the proxy binary into /usr/local/bin/cloud-sql-proxy, and then create a unit file for it:
# cloud-sql-proxy.service
[Install]
WantedBy=multi-user.target
[Unit]
Description=cloud-sql-proxy
Requires=networking.service
After=networking.service
[Service]
Type=simple
WorkingDirectory=/usr/local/bin
ExecStart=/usr/local/bin/cloud-sql-proxy
-instances=REPLACE_WITH_INSTANCE_CONNECTION_ID=tcp:5432
-credential_file=/PATH/TO/CREDENTIALS.JSON -dir=/tmp
Restart=always
StandardOutput=journal
User=root
And finally enable and start it:
sudo systemctl enable --now cloud-sql-proxy
Note: This approach will only provide a secure tunnel between your application and the Cloud SQL instance, and the database connection itself is still handled by the programmer.
Note: You do not need to perform these steps if you are using some Google Cloud services, such as Cloud Run, App Engine, and Cloud Functions, as there's an option within these services to facilitate a proxy like in Option 1, effectively removing the need for the Python Connector.
Google provides a Python package called Cloud SQL Python Connector, which is basically like the Auth proxy built directly into python with Google's Cloud SDK, and it is available on pypi.org and can therefore be installed by just running:
pip3 install --upgrade cloud-sql-python-connector
Note: if you have Python installed without Pip, try running the following commands:
python3 -m venv .venv
source .venv/bin/activate
The exact form of both commands may be different based on your project's preferred venv directory name and on what shell you have running in your terminal.
This package wraps specific database connection drivers (pymysql, pg8000, asyncpg and pytds and effectively also SQLAlchemy at the time of writing this article), so you have to use one of these when using this package.
The last step is to implement your favorite database's Python driver in your source code. This is the part where the exact implementation is more up to you within the possibilities of Python and the libraries in question.
While this part is probably not the one you need any assistance with if you are this article's target audience, we'll go over the most important parts using MySQL and the PyMySQL driver as examples. We won't however go over the exact implementation of these drivers into all the possible web frameworks or whatever else you could be running, as there are just too many possible combinations, but there definitely are other articles for each one.
When using the Auth proxy - which has to run at the same time as the Python app:
import pymysql.cursors
# The host is always 'localhost', as the proxy runs locally.
# The credentials and database name are of course up to you.
connection = pymysql.connect(
host="localhost",
user="user",
password="passwd",
database="db",
cursorclass=pymysql.cursors.DictCursor
)
When using the Cloud SQL Python Connector, which will run the equivalent of pymysql.connect() for you in the background:
from google.cloud.sql.connector import Connector
import sqlalchemy
connector = Connector()
# Replace "project:region:instance" with the connection
# ID you'll see in Google Cloud SQL web console
def getconn() -> pymysql.connections.Connection:
conn: pymysql.connections.Connection = connector.connect(
"project:region:instance",
"pymysql",
user="user",
password="passwd",
db="db"
)
return conn
pool = sqlalchemy.create_engine(
"mysql+pymysql://",
creator=getconn,
)
Following the steps outlined in this tutorial, you can easily connect your Python app to your Google Cloud SQL instance using either the Authentication Proxy or the Cloud SQL Python Connector using an app-specific Service Account. With a secure connection, you can manage your relational databases on the Google Cloud Platform anywhere.
Do you want to learn more tech hacks and find out more about the cloud? Contact us for a free consultation.
Source:
https://cloud.google.com/sql/docs/mysql/sql-proxy
https://cloud.google.com/sql/docs/postgres/sql-proxy
https://cloud.google.com/sql/docs/sqlserver/sql-proxy
https://cloud.google.com/vpc/network-pricing#egress-within-gcp
https://cloud.google.com/compute/docs/regions-zones#identifying_a_region_or_zone
https://cloud.google.com/vpc/docs/private-services-access
https://cloud.google.com/vpc/docs/vpc-peering
https://cloud.google.com/sql/docs/mysql/sql-proxy#install
https://cloud.google.com/sql/docs/postgres/sql-proxy#install
https://cloud.google.com/sql/docs/mysql/sqlserver#install
https://pypi.org/project/cloud-sql-python-connector/
https://pypi.org/project/pymysql/
https://pypi.org/project/pg8000/
https://pypi.org/project/asyncpg/
https://pypi.org/project/python-tds/
https://pypi.org/project/SQLAlchemy/
Q1: What is Google Cloud SQL?
Google Cloud SQL is a fully managed database service that simplifies the setup, maintenance, management, and administration of relational databases on the Google Cloud Platform. It allows for secure connections to your database from anywhere in the world.
Q2: What is required to connect to a Cloud SQL instance?
To connect, you need a Google Cloud Platform project with a valid Billing Account, an active Cloud SQL instance within that project, and an environment with Python 3 and pip installed.
Q3: What are the main differences between a Public and Private connection for Cloud SQL?
A Public connection assigns a public IP address to the instance, allowing connectivity from anywhere, which can be secured using the Cloud SQL Auth proxy. However, it may be a concern for security auditors. A Private connection restricts access to within the same private network (VPC), which enhances security and can reduce egress costs, but it requires a more complex initial setup and has limitations regarding VPC peering and IP range allocation.
Q4: What is the Cloud SQL Auth Proxy?
The Cloud SQL Auth Proxy is a utility that you run on your local machine or server. It creates a secure, authenticated tunnel to your Cloud SQL instance, but you are still responsible for writing the code that handles the database connection through that tunnel.
Q5: What is the Cloud SQL Python Connector?
The Cloud SQL Python Connector is a Python library that provides the same secure tunnel functionality as the Auth Proxy but is integrated directly into your Python application code, removing the need to run a separate proxy process.
Q6: How do I set up a Service Account to connect to Cloud SQL?
In your Google Cloud project, navigate to "IAM & Admin" > "Service accounts." Create a new service account and grant it the "Cloud SQL Client" role. After creating the account, create and download a JSON key file, which you will use for authentication.
Q7: When is a manual setup for the Auth Proxy or Python Connector not required?
A manual setup is not necessary when using certain Google Cloud services like Cloud Run, App Engine, and Cloud Functions, as these services have built-in options to facilitate a secure connection to Cloud SQL.
Q8: Which Python database drivers work with the Cloud SQL Python Connector?
The connector package wraps several common database drivers, including pymysql (for MySQL), pg8000 and asyncpg (for PostgreSQL), and pytds (for SQL Server). It can also be used with SQLAlchemy.
Q9: How does my Python code connect when using the Auth Proxy?
With the Auth Proxy running, your application connects to it as if it were a local database server. For example, using the pymysql library, you would set the host in your connection parameters to "localhost".
Q10: How does my Python code connect when using the Cloud SQL Python Connector?
You first import and initialize the Connector from the library. Then, you call the connector.connect() method, providing the instance connection string (e.g., "project:region:instance"), the name of the Python driver you're using (e.g., "pymysql"), and your database user credentials. This method handles establishing the secure tunnel and returns a database connection object.