MySQL and MariaDB to REST API and MCP Server

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.

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 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.

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
• 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 for screenshots of this flow.

Via CLI

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

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

Or with a connection string:

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

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

faucet db test mydb

Or list tables via the API:

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

Expected response:

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

Step 3: Query Your Data

List all records

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

Filter records

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

Select specific fields

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

Paginate

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

Count records

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

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

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

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:

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:

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 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 decides which tables it can read or change. For Claude Code:

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. 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:

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 -- query filtering operators
• RBAC -- restrict access per role
• API Reference -- full REST API documentation
• Database Connectors -- all connector DSN formats