Using Cloud SQL with Node.js

This part of the Node.js Bookshelf tutorial shows how the sample app stores its persistent data in Google Cloud SQL.

This page is part of a multi-page tutorial. To start from the beginning and see instructions for setting up, go to Node.js Bookshelf App.

Creating a Cloud SQL instance

Install the SQL proxy

Download and install the Cloud SQL Proxy. The Cloud SQL Proxy is used to connect 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
    

OS X 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
    

OS X 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, renaming it 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, renaming it to cloud_sql_proxy.exe.
If your operating system is not included here, you can also compile the proxy from source.

Create a Cloud SQL instance

  1. Create a Cloud SQL for MySQL Second Generation instance. Name the instance library or similar. It can take a few minutes for the instance to be ready. After the instance is ready, it should be visible in the instances list.
  2. Now use the Cloud SDK from command line to run the following command. Copy the value shown for connectionName for the next step.
    gcloud sql instances describe [YOUR_INSTANCE_NAME]

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

Initialize your Cloud SQL instance

  1. Start the Cloud SQL Proxy using the connectionName from the previous step.

    Linux/Mac OS X

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

    Windows

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

    Replace [YOUR_INSTANCE_CONNECTION_NAME] with the value of connectionName 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 application locally.

  2. Next you create a new Cloud SQL user and database.

    CONSOLE

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

    MYSQL CLIENT

    1. In a separate command-line tab, use the MySQL client or similar program to connect to your instance. When prompted, use the root password you configured.
      mysql --host 127.0.0.1 --user root --password
      
    2. Create the required databases, users, and access permissions in your Cloud SQL database using the commands below. Replace [MYSQL_USER] and [MYSQL_PASSWORD] with your desired username and password.
      CREATE DATABASE bookshelf;
      CREATE USER '[MYSQL_USER]' IDENTIFIED BY '[MYSQL_PASSWORD]';
      GRANT ALL ON *.* TO '[MYSQL_USER]';
      

Configuring settings

In the nodejs-getting-started/2-structured-data directory, create a config.json file with this content:

{
  "GCLOUD_PROJECT": "[YOUR_PROJECT_ID]",
  "DATA_BACKEND": "cloudsql",
  "MYSQL_USER": "[YOUR_MYSQL_USERNAME]",
  "MYSQL_PASSWORD": "[YOUR_MYSQL_PASSWORD]",
  "INSTANCE_CONNECTION_NAME": "[YOUR_INSTANCE_CONNECTION_NAME]"
}
  1. Replace [YOUR_PROJECT_ID] with your project ID.

  2. Replace [YOUR_MYSQL_USERNAME] and [YOUR_MYSQL_PASSWORD] with the Cloud SQL username and password that you created previously.

  3. Replace [YOUR_INSTANCE_CONNECTION_NAME] with the Instance Connection Name of your Cloud SQL instance.

Then, add the following content to the end of app.yaml. Be sure to replace [YOUR_INSTANCE_CONNECTION_NAME] with the Instance Connection Name of your Cloud SQL instance.

beta_settings:
  cloud_sql_instances: [YOUR_INSTANCE_CONNECTION_NAME]

Installing dependencies

Install dependencies in the nodejs-getting-started/2-structured-data directory:

  • Using npm:

    npm install
    
  • Using yarn:

    yarn install
    

Creating a database and table

To initialize the SQL database, enter this command with the Cloud SQL proxy running:

npm run init-cloudsql

Upon completion, the script displays a message saying that you successfully created the schema.

Running the app on your local machine

  1. Start a local web server using npm or yarn:

    • Using npm:

      npm start
      
    • Using yarn:

      yarn start
      
  2. In your web browser, enter this address.

    http://localhost:8080

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. Deploy the sample app:

    gcloud app deploy
    

  2. In your web browser, enter this address. Replace [YOUR_PROJECT_ID] with your 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 to deploy the app the first time. The new deployment creates a new version of your app and promotes it to the default version. The older versions of your app remain, as do their associated 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 Cloud Platform Console, go to the App Engine Versions page.

    Go to the Versions page

  2. Click the checkbox next to the non-default app version you want to delete.
  3. Click the Delete button 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.

Application structure

The following diagram shows the application's components and how they fit together.

Bookshelf app structure

Understanding the code

This section walks you through the application code and explains how it works.

Handling user submissions with forms

The add/edit form allows users to add and edit book submissions within the app.

Image of add/edit Form

The HTML form is created using Jade, which is a Node.js template engine. The following Jade template specifies that the form include text input fields for Title, Author, Date Published and Description:

extends ../base.jade

block content
  h3 #{action} book
  form(method="POST")
    .form-group
      label(for="title") Title
      input.form-control(type="text", name="title", id="title", value=book.title)
    .form-group
      label(for="author") Author
      input.form-control(type="text", name="author", id="author", value=book.author)
    .form-group
      label(for="publishedDate") Date Published
      input.form-control(type="text", name="publishedDate", id="publishedDate", value=book.publishedDate)
    .form-group
      label(for="description") Description
      input.form-control(type="text", name="description", id="description", value=book.description)
    .form-group
    button.btn.btn-success(type="submit") Save

Handling form submissions

When a user clicks Add Book, the following code in books/crud.js sends the user to the app's /add page, which displays the add/edit form.

router.get('/add', (req, res) => {
  res.render('books/form.jade', {
    book: {},
    action: 'Add'
  });
});

When a user fills in the Add book form and then clicks Save, the following code handles the form's HTTP POST action and starts the process of sending the submitted data to the Cloud SQL database by passing it to the model.create function.

router.post('/add', (req, res, next) => {
  const data = req.body;

  // Save the data to the database.
  getModel().create(data, (err, savedData) => {
    if (err) {
      next(err);
      return;
    }
    res.redirect(`${req.baseUrl}/${savedData.id}`);
  });
});

The books/model-cloudsql.js file contains the code that performs CRUD functions for data stored in the Cloud SQL database. For example the model.create statement above calls the create function within model-cloudsql.js, which performs a SQL INSERT operation to insert the user's submitted data into the database as a new book submission.

function create (data, cb) {
  connection.query('INSERT INTO `books` SET ?', data, (err, res) => {
    if (err) {
      cb(err);
      return;
    }
    read(res.insertId, cb);
  });
}

When a user edits a book's information after it has been submitted, the update function within model-cloudsql.js is called, which performs a SQL UPDATE operation to update the book's information in the database.

function update (id, data, cb) {
  connection.query(
    'UPDATE `books` SET ? WHERE `id` = ?', [data, id], (err) => {
      if (err) {
        cb(err);
        return;
      }
      read(id, cb);
    });
}

After users have added books, clicking the Books link navigates to the /books page, which lists all the books currently stored in Cloud SQL Database. The list function does the work of listing all the books by using data retrieved from the Cloud SQL Database.

function list (limit, token, cb) {
  token = token ? parseInt(token, 10) : 0;
  connection.query(
    'SELECT * FROM `books` LIMIT ? OFFSET ?', [limit, token],
    (err, results) => {
      if (err) {
        cb(err);
        return;
      }
      const hasMore = results.length === limit ? token + results.length : false;
      cb(null, results, hasMore);
    }
  );
}

The SQL SELECT statement in the list function includes a LIMIT clause that specifies the number of records to return from the database. The SQL SELECT statement also uses the OFFSET keyword, which specifies the starting point for handling pagination and allows additional pages to be requested based on the value of token.

Send feedback about...