> 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/maiagent-user-guide/en/build/database/create-database.md).

# Create and Configure a Database

Set up database Q\&A (Text-to-SQL / Text2SQL) by uploading files to create tables, connecting external databases, and adding column descriptions so Agents can query data in natural language.

MaiAgent's **Database Management** feature lets you centrally manage the database connections used by your Agents. With Text to SQL, Agents can query data in your databases directly using natural language.

{% hint style="info" %}
For an introduction to Text to SQL, see: [Text to SQL](/maiagent-user-guide/en/build/database/text2sql.md)
{% endhint %}

MaiAgent supports two database types:

* **MaiAgent Database**: Upload Excel / CSV / JSON files to have the system automatically create tables. Suitable for users without their own databases
* **External Database**: Connect an existing PostgreSQL, MySQL, SQL Server (MSSQL), Oracle, or SynxDB database

## Network Prerequisites (External Databases) <a href="#network-prerequisites" id="network-prerequisites"></a>

Before connecting an external database, confirm the following with your organization's IT team or database administrator. These settings are unnecessary if you only use MaiAgent databases (spreadsheet uploads).

* [ ] **Allow inbound connections from MaiAgent**: MaiAgent initiates connections to your database (when you click “<mark style="color:blue;">Test Connection</mark>” and whenever an Agent runs a query). Your database does not need to initiate connections to MaiAgent.
* [ ] **Open the connection port**: The defaults are PostgreSQL 5432, MySQL 3306, SQL Server 1433, Oracle 1521, and SynxDB 5432 (depending on deployment settings). If you use a different port, use the port specified in `host:port` in the “<mark style="color:blue;">Connection String</mark>”.
* [ ] **MaiAgent cloud service**: If your database has a firewall or security group, allowlist MaiAgent's outbound IP addresses (contact your MaiAgent representative to obtain them), and open only the ports listed above.
* [ ] **On-premises / private cloud deployments**: Ensure that the network segments and ports between the MaiAgent host and database host can communicate. The database does not need to be exposed to the internet.
* [ ] **Use a read-only account**: Create a dedicated read-only account for MaiAgent and grant access only to the tables the Agent needs. For tables containing personal data or other sensitive columns, you can create a view containing only the necessary columns and grant access to that view. “<mark style="color:blue;">Specified Tables</mark>” only narrows the scope the Agent references; the account's permissions determine which data it can actually read.

