> ## Documentation Index
> Fetch the complete documentation index at: https://docs.adriel.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Data reference

> Query model, field type mapping, refresh cadence, and limits for the MySQL data source in Adriel.

## Introduction

MySQL is a widely used open-source relational database. The Adriel MySQL connector lets a customer connect their own MySQL (or MariaDB) database as a dashboard data source. One Adriel data source binds to one table; the connector discovers that table's columns and exposes them as candidate metrics, breakdowns, or date fields based on their MySQL type. When a widget loads, the connector auto-generates a MySQL SQL statement from the widget configuration, routes it through a shared database proxy, executes it against the connected database, and returns the result.

As a database connector, the available fields are not a fixed catalog — they are derived from the schema of the bound table. Data types are mapped from MySQL native types to Adriel field types, and every column can serve as a metric or a breakdown. MariaDB instances are wire-protocol compatible and are handled by the same connector.

To connect this data source, see [How to connect MySQL to Adriel](/data-sources/g-n/mysql/how-to-connect).

## Data refresh strategy

MySQL data is fetched **on demand**. There is no incremental sync — every dashboard, widget, or report load issues a live query against the bound table, so results always reflect the latest committed data in the source database.

**No query-result cache.** Each widget load re-runs its query against the live database; query results are not cached.

**Field-metadata cache.** The table's column list (field metadata) is memoized with a **12-hour** TTL per data source. New columns added on the MySQL side become visible only after this cache refreshes.

## Architecture levels

Database → Table.

The connection points at a single MySQL database. Discovery walks two levels through the database proxy:

1. **Tables** — the connector lists tables via `SHOW TABLES`.
2. **Columns** — for the selected table, the connector reads column names and types from `INFORMATION_SCHEMA.COLUMNS`.

One Adriel data source binds to one table. To expose a second table, create a second data source.

## Date range limits

There is no platform-imposed date range limit. Query bounds are set by the widget's date-range control combined with the date column on the bound table. A maximum response row count applies to every query (see [Limitations](#limitations)).

## Query model

Widget queries are auto-generated from the widget configuration and built with the Knex query builder in the **MySQL flavor** (`client: 'mysql'`). The SQL string is generated and then sent to a shared database proxy for execution against the connected database — no long-lived database connection is opened on the Adriel side. Each request produces one statement of the form:

```sql theme={null}
SELECT <breakdowns>, SUM(<field>) AS <field>
FROM <table>
WHERE <widget filters>
GROUP BY <breakdowns>
LIMIT <maxResponseLimit>
```

Key MySQL-flavor rules applied by the query builder:

