1  ---
  2  title: SQL Database Integration
  3  sidebar_position: 40
  4  ---
  5  
  6  # SQL Database Integration
  7  
  8  This tutorial sets up a SQL database agent in Agent Mesh, which allows the Agent Mesh agent to answer natural language queries about a sample coffee company database. This tutorial provides some sample data to set up an SQLite database, but you can use the same approach to connect to other database types, such as MySQL or PostgreSQL.
  9  
 10  ## Prerequisites
 11  
 12  Before starting this tutorial, ensure that you have installed and configured Agent Mesh:
 13  
 14  - [Installed Agent Mesh and the Agent Mesh CLI](../../installing-and-configuring/installation.md)
 15  - [Created a new Agent Mesh project](../../installing-and-configuring/run-project.md)
 16  - Access to a SQL database (local or remote)
 17  
 18  ## Adding the SQL Database Plugin
 19  
 20  Add the SQL Database plugin to your Agent Mesh project:
 21  
 22  ```sh
 23  sam plugin add abc-coffee-info --plugin sam-sql-database
 24  ```
 25  You can use any name for your agent, in this tutorial we use `abc-coffee-info`.
 26  
 27  This command:
 28  - Installs the `sam-sql-database` plugin
 29  - Creates a new agent configuration file at `configs/agents/abc-coffee-info.yaml`
 30  
 31  ## Downloading Example Data
 32  
 33  For this tutorial, you can use a sample SQLite database for a fictional coffee company called ABC Coffee Co. 
 34  
 35  First, download the example data.
 36  
 37  You can either visit this link to download with your browser:
 38  
 39    https://github.com/SolaceLabs/solace-agent-mesh-core-plugins/raw/refs/heads/main/sam-sql-database/example-data/abc_coffee_co.zip
 40  
 41  Or you can use the command line to download the ZIP file:
 42  
 43  #### Using wget
 44  ```sh
 45  wget https://github.com/SolaceLabs/solace-agent-mesh-core-plugins/raw/refs/heads/main/sam-sql-database/example-data/abc_coffee_co.zip
 46  ```
 47  
 48  #### Using curl
 49  ```sh
 50  curl -LO https://github.com/SolaceLabs/solace-agent-mesh-core-plugins/raw/refs/heads/main/sam-sql-database/example-data/abc_coffee_co.zip
 51  ```
 52  
 53  After downloading the ZIP file, extract it to a directory of your choice. You can use the following command to extract the ZIP file:
 54  
 55  ```sh
 56  unzip abc_coffee_co.zip
 57  ```
 58  
 59  ## Configuring the Agent
 60  
 61  Now, update the agent configuration to use the SQLite database and import the CSV files.
 62  Open the `configs/agents/abc-coffee-info.yaml` file and modify the `agent_init_function.config` section to specify the CSV directory.
 63  
 64  Here is what you need to modify in the configuration file:
 65  
 66  ```yaml
 67  # Find the agent_init_function section and update the config:
 68  agent_init_function:
 69    module: "sam_sql_database.lifecycle"
 70    name: "initialize_sql_agent"
 71    config:
 72      db_type: "${ABC_COFFEE_INFO_DB_TYPE}"
 73      db_name: "${ABC_COFFEE_INFO_DB_NAME}"
 74      database_purpose: "${ABC_COFFEE_INFO_DB_PURPOSE}"
 75      data_description: "${ABC_COFFEE_INFO_DB_DESCRIPTION}"
 76      # Add the CSV directory path
 77      csv_directories:
 78        - /path/to/your/unzipped/data
 79  ```
 80  
 81  Ensure you replace `/path/to/your/unzipped/data` with the path where you extracted the example data. For example, if you put the ZIP file in the root directory of your Agent Mesh project, you can use `abc_coffee_co`.
 82  
 83  ## Setting the Environment Variables
 84  
 85  The SQL Database agent requires that you configure several environment variables. You must create or update your `.env` file with the following variables for this tutorial:
 86  
 87  ```bash
 88  ABC_COFFEE_INFO_DB_TYPE=sqlite
 89  ABC_COFFEE_INFO_DB_NAME=abc_coffee.db
 90  ABC_COFFEE_INFO_DB_PURPOSE="ABC Coffee Co. sales and operations database"
 91  ABC_COFFEE_INFO_DB_DESCRIPTION="Contains information about ABC Coffee Co. products, sales, customers, employees, and store locations."
 92  # You can leave other environment variables as unset or empty
 93  ```
 94  
 95  SQLite stores the database in a local file and doesn't require a username or password for access. If you're using a database such as MySQL or PostgreSQL, you'll need to provide the appropriate environment variables for them.
 96  
 97  ## Running the Agent
 98  
 99  Now, you can start your SQL database agent:
