Using Cloud SQL for PostgreSQL with PHP

This part of the PHP Bookshelf app tutorial shows how to create, read, update, and delete structured data in Cloud SQL for PostgreSQL.

This page is part of a multipage tutorial. To start from the beginning and read the setup instructions, go to PHP Bookshelf app.

Creating a Cloud SQL instance and database

Installing the Cloud SQL Proxy

Download and install the Cloud SQL Proxy. The Cloud SQL Proxy connects to your Cloud SQL instance when running locally.

Linux 64-bit

  1. Download the proxy:
    wget https://dl.google.com/cloudsql/cloud_sql_proxy.linux.amd64 -O cloud_sql_proxy
    
  2. Make the proxy executable:
    chmod +x cloud_sql_proxy
    

Linux 32-bit

  1. Download the proxy:
    wget https://dl.google.com/cloudsql/cloud_sql_proxy.linux.386 -O cloud_sql_proxy
    
  2. Make the proxy executable:
    chmod +x cloud_sql_proxy
    

macOS 64-bit

  1. Download the proxy:
    curl -o cloud_sql_proxy https://dl.google.com/cloudsql/cloud_sql_proxy.darwin.amd64
    
  2. Make the proxy executable:
    chmod +x cloud_sql_proxy
    

macOS 32-bit

  1. Download the proxy:
    curl -o cloud_sql_proxy https://dl.google.com/cloudsql/cloud_sql_proxy.darwin.386
    
  2. Make the proxy executable:
    chmod +x cloud_sql_proxy
    

Windows 64-bit

Right-click https://dl.google.com/cloudsql/cloud_sql_proxy_x64.exe and select Save Link As to download the proxy. Rename the file to cloud_sql_proxy.exe.

Windows 32-bit

Right-click https://dl.google.com/cloudsql/cloud_sql_proxy_x86.exe and select Save Link As to download the proxy. Rename the file to cloud_sql_proxy.exe.
If your operating system isn't included here, you can also compile the proxy from source.

Creating a Cloud SQL instance

  1. Create a Cloud SQL for PostgreSQL instance.

    Name the instance library or similar. It can take a few minutes for the instance to be ready. When the instance is ready, it's visible in the instances list.

  2. Now use the Cloud SDK to run the following command where [YOUR_INSTANCE_NAME] represents the name of your Cloud SQL instance.
    gcloud sql instances describe [YOUR_INSTANCE_NAME]

    In the output, note the value shown for [CONNECTION_NAME].

    The [CONNECTION_NAME] value is in the format [PROJECT_NAME]:[REGION_NAME]:[INSTANCE_NAME].

Initializing your Cloud SQL instance

  1. Start the Cloud SQL Proxy by using the [CONNECTION_NAME] value from the previous step.

    Linux/macOS

    ./cloud_sql_proxy -instances="[YOUR_INSTANCE_CONNECTION_NAME]"=tcp:5432

    Windows

    cloud_sql_proxy.exe -instances="[YOUR_INSTANCE_CONNECTION_NAME]"=tcp:5432

    Replace [YOUR_INSTANCE_CONNECTION_NAME] with the [CONNECTION_NAME] value that you recorded in the previous step.

    This step establishes a connection from your local computer to your Cloud SQL instance for local testing purposes. Keep the Cloud SQL Proxy running the entire time you test your app locally.

  2. Create a Cloud SQL user and database.

    GCP Console

    1. Create a new database by using the GCP Console for your Cloud SQL instance library. For example, you can use the name bookshelf.
    2. Create a new user by using the GCP Console for your Cloud SQL instance library.

    Postgres client

    1. In a separate command-line tab, install the Postgres client.
      sudo apt-get install postgresql
    2. Use the Postgres client or similar program to connect to your instance. When prompted, use the root password you configured.
      psql --host 127.0.0.1 --user postgres --password
    3. Create the required databases, users, and access permissions in your Cloud SQL database by using the following commands. Replace [POSTGRES_USER] and [POSTGRES_PASSWORD] with the username and password you want to use.
      CREATE DATABASE bookshelf;
      CREATE USER [POSTGRES_USER] WITH PASSWORD '[POSTGRES_PASSWORD]';
      GRANT ALL PRIVILEGES ON DATABASE bookshelf TO [POSTGRES_USER];
      GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO [POSTGRES_USER];
      

