Documentation / Connectors

MySQL

Connect Nexus to MySQL over a native async driver for full read, write, and merge, with SQL filter, aggregation, join, and ordering pushed down to the database. Writes are gated to base tables; views are not writable.

The MySQL connector (bundle version 2.0.5) lets Nexus read from, write to, and discover schema in any MySQL-compatible database. It runs on a native async driver (aiomysql) and surfaces the full SQL adapter capability set: filters and aggregations push down to MySQL rather than executing in memory, and the entire write surface — insert, update, delete, and upsert — is available.

This page is for engineers configuring a MySQL bridge. It covers connection fields and TLS, the exact pushdown and operator surface, the MySQL dialect specifics, the write-and-merge model with its base-table gate, and behavioral limits including the ordering caveat when you page without an ORDER BY.

Capabilities

Capability	Supported
Read	Yes
Write (insert / update / delete)	Yes
Upsert (merge)	Yes — `INSERT ... ON DUPLICATE KEY UPDATE`
Schema discovery	Yes
Filter pushdown	Full
Aggregation pushdown	Yes — `SUM`, `COUNT`, `AVG`, `MIN`, `MAX`
`GROUP BY` / `ORDER BY` / `DISTINCT` / `HAVING` / `JOIN` pushdown	Yes

Because MySQL declares full pushdown and its operators are all pushable, the Nexus engine sends your WHERE, aggregation, ordering, and paging straight to the database and does not re-filter returned rows in memory (see Operators & pushdown). The full analytical shape of a report executes on MySQL.

Connection credentials

Configure the following fields when adding a MySQL bridge. Secrets are held in your tenant Key Vault; see Secrets.

Field	Description	Default
`host`	MySQL server hostname or IP	—
`port`	TCP port	`3306`
`username`	Database user	—
`password`	Database password	—
`database`	Default database to connect to	—
`charset`	Connection character set	`utf8mb4`
`ssl_mode`	TLS mode: `required` or `preferred`	`preferred`

TLS and ssl_mode

Set ssl_mode to required to enforce TLS with certificate and hostname validation. With preferred, the connection uses TLS opportunistically but does not validate the server certificate or hostname. For production deployments over a network boundary, use required.

`ssl_mode` value	Behavior
`required`	TLS enforced; certificate and hostname validated
`preferred`	TLS opportunistic; no certificate/hostname validation (default)

Supported filter operators

All standard UQL filter operators push down to MySQL WHERE clauses:

$eq $ne $gt $gte $lt $lte $like $in $not_in $between $empty $contains $starts_with $ends_with $expr

$like is translated to LIKE; its case sensitivity follows the source collation (MySQL string comparisons are case-insensitive by default under most collations). $expr passes a raw SQL fragment through as-is — treat it as a trusted-author surface and bound authorship with execution roles.

SQL dialect

Identifier quoting: backtick characters (`).
Paging: LIMIT n OFFSET m.
Upsert: INSERT ... ON DUPLICATE KEY UPDATE.
Parameters: %s positional placeholders (format paramstyle).

When a query includes a row limit without an explicit ORDER BY, the adapter does not inject a synthetic order — the result order is whatever MySQL returns naturally. Add an explicit order_by in your report or read definition if deterministic ordering matters.

Schema discovery

When you discover a MySQL bridge, Nexus queries INFORMATION_SCHEMA and returns:

Databases (schemas) — system schemas excluded automatically.
Tables — base tables only (views are not enumerated).
Columns — name, data type, nullability, and any EXTRA flags (e.g. auto_increment).

The following system schemas are always filtered out: information_schema, mysql, performance_schema, sys. Discovery is driven by three INFORMATION_SCHEMA queries (SCHEMATA, TABLES, COLUMNS) and respects the database-level permissions the configured user holds.

Write operations and the base-table gate

All write operations verify that the target is a base table (not a view) before executing. The check queries INFORMATION_SCHEMA.TABLES for the target table name; a missing row raises a permission error rather than proceeding. This writability gate sits beneath the tenant RBAC that governs whether the caller may write at all.

Insert: parameterized INSERT INTO ... VALUES (...).
Update: UPDATE ... SET ... WHERE ... with the fields and filter you specify.
Delete: DELETE FROM ... WHERE ....
Upsert (merge): INSERT INTO ... VALUES (...) ON DUPLICATE KEY UPDATE ... — resolves conflicts on the table's primary key or unique index.

Example: read then stage in a pipeline

async def run(ctx):
    result = await ctx.adapters.orders_db.read({
        "collection": "orders",
        "where": {"status": {"$eq": "open"}},
        "order_by": [{"field": "created_at", "direction": "desc"}],
        "limit": 500,
    })

    await ctx.workspace.upsert("open_orders", result.rows)
    ctx.logger.info("staged %d open orders", len(result.rows))

An upsert back into MySQL from staged data resolves on the target table's primary or unique key:

async def run(ctx):
    staged = await ctx.workspace.read("orders_enriched")
    await ctx.adapters.orders_db.write({
        "collection": "orders",
        "operation": "merge",
        "records": staged.rows,
    })

Limits & behavior notes

Views are not writable and not enumerated. Discovery lists base tables only; writes to a non-base-table target fail fast.
No synthetic ordering. Paging without order_by yields MySQL's natural order — supply an order for determinism.
$like case sensitivity depends on collation. It behaves case-insensitively under utf8mb4_general_ci/utf8mb4_unicode_ci; behavior differs under binary or _bin collations.
Identifier safety. Table/column names are backtick-quoted; names containing a backtick are rejected.
Errors are sanitized. Driver error messages that would expose connection details are scrubbed before they surface in logs or API responses.
Non-blocking I/O. The native async driver keeps reads and writes off the Nexus event loop during I/O.