* **Breakdowns become `GROUP BY`.** When breakdowns are present (and the first breakdown is not the "all" pseudo-breakdown), they are added as both `SELECT` columns and `GROUP BY` columns.
* **Fields become `SUM` aggregates.** Every selected field is emitted as `SUM(field) AS field`. The synthetic row-count field is emitted as `COUNT(*)` instead.
* **`WHERE`.** Widget filters translate into `IN` conditions only (see [Filters](#filters)).
* **Date filtering.** The date-range condition is applied after the `WHERE` clause, using the date format configured for the bound date column.
* **`LIMIT`.** Rows are capped at the maximum response limit and applied last.
* **Sorting is not applied.** Sort settings are ignored by the connector — ordering must be handled downstream or via a pre-sorted view.

<Note>
  **Query cost tracks database load**

  Every widget load issues a live query against the connected database. Query performance depends on the source database, and queries that scan large tables may run slowly or return truncated results. Pairing a mapped date column with a pre-filtered MySQL view is the most effective way to narrow each scan.
</Note>

## Filters

Widget filters translate into MySQL `WHERE` conditions using a deliberately narrow operator set.

### Supported filter operators

Only one operator is enabled for widget filters on MySQL data sources:

* `IN` (including its negation)

Every other operator is rejected. For richer filtering — `LIKE`, ranges, or regular expressions — the recommended pattern is to define a **view** on the MySQL side that applies the predicate, then bind the view as the data source. MySQL data sources do not support a free-form WHERE fragment or a form-driven column pre-filter.

### Date-range filtering

Date-range filtering is applied automatically to the date column on the bound table. The connector supports millisecond and second Unix timestamps (cast and range-compared), ISO date strings (`STR_TO_DATE`), and a custom date format that is converted to MySQL format tokens and wrapped with `DATE_FORMAT(STR_TO_DATE(...))`. Both range bounds are inclusive.

## Field type mapping

MySQL has no fixed metric or breakdown catalog. The available fields are the columns of the bound table, resolved from `INFORMATION_SCHEMA.COLUMNS` at discovery time.

<Note>
  **How to read the columns**

  The **Data type** column uses the platform's ten-value vocabulary: **Number**, **Currency**, **Percentage**, **Ratio**, **Duration**, **Date**, **Text**, **URL**, **Array**, **Boolean**. Every MySQL column is exposed with a **metric or breakdown** role, so any column can be placed in either slot when building a widget.
</Note>

Each MySQL column is classified into one of three buckets by matching its native type name.

| MySQL native type                                                                                                                     | Adriel field type | Data type | Field role                                         |
| ------------------------------------------------------------------------------------------------------------------------------------- | ----------------- | --------- | -------------------------------------------------- |
| `INT`, `BIGINT`, `FLOAT`, `DOUBLE`, `DECIMAL` / `DEC`, `BIT` (any type whose name contains `int`, `float`, `double`, `dec`, or `bit`) | `number`          | Number    | Metric or breakdown                                |
| `DATE`, `DATETIME`, `TIMESTAMP`, `TIME`, `YEAR` (any type whose name contains `date`, `time`, or `year`)                              | `date`            | Date      | Metric or breakdown; used for date-range filtering |
| `VARCHAR`, `CHAR`, `TEXT`, `JSON`, `BLOB`, `BINARY`, `BOOLEAN`, and all other types                                                   | `string`          | Text      | Metric or breakdown                                |

<Note>
  **Text columns are not numerically aggregable**

  Because every selected field is wrapped in `SUM()` (see [Aggregation defaults](#aggregation-defaults)), applying a metric aggregation to a text-classified column may evaluate to `0`. Use text columns as breakdowns, and place numeric columns in metric slots.
</Note>

### Adriel-added fields

Adriel adds one synthetic field on top of the table's own columns.

| Field     | Description                                                                                                                       | Data type | API Key        |
| --------- | --------------------------------------------------------------------------------------------------------------------------------- | --------- | -------------- |
| Row count | Total number of rows in the bound table (or in the widget-filtered, grouped subset). Resolves to `COUNT(*)` in the generated SQL. | Number    | `sql:rowCount` |

### Aggregation defaults

The MySQL connector applies a single aggregation rule — there are no per-type or advanced aggregation settings.

| Field                      | Default aggregation |
| -------------------------- | ------------------- |
| Any selected field         | `SUM(field)`        |
| Row count (`sql:rowCount`) | `COUNT(*)`          |

Because `SUM` is applied uniformly, aggregating a non-numeric (text-classified) column may evaluate to `0`. Fields placed in the Breakdowns section are used for grouping rather than aggregation.

## Limitations

Based on the current connector:

* **Maximum response row count.** Query results are capped at `CONNECTORS_QUERY_MAX_RESPONSE_LIMIT` (default 100,000 rows). Queries that would exceed the cap return a truncated result set.
* **One table per data source.** Multi-table joins are not supported — they must be pre-joined into a MySQL view.
* **Filter operator whitelist.** Only the `IN` operator (with negation) is available in widget filters; every other operator is rejected. Use a pre-filtered view for richer slicing.
* **No sorting.** Widget sort settings are not applied by the connector.
* **No query-result cache.** Every widget load re-queries the live database; there is no result cache to fall back on, so connector performance depends on the source database's performance.
* **12-hour field-metadata cache.** New columns added on the MySQL side become visible only after the field-metadata cache refreshes (up to 12 hours).
* **Uniform `SUM` aggregation.** Every selected field is aggregated with `SUM`, so text columns are not meaningfully aggregable as metrics.
* **Deleted rows are not retained.** Once rows are removed from the source table, they are no longer returned — the connector does not keep historical snapshots.
* **Generic table output.** MySQL data is surfaced as a generic (free-format) data source. To combine it with other connectors that do not share the same breakdowns, configure Blend Data settings.
* **Network reachability required.** Queries reach the database through Adriel's database proxy, so the database must be reachable from the proxy's egress addresses and the MySQL user's host pattern must permit them — see the paired how-to.

## API references

* [MySQL reference manual](https://dev.mysql.com/doc/refman/en/)
* [MySQL data types](https://dev.mysql.com/doc/refman/en/data-types.html)
* [`SELECT` syntax](https://dev.mysql.com/doc/refman/en/select.html)
* [`INFORMATION_SCHEMA.COLUMNS` table](https://dev.mysql.com/doc/refman/en/information-schema-columns-table.html)
* [MariaDB documentation](https://mariadb.com/kb/en/documentation/)

## See also

* [How to connect MySQL](/data-sources/g-n/mysql/how-to-connect) (paired how-to)
* [MongoDB data reference](/data-sources/g-n/mongodb/data-reference) — NoSQL database with a form-driven column pre-filter
* [PostgreSQL data reference](/data-sources/o-z/postgres/data-reference) — alternative SQL database with a richer filter operator set
* [Redshift data reference](/data-sources/o-z/redshift/data-reference) — cloud data warehouse alternative
* [Google BigQuery data reference](/data-sources/g-n/google-bigquery/data-reference) — cloud data warehouse alternative