genai-toolbox/internal/prebuiltconfigs/tools/sqlite.yaml

# Copyright 2025 Google LLC
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
#     http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.

sources:
  sqlite-source:
    kind: sqlite
    database: ${SQLITE_DATABASE}
tools:
  execute_sql:
    kind: sqlite-execute-sql
    source: sqlite-source
    description: Use this tool to execute SQL.
  list_tables:
    kind: sqlite-sql
    source: sqlite-source
    description: "Lists SQLite tables. Use 'output_format' ('simple'/'detailed') and 'table_names' (comma-separated or empty) to control output."
    statement: |
      WITH table_columns AS (
        SELECT
          m.name AS table_name,
          json_group_array(json_object('column_name', ti.name, 'data_type', ti.type, 'ordinal_position', ti.cid, 'is_not_nullable', ti."notnull" = 1, 'column_default', ti.dflt_value, 'is_primary_key', ti.pk > 0)) AS details
        FROM sqlite_master AS m, pragma_table_info(m.name) AS ti
        WHERE m.type = 'table' AND m.name NOT LIKE 'sqlite_%'
        GROUP BY m.name
      ),
      table_constraints AS (
        SELECT
          table_name,
          json_group_array(json(details)) AS details
        FROM (
          SELECT m.name AS table_name, json_object('constraint_name', 'PRIMARY', 'constraint_type', 'PRIMARY KEY', 'constraint_columns', json_group_array(T.name)) AS details
          FROM sqlite_master AS m, pragma_table_info(m.name) AS T
          WHERE m.type = 'table' AND T.pk > 0
          GROUP BY m.name
          HAVING COUNT(T.name) > 0
          UNION ALL
          SELECT m.name, json_object('constraint_name', 'fk_' || m.name || '_' || F.id, 'constraint_type', 'FOREIGN KEY', 'constraint_columns', json_group_array(F."from"), 'foreign_key_referenced_table', F."table", 'foreign_key_referenced_columns', json_group_array(F."to"))
          FROM sqlite_master AS m, pragma_foreign_key_list(m.name) AS F
          WHERE m.type = 'table'
          GROUP BY m.name, F.id
          UNION ALL
          SELECT m.name, json_object('constraint_name', I.name, 'constraint_type', 'UNIQUE', 'constraint_columns', (SELECT json_group_array(C.name) FROM pragma_index_info(I.name) AS C ORDER BY C.seqno))
          FROM sqlite_master AS m, pragma_index_list(m.name) AS I
          WHERE m.type = 'table' AND I."unique" = 1 AND I.origin != 'pk'
        )
        GROUP BY table_name
      ),
      table_indexes AS (
        SELECT
          m.name AS table_name,
          json_group_array(json_object('index_name', il.name, 'is_unique', il."unique" = 1, 'is_primary', il.origin = 'pk', 'index_columns', (SELECT json_group_array(ii.name) FROM pragma_index_info(il.name) AS ii))) AS details
        FROM sqlite_master AS m, pragma_index_list(m.name) AS il
        WHERE m.type = 'table' AND m.name NOT LIKE 'sqlite_%'
        GROUP BY m.name
      ),
      table_triggers AS (
        SELECT
          tbl_name AS table_name,
          json_group_array(json_object('trigger_name', name, 'trigger_definition', sql)) AS details
        FROM sqlite_master
        WHERE type = 'trigger'
        GROUP BY tbl_name
      )
      SELECT
        CASE
          WHEN '{{.output_format}}' = 'simple' THEN json_object('name', m.name)
          ELSE json_object(
            'schema_name', 'main',
            'object_name', m.name,
            'object_type', m.type,
            'columns', json(COALESCE(tc.details, '[]')),
            'constraints', json(COALESCE(tcons.details, '[]')),
            'indexes', json(COALESCE(ti.details, '[]')),
            'triggers', json(COALESCE(tt.details, '[]'))
          )
        END AS object_details
      FROM
        sqlite_master AS m
      LEFT JOIN table_columns tc ON m.name = tc.table_name
      LEFT JOIN table_constraints tcons ON m.name = tcons.table_name
      LEFT JOIN table_indexes ti ON m.name = ti.table_name
      LEFT JOIN table_triggers tt ON m.name = tt.table_name
      WHERE
        m.type = 'table'
        AND m.name NOT LIKE 'sqlite_%'
        {{if .table_names}}
        AND instr(',' || '{{.table_names}}' || ',', ',' || m.name || ',') > 0
        {{end}};
    templateParameters:
      - name: output_format
        type: string
        description: "Optional: Use 'simple' to return table names only or use 'detailed' to return the full information schema."
        default: "detailed"
      - name: table_names
        type: string
        description: "Optional: A comma-separated list of table names. If empty, details for all tables in user-accessible schemas will be listed."
        default: ""
toolsets:
  sqlite_database_tools:
    - execute_sql
    - list_tables