spanner-sql

About

A spanner-sql tool executes a pre-defined SQL statement (either googlesql or postgresql) against a Cloud Spanner database.

Compatible Sources

GoogleSQL

For the googlesql dialect, the specified SQL statement is executed as a data manipulation language (DML) statements, and specified parameters will inserted according to their name: e.g. @name.

Note: This tool uses parameterized queries to prevent SQL injections. Query parameters can be used as substitutes for arbitrary expressions. Parameters cannot be used as substitutes for identifiers, column names, table names, or other parts of the query.

PostgreSQL

For the postgresql dialect, the specified SQL statement is executed as a prepared statement, and specified parameters will be inserted according to their position: e.g. $1 will be the first parameter specified, $2 will be the second parameter, and so on.

Example

Note: This tool uses parameterized queries to prevent SQL injections. Query parameters can be used as substitutes for arbitrary expressions. Parameters cannot be used as substitutes for identifiers, column names, table names, or other parts of the query.

• GoogleSQL
• PostgreSQL

kind: tool
name: search_flights_by_number
type: spanner-sql
source: my-spanner-instance
statement: |
  SELECT * FROM flights
  WHERE airline = @airline
  AND flight_number = @flight_number
  LIMIT 10
description: |
  Use this tool to get information for a specific flight.
  Takes an airline code and flight number and returns info on the flight.
  Do NOT use this tool with a flight id. Do NOT guess an airline code or flight number.
  A airline code is a code for an airline service consisting of two-character
  airline designator and followed by flight number, which is 1 to 4 digit number.
  For example, if given CY 0123, the airline is "CY", and flight_number is "123".
  Another example for this is DL 1234, the airline is "DL", and flight_number is "1234".
  If the tool returns more than one option choose the date closes to today.
  Example:
  {{
      "airline": "CY",
      "flight_number": "888",
  }}
  Example:
  {{
      "airline": "DL",
      "flight_number": "1234",
  }}
parameters:
  - name: airline
    type: string
    description: Airline unique 2 letter identifier
  - name: flight_number
    type: string
    description: 1 to 4 digit number

kind: tool
name: search_flights_by_number
type: spanner
source: my-spanner-instance
statement: |
  SELECT * FROM flights
  WHERE airline = $1
  AND flight_number = $2
  LIMIT 10
description: |
  Use this tool to get information for a specific flight.
  Takes an airline code and flight number and returns info on the flight.
  Do NOT use this tool with a flight id. Do NOT guess an airline code or flight number.
  A airline code is a code for an airline service consisting of two-character
  airline designator and followed by flight number, which is 1 to 4 digit number.
  For example, if given CY 0123, the airline is "CY", and flight_number is "123".
  Another example for this is DL 1234, the airline is "DL", and flight_number is "1234".
  If the tool returns more than one option choose the date closes to today.
  Example:
  {{
      "airline": "CY",
      "flight_number": "888",
  }}
  Example:
  {{
      "airline": "DL",
      "flight_number": "1234",
  }}
parameters:
  - name: airline
    type: string
    description: Airline unique 2 letter identifier
  - name: flight_number
    type: string
    description: 1 to 4 digit number

Example with Vector Search

Spanner supports high-performance vector similarity search. When using an embeddingModel with a spanner-sql tool, the tool automatically converts text parameters into the native ARRAY format required by Spanner.

Define the Embedding Model

See EmbeddingModels for more information.

kind: embeddingModel
name: gemini-model
type: gemini
model: gemini-embedding-001
apiKey: ${GOOGLE_API_KEY}
dimension: 768

Vector Ingestion Tool

This tool stores both the raw text and its vector representation. It uses valueFromParam to hide the vector conversion logic from the LLM, ensuring the Agent only has to provide the content once.

kind: tool
name: insert_doc_spanner
type: spanner-sql
source: my-spanner-source
statement: |
  INSERT INTO vector_table (id, content, embedding)
  VALUES (1, @content, @text_to_embed)
description: |
  Index new documents for semantic search in Spanner.
parameters:
  - name: content
    type: string
    description: The text content to store.
  - name: text_to_embed
    type: string
    # Automatically copies 'content' and converts it to a FLOAT64 array
    valueFromParam: content
    embeddedBy: gemini-model

Vector Search Tool

This tool allows the Agent to perform a natural language search. The query string provided by the Agent is converted into a vector before the SQL is executed.

kind: tool
name: search_docs_spanner
type: spanner-sql
source: my-spanner-source
statement: |
  SELECT 
    id, 
    content, 
    COSINE_DISTANCE(embedding, @query) AS distance 
  FROM 
    vector_table 
  ORDER BY 
    distance 
  LIMIT 1
description: |
  Search for documents in Spanner using natural language. 
  Returns the most semantically similar result.
parameters:
  - name: query
    type: string
    description: The search query to be converted to a vector.
    embeddedBy: gemini-model

Example with Template Parameters

Note: This tool allows direct modifications to the SQL statement, including identifiers, column names, and table names. This makes it more vulnerable to SQL injections. Using basic parameters only (see above) is recommended for performance and safety reasons. For more details, please check templateParameters.

kind: tool
name: list_table
type: spanner
source: my-spanner-instance
statement: |
  SELECT * FROM {{.tableName}};
description: |
  Use this tool to list all information from a specific table.
  Example:
  {{
      "tableName": "flights",
  }}
templateParameters:
  - name: tableName
    type: string
    description: Table to select from

Reference