# Text to SQL with Supabase ## What is Supabase? Supabase is an open-source platform service designed to simplify the development workflow of modern applications. Its core features include: * **Database:** Can store various types of data. * **Real-time Updates:** Your application is immediately notified when data changes. * **Account Management:** Manages user accounts and passwords for you. * **Built-in Authentication Mechanism:** Built-in authentication mechanism simplifies user identity management processes and provides multiple authentication methods. * **Auto-generated API (i.e., data access paths):** Allows you to retrieve data from the database in a simple way. ### What are the benefits of integrating Supabase? * **Cross-query multiple reports:** Supabase supports multiple tables that are interrelated, allowing queries from value A in report A to find its relationship with report B * **Create indexes to improve query efficiency:** You can create indexes for frequently queried content, allowing the AI assistant to find the content you need more precisely ## Create Your Supabase ### 1. Create a Supabase Account * First, visit the [Supabase official website](https://supabase.com/) and click "Sign in / Start your project". > If you haven't registered yet, please register an account first to proceed with the following steps

* After logging in, you can create a new organization or use an existing organization for operations. Within the organization, create a project - each project will have its own independent database.

### 2. Enter the Database Page After entering the project, click on Database > Tables in the left navigation menu to add data

#### Create a New Table Click "New Table" to create a new table and name it:

Supabase provides multiple ways to create new tables:

* **Manually add columns:** Suitable for designing table structure from scratch. You can add columns one by one and set data type, default value, etc. for each column. * **Import .csv/.tsv or plain text:** Suitable for quickly creating tables, especially when you already have existing data. * **Notes:** * The first row of a plain text file must be the column names, with columns separated by commas (CSV) or tabs (TSV, which is the space size when pressing the Tab key on the keyboard). Here, choose to import data: Click "Import data from CSV" and paste a tab-separated text file. Scroll down to see the results formatted as a table. After import is complete, click "Save"

#### Primary Key After import is complete, you will be redirected to the settings page. At this point, you must specify a primary key. The primary key is like an ID number, serving as a unique value to identify each piece of data. Here, we choose the customer number as the primary key.

#### Foreign Key Scrolling down, you can see the Foreign key designation. A Foreign Key is like an address that can correspond to the source location of this data or other more detailed information.

Suppose we have two tables: "Customers" table and "Orders" table. * **Customers Table:** * CustomerID - Primary Key * CustomerName * Phone * Address * **Orders Table:** * OrderID - Primary Key * CustomerID - Foreign Key (references CustomerID in Customers table) * OrderDate * TotalAmount In this example, "CustomerID" in the "Orders" table is a foreign key that references the primary key "CustomerID" in the "Customers" table. Through this foreign key, we can identify which customer placed each order. {% hint style="info" %} In this example, the foreign key should be placed in the Order Table: **Relationship Direction:** * One customer → can have multiple orders (one-to-many relationship) * One order → belongs to only one customer **Foreign Key Principle:** > Foreign keys should be placed on the "many" side Therefore: * ✅ Set `customer_id` (foreign key) in the Order Table, as it corresponds to the "many" side in the one-to-many relationship * ❌ Customer Table doesn't need to store order information {% endhint %} Therefore, in the Order Table, we set up a link where Customer ID in the Customer Table corresponds to Customer ID in the Order Table.

After the relationship is complete, click "Save" to establish the database relationship. ### 3. Creation Complete After the table is created, you have a complete database and can query data within the database using SQL syntax!

## How to Create a Supabase Tool To use the Supabase tool in MaiAgent, you need to create it as an MCP tool so that the AI assistant can use Supabase functionality: {% hint style="info" %} For tool introduction, please refer to: [Tool Function Overview](https://docs.maiagent.ai/maiagent-user-guide/maiagent-user-guide-en/tools/tool_description) {% endhint %} {% stepper %} {% step %} **Create a Server on the MCP Service Platform and Connect with Supabase Service** For how to connect MCP tools, please refer to: [Remote MCP Service Overview](https://docs.maiagent.ai/tech/remote-mcp/remote-mcp). Currently, only the [Composio platform](https://docs.maiagent.ai/tech/remote-mcp/composio) supports Supabase platform connection {% endstep %} {% step %} **Enable Available Functions in Composio** When connecting to Composio, please enable basic database operations such as add, delete, query, etc. {% hint style="warning" %} Composio has pre-selected basic content in Important, you don't need to configure it additionally, just make sure the Important option is checked {% endhint %}

