Connect using Cloud SQL Language Connectors

  import 
  
 com.zaxxer.hikari.HikariConfig 
 ; 
 import 
  
 com.zaxxer.hikari.HikariDataSource 
 ; 
 import 
  
 javax.sql.DataSource 
 ; 
 public 
  
 class 
 ConnectorConnectionPoolFactory 
  
 extends 
  
 ConnectionPoolFactory 
  
 { 
  
 // 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. 
  
 private 
  
 static 
  
 final 
  
 String 
  
 INSTANCE_CONNECTION_NAME 
  
 = 
  
 System 
 . 
 getenv 
 ( 
 "INSTANCE_CONNECTION_NAME" 
 ); 
  
 private 
  
 static 
  
 final 
  
 String 
  
 INSTANCE_UNIX_SOCKET 
  
 = 
  
 System 
 . 
 getenv 
 ( 
 "INSTANCE_UNIX_SOCKET" 
 ); 
  
 private 
  
 static 
  
 final 
  
 String 
  
 DB_USER 
  
 = 
  
 System 
 . 
 getenv 
 ( 
 "DB_USER" 
 ); 
  
 private 
  
 static 
  
 final 
  
 String 
  
 DB_PASS 
  
 = 
  
 System 
 . 
 getenv 
 ( 
 "DB_PASS" 
 ); 
  
 private 
  
 static 
  
 final 
  
 String 
  
 DB_NAME 
  
 = 
  
 System 
 . 
 getenv 
 ( 
 "DB_NAME" 
 ); 
  
 public 
  
 static 
  
 DataSource 
  
 createConnectionPool 
 () 
  
 { 
  
 // The configuration object specifies behaviors for the connection pool. 
  
 HikariConfig 
  
 config 
  
 = 
  
 new 
  
 HikariConfig 
 (); 
  
 // The following URL is equivalent to setting the config options below: 
  
 // jdbc:postgresql:///<DB_NAME>?cloudSqlInstance=<INSTANCE_CONNECTION_NAME> 
&  
 // socketFactory=com.google.cloud.sql.postgres.SocketFactory&user=<DB_USER>&password=<DB_PASS> 
  
 // 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 
 . 
 setJdbcUrl 
 ( 
 String 
 . 
 format 
 ( 
 "jdbc:postgresql:///%s" 
 , 
  
 DB_NAME 
 )); 
  
 config 
 . 
 setUsername 
 ( 
 DB_USER 
 ); 
  
 // e.g. "root", _postgres" 
  
 config 
 . 
 setPassword 
 ( 
 DB_PASS 
 ); 
  
 // e.g. "my-password" 
  
 config 
 . 
 addDataSourceProperty 
 ( 
 "socketFactory" 
 , 
  
 "com.google.cloud.sql.postgres.SocketFactory" 
 ); 
  
 config 
 . 
 addDataSourceProperty 
 ( 
 "cloudSqlInstance" 
 , 
  
 INSTANCE_CONNECTION_NAME 
 ); 
  
 // The ipTypes argument can be used to specify a comma delimited list of preferred IP types 
  
 // for connecting to a Cloud SQL instance. The argument ipTypes=PRIVATE will force the 
  
 // SocketFactory to connect with an instance's associated private IP. 
  
 config 
 . 
 addDataSourceProperty 
 ( 
 "ipTypes" 
 , 
  
 "PUBLIC,PRIVATE" 
 ); 
  
 // 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. 
  
 return 
  
 new 
  
 HikariDataSource 
 ( 
 config 
 ); 
  
 } 
 } 
 

  import 
  
 os 
 from 
  
 google.cloud.sql.connector 
  
 import 
 Connector 
 , 
 IPTypes 
 import 
  
 pg8000 
 import 
  
 sqlalchemy 
 def 
  
 connect_with_connector 
 () 
 - 
