Connect Google App Engine with Google Cloud SQL

Post Views: 6,019

Hello Friends, Today we are going to learn how to connect Google App Engine with Google Cloud SQL. App Engine is a fully managed, serverless platform for developing and hosting web applications at scale while Cloud SQL is a fully-managed database service that makes it easy to set up, maintain, manage, and administer your relational databases on Google Cloud Platform. So, without wasting any time let’s do it by following below steps:-

Step 1: Login to Google Cloud Console and Create a Project

Step 2: Select Project and Go to SQL

Step 3: Create Cloud SQL Instance by following below screenshots & Copy Instance Connection Name and Paste in Text Editor for a while because we need it later

app-engine-with-cloudsql-create-sql-instance-3 — Enter Instance ID and Password then Select Region, Zone and MySQL Version and Click Create

app-engine-with-cloudsql-create-sql-instance-4 — Click on Instance ID to Open Overview of the Instance

Copy Instance Connection Name & Save it Temporary by pasting in any Text Editor

Step 4: Now click on Databases in Left Menu & Create Database

app-engine-with-cloudsql-create-database — Click Create Database then Popup will open then Type Database Name and Select Character set, Collation and Click Create

Step 5: Go to App Engine and Activate Cloud Shell and Execute below Command to Create Application or you can also click on Create Application and follow steps

gcloud app create --region=asia-south1

1	gcloud app create --region=asia-south1

Step 6: After receiving success on Cloud Shell click on pencil icon to Launch Editor

Step 7: Now It’s Time to Code

app.yaml

# Use the PHP 7.3 runtime (BETA) by replacing "php72" below with "php73"
runtime: php72

env_variables:
  # Replace USER, PASSWORD, DATABASE, and CONNECTION_NAME with the
  # values obtained when configuring your Cloud SQL instance.
  CLOUDSQL_USER: root
  CLOUDSQL_PASSWORD: root
  CLOUDSQL_DSN: "mysql:dbname=<strong>mydemodatabase</strong>;unix_socket=/cloudsql/<strong>mydemoproject-273008:asia-south1:my-demo-database-instance</strong>"
# [END gae_cloudsql_mysql_env]

# Use the PHP 7.3 runtime (BETA) by replacing "php72" below with "php73"

runtime: php72

env_variables:

# Replace USER, PASSWORD, DATABASE, and CONNECTION_NAME with the

# values obtained when configuring your Cloud SQL instance.

CLOUDSQL_USER: root

CLOUDSQL_PASSWORD: root

CLOUDSQL_DSN: "mysql:dbname=<strong>mydemodatabase</strong>;unix_socket=/cloudsql/<strong>mydemoproject-273008:asia-south1:my-demo-database-instance</strong>"

# [END gae_cloudsql_mysql_env]

We configure our App Engine app’s settings in the app.yaml file. The app.yaml file also contains information about your app’s code, PHP runtime, and entrypoint.

Here we have pasted Connection Name obtained in step 3 just after unix_socket=/cloudsql/ and database name created in step 4 just after dbname=

config.php

<?php
// Ensure the required environment variables are set to run the application
if (!getenv('CLOUDSQL_DSN') || !getenv('CLOUDSQL_USER') || false === getenv('CLOUDSQL_PASSWORD')) {
    die('Set CLOUDSQL_DSN, CLOUDSQL_USER, and CLOUDSQL_PASSWORD environment variables');
}

# [START gae_cloudsql_example]
// If the unix socket is unavailable, try to connect using TCP. This will work
// if you're running a local MySQL server or using the Cloud SQL proxy, for example:
//
//     $ cloud_sql_proxy -instances=your-connection-name=tcp:3306
//
// This will mean your DSN for connecting locally to Cloud SQL would look like this:
//
//     // for MySQL
//     $dsn = "mysql:dbname=DATABASE;host=127.0.0.1";
//     // for PostgreSQL
//     $dsn = "pgsql:dbname=DATABASE;host=127.0.0.1";
//
$dsn = getenv('CLOUDSQL_DSN');
$user = getenv('CLOUDSQL_USER');
$password = getenv('CLOUDSQL_PASSWORD');

// create the PDO client
$db = new PDO($dsn, $user, $password);

// create the tables if they don't exist
$sql = 'CREATE TABLE IF NOT EXISTS contacts (name VARCHAR(255), phone VARCHAR(255), email VARCHAR(255))';
$stmt = $db->prepare($sql);
$stmt->execute();
# [END gae_cloudsql_example]
?>

<?php

// Ensure the required environment variables are set to run the application

if (!getenv('CLOUDSQL_DSN') || !getenv('CLOUDSQL_USER') || false === getenv('CLOUDSQL_PASSWORD')) {

die('Set CLOUDSQL_DSN, CLOUDSQL_USER, and CLOUDSQL_PASSWORD environment variables');

}

# [START gae_cloudsql_example]

// If the unix socket is unavailable, try to connect using TCP. This will work

// if you're running a local MySQL server or using the Cloud SQL proxy, for example:

// $ cloud_sql_proxy -instances=your-connection-name=tcp:3306

// This will mean your DSN for connecting locally to Cloud SQL would look like this:

