Query a database

Your webhook currently uses hardcoded data in the accountBalanceCheck function. In this step of the tutorial, you will create a Spanner database, populate it with balance data, and update your function to query the database.

Project configuration

It is important that your Dialogflow agent and the database are both in the same project. This is the easiest way for your function to have secure access to your database. Also, you must enable the Spanner API.

1. Before creating the database, select your project from the Google Cloud console.

   Go to project selector

2. Enable the Spanner API for the project.

   Enable the Spanner API

Create a Spanner instance

When you first use Spanner, you must create an instance, which is an allocation of resources that are used by Spanner databases in that instance.

1. In the Google Cloud console, go to the Spanner Instancespage.

   Go to Spanner instances

2. Click Create instance.

3. For the instance name, enter Tutorial Instance.

4. The instance ID is automatically entered based on the instance name.

5. In Choose your configuration, retain the default option Regionaland select the same region location that you chose when creating the function.

6. In Allocate compute capacity, enter 100 processing units. This provides a minimal capacity for the tutorial.

7. Click Create. The Google Cloud console displays the Overviewpage for the instance you created.

Create a Spanner database

Now that you have an instance, you need to create a database. To create a database:

1. In the instance Overviewpage, click Create database.
2. For the database name, enter tutorial-database.
3. Select the Google Standard SQLdatabase dialect.
4. Click Create. The Google Cloud console displays the Overviewpage for the database you created.

Create tables for your database

Now that you have a database, you need to create tables for the database. To create tables:

1. In the Tables section of the database Overviewpage, click Create table.

2. In the Write DDL statementspage, enter:

     CREATE 
     
    TABLE 
     
    Checking 
     
    ( 
     
    AccountId 
     
    INT64 
    , 
     
    Balance 
     
    INT64 
    , 
    ) 
     
    PRIMARY 
     
    KEY 
    ( 
    AccountId 
    ); 
    
   

3. Click Submit. The Google Cloud console returns to the database Overviewpage and shows that Schema updatesare underway. Wait until the update is complete.

4. In the Tables section of the database Overviewpage, click Create table.

5. In the Write DDL statementspage, enter:

     CREATE 
     
    TABLE 
     
    Savings 
     
    ( 
     
    AccountId 
     
    INT64 
    , 
     
    Balance 
     
    INT64 
    , 
    ) 
     
    PRIMARY 
     
    KEY 
    ( 
    AccountId 
    ); 
    
   

6. Click Submit. The Google Cloud console returns to the database Overviewpage and shows that Schema updatesare underway. Wait until the update is complete.

Insert data into your tables

Now that your database has tables, you need to add data to the tables. To add data:

1. In the list of tables on the database Overviewpage, click the Checking table. The Google Cloud console displays the table's Schemapage.
2. In the left navigation menu, click Datato display the table's Datapage.

3. Click Insert. The Google Cloud console displays the table's Querypage with a new query tab that contains INSERT and SELECT statements. Overwrite those statements with the following:

     INSERT 
     
    INTO 
     
    Checking 
     
    ( 
    AccountId 
    , 
     
    Balance 
    ) 
    VALUES 
    ( 
    1 
    , 
     
    1000 
    ), 
     
    ( 
    2 
    , 
     
    2000 
    ); 
    
   

4. Click Run.

5. In the left navigation menu, click Dataagain to display the table's data. Confirm that the table contains the desired data.

6. Click the tutorial-database: Overviewlink to return to the database overview page.

7. In the list of tables on the database Overviewpage, click the Savings table. The Google Cloud console displays the table's Schemapage.

8. In the left navigation menu, click Datato display the table's Datapage.

9. Click Insert. The Google Cloud console displays the table's Querypage with a new query tab that contains INSERT and SELECT statements. Overwrite those statements with the following:

     INSERT 
     
    INTO 
     
    Savings 
     
    ( 
    AccountId 
    , 
     
    Balance 
    ) 
    VALUES 
    ( 
    1 
    , 
     
    10000 
    ), 
     
    ( 
    2 
    , 
     
    20000 
    ); 
    
   

10. Click Run.

11. In the left navigation menu, click Dataagain to display the table's data. Confirm that the table contains the desired data.

Update the Cloud Function to query the database

The accountBalanceCheck function in the webhook code checks whether specific environment variables are set with information for connecting to the database. If these environment variables are not set, the function uses a hardcoded account balance.

Now that you have a database set up, update your Cloud Function to set required environment variables:

1. Open the Cloud Functions overview page.

   Go to Cloud Functions overview

2. Click the tutorial-banking-webhook function to go to its details page.

3. Click Edit.

4. Open the Runtime, build and connections settingssection.

5. Select the Runtimetab.

6. Add the following variables in the Runtime environment variables section:

   • PROJECT_ID : your project ID
   • SPANNER_INSTANCE_ID : your Spanner instance ID (probably tutorial-instance )
   • SPANNER_DATABASE_ID : your spanner database ID (probably tutorial-database )

7. Click Next.

8. Click Deploy.

9. Wait until the status indicator shows that the function has successfully deployed.

Test your agent

Your agent is now ready to try. Click the Test Agentbutton from the Dialogflow console to open the simulator. Attempt to have the following conversation with the agent:

Conversational turn	You	Agent
1	Hello	Hello, thanks for choosing ACME Bank.
2	I want to know my account balance	What account do you want the balance for: savings or checking?
3	Checking	Here's your latest balance: $10.00

If you examine the code, $10 is not the hardcoded value. This means that your webhook has successfully connected to the database, which does have a balance of $10.

Troubleshooting

The webhook code includes logging statements. If you are having issues, try viewing the logs for your Cloud Function.

If your function is not connecting to the database, and you see a permission error in the logs, you may need to fix the role for the default service account used by your function. This service account is of the form PROJECT_ID @appspot.gserviceaccount.com . You can fix this in one of the following ways:

• If the default service account is shown on the IAM main page, change permissions for the service account . The Editor role will work, but you may want a more granular role in a production scenario.

  Go to the IAM main page

• If the a default service account is not shown on the IAM main page, grant the required role by adding the service account as a new principal.

• Alternatively, you can use a service account that you create .

More information

For more information about the steps above, see:

• Using Cloud Spanner with Cloud Functions
• Create and query a database by using the Google Cloud console
• Google Standard SQL data definition language
• Google Standard SQL data manipulation language