Oracle to REST API and MCP Server

How do I turn an Oracle 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 oracle \
  --host localhost --user appuser --password-prompt --database FREEPDB1
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/EMPLOYEES?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 an Oracle database (12c through 26ai) 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)
• An Oracle Database instance with at least one table
• Supported versions: Oracle 12c through 26ai, including Oracle Cloud (OCI), Amazon RDS and Azure
• No Oracle Instant Client required -- Faucet uses a pure-Go driver

Step 1: Connect Oracle to Faucet

The DSN format for Oracle:

oracle://username:password@host:port/service_name

Via Admin UI (easiest)

1. Open http://localhost:8080 and sign in.

2. Go to Databases and click Add database.

3. Pick Oracle and fill in:

   • Host and Port: for example localhost and 1521 (the default; Autonomous Database uses 1522)
   • Service name: for example FREEPDB1 or XEPDB1
   • Username and Password: special characters need no escaping
   • TLS: turn On for Oracle Autonomous Database (TCPS)

   To expose another user's tables, open Access and schema options and set Schema to the owner name.

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 oracle \
  --host localhost --port 1521 --user appuser --password 's3cret@!' --database FREEPDB1

Or with a connection string:

faucet db add \
  --name mydb \
  --driver oracle \
  --dsn "oracle://appuser:secret@localhost:1521/XEPDB1"

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": "oracle",
    "dsn": "oracle://appuser:secret@localhost:1521/XEPDB1"
  }'

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": "EMPLOYEES"},
    {"name": "DEPARTMENTS"},
    {"name": "PRODUCTS"}
  ]
}

Note: Oracle table and column names are typically uppercase.

Step 3: Query Your Data

List all records

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

Filter records

curl "http://localhost:8080/api/v1/mydb/_table/EMPLOYEES?filter=DEPARTMENT_ID%20%3D%2010" \
  -H "Authorization: Bearer $TOKEN"

Select specific fields

curl "http://localhost:8080/api/v1/mydb/_table/EMPLOYEES?fields=FIRST_NAME,LAST_NAME,EMAIL" \
  -H "Authorization: Bearer $TOKEN"

Paginate

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

Count records

curl "http://localhost:8080/api/v1/mydb/_table/EMPLOYEES?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/EMPLOYEES \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"resource": [{"FIRST_NAME": "Jane", "LAST_NAME": "Smith", "EMAIL": "jsmith"}]}'

Note: Oracle does not support PostgreSQL-style RETURNING clauses, so the response echoes the submitted records rather than server-generated values (such as sequences or identity columns). Use a subsequent GET to retrieve auto-generated IDs.

Update a record

curl -X PATCH "http://localhost:8080/api/v1/mydb/_table/EMPLOYEES?ids=207" \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"LAST_NAME": "Johnson"}'

Delete a record

curl -X DELETE "http://localhost:8080/api/v1/mydb/_table/EMPLOYEES?ids=207" \
  -H "Authorization: Bearer $TOKEN"

Oracle-Specific Notes

Schema (Owner)

In Oracle, the schema is the same as the database user (owner). By default, Faucet uses the connected user's schema. To query tables owned by a different user:

faucet db add \
  --name hrdb \
  --driver oracle \
  --dsn "oracle://appuser:secret@localhost:1521/XEPDB1" \
  --schema HR

Service name vs. SID

Oracle databases are identified by either a service name or a SID. The DSN format uses service name by default:

oracle://user:pass@host:1521/SERVICE_NAME

For SID-based connections, use the SID parameter:

oracle://user:pass@host:1521?SID=ORCL

Stored procedures

Faucet can execute Oracle stored procedures:

curl -X POST http://localhost:8080/api/v1/mydb/_proc/UPDATE_SALARY \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"params": {"EMP_ID": 100, "NEW_SALARY": 75000}}'

Oracle procedures are invoked using PL/SQL BEGIN ... END; blocks. Procedures that only perform DML (no result sets) return an empty result.

Pagination

Faucet uses Oracle 12c+ OFFSET ... ROWS FETCH NEXT ... ROWS ONLY syntax for pagination. This requires Oracle Database 12c (12.1) or later. Tested through Oracle 26ai.

Supported features

Feature	Supported
Schema introspection	Yes
RETURNING clause	No
Upsert (MERGE)	No
Stored procedures	Yes
Views	Yes
Parameter style	:1, :2, :3
Default schema	Current user
Pagination	OFFSET/FETCH (12c+)

Docker for local development

Run Oracle Database Express Edition locally with Docker:

docker run -e ORACLE_PWD=YourStrong!Passw0rd \
  -p 1521:1521 --name oracle-xe \
  -d container-registry.oracle.com/database/express:latest

Wait for the database to start (this can take a few minutes), then connect:

faucet db add \
  --name localdb \
  --driver oracle \
  --dsn "oracle://system:YourStrong!Passw0rd@localhost:1521/XEPDB1"

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

"ORA-01017: invalid username/password"

Check your username and password. Oracle passwords are case-sensitive. Ensure the user has appropriate permissions (CONNECT and SELECT privileges at minimum).

"ORA-12514: TNS:listener does not currently know of service requested"

The service name in your DSN doesn't match any registered service. Verify the service name with:

SELECT VALUE FROM V$PARAMETER WHERE NAME = 'service_names';

"ORA-12541: TNS:no listener"

Oracle's listener isn't running on the specified host/port. Check that the Oracle listener is started and listening on port 1521 (default).

"connection refused"

Oracle isn't running, the port is wrong, or a firewall is blocking the connection. Verify the host, port, and network connectivity.

Password with special characters

If your password contains @, #, %, or other URL-special characters, URL-encode them in the DSN:

# Password: Pa@ss#1 → Pa%40ss%231
oracle://user:Pa%40ss%231@localhost:1521/XEPDB1

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