ELITEA Toolkit Guide: SQL Integration

Introduction

Purpose of this Guide

This guide is your definitive resource for integrating and utilizing the SQL toolkit within ELITEA. It provides a step-by-step walkthrough, from understanding SQL database prerequisites to configuring the toolkit in ELITEA and effectively using it within your Agents. By following this guide, you will unlock the power of automated data interaction, streamlined database workflows, and enhanced data-driven decision making, all directly within the ELITEA platform. This integration empowers you to leverage AI-driven automation to optimize your workflows that rely on structured data stored in SQL databases, using the combined strengths of ELITEA and SQL.

Brief Overview of SQL

SQL (Structured Query Language) is the standard language for interacting with relational database management systems (RDBMS). It's designed for managing and manipulating structured data held in databases. SQL provides a powerful and efficient way to:

Integrating SQL with ELITEA brings these robust data management capabilities directly into your AI-powered workflows. Your ELITEA Agents can then interact with your SQL databases to automate data-related tasks, enhance data processing, and improve data accessibility through intelligent automation.

SQL Database Prerequisites and Configuration

Preconditions

Before integrating your SQL database with ELITEA, ensure the following prerequisites are met:

  1. Running SQL Database Instance: You need an operational SQL database instance. This could be MySQL, PostgreSQL, SQL Server, or another supported SQL database. The database server must be running and accessible.
  2. Database Access Credentials: You must have valid credentials (username and password) that ELITEA can use to connect to your database.
  3. Database Server Accessibility: ELITEA needs to be able to communicate with your SQL server. Consider the following network accessibility requirements:
    • Public Accessibility (for externally hosted ELITEA): If your ELITEA instance is hosted externally (e.g., in the cloud) and your SQL server is on a private network, your SQL server must be accessible from the public internet or through a secure tunnel (like a VPN) that ELITEA can connect to. For locally hosted SQL servers, assigning a Public IP address might be necessary. Security Note: Exposing your database directly to the public internet is generally discouraged for security reasons. Consider using secure methods like VPNs or allowing access only from specific IP ranges.
    • Same Network Environment (for internally hosted ELITEA): If you are hosting ELITEA within the same network environment as your SQL server (e.g., behind a corporate firewall), ensure that there are no firewall rules blocking communication between the ELITEA instance and the SQL server on the designated port (e.g., 3306 for MySQL, 5432 for PostgreSQL).

Database Connection