// // for MySQL

// $dsn = "mysql:dbname=DATABASE;host=127.0.0.1";

// // for PostgreSQL

// $dsn = "pgsql:dbname=DATABASE;host=127.0.0.1";

$dsn = getenv('CLOUDSQL_DSN');

$user = getenv('CLOUDSQL_USER');

$password = getenv('CLOUDSQL_PASSWORD');

// create the PDO client

$db = new PDO($dsn, $user, $password);

// create the tables if they don't exist

$sql = 'CREATE TABLE IF NOT EXISTS contacts (name VARCHAR(255), phone VARCHAR(255), email VARCHAR(255))';

$stmt = $db->prepare($sql);

$stmt->execute();

# [END gae_cloudsql_example]

index.php

<?php
require_once 'config.php';
# [START gae_simple_front_controller]
switch (@parse_url($_SERVER['REQUEST_URI'])['path']) {
    case '/':
    case '/contacts.php':
        require 'contacts.php';
        break;
    case '/contact-add.php':
        require 'contact-add.php';
        break;
    default:
        http_response_code(404);
        exit('Not Found');
}
# [END gae_simple_front_controller]

<?php

require_once 'config.php';

# [START gae_simple_front_controller]

switch (@parse_url($_SERVER['REQUEST_URI'])['path']) {

case '/':

case '/contacts.php':

require 'contacts.php';

break;

case '/contact-add.php':

require 'contact-add.php';

break;

default:

http_response_code(404);

exit('Not Found');

}

# [END gae_simple_front_controller]

contacts.php

<?php
// Query existing entries.
$results = $db->query('SELECT * FROM contacts');
?>

<html>
    <body>
        <h2>Contacts</h2>
	<a href="/contact-add.php">Add New</a>
        <table border="1">
            <thead>
                <tr>
                    <th>Name</th>
                    <th>Phone</th>
                    <th>Email</th>
                </tr>
            </thead>
            <tbody>
                <?php if ($results->rowCount() > 0) { ?>
                    <?php foreach ($results as $row) { ?>
                    <tr>
                        <td><?= $row['name'] ?></td>
                        <td><?= $row['phone'] ?></td>
                        <td><?= $row['email'] ?></td>
                    </tr>
                    <?php } ?>
                <?php } ?>
            </tbody>
        </table>
    </body>
</html>

<?php

// Query existing entries.

$results = $db->query('SELECT * FROM contacts');

<html>

<body>

<h2>Contacts</h2>

<thead>

<tr>

<th>Phone</th>

<th>Email</th>

</tr>

</thead>

<tbody>

<?php if ($results->rowCount() > 0) { ?>

<?php foreach ($results as $row) { ?>

<tr>

</tr>

<?php } ?>

</tbody>

</table>

</body>

</html>

contact-add.php

<?php
$msg = '';
// Insert a new row into the guestbook on POST
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    $params = [
        ':name' => $_POST['name'],
        ':phone' => $_POST['phone'],
        ':email' => $_POST['email'],
    ];
    $stmt = $db->prepare('INSERT INTO contacts (name, phone, email) VALUES (:name, :phone, :email)');
    if($stmt->execute($params)) {
        $msg = 'Added Successfully!';
    }
}

?>

<html>
    <body>
        <h2>Add New Contact</h2>
        <form action="/contact-add.php" method="post">
            <div>Name: <input name="name" /></div>
            <div>Phone: <input name="phone" /></div>
            <div>Email: <input type="email" name="email" /></div>
            <?= $msg ?>
            <div>
                <input type="submit" value="Add">
                <a href="/">Back</a>
            </div>
        </form>
    </body>
</html>

<?php

$msg = '';

// Insert a new row into the guestbook on POST

if ($_SERVER['REQUEST_METHOD'] == 'POST') {

$params = [

':name' => $_POST['name'],

':phone' => $_POST['phone'],

':email' => $_POST['email'],

];

$stmt = $db->prepare('INSERT INTO contacts (name, phone, email) VALUES (:name, :phone, :email)');

if($stmt->execute($params)) {

$msg = 'Added Successfully!';

}

<html>

<body>

<h2>Add New Contact</h2>

<div>Phone: <input name="phone" /></div>

<div>Email: <input type="email" name="email" /></div>

<?= $msg ?>

<div>

</div>

</form>

</body>

</html>

Step 8: Go to Editor which we had launched in step 6 and create directory connect-appengine-to-cloudsql by running below command in cloud shell and upload all the files into it created in step 7.

mkdir connect-appengine-to-cloudsql

1	mkdir connect-appengine-to-cloudsql

Step 9: Go inside connect-appengine-to-cloudsql directory using cd command and run below command to deploy your app and press y when asked, wait for a while and you are done.

gcloud app deploy --version v1

1	gcloud app deploy --version v1

Step 10: Copy target url or the url highlighted in blue shown in step 9 and open it in new tab or window

Download Script

Connect App Engine to Cloud SQL

1 file(s) 3.25 KB

Download

Thanks friends, I hope it helped
Please don’t forget to share

Connect App Engine to Cloud SQL

Comments

Leave a Reply Cancel reply