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
Step 4: Now click on Databases in Left Menu & Create Database
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
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
1 2 3 4 5 6 7 8 9 10 |
# 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
<?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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
<?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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
<?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> |
contact-add.php
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
<?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> |
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.
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.
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
Thanks friends, I hope it helped
Please don’t forget to share
Comments