8000 Update Cloud SQL for SQL Server sample app w/ Kubernetes Engine (#7414) · mortn/python-docs-samples@57ce0e5 · GitHub
[go: up one dir, main page]
Skip to content

Commit 57ce0e5

Browse files
jsimonwebjsimonweb
authored
Update Cloud SQL for SQL Server sample app w/ Kubernetes Engine (GoogleCloudPlatform#7414)
* Update Cloud SQL for SQL Server sample app w/ Kubernetes Engine * Lint. * Address review comment.
1 parent 6cd9303 commit 57ce0e5

File tree

5 files changed

+182
-1
lines changed

5 files changed

+182
-1
lines changed

‎cloud-sql/sql-server/sqlalchemy/Dockerfile

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -45,7 +45,10 @@ COPY . ./
4545
COPY ./certs /app/certs
4646

4747
# Use server certificate for encrypted connection.
48-
COPY ./certs/server-ca.pem /usr/local/share/ca-certificates/server-ca.crt
48+
# Conditionally copy the server-ca.pem file to the container image if the file is present.
49+
COPY Dockerfile ./certs/server-ca.pem* /usr/local/share/ca-certificates/
50+
# Rename the copied server-ca.pem file if it exists.
51+
RUN mv /usr/local/share/ca-certificates/server-ca.pem /usr/local/share/ca-certificates/server-ca.crt; exit 0
4952
RUN update-ca-certificates
5053

5154
# Run the web service on container startup. Here we use the gunicorn

‎cloud-sql/sql-server/sqlalchemy/deployment.yaml

Lines changed: 84 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,84 @@
1+
# Copyright 2022 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+
# [START cloud_sql_sqlserver_sqlalchemy_gke_quickstart_deployment]
16+
apiVersion: apps/v1
17+
kind: Deployment
18+
metadata:
19+
name: gke-cloud-sql-quickstart
20+
spec:
21+
selector:
22+
matchLabels:
23+
app: gke-cloud-sql-app
24+
template:
25+
metadata:
26+
labels:
27+
app: gke-cloud-sql-app
28+
spec:
29+
serviceAccountName: <YOUR-KSA-NAME>
30+
containers:
31+
- name: gke-cloud-sql-app
32+
# Replace <LOCATION> with your Artifact Registry location (e.g., us-central1).
33+
# Replace <YOUR_PROJECT_ID> with your project ID.
34+
image: <LOCATION>-docker.pkg.dev/<YOUR_PROJECT_ID>/gke-cloud-sql-repo/gke-sql:latest
35+
# This app listens on port 8080 for web traffic by default.
36+
ports:
37+
- containerPort: 8080
38+
env:
39+
- name: PORT
40+
value: "8080"
41+
- name: DB_HOST
42+
value: "127.0.0.1"
43+
- name: DB_PORT
44+
value: "1433"
45+
# If connecting from a VPC-native GKE cluster, you can use the
46+
# following environment variable to have the proxy connect over private IP
47+
# - name: CLOUD_SQL_AUTH_PROXY_IP_ADDRESS_TYPE
48+
# value: "PRIVATE"
49+
- name: DB_USER
50+
valueFrom:
51+
secretKeyRef:
52+
name: <YOUR-DB-SECRET>
53+
key: username
54+
- name: DB_PASS
55+
valueFrom:
56+
secretKeyRef:
57+
name: <YOUR-DB-SECRET>
58+
key: password
59+
- name: DB_NAME
60+
valueFrom:
61+
secretKeyRef:
62+
name: <YOUR-DB-SECRET>
63+
key: database
64+
- name: cloud-sql-proxy
65+
# This uses the latest version of the Cloud SQL proxy
66+
# It is recommended to use a specific version for production environments.
67+
# See: https://github.com/GoogleCloudPlatform/cloudsql-proxy
68+
image: gcr.io/cloudsql-docker/gce-proxy:latest
69+
command:
70+
- "/cloud_sql_proxy"
71+
72+
# If connecting from a VPC-native GKE cluster, you can use the
73+
# following flag to have the proxy connect over private IP
74+
# - "-ip_address_types=PRIVATE"
75+
76+
# tcp should be set to the port the proxy should listen on
77+
# and should match the DB_PORT value set above.
78+
# Defaults: MySQL: 3306, Postgres: 5432, SQLServer: 1433
79+
- "-instances=<INSTANCE_CONNECTION_NAME>=tcp:1433"
80+
securityContext:
81+
# The default Cloud SQL proxy image runs as the
82+
# "nonroot" user and group (uid: 65532) by default.
83+
runAsNonRoot: true
84+
# [END cloud_sql_sqlserver_sqlalchemy_gke_quickstart_deployment]

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

Lines changed: 44 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -68,9 +68,53 @@ def init_connection_engine():
6868

6969
if os.environ.get("DB_ROOT_CERT"):
7070
return init_tcp_sslcerts_connection_engine(db_config)
71+
if os.environ.get("CLOUD_SQL_AUTH_PROXY_IP_ADDRESS_TYPE") == "PRIVATE":
72+
return init_tcp_private_ip_ssl_connection_engine(db_config)
7173
return init_tcp_connection_engine(db_config)
7274

7375

76+
def init_tcp_private_ip_ssl_connection_engine(db_config):
77+
# [START cloud_sql_sqlserver_sqlalchemy_create_tcp_private_ip_ssl]
78+
# Remember - storing secrets in plaintext is potentially unsafe. Consider using
79+
# something like https://cloud.google.com/secret-manager/docs/overview to help keep
80+
# secrets secret.
81+
db_user = os.environ["DB_USER"]
82+
db_pass = os.environ["DB_PASS"]
83+
db_name = os.environ["DB_NAME"]
84+
db_host = os.environ["DB_HOST"]
85+
86+
# Extract port from db_host if present,
87+
# otherwise use DB_PORT environment variable.
88+
host_args = db_host.split(":")
89+
if len(host_args) == 1:
90+
db_hostname = host_args[0]
91+
db_port = int(os.environ["DB_PORT"])
92+
elif len(host_args) == 2:
93+
db_hostname, db_port = host_args[0], int(host_args[1])
94+
95+
# The SQLAlchemy engine will help manage interactions, including automatically
96+
# managing a pool of connections to your database
97+
pool = sqlalchemy.create_engine(
98+
# Equivalent URL:
99+
# mssql+pyodbc://<db_user>:<db_pass>@/<host>:<port>/<db_name>?driver=ODBC+Driver+17+for+SQL+Server
100+
sqlalchemy.engine.url.URL.create(
101+
"mssql+pyodbc",
102+
username=db_user,
103+
password=db_pass,
104+
database=db_name,
105+
host=db_hostname,
106+
port=db_port,
107+
query={
108+
"driver": "ODBC Driver 17 for SQL Server",
109+
},
110+
),
111+
**db_config
112+
)
113+
# [END cloud_sql_sqlserver_sqlalchemy_create_tcp_private_ip_ssl]
114+
115+
return pool
116+
117+
74118
def init_tcp_sslcerts_connection_engine(db_config):
75119
# [START cloud_sql_postgres_sqlalchemy_create_tcp_sslcerts]
76120
# Remember - storing secrets in plaintext is potentially unsafe. Consider using

‎cloud-sql/sql-server/sqlalchemy/service-account.yaml

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,20 @@
1+
# Copyright 2022 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+
# [START cloud_sql_sqlserver_sqlalchemy_gke_quickstart_sa]
16+
apiVersion: v1
17+
kind: ServiceAccount
18+
metadata:
19+
name: <YOUR-KSA-NAME> # TODO(developer): replace this value
20+
# [END cloud_sql_sqlserver_sqlalchemy_gke_quickstart_sa]

‎cloud-sql/sql-server/sqlalchemy/service.yaml

Lines changed: 30 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,30 @@
1+
# Copyright 2022 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+
# [START cloud_sql_sqlserver_sqlalchemy_gke_quickstart_service]
16+
# The ser 81C9 vice provides a load-balancing proxy over the gke-cloud-sql-app
17+
# pods. By specifying the type as a 'LoadBalancer', Kubernetes Engine will
18+
# create an external HTTP load balancer.
19+
apiVersion: v1
20+
kind: Service
21+
metadata:
22+
name: gke-cloud-sql-app
23+
spec:
24+
type: LoadBalancer
25+
selector:
26+
app: gke-cloud-sql-app
27+
ports:
28+
- port: 80
29+
targetPort: 8080
30+
# [END cloud_sql_sqlserver_sqlalchemy_gke_quickstart_service]

0 commit comments

Comments
 (0)
0