{% endstep %} {% step %} **Add the Built Supabase Tool to the Available Tools List** For how to create MCP tools, please refer to: [Creating MCP Tools](https://docs.maiagent.ai/maiagent-user-guide/maiagent-user-guide-en/tools/setup) **Supabase Tool Connection URL** After creating the server service on the MCP service platform, please process the URL as follows: 1. Original URL (obtained from MCP Server):\ [https://backend.composio.dev/v3/mcp/12345678/mcp?include\_composio\_helper\_actions=true](https://backend.composio.dev/v3/mcp/5987158a-806a-4c32-9ff6-4236e8891ac2/mcp?include_composio_helper_actions=true) 2. Delete "[?include\_composio\_helper\_actions=true](https://backend.composio.dev/v3/mcp/5987158a-806a-4c32-9ff6-4236e8891ac2/mcp?include_composio_helper_actions=true)" directly 3. New URL (to be pasted on the MaiAgent tools page):\ [https://backend.composio.dev/v3/mcp/12345678/mcp](https://backend.composio.dev/v3/mcp/5987158a-806a-4c32-9ff6-4236e8891ac2/mcp?include_composio_helper_actions=true) {% hint style="danger" %} Please be sure to delete the above content, otherwise the AI assistant will not be able to use the supabase tool correctly {% endhint %} {% endstep %} {% endstepper %} After the tool is created, please go to the AI assistant settings and add the Supabase tool to the assistant's available tools list:

{% hint style="danger" %} Please click Save on the tool usage page, otherwise the AI assistant still cannot use the Supabase tool {% endhint %} ## Effects of Using the Supabase Tool Through the MaiAgent AI assistant combined with the Supabase tool, you only need to use everyday language to describe the data you want to query, and Supabase will automatically generate the corresponding SQL syntax and extract the required information from the relational database. ### Example Database #### 🏢 **1. Customers Table (Customer Information)**

Column Name	Primary Key	Required	Description
`CustomerID`	✅	✅	Customer unique identifier
`CustomerName`	❌	❌	Customer company or personal name
`CustomerType`	❌	❌	Customer classification (e.g., retailer, restaurant, distributor)
`ContactName`	❌	❌	Primary contact name
`Phone`	❌	❌	Contact phone number
`Email`	❌	❌	Email address
`Address`	❌	❌	Customer address
`Region`	❌	❌	Geographic region (e.g., North, South)
`CustomerLevel`	❌	❌	Customer importance level (A, B, C tier)

**🔑 Primary Key**: `CustomerID`\ **🔗 Foreign Key Relationships**: None *** #### 📦 **2. Orders Table (Order Information)**

Column Name	Primary Key	Required	Description
`OrderID`	✅	✅	Order unique identifier
`CustomerID`	❌	❌	Links to Customers table
`OrderDate`	❌	❌	Order creation date
`DeliveryDate`	❌	❌	Expected or actual delivery date
`PaymentMethod`	❌	❌	Payment method (cash, credit card, wire transfer)
`OrderStatus`	❌	❌	Order processing status
`TotalAmount`	❌	❌	Order total amount (numeric type)
`ShippingFee`	❌	❌	Shipping cost

**🔑 Primary Key**: `OrderID`\ **🔗 Foreign Key Relationships**: * `CustomerID` → `Customers.CustomerID` (CustomerID in Orders corresponds to CustomerID in the Customer table) *** #### 🛍️ **3. Products Table (Product Information)**

Column Name	Primary Key	Required	Description
`ProductID`	✅	✅	Product unique identifier
`ProductName`	❌	❌	Product name
`Description`	❌	❌	Detailed product description
`Category`	❌	❌	Product category
`Brand`	❌	❌	Product brand
`Size`	❌	❌	Product specification or size
`Cost`	❌	❌	Product cost (numeric type)
`Price`	❌	❌	Product selling price (numeric type)
`StockQuantity`	❌	❌	Current stock quantity (numeric type)

**🔑 Primary Key**: `ProductID`\ **🔗 Foreign Key Relationships**: None ### Scenario 1: Tracking Incomplete Orders * **Database State:** The `Orders` table has two incomplete orders, `OR003` and `OR004`.

* **Natural Language Input:** In the AI assistant's Q\&A, you only need to enter: "Please tell me what incomplete orders there are". The AI assistant automatically invokes the tool and generates an SQL structured query statement.

* **Supabase Automatic Query:** The AI assistant will automatically invoke the Supabase tool, converting your natural language into an SQL query statement, for example:

Combining the query results with the AI assistant's analysis, the AI assistant will respond with the following order content, sorted by priority:

Through the collaboration of the Supabase tool and the AI assistant, you can easily track incomplete orders and receive analysis and sorting suggestions provided by the AI assistant to process orders more effectively and improve customer satisfaction. ### Scenario 2: Querying Customer Contact Information for Incomplete Orders * OR003 is an order still being processed, the customer is CU003, and the contact person is **Huang Purchasing**

* **Natural Language Input:** In the AI assistant's Q\&A, enter: "For orders still being processed, who should I contact". The AI assistant automatically invokes the tool and generates an SQL structured query statement.

* **Supabase Automatic Corresponding Query:** You can see that although the conversation content with the AI assistant all relates to the order table, through the set foreign key correspondence relationship, supabase can know that the `Customer ID` here corresponds to the `ID` query in the `Customers` table, so the returned content is from the `Customers` table.

* **AI Assistant Response:** The AI assistant then comprehensively analyzes and responds with the correct contact information and other contact methods

Through the Supabase tool, you can fully utilize the table correspondence relationships in the database, easily extract information from multiple related tables, and receive a list of customer names provided by the AI assistant. The correspondence relationships between tables and clear definitions ensure data relevance and consistency, making query results more reliable. ## **Additional Notes:** * You can adjust the natural language input according to actual needs, for example: "Please tell me what incomplete orders there are today", "Please tell me what incomplete orders VIP customers have", etc. The Supabase tool can accurately parse and execute queries. * The AI assistant can further integrate other information, such as: inventory status, logistics information, etc., to provide more comprehensive order analysis. {% hint style="warning" %} The tool can only call content you store in the database. If you have analysis needs, please be sure to upload the data to the database before starting analysis. {% endhint %}