Skip to content

Commit 9c2ea53

Browse files
SaketramDurbhashubha-rajanleahecoledinagraves
authored
refactor Cloud SQL MySQL & Postgres samples to demonstrate both TCP and Unix socket connection patterns (GoogleCloudPlatform#3899)
* refactor Cloud SQL Postgres sample to demonstrate both TCP and Unix socket connection patterns * refactor README for TCP/Unix socket connection patterns * refactor Cloud SQL MySQL sample to demonstrate both TCP and Unix socket connection patterns * refactor README for TCP/Unix socket connection patterns * move config dict initialization into init_connection_engine * update READMEs to advise users to use the DB_SOCKET_PATH env var * Update Cloud SQL MySQL README to provide consistency for unix socket directory bash examples Co-authored-by: Shubha Rajan <shubhadayini@google.com> * Fix "socket" misspelling in Cloud SQL PostgreSQL sample Co-authored-by: Shubha Rajan <shubhadayini@google.com> * Fix comment in Cloud SQL Postgres sample to display a tcp connection string rather than a unix one * Update CloudSQL PostgreSQL & MySQL app config to allow for deploying to App Engine Run Standard and Flexible environments * Update Cloud SQL MySQL & PostgreSQL READMEs with instructions on deploying to GAE-Flexible * Provide more consistency in Cloud SQL Postgres & MySQL samples' app.flexible.yaml w.r.t. the instance connection name template * Update Cloud SQL Postgres sample requirements to only support Python 3 Lock Postgres driver pg8000 to version 1.13.2 Co-authored-by: Shubha Rajan <shubhadayini@google.com> Co-authored-by: Leah E. Cole <6719667+leahecole@users.noreply.github.com> Co-authored-by: Dina Graves Portman <55413432+dinagraves@users.noreply.github.com>
1 parent 65e808c commit 9c2ea53

File tree

10 files changed

+437
-112
lines changed

10 files changed

+437
-112
lines changed

‎cloud-sql/mysql/sqlalchemy/README.md‎

Lines changed: 82 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -32,22 +32,79 @@ secure solution such as [Cloud KMS](https://cloud.google.com/kms/) to help keep
3232

3333
To run this application locally, download and install the `cloud_sql_proxy` by
3434
following the instructions
35-
[here](https://cloud.google.com/sql/docs/mysql/sql-proxy#install). Once the
36-
proxy has been downloaded, use the following commands to create the `/cloudsql`
37-
directory and give the user running the proxy the appropriate permissions:
35+
[here](https://cloud.google.com/sql/docs/mysql/sql-proxy#install).
36+
37+
Instructions are provided below for using the proxy with a TCP connection or a Unix Domain Socket.
38+
On Linux or Mac OS you can use either option, but on Windows the proxy currently requires a TCP
39+
connection.
40+
41+
### Launch proxy with TCP
42+
43+
To run the sample locally with a TCP connection, set environment variables and launch the proxy as
44+
shown below.
45+
46+
#### Linux / Mac OS
47+
Use these terminal commands to initialize environment variables:
48+
```bash
49+
export GOOGLE_APPLICATION_CREDENTIALS=/path/to/service/account/key.json
50+
export DB_HOST='127.0.0.1:3306'
51+
export DB_USER='<DB_USER_NAME>'
52+
export DB_PASS='<DB_PASSWORD>'
53+
export DB_NAME='<DB_NAME>'
54+
```
55+
56+
Then use this command to launch the proxy in the background:
57+
```bash
58+
./cloud_sql_proxy -instances=<project-id>:<region>:<instance-name>=tcp:3306 -credential_file=$GOOGLE_APPLICATION_CREDENTIALS &
59+
```
60+
61+
#### Windows/PowerShell
62+
Use these PowerShell commands to initialize environment variables:
63+
```powershell
64+
$env:GOOGLE_APPLICATION_CREDENTIALS="<CREDENTIALS_JSON_FILE>"
65+
$env:DB_HOST="127.0.0.1:3306"
66+
$env:DB_USER="<DB_USER_NAME>"
67+
$env:DB_PASS="<DB_PASSWORD>"
68+
$env:DB_NAME="<DB_NAME>"
69+
```
70+
71+
Then use this command to launch the proxy in a separate PowerShell session:
72+
```powershell
73+
Start-Process -filepath "C:\<path to proxy exe>" -ArgumentList "-instances=<project-id>:<region>:<instance-name>=tcp:3306 -credential_file=<CREDENTIALS_JSON_FILE>"
74+
```
75+
76+
### Launch proxy with Unix Domain Socket
77+
NOTE: this option is currently only supported on Linux and Mac OS. Windows users should use the
78+
[Launch proxy with TCP](#launch-proxy-with-tcp) option.
79+
80+
To use a Unix socket, you'll need to create a directory and give write access to the user running
81+
the proxy. For example:
82+
3883
```bash
3984
sudo mkdir /cloudsql
4085
sudo chown -R $USER /cloudsql
4186
```
4287

43-
Once the `/cloudsql` directory is ready, use the following command to start the proxy in the
44-
background:
88+
You'll also need to initialize an environment variable containing the directory you just created:
89+
```bash
90+
export DB_SOCKET_PATH=/path/to/the/new/directory
91+
```
92+
93+
Use these terminal commands to initialize other environment variables as well:
94+
```bash
95+
export GOOGLE_APPLICATION_CREDENTIALS=/path/to/service/account/key.json
96+
export INSTANCE_CONNECTION_NAME='<MY-PROJECT>:<INSTANCE-REGION>:<INSTANCE-NAME>'
97+
export DB_USER='<DB_USER_NAME>'
98+
export DB_PASS='<DB_PASSWORD>'
99+
export DB_NAME='<DB_NAME>'
100+
```
101+
102+
Then use this command to launch the proxy in the background:
45103
```bash
46-
./cloud_sql_proxy -dir=/cloudsql --instances=$CLOUD_SQL_CONNECTION_NAME --credential_file=$GOOGLE_APPLICATION_CREDENTIALS
104+
./cloud_sql_proxy -dir=$DB_SOCKET_PATH --instances=$INSTANCE_CONNECTION_NAME --credential_file=$GOOGLE_APPLICATION_CREDENTIALS &
47105
```
48-
Note: Make sure to run the command under a user with write access in the
49-
`/cloudsql` directory. This proxy will use this folder to create a unix socket
50-
the application will use to connect to Cloud SQL.
106+
107+
### Testing the application
51108

52109
Next, setup install the requirements into a virtual enviroment:
53110
```bash
@@ -68,12 +125,26 @@ Navigate towards `http://127.0.0.1:8080` to verify your application is running c
68125
To run on GAE-Standard, create an App Engine project by following the setup for these
69126
[instructions](https://cloud.google.com/appengine/docs/standard/python3/quickstart#before-you-begin).
70127

71-
First, update `app.yaml` with the correct values to pass the environment
128+
First, update `app.standard.yaml` with the correct values to pass the environment
72129
variables into the runtime.
73130

74131
Next, the following command will deploy the application to your Google Cloud project:
75132
```bash
76-
gcloud app deploy
133+
gcloud app deploy app.standard.yaml
134+
```
135+
136+
## Google App Engine Flexible
137+
138+
To run on GAE-Flexible, create an App Engine project by following the setup for these
139+
[instructions](https://cloud.google.com/appengine/docs/flexible/python/quickstart#before-you-begin).
140+
141+
First, update `app.flexible.yaml` with the correct values to pass the environment
142+
variables into the runtime. Also update this file to configure either a TCP or a
143+
Unix domain socket connection to your database.
144+
145+
Next, the following command will deploy the application to your Google Cloud project:
146+
```bash
147+
gcloud app deploy app.flexible.yaml
77148
```
78149

79150
## Deploy to Cloud Run

‎cloud-sql/mysql/sqlalchemy/app.flexible.yaml‎

Lines changed: 40 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,40 @@
1+
# Copyright 2018 Google LLC
2+
#
3+
# Licensed under the Apache License, Version 2.0 (the "License");
4+
# you may not use this file except in compliance with the License.
5+
# You may obtain a copy of the License at
6+
#
7+
# http://www.apache.org/licenses/LICENSE-2.0
8+
#
9+
# Unless required by applicable law or agreed to in writing, software
10+
# distributed under the License is distributed on an "AS IS" BASIS,
11+
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
12+
# See the License for the specific language governing permissions and
13+
# limitations under the License.
14+
15+
runtime: python
16+
env: flex
17+
entrypoint: gunicorn -b :$PORT main:app
18+
19+
runtime_config:
20+
python_version: 3
21+
22+
# Choose to enable either a TCP or Unix domain socket for your database connection
23+
# # Enable a Unix domain socket
24+
# beta_settings:
25+
# cloud_sql_instances: <MY-PROJECT>:<INSTANCE-REGION>:<MY-DATABASE>
26+
27+
# Enable a TCP domain socket
28+
beta_settings:
29+
cloud_sql_instances: <MY-PROJECT>:<INSTANCE-REGION>:<MY-DATABASE>=tcp:<PORT>
30+
31+
# Remember - storing secrets in plaintext is potentially unsafe. Consider using
32+
# something like https://cloud.google.com/kms/ to help keep secrets secret.
33+
env_variables:
34+
CLOUD_SQL_CONNECTION_NAME: <MY-PROJECT>:<INSTANCE-REGION>:<MY-DATABASE>
35+
DB_USER: my-db-user
36+
DB_PASS: my-db-pass
37+
DB_NAME: my_db
38+
39+
# TCP connection setup; remove if using a Unix domain socket instead
40+
DB_HOST: 172.17.0.1:<PORT>

cloud-sql/mysql/sqlalchemy/app.yaml renamed to cloud-sql/mysql/sqlalchemy/app.standard.yaml

File renamed without changes.

‎cloud-sql/mysql/sqlalchemy/main.py‎

Lines changed: 92 additions & 40 deletions
Original file line numberDiff line numberDiff line change
@@ -31,48 +31,100 @@
3131

3232
logger = logging.getLogger()
3333

34-
# [START cloud_sql_mysql_sqlalchemy_create]
34+
35+
def init_connection_engine():
36+
db_config = {
37+
# [START cloud_sql_mysql_sqlalchemy_limit]
38+
# Pool size is the maximum number of permanent connections to keep.
39+
"pool_size": 5,
40+
# Temporarily exceeds the set pool_size if no connections are available.
41+
"max_overflow": 2,
42+
# The total number of concurrent connections for your application will be
43+
# a total of pool_size and max_overflow.
44+
# [END cloud_sql_mysql_sqlalchemy_limit]
45+
# [START cloud_sql_mysql_sqlalchemy_backoff]
46+
# SQLAlchemy automatically uses delays between failed connection attempts,
47+
# but provides no arguments for configuration.
48+
# [END cloud_sql_mysql_sqlalchemy_backoff]
49+
# [START cloud_sql_mysql_sqlalchemy_timeout]
50+
# 'pool_timeout' is the maximum number of seconds to wait when retrieving a
51+
# new connection from the pool. After the specified amount of time, an
52+
# exception will be thrown.
53+
"pool_timeout": 30, # 30 seconds
54+
# [END cloud_sql_mysql_sqlalchemy_timeout]
55+
# [START cloud_sql_mysql_sqlalchemy_lifetime]
56+
# 'pool_recycle' is the maximum number of seconds a connection can persist.
57+
# Connections that live longer than the specified amount of time will be
58+
# reestablished
59+
"pool_recycle": 1800, # 30 minutes
60+
# [END cloud_sql_mysql_sqlalchemy_lifetime]
61+
}
62+
63+
if os.environ.get("DB_HOST"):
64+
return init_tcp_connection_engine(db_config)
65+
else:
66+
return init_unix_connection_engine(db_config)
67+
68+
69+
def init_tcp_connection_engine(db_config):
70+
# [START cloud_sql_mysql_sqlalchemy_create_tcp]
71+
db_socket_addr = os.environ.get("DB_HOST").split(":")
72+
73+
# Extract host and port from socket address
74+
db_host = db_socket_addr[0]
75+
db_port = int(db_socket_addr[1])
76+
77+
return sqlalchemy.create_engine(
78+
# Equivalent URL:
79+
# mysql+pymysql://<db_user>:<db_pass>@<db_host>:<db_port>/<db_name>
80+
sqlalchemy.engine.url.URL(
81+
drivername="mysql+pymysql",
82+
username=db_user,
83+
password=db_pass,
84+
host=db_host,
85+
port=db_port,
86+
database=db_name,
87+
),
88+
# ... Specify additional properties here.
89+
# [START_EXCLUDE]
90+
**db_config
91+
# [END_EXCLUDE]
92+
)
93+
# [END cloud_sql_mysql_sqlalchemy_create_tcp]
94+
95+
96+
def init_unix_connection_engine(db_config):
97+
# [START cloud_sql_mysql_sqlalchemy_create_socket]
98+
if os.environ.get("DB_SOCKET_PATH"):
99+
socket_path = os.environ.get("DB_SOCKET_PATH")
100+
else:
101+
socket_path = "/cloudsql"
102+
103+
return sqlalchemy.create_engine(
104+
# Equivalent URL:
105+
# mysql+pymysql://<db_user>:<db_pass>@/<db_name>?unix_socket=<socket_path>/<cloud_sql_instance_name>
106+
sqlalchemy.engine.url.URL(
107+
drivername="mysql+pymysql",
108+
username=db_user,
109+
password=db_pass,
110+
database=db_name,
111+
query={
112+
"unix_socket": "{}/{}".format(
113+
socket_path,
114+
cloud_sql_connection_name)
115+
}
116+
),
117+
# ... Specify additional properties here.
118+
# [START_EXCLUDE]
119+
**db_config
120+
# [END_EXCLUDE]
121+
)
122+
# [END cloud_sql_mysql_sqlalchemy_create_socket]
123+
124+
35125
# The SQLAlchemy engine will help manage interactions, including automatically
36126
# managing a pool of connections to your database
37-
db = sqlalchemy.create_engine(
38-
# Equivalent URL:
39-
# mysql+pymysql://<db_user>:<db_pass>@/<db_name>?unix_socket=/cloudsql/<cloud_sql_instance_name>
40-
sqlalchemy.engine.url.URL(
41-
drivername="mysql+pymysql",
42-
username=db_user,
43-
password=db_pass,
44-
database=db_name,
45-
query={"unix_socket": "/cloudsql/{}".format(cloud_sql_connection_name)},
46-
),
47-
# ... Specify additional properties here.
48-
# [START_EXCLUDE]
49-
# [START cloud_sql_mysql_sqlalchemy_limit]
50-
# Pool size is the maximum number of permanent connections to keep.
51-
pool_size=5,
52-
# Temporarily exceeds the set pool_size if no connections are available.
53-
max_overflow=2,
54-
# The total number of concurrent connections for your application will be
55-
# a total of pool_size and max_overflow.
56-
# [END cloud_sql_mysql_sqlalchemy_limit]
57-
# [START cloud_sql_mysql_sqlalchemy_backoff]
58-
# SQLAlchemy automatically uses delays between failed connection attempts,
59-
# but provides no arguments for configuration.
60-
# [END cloud_sql_mysql_sqlalchemy_backoff]
61-
# [START cloud_sql_mysql_sqlalchemy_timeout]
62-
# 'pool_timeout' is the maximum number of seconds to wait when retrieving a
63-
# new connection from the pool. After the specified amount of time, an
64-
# exception will be thrown.
65-
pool_timeout=30, # 30 seconds
66-
# [END cloud_sql_mysql_sqlalchemy_timeout]
67-
# [START cloud_sql_mysql_sqlalchemy_lifetime]
68-
# 'pool_recycle' is the maximum number of seconds a connection can persist.
69-
# Connections that live longer than the specified amount of time will be
70-
# reestablished
71-
pool_recycle=1800, # 30 minutes
72-
# [END cloud_sql_mysql_sqlalchemy_lifetime]
73-
# [END_EXCLUDE]
74-
)
75-
# [END cloud_sql_mysql_sqlalchemy_create]
127+
db = init_connection_engine()
76128

77129

78130
@app.before_first_request

‎cloud-sql/mysql/sqlalchemy/requirements.txt‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1,3 +1,4 @@
11
Flask==1.1.2
22
SQLAlchemy==1.3.17
33
PyMySQL==0.9.3
4+
gunicorn==20.0.4

0 commit comments

Comments
 (0)