# MySQL and MariaDB to REST API and MCP Server

Source: https://wiki.faucetdb.ai/tutorial-mysql

**How do I turn a MySQL or MariaDB database into a REST API?** Add the database to Faucet, create an API key, and start the server. Every table is then available at `/api/v1/mydb/_table/<table>` with filters, sorting and pagination, described by an OpenAPI 3.1 spec at `/openapi.json`. AI agents can use the same database through the MCP endpoint at `/mcp`, limited by the same API key's role.

```bash
faucet db add --name mydb --driver mysql \
  --host localhost --user root --password-prompt --database mydb
faucet role create --name reader --verbs GET
faucet key create --role reader          # prints the key once
faucet serve

curl "http://localhost:8080/api/v1/mydb/_table/customers?limit=10" \
  -H "X-API-Key: faucet_YOUR_KEY"
```

Prefer a browser? The [admin UI](https://wiki.faucetdb.ai/admin-ui#connect-a-database) does the same with a form and a **Test connection** button. To give Claude, Cursor or another AI agent access, see [Give AI Agents Access](https://wiki.faucetdb.ai/tutorial-mysql#give-ai-agents-access-mcp).

This tutorial walks you through connecting a MySQL database to Faucet and querying it via the REST API.

## Prerequisites

- Faucet installed and running (`faucet serve`) — see [Getting Started](https://wiki.faucetdb.ai/getting-started)
- An admin account created (`faucet admin create`)
- A MySQL or MariaDB server with a database and at least one table
- Supported versions: MySQL 5.7 through 9.x and MariaDB 10.2 through 11.x (both use the `mysql` driver)

## Step 1: Connect MySQL to Faucet

The DSN format for MySQL:

```
username:password@tcp(host:port)/database?parseTime=true
```

### Via Admin UI (easiest)

1. Open `http://localhost:8080` and sign in.
2. Go to **Databases** and click **Add database**.
3. Pick **MySQL** and fill in:
   - **Host** and **Port**: for example `localhost` and `3306` (the default)
   - **Database**: the schema name, for example `mydb`
   - **Username** and **Password**: special characters need no escaping
   - **TLS**: leave **Off** for local servers; choose **Required** for PlanetScale, Amazon RDS and other hosted MySQL

   Faucet adds `parseTime=true` for you. For MariaDB, pick the **MariaDB** tile; the fields are the same.
4. Check the **API name**. It is filled in from the database name and becomes the URL prefix (`/api/v1/<name>`).
5. Click **Test connection**. Faucet connects and reports how many tables it found, or explains what went wrong.
6. Click **Add database**. The REST API and MCP tools are live right away.

See [Admin UI](https://wiki.faucetdb.ai/admin-ui#connect-a-database) for screenshots of this flow.

### Via CLI

With separate connection flags (Faucet builds and escapes the connection string):

```bash
faucet db add --name mydb --driver mysql \
  --host localhost --port 3306 --user root --password 's3cret@!' --database mydb
```

Or with a connection string:

```bash
faucet db add \
  --name mydb \
  --driver mysql \
  --dsn "root:password@tcp(localhost:3306)/mydb?parseTime=true"
```

::: tip
A server that is already running loads databases added with `faucet db add` on its next start (`faucet stop && faucet serve`). Databases added in the admin UI or through the API are live immediately.
:::

### Via API

```bash
TOKEN=$(curl -s http://localhost:8080/api/v1/system/admin/session \
  -H "Content-Type: application/json" \
  -d '{"email":"admin@example.com","password":"changeme123"}' \
  | jq -r '.session_token')

curl -X POST http://localhost:8080/api/v1/system/service \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "name": "mydb",
    "driver": "mysql",
    "dsn": "root:password@tcp(localhost:3306)/mydb?parseTime=true"
  }'
```

## Step 2: Verify the Connection

```bash
faucet db test mydb
```

Or list tables via the API:

```bash
curl http://localhost:8080/api/v1/mydb/_table \
  -H "Authorization: Bearer $TOKEN"
```

Expected response:

```json
{
  "resource": [
    {"name": "customers"},
    {"name": "orders"},
    {"name": "products"}
  ]
}
```

## Step 3: Query Your Data

### List all records

```bash
curl "http://localhost:8080/api/v1/mydb/_table/customers?limit=10" \
  -H "Authorization: Bearer $TOKEN"
```

### Filter records

```bash
curl "http://localhost:8080/api/v1/mydb/_table/orders?filter=status%20%3D%20'shipped'" \
  -H "Authorization: Bearer $TOKEN"
```

### Select specific fields

```bash
curl "http://localhost:8080/api/v1/mydb/_table/customers?fields=name,email" \
  -H "Authorization: Bearer $TOKEN"
```

### Paginate

```bash
curl "http://localhost:8080/api/v1/mydb/_table/orders?limit=10&offset=0&order=created_at%20DESC" \
  -H "Authorization: Bearer $TOKEN"
```

### Count records

```bash
curl "http://localhost:8080/api/v1/mydb/_table/orders?include_count=true" \
  -H "Authorization: Bearer $TOKEN"
```

## Step 4: Insert, Update, and Delete

### Insert a record

```bash
curl -X POST http://localhost:8080/api/v1/mydb/_table/customers \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"resource": [{"name": "Acme Corp", "email": "contact@acme.com"}]}'
```

Note: MySQL does not support RETURNING, so the response echoes back the submitted data. Use a subsequent GET to retrieve auto-incremented IDs.

### Update a record

```bash
curl -X PATCH "http://localhost:8080/api/v1/mydb/_table/customers?ids=1" \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"name": "Acme Corporation"}'
```

### Delete a record

```bash
curl -X DELETE "http://localhost:8080/api/v1/mydb/_table/customers?ids=1" \
  -H "Authorization: Bearer $TOKEN"
```

## MySQL-Specific Notes

### Always use parseTime=true

The `parseTime=true` parameter is strongly recommended. Without it, MySQL DATE and DATETIME columns are returned as byte strings instead of proper timestamps.

### Stored procedures

Faucet can execute MySQL stored procedures:

```bash
curl -X POST http://localhost:8080/api/v1/mydb/_proc/get_active_users \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"params": {"min_age": 18}}'
```

### Supported features

| Feature | Supported |
|---------|-----------|
| Schema introspection | Yes |
| RETURNING clause | No (MySQL < 8.0.21) |
| Upsert (ON DUPLICATE KEY) | Yes |
| Stored procedures | Yes |
| Views | Yes |
| Parameter style | `?` |
| Default schema | database name |

## MariaDB Notes

MariaDB speaks the MySQL protocol, so Faucet connects to it with the `mysql` driver. Everything in this tutorial applies unchanged: the fields, the DSN format, `parseTime=true`, and the REST and MCP endpoints.

- **Versions:** MariaDB 10.2 through 11.x, including SkySQL and Amazon RDS for MariaDB.
- **Admin UI:** pick the **MariaDB** tile. The fields are the same as for MySQL, and the saved database is listed with the `mysql` driver.
- **CLI:** use `--driver mysql`:

```bash
faucet db add --name shop --driver mysql \
  --host mariadb.internal --user app --password 's3cret@!' --database shop
```

- **Inserts:** as with MySQL, Faucet does not use `RETURNING`, so an insert returns the records you sent. Read auto-incremented IDs back with a follow-up `GET`.

See [Database Connectors → MariaDB](https://wiki.faucetdb.ai/database-connectors#mariadb) for more.

## Give AI Agents Access (MCP)

`faucet serve` also runs an MCP server at `http://localhost:8080/mcp`. Connect an agent with an API key, and the key's [role](https://wiki.faucetdb.ai/rbac) decides which tables it can read or change. For Claude Code:

```bash
claude mcp add --transport http faucet http://localhost:8080/mcp \
  --header 'X-API-Key: faucet_YOUR_KEY'
```

The agent can then call `faucet_list_tables`, `faucet_describe_table` and `faucet_query` on `mydb`. For Cursor, VS Code, Windsurf, Claude Desktop and ChatGPT, see [MCP Server → Connect an AI agent](https://wiki.faucetdb.ai/mcp-server#connect-claude-code). Use a read-only role for agents unless they need to write.

## Troubleshooting

### "Access denied for user"

Check that your username, password, and host are correct. MySQL restricts access by host -- a user allowed from `localhost` may not be allowed from `127.0.0.1`.

### "Unknown database"

The database specified in the DSN doesn't exist. Create it first:
```sql
CREATE DATABASE mydb;
```

### "dial tcp: connect: connection refused"

MySQL isn't running on the specified host/port, or a firewall is blocking the connection.

### Timezone issues

If timestamps look wrong, add `&loc=Local` or `&loc=UTC` to the DSN to control timezone interpretation.

## What's Next

- [Filter Syntax](https://wiki.faucetdb.ai/filter-syntax) -- query filtering operators
- [RBAC](https://wiki.faucetdb.ai/rbac) -- restrict access per role
- [API Reference](https://wiki.faucetdb.ai/api-reference) -- full REST API documentation
- [Database Connectors](https://wiki.faucetdb.ai/database-connectors) -- all connector DSN formats