100  
101  ```sh
102  sam run configs/agents/abc-coffee-info.yaml
103  ```
104  
105  The agent:
106  1. Connects to the A2A control plane
107  2. Initializes the SQLite database and imports CSV data from the specified directory
108  3. Detects the database schema automatically
109  4. Registers its capabilities with the agent discovery system
110  
111  ## Interacting with the Database
112  
113  After your SQL database agent is running, you can interact with the ABC Coffee database through any gateway in your Agent Mesh project (such as the Web UI gateway at `http://localhost:8000`).
114  
115  You can ask natural language questions about the ABC Coffee Co. database, such as:
116  
117  - "How many customers does ABC Coffee have?"
118  - "What are the top-selling products?"
119  - "Show me the sales by region"
120  - "List all orders from the last 30 days"
121  - "What products are currently low in inventory?"
122  
123  Try creating reports by asking questions such as:
124  
125  - "Create a report of our sales in 2024"
126  - "Generate a summary of customer demographics"
127  
128  The SQL Database agent converts your natural language questions into SQL queries, executes them against the database, and returns the results. For large result sets, the agent automatically saves the results as artifacts that you can download.
129  
130  ## Advanced Configuration
131  
132  The SQL Database plugin supports many advanced configuration options. Here is a complete example based on the plugin structure:
133  
134  ```yaml
135  log:
136    stdout_log_level: INFO
137    log_file_level: DEBUG
138    log_file: abc-coffee-info.log
139  
140  !include ../shared_config.yaml
141  
142  apps:
143    - name: abc-coffee-info-app
144      app_module: solace_agent_mesh.agent.sac.app 
145      broker:
146        <<: *broker_connection
147  
148      app_config:
149        namespace: ${NAMESPACE}
150        agent_name: "AbcCoffeeInfo"
151        display_name: "ABC Coffee Database Agent"
152        supports_streaming: false
153        model: *general_model
154        model_provider: 
155          - "general"
156  
157        instruction: |
158          You are an expert SQL assistant for the ABC Coffee Co. database.
159          The database schema and query examples are provided to you.
160          Your primary goal is to translate user questions into accurate SQL queries.
161          If a user asks to query the database, generate the SQL and call the 'execute_sql_query' tool.
162          If the 'execute_sql_query' tool returns an error, analyze the error message and the original SQL,
163          then try to correct the SQL query and call the tool again.
164          If the results are large and the tool indicates they were saved as an artifact, inform the user about the artifact.
165          Always use the 'execute_sql_query' tool to interact with the database.
166  
167        # Agent initialization with database setup
168        agent_init_function:
169          module: "sam_sql_database.lifecycle"
170          name: "initialize_sql_agent"
171          config:
172            db_type: "${ABC_COFFEE_INFO_DB_TYPE}"
173            db_name: "${ABC_COFFEE_INFO_DB_NAME}"
174            database_purpose: "${ABC_COFFEE_INFO_DB_PURPOSE}"
175            data_description: "${ABC_COFFEE_INFO_DB_DESCRIPTION}"
176            auto_detect_schema: true
177            csv_directories:
178              - "abc_coffee_co"  # Path to your extracted data
179            query_examples:
180              - natural_language: "Show all customers from New York"
181                sql_query: "SELECT * FROM customers WHERE city = 'New York';"
182              - natural_language: "What are the top 5 best-selling products?"
183                sql_query: "SELECT product_name, SUM(quantity) as total_sold FROM order_items JOIN products ON order_items.product_id = products.id GROUP BY product_name ORDER BY total_sold DESC LIMIT 5;"
184  
185        agent_cleanup_function:
186          module: "sam_sql_database.lifecycle"
187          name: "cleanup_sql_agent_resources"
188  
189        # SQL query tool
190        tools:
191          - tool_type: python
192            component_module: "sam_sql_database.tools"
193            function_name: "execute_sql_query"
194  
195        session_service: *default_session_service
196        artifact_service: *default_artifact_service
197  
198        # Agent capabilities - This is what other agents see during discovery
199        agent_card:
200          description: "ABC Coffee Co. Database Agent - Access to comprehensive coffee shop data including customers, orders, products, inventory, and sales history. Can answer questions about business metrics, customer analytics, product performance, and operational data."
201          defaultInputModes: ["text"]
202          defaultOutputModes: ["text", "file"]
203          skills:
204            - id: "sql_query"
205              name: "Coffee Shop Database Query"
206              description: "Queries ABC Coffee Co. database containing customer orders, product catalog, inventory levels, sales history, and employee data."
207  
208        # A2A Protocol settings
209        agent_card_publishing: { interval_seconds: 30 }
210        agent_discovery: { enabled: true }
211        inter_agent_communication:
212          allow_list: ["*"]
213          request_timeout_seconds: 60
214  ```
215  
216  ## Customizing the Agent Card
217  
218  The `agent_card` section is crucial as it defines how other agents in your Agent Mesh ecosystem discover and understand this database agent's capabilities. When other agents use agent discovery, they can see this information to decide whether to delegate tasks to your database agent.
219  
220  ### Key Agent Card Elements
221  
222  1. **Description**: Clearly describe what data the agent has access to and what types of questions it can answer
223  2. **Skills**: List specific capabilities with concrete examples that show the scope of data available
224  3. **Data Context**: Mention the business domain, data types, and scope of information available
225  
226  ### Example of a Well-Configured Agent Card
227  
228  ```yaml
229  agent_card:
230    description: "ABC Coffee Co. Database Agent - Access to comprehensive coffee shop data including customers, orders, products, inventory, and sales history. Can answer questions about business metrics, customer analytics, product performance, and operational data."
231    defaultInputModes: ["text"]
232    defaultOutputModes: ["text", "file"]
233    skills:
234      - id: "sql_query"
235        name: "Coffee Shop Database Query"
236        description: "Queries ABC Coffee Co. database containing customer orders, product catalog, inventory levels, sales history, and employee data."
237  ```
238  
239  This detailed information helps other agents understand:
240  - What business domain this agent covers (coffee shop operations)
241  - What types of data are available (customers, orders, products, inventory, sales)
242  - What kinds of questions can be answered (metrics, analytics, performance data)
243  - Specific examples of queries that work well
244  
245  When configuring your own database agent, customize the description and examples to match your specific data and use cases.