For details on how the platform blocks write statements, the level of read-only protection for each database, and network requirements, see [Text to SQL: Read-only Protection](https://docs.maiagent.ai/tech/advanced-genai-tech/text-to-sql#read-only) and [Network Requirements](https://docs.maiagent.ai/tech/advanced-genai-tech/text-to-sql#network) in the technical manual.

## Create a Database <a href="#create-database" id="create-database"></a>

### 1. Open the Database Management Page <a href="#enter-database-management-page" id="enter-database-management-page"></a>

In the left navigation bar, go to “<mark style="color:blue;">AI Features</mark>” and click “<mark style="color:blue;">Databases</mark>”. The page has two tabs: <mark style="color:blue;">MaiAgent Databases</mark> and <mark style="color:blue;">External Databases</mark>.

Click “<mark style="color:blue;">Add Database</mark>” in the upper-right corner to begin.

<figure><img src="https://1360999650-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F6v6TNkkOQVfRYfcNirHL%2Fuploads%2Fgit-blob-1679e98bd1aac4aaa4518e438af0f88ac67c34e7%2Fdatabase-list-page.png?alt=media" alt="Database management page"><figcaption><p>Database management page showing the list of MaiAgent databases</p></figcaption></figure>

{% hint style="info" %}
If the button is grayed out, your role has not been granted permission to create databases. Contact your organization administrator to configure permissions.
{% endhint %}

### 2. Select a Database Type <a href="#select-database-type" id="select-database-type"></a>

Select the type of database you want to create, click its card, and then click “<mark style="color:blue;">Next</mark>”:

* **MaiAgent Database**: For uploading spreadsheet files; no connection string is required
* **PostgreSQL / MySQL / SQL Server / Oracle / SynxDB**: Connect an existing external database in your organization

<figure><img src="https://1360999650-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F6v6TNkkOQVfRYfcNirHL%2Fuploads%2Fgit-blob-7bfa7cfc7a933807d14941d090f30a8442c0e3af%2Fdatabase-create-select-type.png?alt=media" alt="Select a database type"><figcaption><p>Step 1: Select a database type</p></figcaption></figure>

### 3. Enter Basic Information <a href="#fill-in-basic-info" id="fill-in-basic-info"></a>

#### MaiAgent Database <a href="#maiagent-database-info" id="maiagent-database-info"></a>

| Field         | Required | Description                                                     |
| ------------- | -------- | --------------------------------------------------------------- |
| Database Name | Yes      | For example, “2024 Sales Data”                                  |
| Description   | No       | Additional information about the database's purpose or contents |

{% hint style="info" %}
MaiAgent databases do not require a connection string; the system handles data storage automatically. Simply upload spreadsheet files after creating the database.
{% endhint %}

<figure><img src="https://1360999650-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F6v6TNkkOQVfRYfcNirHL%2Fuploads%2Fgit-blob-87d0a0885bc2b8454421703e90225a019c1e2b57%2Fdatabase-create-info-maiagent.png?alt=media" alt="MaiAgent database settings"><figcaption><p>Step 2: MaiAgent database settings screen</p></figcaption></figure>

#### External Database <a href="#external-database-info" id="external-database-info"></a>

| Field             | Required | Description                                                      |
| ----------------- | -------- | ---------------------------------------------------------------- |
| Database Name     | Yes      | For example, “Corporate ERP Database”                            |
| Description       | No       | Additional information about the database's purpose or contents  |
| Connection String | Yes      | The database connection URL                                      |
| Specified Tables  | No       | Limit the tables the Agent can query; separate names with commas |

Connection string formats are shown below (the format for the selected type also appears below the field):

```
PostgreSQL:   postgresql://使用者名稱:密碼@主機位址:連接埠/資料庫名稱
MySQL:        mysql+pymysql://使用者名稱:密碼@主機位址:連接埠/資料庫名稱
SQL Server:   mssql+pyodbc://使用者名稱:密碼@主機位址:連接埠/資料庫名稱?driver=ODBC+Driver+18+for+SQL+Server&TrustServerCertificate=yes&Encrypt=no
Oracle:       oracle+oracledb://使用者名稱:密碼@主機位址:連接埠/?service_name=服務名稱
SynxDB:       postgresql://使用者名稱:密碼@主機位址:連接埠/資料庫名稱
```

{% hint style="danger" %}
Make sure the connection string uses the correct format and includes all required connection information. Its prefix must match the format above; otherwise, an “Invalid connection string format” message appears and you cannot proceed to the next step. Also confirm that your database allows connections from MaiAgent; see [Network Prerequisites](#network-prerequisites).
{% endhint %}

<figure><img src="https://1360999650-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F6v6TNkkOQVfRYfcNirHL%2Fuploads%2Fgit-blob-6158a998800e16ec360cd66dc1d0203786acdcf4%2Fdatabase-create-info-external.png?alt=media" alt="External database settings"><figcaption><p>Step 2: External database (PostgreSQL) settings screen</p></figcaption></figure>

After entering the connection string, we recommend clicking “<mark style="color:blue;">Test Connection</mark>” to verify connectivity (you can also skip the test and save directly):

* **Connection successful**: MaiAgent can access your database
* **Connection failed**: Check the connection string format, username and password, and whether your firewall, allowlist, and ports permit access as described in [Network Prerequisites](#network-prerequisites)

If your database contains many tables, enter the names of the tables you want to make available (for example, `customers, orders, products`) in “<mark style="color:blue;">Specified Tables</mark>” to improve query efficiency and security. Leave it blank to query all tables.

### 4. Configure Role Permissions <a href="#configure-group-permission" id="configure-group-permission"></a>

The final step is to specify which roles can access this database. Choose one option under “<mark style="color:blue;">Quick Permission Scope Selection</mark>”:

* <mark style="color:blue;">All Roles</mark>: Allow members of all roles in the organization to use this database
* <mark style="color:blue;">Exclude Default Roles</mark>: Allow only custom roles, excluding default roles
* <mark style="color:blue;">Select Custom Roles</mark>: Manually select specific roles by moving the roles to authorize from “<mark style="color:blue;">Available Roles</mark>” to “<mark style="color:blue;">Selected Roles</mark>”

<figure><img src="https://1360999650-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F6v6TNkkOQVfRYfcNirHL%2Fuploads%2Fgit-blob-370933d45e5081799639a1b310f3f3ae875936a2%2Fdatabase-create-role-permission.png?alt=media" alt="Configure role permissions"><figcaption><p>Step 3: Quick permission scope selection</p></figcaption></figure>

{% hint style="info" %}
If you do not need to configure role permissions yet, skip this step and adjust them later in the “<mark style="color:blue;">Role Permissions</mark>” tab on the database settings page.
{% endhint %}

### 5. Complete Creation <a href="#complete-creation" id="complete-creation"></a>

After confirming all settings, click “<mark style="color:blue;">Create Database</mark>” (MaiAgent databases) or “<mark style="color:blue;">Save</mark>” (external databases). Once created, the database settings page opens for further configuration.

{% hint style="warning" %}
New databases are **disabled** by default. Enable “<mark style="color:blue;">Enable Database</mark>” in the “<mark style="color:blue;">Basic Information</mark>” tab and save, or toggle “<mark style="color:blue;">Enabled</mark>” in the database list, before the Agent can use this database for Text to SQL queries.
{% endhint %}

{% hint style="info" %}
After creation, remember to **link the database to an Agent** so the Agent can use it for queries. See: [Link Agents and Group Permissions](/maiagent-user-guide/en/build/database/link-chatbots-groups.md)
{% endhint %}

## Upload Files to Create Tables (MaiAgent Databases) <a href="#upload-file-to-create-table" id="upload-file-to-create-table"></a>

After creating a MaiAgent database, open its settings page and switch to “<mark style="color:blue;">Table Management</mark>”. Click “<mark style="color:blue;">Upload Table File</mark>”, select a file or drag it into the upload area, then click “<mark style="color:blue;">Upload</mark>”.

<figure><img src="https://1360999650-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F6v6TNkkOQVfRYfcNirHL%2Fuploads%2Fgit-blob-20335fe758b8e0f9dbd3fd5f1bb89d70b698624a%2Fdatabase-config-table-management.png?alt=media" alt="Table Management tab"><figcaption><p>Table Management tab, with tables grouped by source file</p></figcaption></figure>

### Supported File Formats <a href="#supported-file-formats" id="supported-file-formats"></a>

| Format | Extension         | Description                                |
| ------ | ----------------- | ------------------------------------------ |
| Excel  | `.xlsx`, `.xls`   | A separate table is created for each sheet |
| CSV    | `.csv`            | Comma-separated values file                |
| JSON   | `.json`, `.jsonl` | JSON data file                             |

After upload, the system automatically parses the file structure and creates tables in the background. Tables are grouped by source file and show their processing status:

| Status          | Description                                                                   |
| --------------- | ----------------------------------------------------------------------------- |
| Pending         | The file has been uploaded and is waiting for processing                      |
| Processing      | The system is parsing the file and creating tables                            |
| Success         | The table has been created and can be queried by Agents                       |
| Failed          | An error occurred during creation; hover over the error icon to see the cause |
| Deleting        | The table is being removed in the background                                  |
| Deletion Failed | Removal did not complete; try deleting again                                  |

Tables labeled “<mark style="color:blue;">From Knowledge Base</mark>” below the source file name were not uploaded here. They were synchronized when someone uploaded a structured file to a knowledge base and chose to [use a database instead](/maiagent-user-guide/en/build/knowledge-base/km.md#table-row-limit). The knowledge base and database each have their own copy of the file; deleting one does not affect the other.

### Table Names and Repeated Uploads <a href="#table-naming-and-reupload" id="table-naming-and-reupload"></a>

* The system generates table names in the format `table_<檔名>_<10 碼雜湊>`. It takes the uploaded file name without its extension, keeps only Chinese and English characters, numbers, and underscores, and limits it to 45 characters. The hash is calculated from the file ID for this upload and the sheet name. For example, uploading the “Q1” sheet in `2024 銷售.xlsx` produces `table_2024銷售_` followed by a 10-character hash. The Agent uses this name when querying, so we recommend specifying the source file and sheet in the [Table Description](#edit-column-description).
* Each Excel sheet becomes a separate table. Successfully created tables are automatically added to the database's <mark style="color:blue;">Specified Tables</mark>.
* **Uploading a file with the same name does not overwrite existing tables**: Every upload receives a new file ID and therefore a different hash, creating another set of tables. Existing tables and column descriptions remain unchanged, and the Agent sees both sets. To update data, first delete the old tables (or the entire source file) in <mark style="color:blue;">Table Management</mark>, then upload the new file. Column descriptions are not retained after deletion, so record them beforehand.
* Tables synchronized from a knowledge base (labeled <mark style="color:blue;">From Knowledge Base</mark>) follow the same naming rules. Deleting them on either side does not affect the other.

### File Format Notes <a href="#file-format-notes" id="file-format-notes"></a>

* [x] The first row must contain **column names**
* [x] Each column must use a consistent **data type** (for example, all values in a price column should be numbers)
* [x] Avoid **merged cells**
* [x] Avoid **duplicate column names**
* [x] Each sheet in an Excel file must contain **only one table**

{% hint style="warning" %}
Common causes of table creation failures include incorrect or corrupted file formats, special characters in column names, duplicate column names, and inconsistent data types within a column.
{% endhint %}

## Edit Column Descriptions <a href="#edit-column-description" id="edit-column-description"></a>

Column descriptions are a **key setting** for improving Text to SQL query accuracy. Agents use these descriptions to understand what the data means and generate more accurate SQL queries.

For example, if a table contains a column named `status`, the Agent may not know what it represents. Adding a description such as “Order status; possible values: pending (awaiting processing), completed (finished), cancelled (canceled)” helps the Agent understand the column correctly.

### How to Edit <a href="#how-to-edit" id="how-to-edit"></a>

**MaiAgent Database**: In the “<mark style="color:blue;">Table Management</mark>” tab, click the “<mark style="color:blue;">Edit</mark>” icon in the table's actions column to open “<mark style="color:blue;">Edit Table Information</mark>”. Enter the “<mark style="color:blue;">Table Description</mark>” and each “<mark style="color:blue;">Column Description</mark>”, then click “<mark style="color:blue;">Save</mark>”. The dialog shows progress (for example, “3 / 10 columns configured”).

<figure><img src="https://1360999650-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F6v6TNkkOQVfRYfcNirHL%2Fuploads%2Fgit-blob-55bbc387eb441d873faf95addb67a70ecba7ce0c%2Fdatabase-edit-table-info.png?alt=media" alt="Edit Table Information dialog"><figcaption><p>Edit Table Information: enter table and column descriptions</p></figcaption></figure>

**External Database**: In the “<mark style="color:blue;">Table Schema Descriptions</mark>” section at the bottom of “<mark style="color:blue;">Basic Information</mark>”, click “<mark style="color:blue;">Add Table</mark>”. Enter the “<mark style="color:blue;">Table Name</mark>” and “<mark style="color:blue;">Columns and Their Purpose</mark>”, then click “<mark style="color:blue;">Save</mark>”.

### Writing Suggestions <a href="#writing-suggestions" id="writing-suggestions"></a>

{% hint style="info" %}
Good column descriptions can significantly improve AI query accuracy:
{% endhint %}

| Column Name | Poor Description | Better Description                                                                                                                       |
| ----------- | ---------------- | ---------------------------------------------------------------------------------------------------------------------------------------- |
| `amt`       | Amount           | Total order amount (NTD), including tax and shipping                                                                                     |
| `cust_lvl`  | Level            | Customer tier: A for VIP customers with annual spending over 1 million, B for annual spending of 100,000–1 million, and C for all others |
| `reg`       | Region           | Customer's geographic region; possible values: northern, central, southern, eastern, offshore islands                                    |

## Manage Existing Databases <a href="#manage-existing-databases" id="manage-existing-databases"></a>

### Edit a Database <a href="#edit-database" id="edit-database"></a>

In the database list, click the “<mark style="color:blue;">Edit</mark>” icon in the actions column to open the settings page.

<figure><img src="https://1360999650-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F6v6TNkkOQVfRYfcNirHL%2Fuploads%2Fgit-blob-4730481d7efc94106f51933b1d9399496e2a39b8%2Fdatabase-config-basic-info.png?alt=media" alt="Database settings page"><figcaption><p>Database settings page: Basic Information tab</p></figcaption></figure>

The settings page contains the following tabs:

| Tab                                                   | Description                                                                                                                                                | Applicable Types |
| ----------------------------------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------- | ---------------- |
| Basic Information                                     | Edit the name, description, and enabled status; for external databases, also change the connection string, specified tables, and table schema descriptions | All              |
| Table Management                                      | Upload files and manage tables and column descriptions                                                                                                     | MaiAgent only    |
| <mark style="color:blue;">Linked AI Assistants</mark> | Specify which Agents can use this database                                                                                                                 | All              |
| Role Permissions                                      | Configure role access permissions                                                                                                                          | All              |

{% hint style="info" %}
For security, passwords in external database connection strings are masked. To modify a connection string, click “<mark style="color:blue;">Change</mark>” and enter the complete new connection string.
{% endhint %}

### Enable / Disable <a href="#enable-disable" id="enable-disable"></a>

Use the “Enabled” toggle in the database list to quickly change its status. When disabled, Agents temporarily cannot query the database, but its settings and data are retained.

### Delete a Database <a href="#delete-database" id="delete-database"></a>

Click <mark style="color:blue;">Delete</mark> in the database list's actions column to open the “<mark style="color:blue;">Delete Database</mark>” confirmation dialog, which asks “Are you sure you want to delete ‘Database Name’?”. For MaiAgent databases, the dialog also shows a “<mark style="color:blue;">Before You Delete</mark>” reminder: tables in this database may have been synchronized from knowledge base files. To remove everything, also check the knowledge base for files that need to be deleted. Once deleted, this database and all its table data cannot be recovered. For external databases, the dialog states, “Once deleted, this database connection configuration cannot be recovered.” Click <mark style="color:blue;">Confirm Deletion</mark> to proceed.

{% hint style="danger" %}
Deleting a database is irreversible.

* **MaiAgent Database**: All tables created through file uploads are removed along with the database. For tables synchronized from a knowledge base, the files in the knowledge base are retained
* **External Database**: Only the connection configuration in MaiAgent is removed; data in the original database is unaffected
  {% endhint %}


---

# 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/maiagent-user-guide/en/build/database/create-database.md?ask=<question>&goal=<user_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.