> For the complete documentation index, see [llms.txt](https://docs.maiagent.ai/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.maiagent.ai/tech/en/advanced-genai-tech/text-to-sql.md). # Text to SQL Text to SQL technical specifications: supported databases and connection string formats, read-only protection, network and account requirements, handling large schemas, and query result limits. Text to SQL lets an Agent turn a user's natural language question into SQL, query a connected database, and answer based on the results. This page provides specifications for database administrators and technical staff. For steps to create a database connection and associate it with an Agent, see [Text to SQL](https://docs.maiagent.ai/build/database/text2sql) and [Create and Configure a Database](https://docs.maiagent.ai/build/database/create-database) in the user guide.
## How It Works Text to SQL is enabled only when the Agent uses **Agent mode**. The Agent calls the following tools step by step as needed: 1. List databases available for queries (an Agent can be associated with multiple databases). 2. List queryable tables and views in a database, along with the table descriptions you provide. 3. Read the column structure and column descriptions for a specific table. 4. Execute an SQL query and answer using the results. Each query returns at most the number of rows configured on the platform (1,000 by default). If the result exceeds this limit, only the initial rows are returned, and the model is informed that the result has been truncated. Temporary errors such as connection interruptions are retried automatically. SQL syntax or permission errors are passed directly to the model so it can correct the query. ## Supported Databases | Type | Connection String Format | Description | | -------------------- | -------------------------------------------------------------------------------------------------------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | MaiAgent Database | Not required | Upload spreadsheets or other files to create tables managed by the platform. See [Text to SQL with MaiAgent Database](https://docs.maiagent.ai/build/database/text-to-sql-maiagent) in the user guide | | PostgreSQL | `postgresql://username:password@host:port/database` | The `postgres://` prefix is also accepted | | MySQL | `mysql+pymysql://username:password@host:port/database` | | | Microsoft SQL Server | `mssql+pyodbc://username:password@host:port/database?driver=ODBC+Driver+18+for+SQL+Server&TrustServerCertificate=yes&Encrypt=no` | The platform includes ODBC Driver 18 for SQL Server | | Oracle | `oracle+oracledb://username:password@host:port/?service_name=service_name` | Supports Oracle Database 12.1 and later. For 11g, Oracle Client must also be enabled on the platform; contact MaiAgent | | SynxDB | `postgresql://username:password@host:port/database` | PostgreSQL-compatible MPP data warehouse | The connection string prefix must match the selected type, or the settings cannot be saved. During creation, click Test Connection to confirm that the platform can connect to the database. ## Connection Security ### Read-Only Protection The platform restricts queries to read operations at two levels: * **Syntax checks**: Only `SELECT` is allowed (including read syntax such as `WITH` and `UNION`). `INSERT`, `UPDATE`, `DELETE`, `MERGE`, `CREATE`, `ALTER`, `DROP`, `TRUNCATE`, `SELECT … INTO`, and multiple statements joined by semicolons are blocked. * **Read-only transactions**: PostgreSQL, MySQL, and Oracle queries run in read-only transactions, so the database itself rejects writes. SQL Server has no equivalent read-only session setting. Instead, the platform always rolls back after a query and never commits, which provides weaker protection. {% hint style="warning" %} **Always connect with a read-only account.** The platform's protection is a second line of defense. Granting the database account read permissions only on the required tables or views provides the most direct control, especially for SQL Server. {% endhint %} ### Accounts and Connection Strings * Create a dedicated account for MaiAgent and grant it only `SELECT` permissions on the tables or views it needs to query. * Connection strings contain a username and password. After saving, only masked content is displayed when they are read. To change a connection string, enter the complete string again. * Who can associate a database with an Agent and which roles can use it depend on the database's role permissions. See [Create and Configure a Database](https://docs.maiagent.ai/build/database/create-database). ### Network Requirements * The database must be reachable from the MaiAgent platform: the platform initiates connections to the database host and port (the address in the connection string). A connection is established when you click Test Connection and for each query. * The port is determined by the connection string. Default ports are PostgreSQL 5432, MySQL 3306, SQL Server 1433, Oracle 1521, and SynxDB 5432 (depending on deployment settings). * **SaaS**: The database must allow connections from the MaiAgent platform's outbound addresses. Contact MaiAgent for the outbound IP addresses when configuring a firewall allowlist. * **On-premises and private cloud**: The platform and database must be on the same network segment or connected through a VPN or similar method. ## Handling Large Schemas The platform does not send the entire schema to the model at once. The model first receives the list of tables and their descriptions, then reads individual table column structures as needed. If there are many tables: * **Narrow the scope with ****Specify Tables**: Expose only the tables needed to answer questions. If left blank, all tables and views in the default schema can be queried. * **Include the schema name for tables in other schemas**: For example, `sales.orders`. The platform scans only the default schema and schemas mentioned in the specified tables. * **Fill in ****Table Structure Description**: Explain each table's purpose and the meaning of its columns to help the model select the correct tables and columns. * **Create views in advance**: Encapsulate common joins (JOIN) and business logic in views, then expose only those views to reduce both query errors and permission risks. ## Frequently Asked Questions
Why does the connection test fail? Check in order that the connection string prefix matches the selected type, the host and port are correct, the database allows connections from the MaiAgent platform's addresses (for SaaS, contact MaiAgent for outbound IP addresses), and the username and password are correct. Oracle 11g requires additional platform configuration; contact MaiAgent.
How can I use the API to get the row count of a table? The database API (`/api/v1/sql-databases/…`) only manages connection, table, and column settings. The `count` in the table list response is the paginated table count, not the number of data rows. There is no endpoint that returns row counts or executes SQL. To find the row count, ask the Agent in a conversation (for example, “How many rows are in the orders table?”). The model generates a `SELECT COUNT(*)` query and answers. Alternatively, connect directly to your database and query it.
Can an Agent modify data through Text to SQL? No. The platform allows only read queries and blocks syntax for writes and structural changes. The `POST` / `PUT` / `PATCH` endpoints for databases, tables, and columns in the API documentation manage connection settings, table and column descriptions, and uploading spreadsheets as tables. They do not write to or modify rows in existing tables, and the Agent in a conversation cannot call these endpoints.
Does Query Metadata restrict Text to SQL? No. Query Metadata applies only to knowledge base retrieval. The Agent does not consult it when generating or executing SQL, and there are no fields for restricting tables or rows on a per-request basis. The only controls that narrow database access are whether the database is enabled, whether it is associated with this Agent, Specify Tables, and the database account's own permissions (including views). To give different identities access to different data, use separate Agents with different connections or read-only accounts.
Why are database settings missing from the Agent settings page? Database settings are available only in Agent mode. Select Agent mode in the Agent's reply mode settings first.
--- # Agent Instructions This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com. ## Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation by asking a question. Perform an HTTP GET request on the following URL with the `ask` and `goal` query parameters: ``` GET https://docs.maiagent.ai/tech/en/advanced-genai-tech/text-to-sql.md?ask=&goal= ``` `ask` is the immediate question: it should be specific, self-contained, and written in natural language. `goal` is what the user is ultimately trying to achieve, the reason they need the answer. Sharing it helps GitBook give you a better, more relevant answer. A goal is most helpful when it describes the outcome the user wants rather than restating the question. For example, with `ask=how do I create an API token`, a goal like `automate deployments from our CI pipeline` lets GitBook tailor the answer to that use case. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.