You're viewing **Apigee Edge** documentation.  
[Go to the
**Apigee X** documentation](http://cloud.google.com/apigee/docs). info


> [!NOTE]
> **Note:** Apigee Extensions are available in the Edge UI to Google Cloud Apigee customers with an Enterprise [support](https://cloud.google.com/apigee/pricing) plan.

> [!NOTE]
> **Note:** Extensions marked as deprecated will no longer be available for selection by users. However, the currently deployed version(s) will remain unaffected.

> [!CAUTION]
> **Caution:** This version of the extension is no longer supported and has been deprecated.

<br />

> Version 1.4.1

Perform insert, query, and update operations on a Cloud Spanner database.

This content provides reference for configuring and using this extension.
Before using the extension from an API proxy using the
[ExtensionCallout policy](https://docs.apigee.com/api-platform/reference/policies/extension-callout-policy), you must:

1. Create a Cloud Spanner instance, as described in [Creating and Managing Instances](https://cloud.google.com/spanner/docs/create-manage-instances), and create a database.

2. Once you have the instance and a database, [grant permission](https://cloud.google.com/iam/docs/granting-roles-to-service-accounts) for accessing your database to the GCP service account that represents your extension. For more on the role to use, see [Cloud Spanner roles](https://cloud.google.com/iam/docs/understanding-roles#cloud_spanner_roles). For more on Cloud Spanner access control, see [Applying IAM Roles](https://cloud.google.com/spanner/docs/grant-permissions) and [Access Control for Cloud Spanner](https://cloud.google.com/spanner/docs/iam).

3. When you have a service account that has permission for the level of access to your database you want, [use the GCP Console to generate a key for the service account](https://cloud.google.com/iam/docs/creating-managing-service-account-keys). Use the contents of the resulting key JSON file when you configure this extension.

4. Use the contents of the resulting key JSON file when [adding and configuring](https://docs.apigee.com/api-platform/extensions/configuring-an-extension) the extension using the [configuration reference](https://docs.apigee.com/api-platform/reference/extensions/google-cloud-spanner-database/google-cloud-spanner-database-extension-141#configuration_reference).

## About Cloud Spanner

[Cloud Spanner](https://cloud.google.com/spanner/docs/overview) is a relational database service that is useful for relational, structured, and semi-structured data that requires high availability, strong consistency, and transactional reads and writes.

If you're just getting started with Cloud Spanner, the [quickstart in the Cloud Spanner documentation](https://cloud.google.com/spanner/docs/quickstart-console) is a good place to begin.

## Samples

The following examples illustrate how to configure support for Cloud Spanner extension actions using the [ExtensionCallout policy](https://docs.apigee.com/api-platform/reference/policies/extension-callout-policy).

### Add data

In the following example, the extension's `insert` action adds a new user to the user table.

    <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
    <ConnectorCallout async="false" continueOnError="true" enabled="true" name="Insert-New-User">
        <DisplayName>Insert New User</DisplayName>
        <Connector>spanner-users-products</Connector>
        <Action>insert</Action>
        <Input><![CDATA[{
            "tableName" : "user",
            "rows" : [{
              "username": "jonesy42",
              "firstName": "Floyd",
              "lastName": "Jones",
              "address": "3695 Auctor Street",
              "city": "Gresham",
              "region": "OR",
              "postalCode": "12693",
              "email": "floydster@example.com"
          }]
      }]]></Input>
    </ConnectorCallout>

### Get data

In this example, a query retrieves username and email values from the `user` table.

First, an [AssignMessage policy](https://docs.apigee.com/api-platform/reference/policies/assign-message-policy) assigns a `postal.code.value` variable for use in a query WHERE clause. This is an example. Your policy would probably set the value based on client request parameters.

    <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
    <AssignMessage async="false" continueOnError="false" enabled="true" name="Assign-Postal-Code">
        <AssignTo createNew="true" transport="http" type="request"/>
        <AssignVariable>
            <Name>postal.code</Name>
            <Value>86519</Value>
        </AssignVariable>
        <IgnoreUnresolvedVariables>true</IgnoreUnresolvedVariables>
    </AssignMessage>

The following [ExtensionCallout policy](https://docs.apigee.com/api-platform/reference/policies/extension-callout-policy) executes a query against the database, using the contents of the `postal.code.value` variable in the WHERE clause.

    <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
    <ConnectorCallout async="false" continueOnError="true" enabled="true" name="Get-User-Data">
        <DisplayName>Get User Data</DisplayName>
        <Connector>spanner-users-products</Connector>
        <Action>querySQL</Action>
        <Input><![CDATA[{
          "sql" : "SELECT username, email FROM user WHERE postalCode = @postalCode",
          "params" : {
            "postalCode" : "{postal.code.value}"
          }
        }]]></Input>
      <Output>spanner.userdata.retrieved</Output>
    </ConnectorCallout>

The following [AssignMessage policy](https://docs.apigee.com/api-platform/reference/policies/assign-message-policy) then uses the extension's response,
stored in the `spanner.userdata.retrieved` variable, as the response returned
to the client.

    <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
    <AssignMessage async="false" continueOnError="false" enabled="true" name="Get-Query-Response-Data">
        <DisplayName>Get Query Response Data</DisplayName>
        <AssignTo type="response" createNew="false"/>
        <Set>
            <Payload contentType="application/json">{spanner.userdata.retrieved}</Payload>
        </Set>
    </AssignMessage>

In this example, the response data is returned as JSON such as the following.

    {
      "rows": [
        {
          "username": "freewill444",
          "email": "freewill@example.com"
        }
      ]
    }

### Update data

In this example, the `<Input>` element contains the `username` -- the table's primary key -- and a new value for the `email` column.

    <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
    <ConnectorCallout async="false" continueOnError="true" enabled="true" name="Update-User-Data">
        <DisplayName>Update User Data</DisplayName>
        <Connector>spanner-users-products</Connector>
        <Action>update</Action>
        <Input><![CDATA[{
            "tableName" : "user",
            "rows": [{
                "username":"Liz456",
                "email":"lizzard@example.com"
            }]
        }]]></Input>
    </ConnectorCallout>

## Actions


> [!NOTE]
> **Note:** See the [ExtensionCallout policy](https://docs.apigee.com/api-platform/reference/policies/extension-callout-policy) for more information on setting [`<Action>`](https://docs.apigee.com/api-platform/reference/policies/extension-callout-policy#action_element), [`<Input>`](https://docs.apigee.com/api-platform/reference/policies/extension-callout-policy#input_element), and [`<Output>`](https://docs.apigee.com/api-platform/reference/policies/extension-callout-policy#output_element).

<br />

### insert

Inserts the specified rows into the database.

### Syntax

    <Action>insert</Action>
    <Input><![CDATA[{
      "tableName" : "table-to-insert-into",
      "rows" : "rows-to-insert"
    }]]></Input>

### Example

In the following example, the extension's `insert` action adds a new user to the user table. One row is added.

    <Action>insert</Action>
    <Input><![CDATA[{
        "tableName" : "user",
        "rows" : [{
          "username": "jonesy42",
          "firstName": "Floyd",
          "lastName": "Jones",
          "address": "3695 Auctor Street",
          "city": "Gresham",
          "region": "OR",
          "postalCode": "12693",
          "email": "floydster@example.com"
      }]
    }]]></Input>

#### Request parameters

| Parameter | Description | Type | Default | Required |
|---|---|---|---|---|
| tableName | The table in the database to which the rows should be inserted. | String | None. | Yes. |
| rows | The rows to insert expressed as an array in a `rows` JSON object. | Array | None. | Yes. |

#### Response

None.

### querySQL

Queries the database using the SQL statement with the specified parameters. Parameters are given in the SQL statement with @-prepended names; parameter values are specified in this action's `params` parameter.

For details about Cloud Spanner query syntax, see [Query Syntax](https://cloud.google.com/spanner/docs/query-syntax).

### Syntax

    <Action>querySQL</Action>
    <Input><![CDATA[{
      "sql" : "sql-query-statement",
      "params" : {
        "param1" : "columnValue"
      }
    }]]></Input>

### Example

In this example, a query retrieves `username` and `email` column values from the `user` table. The SQL statement specifies a `postalCode` parameter that's set from the flow variable `postal.code.value`.

    <Action>querySQL</Action>
    <Input><![CDATA[{
      "sql" : "SELECT username, email FROM user WHERE postalCode = @postalCode",
      "params" : {
        "postalCode" : "{postal.code.value}"
      }
    }]]></Input>

#### Request parameters

| Parameter | Description | Type | Default | Required |
|---|---|---|---|---|
| sql | The SQL query to execute. You can specify parameters with @-prepended parameter names. Those parameter names must correspond to keys in the `params` parameter of this action. | String | None. | Yes. |
| params | An object whose keys and values are the parameter names and values used in the SQL query. You can list multiple parameters here. | Object | None. | No. |

#### Response

A `rows` object containing an array of column name-value pairs returned by the query. For example:

    {
      "rows": [
        {
          "username": "freewill444",
          "email": "freewill@example.com"
        }
      ]
    }

### update

Updates rows in the database with the specified data.

### Syntax

    <Input><![CDATA[{
      "tableName" : "table-with-rows-to-update",
      "rows" : "rows-to-update"
    }]]></Input>

### Example

In this example, the email address for the user whose `username` is Liz456 is updated with a new value. One row is updated.

    <Action>update</Action>
    <Input><![CDATA[{
      "tableName" : "user",
      "rows": [{
          "username":"Liz456",
          "email":"lizzard@example.com"
      }]
    }]]></Input>

#### Request parameters

| Parameter | Description | Type | Default | Required |
|---|---|---|---|---|
| tableName | The table in the database where rows should be updated. | String | None. | Yes. |
| rows | An array of row data to update. Each entity in the array should contain the unique ID (such as primary key) value for the row to update. | Array | None. | Yes. |

#### Response

None.

## Configuration Reference

Use the following when you're configuring and deploying this extension for use in API proxies. For steps to configure an extension using the Apigee console, see [Adding and configuring an extension](https://docs.apigee.com/api-platform/extensions/configuring-an-extension).

> [!NOTE]
> **Note:** The ability to configure and deploy extensions is available only to organization administrators.

### Common extension properties

The following properties are present for every extension.

| Property | Description | Default | Required |
|---|---|---|---|
| `name` | Name you're giving this configuration of the extension. | None | Yes |
| `packageName` | Name of the extension package as given by Apigee Edge. | None | Yes |
| `version` | Version number for the extension package from which you're configuring an extension. | None | Yes |
| `configuration` | Configuration value specific to the extension you're adding. See [Properties for this extension package](https://docs.apigee.com/api-platform/reference/extensions/google-cloud-spanner-database/google-cloud-spanner-database-extension-141#properties_for_this_extension_package) | None | Yes |

### Properties for this extension package

Specify values for the following configuration properties specific to this extension.

| Property | Description | Default | Required |
|---|---|---|---|
| projectId | ID of the GCP project containing the database. | None. | Yes. |
| instanceId | ID of the Cloud Spanner instance in your GCP project. | None. | Yes. |
| databaseId | ID of the Cloud Spanner database. | None. | Yes. |
| credentials | When entered in the Apigee Edge console, this is the contents of your [service account key file](https://cloud.google.com/iam/docs/creating-managing-service-account-keys). When sent via the management API, it is a base64-encoded value generated from the service account key file. | None. | Yes. |