> sqlalchemy 
 . 
 engine 
 . 
 base 
 . 
 Engine 
 : 
  
 """ 
 Initializes a connection pool for a Cloud SQL instance of Postgres. 
 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 
 [ 
 "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 
 ( 
 refresh_strategy 
 = 
 "LAZY" 
 ) 
 def 
  
 getconn 
 () 
 - 
> pg8000 
 . 
 dbapi 
 . 
 Connection 
 : 
 conn 
 : 
 pg8000 
 . 
 dbapi 
 . 
 Connection 
 = 
 connector 
 . 
 connect 
 ( 
 instance_connection_name 
 , 
 "pg8000" 
 , 
 user 
 = 
 db_user 
 , 
 password 
 = 
 db_pass 
 , 
 db 
 = 
 db_name 
 , 
 ip_type 
 = 
 ip_type 
 , 
 ) 
 return 
 conn 
 # The Cloud SQL Python Connector can be used with SQLAlchemy 
 # using the 'creator' argument to 'create_engine' 
 pool 
 = 
 sqlalchemy 
 . 
 create_engine 
 ( 
 "postgresql+pg8000://" 
 , 
 creator 
 = 
 getconn 
 , 
 # ... 
 ) 
 return 
 pool 
 

  package 
  
 cloudsql 
 import 
  
 ( 
  
 "context" 
  
 "database/sql" 
  
 "fmt" 
  
 "log" 
  
 "net" 
  
 "os" 
  
 "cloud.google.com/go/cloudsqlconn" 
  
 "github.com/jackc/pgx/v5" 
  
 "github.com/jackc/pgx/v5/stdlib" 
 ) 
 func 
  
 connectWithConnector 
 () 
  
 ( 
 * 
 sql 
 . 
 DB 
 , 
  
 error 
 ) 
  
 { 
  
 mustGetenv 
  
 := 
  
 func 
 ( 
 k 
  
 string 
 ) 
  
 string 
  
 { 
  
 v 
  
 := 
  
 os 
 . 
 Getenv 
 ( 
 k 
 ) 
  
 if 
  
 v 
  
 == 
  
 "" 
  
 { 
  
 log 
 . 
 Fatalf 
 ( 
 "Fatal Error in connect_connector.go: %s environment variable not set.\n" 
 , 
  
 k 
 ) 
  
 } 
  
 return 
  
 v 
  
 } 
  
 // 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 passwords and other 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" 
 ) 
  
 ) 
  
 dsn 
  
 := 
  
 fmt 
 . 
 Sprintf 
 ( 
 "user=%s password=%s database=%s" 
 , 
  
 dbUser 
 , 
  
 dbPwd 
 , 
  
 dbName 
 ) 
  
 config 
 , 
  
 err 
  
 := 
  
 pgx 
 . 
 ParseConfig 
 ( 
 dsn 
 ) 
  
 if 
  
 err 
  
 != 
  
 nil 
  
 { 
  
 return 
  
 nil 
 , 
  
 err 
  
 } 
  
 var 
  
 opts 
  
 [] 
 cloudsqlconn 
 . 
  Option 
 
  
 if 
  
 usePrivate 
  
 != 
  
 "" 
  
 { 
  
 opts 
  
 = 
  
 append 
 ( 
 opts 
 , 
  
 cloudsqlconn 
 . 
  WithDefaultDialOptions 
 
 ( 
 cloudsqlconn 
 . 
  WithPrivateIP 
 
 ())) 
  
 } 
  
 // 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. 
  
 opts 
  
 = 
  
 append 
 ( 
 opts 
 , 
  
 cloudsqlconn 
 . 
  WithLazyRefresh 
 
 ()) 
  
 d 
 , 
  
 err 
  
 := 
  
 cloudsqlconn 
 . 
  NewDialer 
 
 ( 
 context 
 . 
 Background 
 (), 
  
 opts 
 ... 
 ) 
  
 if 
  
 err 
  
 != 
  
 nil 
  
 { 
  
 return 
  
 nil 
 , 
  
 err 
  
 } 
  
 // Use the Cloud SQL connector to handle connecting to the instance. 
  
 // This approach does *NOT* require the Cloud SQL proxy. 
  
 config 
 . 
 DialFunc 
  
 = 
  
 func 
 ( 
 ctx 
  
 context 
 . 
 Context 
 , 
  
 network 
 , 
  
 instance 
  
 string 
 ) 
  
 ( 
 net 
 . 
 Conn 
 , 
  
 error 
 ) 
  
 { 
  
 return 
  
 d 
 . 
  Dial 
 
 ( 
 ctx 
 , 
  
 instanceConnectionName 
 ) 
  
 } 
  
 dbURI 
  
 := 
  
 stdlib 
 . 
 RegisterConnConfig 
 ( 
 config 
 ) 
  
 dbPool 
 , 
  
 err 
  
 := 
  
 sql 
 . 
 Open 
 ( 
 "pgx" 
 , 
  
 dbURI 
 ) 
  
 if 
  
 err 
  
 != 
  
 nil 
  
 { 
  
 return 
  
 nil 
 , 
  
 fmt 
 . 
 Errorf 
 ( 
 "sql.Open: %w" 
 , 
  
 err 
 ) 
  
 } 
  
 return 
  
 dbPool 
 , 
  
 nil 
 } 
 

  const 
  
 Knex 
  
 = 
  
 require 
 ( 
 'knex' 
 ); 
 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. 
 const 
  
 getIpType 
  
 = 
  
 () 
  
 = 
>  
 process 
 . 
 env 
 . 
 PRIVATE_IP 
  
 === 
  
 '1' 
  
 || 
  
 process 
 . 
 env 
 . 
 PRIVATE_IP 
  
 === 
  
 'true' 
  
 ? 
  
 'PRIVATE' 
  
 : 
  
 'PUBLIC' 
 ; 
 // connectWithConnector initializes connection pool for a Cloud SQL instance 
 // of Postgres using the Cloud SQL Node.js Connector. 
 const 
  
 connectWithConnector 
  
 = 
  
 async 
  
 config 
  
 = 
>  
 { 
  
 // 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. 
  
 const 
  
 connector 
  
 = 
  
 new 
  
 Connector 
 (); 
  
 const 
  
 clientOpts 
  
 = 
  
 await 
  
 connector 
 . 
 getOptions 
 ({ 
  
 instanceConnectionName 
 : 
  
 process 
 . 
 env 
 . 
 INSTANCE_CONNECTION_NAME 
 , 
  
 ipType 
 : 
  
 getIpType 
 (), 
  
 }); 
  
 const 
  
 dbConfig 
  
 = 
  
 { 
  
 client 
 : 
  
 'pg' 
 , 
  
 connection 
 : 
  
 { 
  
 ... 
 clientOpts 
 , 
  
 user 
 : 
  
 process 
 . 
 env 
 . 
 DB_USER 
 , 
  
 // e.g. 'my-user' 
  
 password 
 : 
  
 process 
 . 
 env 
 . 
 DB_PASS 
 , 
  
 // e.g. 'my-user-password' 
  
 database 
 : 
  
 process 
 . 
 env 
 . 
 DB_NAME 
 , 
  
 // e.g. 'my-database' 
  
 }, 
  
 // ... Specify additional properties here. 
  
 ... 
 config 
 , 
  
 }; 
  
 // Establish a connection to the database. 
  
 return 
  
 Knex 
 ( 
 dbConfig 
 ); 
 };