This section describes how to connect to your database server using a database client application.

  1. Launch Your Database Client: Open either pgAdmin (for PostgreSQL), MySQL Workbench (for MySQL), or DBeaver (which supports multiple database types).

  2. Initiate a New Connection:

    • pgAdmin: Expand "Servers" in the object browser, right-click, and select "Create" -> "Server". A server creation dialog will appear.
    • MySQL Workbench: Navigate to "Database" -> "Manage Connections" -> "New". This opens the connection management window.
    • DBeaver: Go to "Database" -> "New Database Connection". A wizard for selecting the database type will appear.

  3. Configure the Connection Settings: Carefully enter the following information.

    • Host/Server Address: dbserver.company.com (This may also be an IP address like 192.168.1.100 if you know the server's IP.) This is the network address where your database server is located.
    • Port: The port number your database server is listening on. Use the correct port for your database type:
      • PostgreSQL: 5432
      • MySQL: 3306
      • SQL Server: 1433
    • Database Name: test_db (The specific database you want to access.)
    • Username: test_user (The username you've been assigned to access the database.)
    • Password: Test@123 (The password associated with the username.)

  4. Test the Connection: Click the "Test Connection" button (or its equivalent) within the connection configuration window.

    • Successful Test: You should receive a confirmation message. Proceed to step 5.
    • Unsuccessful Test: If the test fails, carefully review the following:
      • Typographical Errors: Double-check the host address, port, username, and password for any typos.
      • Server Availability: Ensure the database server is running and accessible from your network. (You may need to contact your system administrator.)
      • Firewall Rules: Confirm that your firewall is not blocking connections to the database server on the specified port.

  5. Establish the Connection: If the connection test was successful, click "OK" (or "Save") and then "Connect" to establish the connection to the database. You should now be able to browse the database schema and execute queries. Database Connection

SQL Integration with ELITEA

Overview of SQL Toolkit

The SQL integration within the ELITEA platform enhances data management by enabling seamless access to structured database information. This integration allows users to query, retrieve, and analyze SQL databases directly from ELITEA, ensuring that critical business data remains synchronized and readily available. By eliminating the need to switch between database management tools, teams can improve efficiency and maintain a single source of truth for key operational and analytical data. With this integration, users can pull real-time structured data from SQL databases into ELITEA, enhancing workflows with up-to-date records, analytical insights, and system-generated reports. The integration supports automated query execution, data visualization, ensuring that the latest database information is always accessible within the ELITEA environment.

Agent Creation/Configuration

To integrate SQL, you'll need to configure it within an ELITEA Agent. You can either create a new Agent or modify an existing one.

Creating a New Agent

To create a new agent within the system:

  1. Click the "+ Agent" Button: Locate and click the + Agent button, typically found in the top right corner of the application. This will initiate the agent creation process.

  2. Fill Out Basic Information (Name and Description):

  3. Name: Enter a descriptive and easily identifiable name for the agent in the "Name" field.

  4. Description: Provide a brief description of the agent's purpose and functionality in the "Description" field. This helps other users understand the agent's intended use.

  5. Add Tags (Optional): The "Tags" input box allows you to categorize and organize agents.

  6. Add a New Tag: Type the desired tag name in the "Tags" input box.

  7. Select from Existing Tags: If there are pre-existing tags, you can select them from a dropdown or list of available tags presented in the "Tags" input box. Using consistent tagging helps with searching and filtering agents.

  8. Select the Agent Type: Choose the appropriate agent type from the available options (e.g., OpenAI, ReAct, Pipeline).

  9. Instructions: Provide Instructions for selected Agent Type in the Instarction field.

  10. Configure Conversation Starters and Welcome Message (Optional):

  11. Conversation Starter: Define phrases or questions that users can click on to initiate a conversation with the agent. This provides users with guidance and examples of how to interact with the agent.
  12. Welcome Message: Create a welcome message that the agent displays when a user first interacts with it. This provides context and sets expectations for the interaction. A good welcome message might introduce the agent's purpose and capabilities. Agent Creation

Modifying an Existing Agent

To modify an existing agent within the system:

  1. Locate the Agent: Find the agent you want to modify in the agent list or using search.

  2. Open for Editing: Click the agent's name to open its configuration page.

  3. Modify Settings: Change the Name, Description, Tags, Agent Type, Instructions, Conversation Starters, or Welcome Message as needed.

  4. Save Changes: Click the "Save" button.

Toolkit Configuration

This section details how to configure the SQL toolkit within your ELITEA Agent.

  1. Add Toolkit: In the "Tools" section, click the "+" icon.
  2. Select SQL: Choose "SQL" from the dropdown list of available toolkits. This opens the "New SQL tool" configuration panel.

  3. Configure Toolkit Details: Fill in the following fields:

    • Name: Enter a Name for this SQL toolkit instance. Choose a descriptive name that helps you identify its purpose within your Agent's instructions (e.g., "DataQueryTool", "CustomerDatabase").
    • Description: Provide a Description for the toolkit, clarifying its purpose or the specific database it connects to (e.g., "Toolkit to query the main customer database").
    • Dialect: Select the SQL Dialect of your database from the dropdown menu. Choose either "MySQL" or "PostgreSQL" based on your database type.
    • Host: Enter the Hostname or IP Address of your SQL server. For example: dbserver.example.com or 192.168.1.100.
    • Port: Specify the Port Number used to connect to your SQL server. The default ports are:
      • PostgreSQL: 5432
      • MySQL: 3306
      • If your database uses a non-default port, enter that port number here.
    • Database name: Enter the Name of the Database you want to connect to. For example: mydatabase.
    • Username: Provide the Username for database authentication. This should be the username of the dedicated database user you configured for ELITEA.
    • Password: Choose the authentication method for the password:
      • Secret (Recommended): Select "Secret" and choose a pre-configured secret from the dropdown list. You must first create and securely store your database password as a Secret in ELITEA's Secret section. Using Secrets is highly recommended for enhanced security. Secrets
      • Password: Select "Password" and directly enter the Password associated with the provided username in the "Password" field. Caution: While convenient, directly entering the password is less secure than using Secrets.

  4. Enable Tools: In the "Tools" section of the SQL toolkit configuration, select the checkboxes next to the SQL tools you want to enable for your Agent. Enable only the tools your Agent will actually use to adhere to the principle of least privilege and enhance security.

  5. Complete Configuration: Click the arrow icon (at the top right of the toolkit configuration) to save the SQL toolkit setup and return to the main Agent configuration.
  6. Click Save to apply configuration and changes to the Agent. SQL Tool configuration

NOTE: Database Server Accessibility: Elitea must be able to communicate with your SQL server. Please ensure the following requirements are met:

Tool Overview

The SQL toolkit provides the following tools for ELITEA SQL Agents:

Instructions and Prompts for Using the Toolkit

To instruct your ELITEA Agent to use the SQL toolkit, you need to provide clear instructions within the Agent's "Instructions" field. These instructions guide the Agent on when and how to use the available tools.

Instruction Creation for OpenAI Agents

When creating instructions for the SQL toolkit for OpenAI-based Agents, focus on clear, action-oriented language. Break down tasks into simple steps and explicitly state the parameters required for each tool. OpenAI Agents respond best to instructions that are:

When instructing your Agent to use a SQL toolkit, use this pattern:

1. Identify the goal: [State the objective, e.g., "To list all tables in the database"].
2. Tool Selection: Use the "[tool_name]" tool.
3. Parameter Specification: Provide the following parameters:
    - Parameter Name 1: <value or description of value>
    - Parameter Name 2: <value or description of value>
    - ...
4. Expected Outcome: [Optionally, describe what should happen after the tool is used].

Example Agent Instructions for SQL Toolkit Tools (OpenAI Agent Friendly):

1. Goal: To get a list of all tables and their columns in the database.
2. Tool: Use the "list_tables_and_columns" tool.
3. Parameters:
    - Include Data Types: "true" (to include data types in the output)
    - Output Format: "table" (to display output in tabular format)
4. Outcome: The Agent will list all tables and their columns, including data types, in a table format.

1. Goal: To retrieve the names and emails of all users from the 'users' table.
2. Tool: Use the "execute_sql" tool.
3. Parameters:
    - Query: "SELECT name, email FROM users;"
    - Output Format: "JSON" (to get output in JSON format)
    - Limit: "100" (to limit the output to the first 100 rows)
4. Outcome: The Agent will execute the SQL query and return a JSON formatted list of user names and emails, limited to 100 entries.

Conversation Starters

Use these conversation starters to interact with your SQL-integrated Agent.

1. For Testing and Troubleshooting Connection:

These starters are designed to verify if the SQL toolkit is correctly configured and connected to your database.

2. For General Agent Usage Scenarios:

These starters demonstrate how to initiate agent execution for common SQL-related tasks.

These conversation starters provide a starting point for interacting with your SQL-integrated ELITEA Agent and can be customized further based on your specific use cases and workflows.

Use Cases

The SQL toolkit unlocks numerous automation possibilities for data-driven workflows within ELITEA. Here are some key use cases, demonstrating how each tool can be applied:

Troubleshooting and Support

Troubleshooting

FAQ

  1. Q: Can I use this toolkit with other SQL databases besides MySQL and PostgreSQL?
    • A: Currently, the ELITEA SQL toolkit officially supports MySQL and PostgreSQL dialects. Support for other SQL databases might be added in future updates. If you require integration with a different SQL database, please contact ELITEA support to discuss your needs.
  2. Q: Why can't Elitea connect to my SQL server?
    • A: Elitea requires direct access to your SQL server. If you're experiencing connectivity issues, check the following:
      1. Public IP: Ensure that your local-hosted SQL server has a Public IP address if it needs to be accessed from outside your local network. Without this, ELITEA cannot initiate a connection to your database.
      2. Network Environment: If your SQL server operates within a closed network or a VPN-restricted environment, deploy ELITEA within the same network. This ensures that ELITEA can reach the SQL server without external network barriers.

Support Contact

For any issues, questions, or further assistance with the SQL integration or ELITEA Agents, please reach out to our dedicated ELITEA Support Team. We are committed to providing prompt and effective support to ensure your success with ELITEA.

Contact ELITEA Support:

Best Practices for Effective Support Requests:

To help us understand and resolve your issue as quickly as possible, please ensure you provide the following information in your support email:

Before Contacting Support:

We encourage you to first explore the resources available within this guide and the broader ELITEA documentation. You may find answers to common questions or solutions to known issues in the documentation.

To further enhance your understanding and skills in integrating SQL with ELITEA, here are some helpful resources: