Wednesday, February 12, 2020

Connecting Google cloud SQL from App engine.

Google cloud platform support PHP application in its app engine. App Engine is an elastic container it automatically scales up and down based on the traffic.

Here is the https://cloud.google.com/appengine/docs/standard/php7/quickstart explaining how to deploy PHP application into GCP app.

The scope of this article is how to connect to the cloud sql in your PHP from app engine. To deploy into app engine we need to configure some meta information in app.yaml
If we are connecting by dsn we don't need any special configuration. we can refer to the database directly like
mysql:unix_socket=/cloudsql/<MY-PROJECT>:<INSTANCE-REGION>:<MY-DATABASE>;dbname=<my_db>

I am assuming both your app engine and cloud database in the same project. Else we have to explicitly give access.

$dbdsn = "mysql:unix_socket=/cloudsql/<MY-PROJECT>:<INSTANCE-REGION>:<MY-DATABASE>;dbname=<my_db>";
$conn = new PDO($dbdsn, $username, $password);

We can add the dsn in app.yaml read as an environment variable.

In app.yaml

env_variables:
  DB_DSN: mysql:unix_socket=/cloudsql/<MY-PROJECT>:<INSTANCE-REGION>:<MY-DATABASE>;dbname=<my_db>
DB_USER: my-db-user
DB_PASS: my-db-pass
DB_NAME: my_db 
DB_SERVER: my_server

And in the code

$dbdsn = getenv('DB_DSN');
$username = getenv('DB_USER');
$password = getenv('DB_PASS');

$conn = new PDO($dbdsn, $username, $password);

If we want to do tcp connection by providing host and port

$conn = new mysqli($servername, $username, $password, $dbname);

Then app.yaml we have to add 

beta_settings:
  cloud_sql_instances: <INSTANCE_CONNECTION_NAME>=tcp:<PORT>

And in the code $servername= 172.17.0.1:<PORT>;