> For the complete documentation index, see [llms.txt](https://docs.prestd.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.prestd.com/databases/mysql.md).

# MySQL

Use MySQL 8.0.19+ with pREST — native dialect from v2.5.0, MCP from v2.5.1. REST CRUD, alias schema scope, and documented gaps. MariaDB is not this adapter.

**pREST** v2.5.0+ exposes **MySQL 8.0.19+** through a native dialect adapter. Set `engine = "mysql"`. An empty `engine` stays PostgreSQL.

*Last updated: October 8, 2026* · **Label:** Native (MySQL 8.0.19+)

MariaDB, TiDB, and Amazon Aurora MySQL are **not** this adapter. MCP (`/_mcp`) is supported from **v2.5.1**, read-only.

***

## Supported features

| Capability                        | Status                                                                                |
| --------------------------------- | ------------------------------------------------------------------------------------- |
| Connection                        | `engine = "mysql"`, or a `[[databases]]` URL starting with `mysql://` and no `engine` |
| Schema discovery                  | supported (`information_schema`; system schemas hidden)                               |
| CRUD                              | supported, with write differences below                                               |
| Filtering / ordering / pagination | supported (`LIMIT` / `OFFSET`)                                                        |
| Scripts (`/_QUERIES`)             | supported — `?` placeholders and backtick identifiers                                 |
| ACL / permissions                 | same TOML allowlist as PostgreSQL                                                     |
| MCP (`/_mcp`)                     | supported (v2.5.1+), read-only, backticks / `?`                                       |
| pgvector / ltree / `tsquery`      | unsupported operators                                                                 |

***

## Connection

```toml
engine = "mysql"

[pg]
url = "mysql://user:pass@tcp(localhost:3306)/mydb"
```

Or environment variables. `PREST_DB_*` is read first, then legacy `PREST_PG_*`:

```sh
export PREST_VERSION=2
export PREST_DB_URL='mysql://user:pass@tcp(localhost:3306)/mydb'
```

Port defaults to **3306** when unset. Postgres defaults (user `postgres`, database `prest`, port 5432) are not applied. A missing user or database fails startup.

TLS: `disable`, `require`, or `skip-verify`. A client cert, key, or root certificate fails startup in this release.

`[[databases]]` entries can set `engine = "mysql"` per alias. See [Multi-database](/get-started/multi-database.md).

***

## Paths

Routes stay `/{database}/{schema}/{table}`.

* `{database}` selects the pool (the alias, or the configured database on one connection).
* `{schema}` is the MySQL database. `/databases` and `/schemas` both list user databases.
* Hidden schemas: `mysql`, `information_schema`, `performance_schema`, `sys`.

***

## Registry

From **v2.5.1** ([#1056](https://github.com/prest/prest/pull/1056)):

* A `[[databases]]` alias serves only the database named in its `url` or `database`. Any other `{schema}` is **404** on CRUD, `/show`, and `/{alias}/{schema}` table listing.
* In registry mode `/databases` returns `[{"datname": alias, "name": alias, "physical_name": database}]` instead of every database on the server.
* With no `[[databases]]` block, the single connection still serves every granted database as `{schema}`.
* A `[[databases]]` entry whose `url` starts with `mysql://` and has **no** `engine` is MySQL. Any other URL is not probed. Empty root `engine` stays PostgreSQL.

***

## Known limitations

* **MySQL 8.0.19+.** The upsert form needs the row alias added in 8.0.19. MariaDB is out of scope.
* **No SQL `RETURNING`.** Insert is followed by `SELECT *` in the same transaction. `UPDATE`/`DELETE` without `_returning` return `{"rows_affected": n}`. With `_returning`, pREST emulates the row image inside a `REPEATABLE READ` transaction.
* **`Prest-Batch-Method: copy`** is a transactional multi-row `INSERT`, not `LOAD DATA`. Batch insert uses the union of keys. A missing key is SQL `DEFAULT`.
* **No `FULL` join.** `INNER`, `LEFT`, `RIGHT`, and `CROSS` are generated.
* **`$ilike` is `LIKE`.** Case follows the column collation. pREST does not wrap the column in `LOWER()`.
* **`$all`, ltree, `$tsquery`, and `vecdist` error** with `unsupported operator`.
* **Scripts** use `?` placeholders and backtick `ident`, not Postgres `$n` and double quotes.
* **Connection failure** (down, refused, timed out) is **503** `{"error":"database unavailable"}` with no driver text.
* **MySQL 1146, 1142, 1044, and 1049** are **404**. For 1142, 1044, and 1049 the server text is dropped because it names the account.
* **`DATETIME` has no zone** (`YYYY-MM-DDTHH:MM:SS`). **`TIMESTAMP` is UTC** with `+00:00`. `DATE` is `YYYY-MM-DD`.
* **`DECIMAL` / `NUMERIC` stay strings**, so `SUM` / `AVG` of an integer column is a string.

Dialect matrix in the server repo: [`integration/mysql/DIFFERENCES.md`](https://github.com/prest/prest/blob/v2.5.1/integration/mysql/DIFFERENCES.md).

***

## Related

* [v2.5.1 release notes](/releases/v2.5.1.md)
* [v2.5.0 release notes](/releases/v2.5.0.md)
* [MCP over HTTP](/get-started/mcp-over-http.md)
* [Databases](/databases.md)
* [Database roadmap](/databases/roadmap.md)
* [API Reference](/api-reference.md)
* [Acronyms](/readme/acronyms.md) ([MCP](/readme/acronyms.md#mcp), [REST](/readme/acronyms.md#rest))


---

# 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.prestd.com/databases/mysql.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.