Configuring settings

  1. In your terminal window, go to the getting-started-php/2-structured-data directory, and copy the settings.yml.dist file:

    cp config/settings.yml.dist config/settings.yml
    
  2. Open config/settings.yml for editing.

  3. Replace [YOUR_PROJECT_ID] with your GCP project ID.

  4. Set the value of bookshelf_backend to postgres.

  5. Set the values of cloudsql_connection_name, cloudsql_database_name, cloudsql_user, cloudsql_password, and cloudsql_port to the appropriate values for your Cloud SQL instance. Since you're using postgres, use port 5432. For example:

    cloudsql_connection_name: [YOUR_PROJECT_NAME]:[YOUR_REGION]:[YOUR_INSTANCE]
    cloudsql_database_name: bookshelf
    cloudsql_user: phpapp
    cloudsql_password: password
    cloudsql_port: 5432
    
  6. Save and close config/settings.yml.

You also need to update the app.yaml file before deploying:

  1. Open app.yaml for editing.

  2. Uncomment the beta_settings and cloud_sql_instances lines.

  3. Set the value of cloud_sql_instances to the value you used for cloudsql_connection_name in config/settings.yml. The value uses the format [YOUR_PROJECT_NAME]:[YOUR_REGION]:[YOUR_INSTANCE].

  4. Save and close app.yaml.

Installing dependencies

In the 2-structured-data directory, enter this command:

composer install

Running the app on your local machine

  1. Start a local web server:

    php -S localhost:8000 -t web
    
  2. In your browser, enter this address:

    http://localhost:8000

Now you can browse the app's web pages and add, edit, and delete books.

Deploying the app to the App Engine flexible environment

  1. In your terminal window, deploy the sample app:

    gcloud app deploy
    
  2. In your web browser, enter this address. Replace [YOUR_PROJECT_ID] with your GCP project ID:

    https://[YOUR_PROJECT_ID].appspot.com
    

    If you update your app, you can deploy the updated version by entering the same command you used when you first deployed the app. The new deployment creates a version of your app and promotes it to the default version.

    The older versions of your app remain, as do their associated virtual machine (VM) instances. Be aware that all of these app versions and VM instances are billable resources.

    You can reduce costs by deleting the non-default versions of your app.

    To delete an app version:

    1. In the GCP Console, go to the App Engine Versions page.

      Go to the Versions page

    2. Select the checkbox next to the non-default app version you want to delete.
    3. Click Delete delete at the top of the page to delete the app version.

    For complete information about cleaning up billable resources, see the Cleaning up section in the final step of this tutorial.

    App structure

    This diagram shows the app's components and how they fit together.

    Bookshelf app deployment process and structure

    Understanding the code

    Previously, you edited settings.yml and set the value of bookshelf_backend to postgres. This setting tells the app to load the Sql class, which is defined in src/DataModel/Sql.php. The Sql class wraps the PDO API and is responsible for storing books in your Cloud SQL database.

    This code in controllers.php defines and registers a handler for the GET '/books' route. The $model variable is an instance of the Sql class. The $model->listBooks method returns an array that contains an array of books and a cursor. Then the Twig template engine renders the list of books according to the list.html.twig template:

    $app->get('/books/', function (Request $request) use ($app) {
        /** @var DataModelInterface $model */
        $model = $app['bookshelf.model'];
        /** @var Twig_Environment $twig */
        $twig = $app['twig'];
        $token = $request->query->get('page_token');
        $bookList = $model->listBooks($app['bookshelf.page_size'], $token);
    
        return $twig->render('list.html.twig', array(
            'books' => $bookList['books'],
            'next_page_token' => $bookList['cursor'],
        ));
    });

    Here is the Twig template for listing books that are retrieved from the Cloud SQL database. The template receives an array variable named books. For each book in the array, it displays the title and author. The template also receives a next_page_token variable that determines whether the More button is displayed.

    {% for book in books %}
    <div class="media">
      <a href="/books/{{book.id}}">
        <div class="media-left">
          <img src="http://placekitten.com/g/128/192">
        </div>
        <div class="media-body">
          <h4>{{book.title}}</h4>
          <p>{{book.author}}</p>
        </div>
      </a>
    </div>
    {% else %}
    <p>No books found</p>
    {% endfor %}

    This code defines and registers a handler for the GET '/books/{id}' route, where {id} is the ID of an individual book. The handler calls the $model->read method to get the specified book from Cloud SQL. The Twig template engine renders the book according to the view.html.twig template:

    $app->get('/books/{id}', function ($id) use ($app) {
        /** @var DataModelInterface $model */
        $model = $app['bookshelf.model'];
        $book = $model->read($id);
        if (!$book) {
            return new Response('', Response::HTTP_NOT_FOUND);
        }
        /** @var Twig_Environment $twig */
        $twig = $app['twig'];
    
        return $twig->render('view.html.twig', array('book' => $book));
    });

    The view.html.twig template receives a variable named book and displays the book's title, publication date, author, and description.

    <div class="media">
      <div class="media-body">
        <h4 class="book-title">
          {{book.title}}
          <small>{{book.published_date}}</small>
        </h4>
        <h5 class="book-author">By {{book.author|default('Unknown', True)}}</h5>
        <p class="book-description">{{book.description}}</p>
      </div>
    </div>

    When the user clicks Add book, the handler for GET /books/add displays a form for entering the title, author, and other information about a book. When the user clicks Save, the handler for POST /books/add gets the new book from the request and calls $model->create to store the book in Cloud SQL:

    $app->get('/books/add', function () use ($app) {
        /** @var Twig_Environment $twig */
        $twig = $app['twig'];
    
        return $twig->render('form.html.twig', array(
            'action' => 'Add',
            'book' => array(),
        ));
    });
    
    $app->post('/books/add', function (Request $request) use ($app) {
        /** @var DataModelInterface $model */
        $model = $app['bookshelf.model'];
        $book = $request->request->all();
        $id = $model->create($book);
    
        return $app->redirect("/books/$id");
    });

    Here's the template for the book entry form:

    {% extends "base.html.twig" %}
    
    {% block content %}
    <h3>{{action}} book</h3>
    
    <form method="POST" enctype="multipart/form-data">
    
      <div class="form-group">
        <label for="title">Title</label>
        <input type="text" name="title" id="title" value="{{book.title}}" class="form-control"/>
      </div>
    
      <div class="form-group">
        <label for="author">Author</label>
        <input type="text" name="author" id="author" value="{{book.author}}" class="form-control"/>
      </div>
    
      <div class="form-group">
        <label for="published_date">Date Published</label>
        <input type="text" name="published_date" id="published_date" value="{{book.published_date}}" class="form-control"/>
      </div>
    
      <div class="form-group">
        <label for="description">Description</label>
        <textarea name="description" id="description" class="form-control">{{book.description}}</textarea>
      </div>
    
      <button id="submit" type="submit" class="btn btn-success">Save</button>
    </form>
    
    {% endblock %}

    The sample code includes more handlers for editing and deleting individual books:

    $app->get('/books/{id}/edit', function ($id) use ($app) {
        /** @var DataModelInterface $model */
        $model = $app['bookshelf.model'];
        $book = $model->read($id);
        if (!$book) {
            return new Response('', Response::HTTP_NOT_FOUND);
        }
        /** @var Twig_Environment $twig */
        $twig = $app['twig'];
    
        return $twig->render('form.html.twig', array(
            'action' => 'Edit',
            'book' => $book,
        ));
    });
    
    $app->post('/books/{id}/edit', function (Request $request, $id) use ($app) {
        $book = $request->request->all();
        $book['id'] = $id;
        /** @var DataModelInterface $model */
        $model = $app['bookshelf.model'];
        if (!$model->read($id)) {
            return new Response('', Response::HTTP_NOT_FOUND);
        }
        if ($model->update($book)) {
            return $app->redirect("/books/$id");
        }
    
        return new Response('Could not update book');
    });
    $app->post('/books/{id}/delete', function ($id) use ($app) {
        /** @var DataModelInterface $model */
        $model = $app['bookshelf.model'];
        $book = $model->read($id);
        if ($book) {
            $model->delete($id);
    
            return $app->redirect('/books/', Response::HTTP_SEE_OTHER);
        }
    
        return new Response('', Response::HTTP_NOT_FOUND);
    });
Was this page helpful? Let us know how we did:

Send feedback about...