Connect from Cloud Run

importos
fromgoogle.cloud.sql.connectorimport Connector, IPTypes
importpytds
importsqlalchemy
defconnect_with_connector() -> sqlalchemy.engine.base.Engine:
"""
 Initializes a connection pool for a Cloud SQL instance of SQL Server.
 Uses the Cloud SQL Python Connector package.
 """
 # Note: Saving credentials in environment variables is convenient, but not
 # secure - consider a more secure solution such as
 # Cloud Secret Manager (https://cloud.google.com/secret-manager) to help
 # keep secrets safe.
 instance_connection_name = os.environ[
 "INSTANCE_CONNECTION_NAME"
 ] # e.g. 'project:region:instance'
 db_user = os.environ.get("DB_USER", "") # e.g. 'my-db-user'
 db_pass = os.environ["DB_PASS"] # e.g. 'my-db-password'
 db_name = os.environ["DB_NAME"] # e.g. 'my-database'
 ip_type = IPTypes.PRIVATE if os.environ.get("PRIVATE_IP") else IPTypes.PUBLIC
 # initialize Cloud SQL Python Connector object
 connector = Connector(ip_type=ip_type, refresh_strategy="LAZY")
 connect_args = {}
 # If your SQL Server instance requires SSL, you need to download the CA
 # certificate for your instance and include cafile={path to downloaded
 # certificate} and validate_host=False. This is a workaround for a known issue.
 if os.environ.get("DB_ROOT_CERT"): # e.g. '/path/to/my/server-ca.pem'
 connect_args = {
 "cafile": os.environ["DB_ROOT_CERT"],
 "validate_host": False,
 }
 defgetconn() -> pytds.Connection:
 conn = connector.connect(
 instance_connection_name,
 "pytds",
 user=db_user,
 password=db_pass,
 db=db_name,
 **connect_args
 )
 return conn
 pool = sqlalchemy.create_engine(
 "mssql+pytds://",
 creator=getconn,
 # ...
 )
 return pool

importcom.zaxxer.hikari.HikariConfig;
importcom.zaxxer.hikari.HikariDataSource;
importjavax.sql.DataSource;
publicclass ConnectorConnectionPoolFactoryextendsConnectionPoolFactory{
// Note: Saving credentials in environment variables is convenient, but not
// secure - consider a more secure solution such as
// Cloud Secret Manager (https://cloud.google.com/secret-manager) to help
// keep secrets safe.
privatestaticfinalStringINSTANCE_CONNECTION_NAME=
System.getenv("INSTANCE_CONNECTION_NAME");
privatestaticfinalStringDB_USER=System.getenv("DB_USER");
privatestaticfinalStringDB_PASS=System.getenv("DB_PASS");
privatestaticfinalStringDB_NAME=System.getenv("DB_NAME");
publicstaticDataSourcecreateConnectionPool(){
// The configuration object specifies behaviors for the connection pool.
HikariConfigconfig=newHikariConfig();
// The following is equivalent to setting the config options below:
// jdbc:sqlserver://;user=<DB_USER>;password=<DB_PASS>;databaseName=<DB_NAME>;
// socketFactoryClass=com.google.cloud.sql.sqlserver.SocketFactory;
// socketFactoryConstructorArg=<INSTANCE_CONNECTION_NAME>
// See the link below for more info on building a JDBC URL for the Cloud SQL JDBC Socket Factory
// https://github.com/GoogleCloudPlatform/cloud-sql-jdbc-socket-factory#creating-the-jdbc-url
// Configure which instance and what database user to connect with.
config
.setDataSourceClassName("com.microsoft.sqlserver.jdbc.SQLServerDataSource");
config.setUsername(DB_USER);// e.g. "root", "sqlserver"
config.setPassword(DB_PASS);// e.g. "my-password"
config.addDataSourceProperty("databaseName",DB_NAME);
config.addDataSourceProperty("socketFactoryClass",
"com.google.cloud.sql.sqlserver.SocketFactory");
config.addDataSourceProperty("socketFactoryConstructorArg",INSTANCE_CONNECTION_NAME);
// The Java Connector provides SSL encryption, so it should be disabled
// at the driver level.
config.addDataSourceProperty("encrypt","false");
// cloudSqlRefreshStrategy set to "lazy" is used to perform a
// refresh when needed, rather than on a scheduled interval.
// This is recommended for serverless environments to
// avoid background refreshes from throttling CPU.
config.addDataSourceProperty("cloudSqlRefreshStrategy","lazy");
// ... Specify additional connection properties here.
// ...
// Initialize the connection pool using the configuration object.
returnnewHikariDataSource(config);
}
}

packagecloudsql
import(
"context"
"database/sql"
"fmt"
"log"
"net"
"os"
"cloud.google.com/go/cloudsqlconn"
mssql"github.com/denisenkom/go-mssqldb"
)
typecsqlDialerstruct{
dialer*cloudsqlconn.Dialer
connNamestring
usePrivatebool
}
// DialContext adheres to the mssql.Dialer interface.
func(c*csqlDialer)DialContext(ctxcontext.Context,network,addrstring)(net.Conn,error){
varopts[]cloudsqlconn.DialOption
ifc.usePrivate{
opts=append(opts,cloudsqlconn.WithPrivateIP ())
}
returnc.dialer.Dial (ctx,c.connName,opts...)
}
funcconnectWithConnector()(*sql.DB,error){
mustGetenv:=func(kstring)string{
v:=os.Getenv(k)
ifv==""{
log.Fatalf("Fatal Error in connect_connector.go: %s environment variable not set.\n",k)
}
returnv
}
// Note: Saving credentials in environment variables is convenient, but not
// secure - consider a more secure solution such as
// Cloud Secret Manager (https://cloud.google.com/secret-manager) to help
// keep secrets safe.
var(
dbUser=mustGetenv("DB_USER")// e.g. 'my-db-user'
dbPwd=mustGetenv("DB_PASS")// e.g. 'my-db-password'
dbName=mustGetenv("DB_NAME")// e.g. 'my-database'
instanceConnectionName=mustGetenv("INSTANCE_CONNECTION_NAME")// e.g. 'project:region:instance'
usePrivate=os.Getenv("PRIVATE_IP")
)
dbURI:=fmt.Sprintf("user id=%s;password=%s;database=%s;",dbUser,dbPwd,dbName)
c,err:=mssql.NewConnector(dbURI)
iferr!=nil{
returnnil,fmt.Errorf("mssql.NewConnector: %w",err)
}
// WithLazyRefresh() Option is used to perform refresh
// when needed, rather than on a scheduled interval.
// This is recommended for serverless environments to
// avoid background refreshes from throttling CPU.
dialer,err:=cloudsqlconn.NewDialer (context.Background(),cloudsqlconn.WithLazyRefresh ())
iferr!=nil{
returnnil,fmt.Errorf("cloudsqlconn.NewDailer: %w",err)
}
c.Dialer =&csqlDialer{
dialer:dialer,
connName:instanceConnectionName,
usePrivate:usePrivate!="",
}
dbPool:=sql.OpenDB(c)
iferr!=nil{
returnnil,fmt.Errorf("sql.Open: %w",err)
}
returndbPool,nil
}

const{Connection}=require('tedious');
const{Connector}=require('@google-cloud/cloud-sql-connector');
// In case the PRIVATE_IP environment variable is defined then we set
// the ipType=PRIVATE for the new connector instance, otherwise defaults
// to public ip type.
constgetIpType=()=>
process.env.PRIVATE_IP==='1'||process.env.PRIVATE_IP==='true'
?'PRIVATE'
:'PUBLIC';
// connectWithConnector initializes a TCP connection
// to a Cloud SQL instance of SQL Server.
constconnectWithConnector=asyncconfig=>{
// Note: Saving credentials in environment variables is convenient, but not
// secure - consider a more secure solution such as
// Cloud Secret Manager (https://cloud.google.com/secret-manager) to help
// keep secrets safe.
constconnector=newConnector();
constclientOpts=awaitconnector.getTediousOptions({
instanceConnectionName:process.env.INSTANCE_CONNECTION_NAME,
ipType:getIpType(),
});
constdbConfig={
// Please note that the `server` property here is not used and is only
// defined due to a bug in the tedious driver
// (ref: https://github.com/tediousjs/tedious/issues/1541)
// With that in mind, do not try to change this value since it will have no
// impact in how the connector works, this sample will be updated to remove
// this property declaration as soon as the tedious driver bug is fixed
server:'0.0.0.0',// e.g. '127.0.0.1'
authentication:{
type:'default',
options:{
userName:process.env.DB_USER,// e.g. 'my-db-user'
password:process.env.DB_PASS,// e.g. 'my-db-password'
},
},
options:{
...clientOpts,
// Please note that the `port` property here is not used and is only
// defined due to a bug in the tedious driver
// (ref: https://github.com/tediousjs/tedious/issues/1541)
// With that in mind, do not try to change this value since it will have
// no impact in how the connector works, this sample will be updated to
// remove this property declaration as soon as the tedious driver bug is
// fixed
port:9999,
database:process.env.DB_NAME,// e.g. 'my-database'
useColumnNames:true,
},
// ... Specify additional properties here.
...config,
};
// Establish a connection to the database.
returnnewConnection(dbConfig);
};

# Create dbuser secret
resource"google_secret_manager_secret""dbuser"{
secret_id="dbusersecret"
replication{
auto{}
}
depends_on=[google_project_service.secretmanager_api]
}
# Attaches secret data for dbuser secret
resource"google_secret_manager_secret_version""dbuser_data"{
secret=google_secret_manager_secret.dbuser.id
secret_data="secret-data" # Stores secret as a plain txt in state
}
# Update service account for dbuser secret
resource"google_secret_manager_secret_iam_member""secretaccess_compute_dbuser"{
secret_id=google_secret_manager_secret.dbuser.id
role="roles/secretmanager.secretAccessor"
member="serviceAccount:${data.google_project.project.number}-compute@developer.gserviceaccount.com" # Project's compute service account
}

# Create dbpass secret
resource"google_secret_manager_secret""dbpass"{
secret_id="dbpasssecret"
replication{
auto{}
}
depends_on=[google_project_service.secretmanager_api]
}
# Attaches secret data for dbpass secret
resource"google_secret_manager_secret_version""dbpass_data"{
secret=google_secret_manager_secret.dbpass.id
secret_data="secret-data" # Stores secret as a plain txt in state
}
# Update service account for dbpass secret
resource"google_secret_manager_secret_iam_member""secretaccess_compute_dbpass"{
secret_id=google_secret_manager_secret.dbpass.id
role="roles/secretmanager.secretAccessor"
member="serviceAccount:${data.google_project.project.number}-compute@developer.gserviceaccount.com" # Project's compute service account
}

# Create dbname secret
resource"google_secret_manager_secret""dbname"{
secret_id="dbnamesecret"
replication{
auto{}
}
depends_on=[google_project_service.secretmanager_api]
}
# Attaches secret data for dbname secret
resource"google_secret_manager_secret_version""dbname_data"{
secret=google_secret_manager_secret.dbname.id
secret_data="secret-data" # Stores secret as a plain txt in state
}
# Update service account for dbname secret
resource"google_secret_manager_secret_iam_member""secretaccess_compute_dbname"{
secret_id=google_secret_manager_secret.dbname.id
role="roles/secretmanager.secretAccessor"
member="serviceAccount:${data.google_project.project.number}-compute@developer.gserviceaccount.com" # Project's compute service account
}

resource"google_cloud_run_v2_service""default"{
name="cloudrun-service"
location="us-central1"
deletion_protection=false # set to "true" in production
template{
containers{
image="us-docker.pkg.dev/cloudrun/container/hello:latest" # Image to deploy

 # Sets a environment variable for instance connection name
env{
name="INSTANCE_CONNECTION_NAME"
value=google_sql_database_instance.default.connection_name
}
 # Sets a secret environment variable for database user secret
env{
name="DB_USER"
value_source{
secret_key_ref{
secret=google_secret_manager_secret.dbuser.secret_id # secret name
version="latest" # secret version number or 'latest'
}
}
}
 # Sets a secret environment variable for database password secret
env{
name="DB_PASS"
value_source{
secret_key_ref{
secret=google_secret_manager_secret.dbpass.secret_id # secret name
version="latest" # secret version number or 'latest'
}
}
}
 # Sets a secret environment variable for database name secret
env{
name="DB_NAME"
value_source{
secret_key_ref{
secret=google_secret_manager_secret.dbname.secret_id # secret name
version="latest" # secret version number or 'latest'
}
}
}
volume_mounts{
name="cloudsql"
mount_path="/cloudsql"
}
}
volumes{
name="cloudsql"
cloud_sql_instance{
instances=[google_sql_database_instance.default.connection_name]
}
}
}
client="terraform"
depends_on=[google_project_service.secretmanager_api,google_project_service.cloudrun_api,google_project_service.sqladmin_api]
}