API Reference

Faucet exposes a REST API at /api/v1 with two main sections:

• System API (/api/v1/system/) -- manage Faucet configuration (services, roles, API keys, admins)
• Service API (/api/v1/{serviceName}/) -- query and modify data in connected databases

Authentication

All API endpoints (except login, health checks, and OpenAPI spec) require authentication. Faucet supports two authentication methods:

API Key (X-API-Key header)

API keys are bound to roles that define what operations are allowed. Use the X-API-Key header:

curl http://localhost:8080/api/v1/mydb/_table/users \
  -H "X-API-Key: faucet_a1b2c3d4e5f6..."

JWT Bearer Token (Authorization header)

Admin users authenticate with email/password and receive a JWT token. Use the Authorization: Bearer header:

curl http://localhost:8080/api/v1/mydb/_table/users \
  -H "Authorization: Bearer eyJhbGciOiJIUzI1NiIs..."

Authentication Flow

Request arrives
  |
  +-- X-API-Key header present?
  |     YES -> Validate key hash against stored keys -> Principal{type:"api_key", role_id:N}
  |     NO  -> Continue
  |
  +-- Authorization: Bearer <token> present?
  |     YES -> Validate JWT signature + claims -> Principal{type:"admin", admin_id:N}
  |     NO  -> 401 Unauthorized

Obtaining a JWT Token

curl -X POST http://localhost:8080/api/v1/system/admin/session \
  -H "Content-Type: application/json" \
  -d '{"email": "admin@example.com", "password": "yourpassword"}'

Response:

{
  "session_token": "eyJhbGciOiJIUzI1NiIs...",
  "token_type": "bearer",
  "expires_in": 86400,
  "admin_id": 1,
  "email": "admin@example.com",
  "name": "Admin"
}

Tokens expire after 24 hours by default.

---

Response Format

Success (list)

All list endpoints return a resource array with optional meta for pagination:

{
  "resource": [
    {"id": 1, "name": "Alice", "email": "alice@example.com"},
    {"id": 2, "name": "Bob", "email": "bob@example.com"}
  ],
  "meta": {
    "count": 2,
    "total": 150,
    "limit": 25,
    "offset": 0,
    "took_ms": 3.45
  }
}

Meta fields:

Field	Type	Description
count	integer	Number of records in this response
total	integer	Total matching records (only when include_count=true)
limit	integer	Maximum records per page
offset	integer	Number of records skipped
took_ms	float	Query execution time in milliseconds

Success (single)

Single-resource endpoints return the resource object directly (not wrapped in resource).

Error

{
  "error": {
    "code": 404,
    "message": "Table not found: nonexistent",
    "context": {
      "service": "mydb",
      "table": "nonexistent"
    }
  }
}

Field	Type	Description
code	integer	HTTP status code
message	string	Human-readable error description
context	object	Additional error details (optional)

---

Health Check Endpoints

These endpoints require no authentication.

GET /healthz

Liveness probe. Returns 200 if the process is running.

{"status": "ok"}

GET /readyz

Readiness probe. Returns 200 when the server is ready to accept traffic. Pings all database services and reports their status. Returns 503 if any service is unhealthy.

{
  "status": "ok",
  "checks": {
    "mydb": "ok",
    "analytics": "ok"
  }
}

GET /openapi.json

Returns the combined OpenAPI 3.1 specification for all connected services.

---

System API

All system endpoints (except session management) require admin authentication.

Session Management

POST /api/v1/system/admin/session

Authenticate and obtain a JWT token.

Request body:

{
  "email": "admin@example.com",
  "password": "yourpassword"
}

Response (200):

{
  "session_token": "eyJhbGciOiJIUzI1NiIs...",
  "token_type": "bearer",
  "expires_in": 86400,
  "admin_id": 1,
  "email": "admin@example.com",
  "name": "Admin"
}

DELETE /api/v1/system/admin/session

Log out (instructs client to discard token).

Response (200):

{
  "success": true,
  "message": "Session invalidated"
}

Service Management

GET /api/v1/system/service

List all configured database services.

Response (200):

{
  "resource": [
    {
      "id": 1,
      "name": "mydb",
      "label": "My Database",
      "driver": "postgres",
      "schema": "public",
      "read_only": false,
      "raw_sql_allowed": false,
      "is_active": true,
      "schema_lock": "none",
      "connection": {
        "host": "db.example.com",
        "port": 5432,
        "database": "mydb",
        "username": "api",
        "ssl_mode": "require"
      },
      "created_at": "2025-01-15T10:30:00Z",
      "updated_at": "2025-01-15T10:30:00Z"
    }
  ],
  "meta": {"count": 1}
}

The DSN and the password are never returned. connection is a read-only summary parsed from the stored DSN (host, port, database, username, plus path for SQLite and account, schema, warehouse, role for Snowflake). The admin UI uses it to show where each database lives and to pre-fill the edit form.

POST /api/v1/system/service

Create a new database service. You can describe the connection in either of two ways:

• connection: individual fields. Faucet builds a correctly escaped connection string for the driver, so passwords with @, #, / or % need no encoding.
• dsn: a complete driver-specific connection string. See Database Connectors for the formats.

{
  "name": "mydb",
  "label": "My Database",
  "driver": "postgres",
  "connection": {
    "host": "db.example.com",
    "port": 5432,
    "database": "mydb",
    "username": "api",
    "password": "s3cret@!",
    "ssl_mode": "require"
  },
  "schema": "public",
  "read_only": false,
  "raw_sql_allowed": false
}

Required fields: name, driver, and either connection or dsn. If both are sent, dsn wins.

connection fields by driver:

Driver	Fields	ssl_mode values
postgres	host, port (5432), database, username, password, ssl_mode	disable, allow, prefer, require, verify-ca, verify-full
mysql (also MariaDB)	host, port (3306), database, username, password, ssl_mode	false, preferred, true, skip-verify
mssql	host, port (1433), database, username, password, ssl_mode; options.TrustServerCertificate	disable, false, true, strict (sent as encrypt)
oracle	host, port (1521), database (the service name, required), username, password, ssl_mode	true to enable TLS
sqlite	path
snowflake	account (required), username (required), password, database, schema, warehouse, role

port defaults to the driver's standard port when omitted. options is a map of extra driver parameters appended to the connection string. For Snowflake key-pair authentication, leave out password and set private_key_path on the service.

The service is saved even if Faucet cannot connect yet; the response then includes a connection_warning string. To check settings before saving, use POST /api/v1/system/connection/test.

GET /api/v1/system/service/

Get a single service by name. The response has the same shape as a list item.

PUT /api/v1/system/service/

Update a service configuration. label, driver, dsn, connection, private_key_path and schema are only changed when present and non-empty. read_only, raw_sql_allowed and is_active are always set from the body, so send their current values when you change something else (omitting is_active pauses the service).

When you send connection without a password, Faucet keeps the stored password, so you can move a database to a new host without re-entering it. The service is reconnected immediately; a failed reconnect is reported as connection_warning.

DELETE /api/v1/system/service/

Delete a service and disconnect it.

Role Management

GET /api/v1/system/role

List all roles.

POST /api/v1/system/role

Create a new role with optional access rules.

Request body:

{
  "name": "readonly",
  "description": "Read-only access to all services",
  "access": [
    {
      "service_name": "mydb",
      "component": "_table/*",
      "verb_mask": 1
    }
  ]
}

See RBAC for verb mask values and access rule details.

GET /api/v1/system/role/

Get a single role by ID, including its access rules.

PUT /api/v1/system/role/

Update a role. If access is provided, it replaces all existing access rules.

DELETE /api/v1/system/role/

Delete a role by ID.

Admin Management

GET /api/v1/system/admin

List all admin accounts (passwords are never exposed).

POST /api/v1/system/admin

Create a new admin account.

Request body:

{
  "email": "newadmin@example.com",
  "password": "securepassword",
  "name": "New Admin"
}

Password must be at least 8 characters.

API Key Management

GET /api/v1/system/api-key

List all API keys. The actual key value is never shown -- only the prefix.

Response (200):

{
  "resource": [
    {
      "id": 1,
      "key_prefix": "faucet_a1b2c3d",
      "label": "CI pipeline",
      "role_id": 2,
      "is_active": true,
      "created_at": "2025-01-15T10:30:00Z",
      "last_used": "2025-01-16T08:00:00Z"
    }
  ],
  "meta": {"count": 1}
}

POST /api/v1/system/api-key

Create a new API key. The plaintext key is returned once in the response and cannot be retrieved again.

Request body:

{
  "role_id": 2,
  "label": "CI pipeline",
  "expires_at": "2026-01-01T00:00:00Z"
}

Required field: role_id

Response (201):

{
  "id": 1,
  "api_key": "faucet_a1b2c3d4e5f67890abcdef1234567890abcdef1234567890abcdef12345678",
  "key_prefix": "faucet_a1b2c3d",
  "label": "CI pipeline",
  "role_id": 2,
  "is_active": true,
  "created_at": "2025-01-15T10:30:00Z"
}

Save the api_key value immediately. It cannot be retrieved after this response.

DELETE /api/v1/system/api-key/

Revoke (deactivate) an API key by ID.

Connection Testing

POST /api/v1/system/connection/test

Try connection settings without saving anything. This is what the admin UI's Test connection button calls. The body takes the same fields as POST /api/v1/system/service (driver plus connection or dsn; name is optional). If name matches an existing service and no password is given, the stored password is used, which lets you test an edit without re-typing it.

Faucet opens a temporary connection, pings the database, lists its tables, and closes the connection. It gives up after 15 seconds.

Response (200):

{
  "success": true,
  "message": "Connection successful",
  "table_count": 4,
  "tables": ["customers", "order_items", "orders", "products"],
  "latency_ms": 38
}

Errors: 400 when required fields are missing or invalid (for example Invalid connection details: host is required), and 422 with the driver's error when the database can't be reached (for example Could not connect: ... connection refused).

GET /api/v1/system/service/{serviceName}/test

Test connectivity of a saved service by pinging it. If the service is not currently connected, Faucet tries to connect it first.

Response (200):

{
  "success": true,
  "message": "Connection successful"
}

Server Information

GET /api/v1/system/info

Returns the running Faucet version and the registered database drivers. Requires an admin session.

{
  "version": "v0.1.14",
  "drivers": ["mssql", "mysql", "oracle", "postgres", "snowflake", "sqlite"]
}

MCP Configuration

GET /api/v1/system/mcp

Returns MCP server configuration, available tools, resources, and transports.

Schema Contract Locking

Manage schema contracts that protect your API from silent breaking changes. See Schema Locking for full documentation.

PUT /api/v1/system/contract/{serviceName}/mode

Set the schema lock mode for a service.

Request body:

{ "mode": "auto" }

Valid modes: none, auto, strict.

POST /api/v1/system/contract/

Lock all tables in a service (create contracts for each table).

GET /api/v1/system/contract/

List all contracts for a service.

DELETE /api/v1/system/contract/

Unlock all tables in a service (remove all contracts).

GET /api/v1/system/contract/{serviceName}/diff

Get a drift report comparing all locked contracts against the live database schema.

Response (200):

{
  "service_name": "mydb",
  "has_drift": true,
  "has_breaking": true,
  "total_additive": 2,
  "total_breaking": 1,
  "tables": [
    {
      "table_name": "users",
      "has_drift": true,
      "has_breaking": false,
      "items": [
        {
          "type": "additive",
          "category": "column_added",
          "table_name": "users",
          "column_name": "bio",
          "description": "Column \"bio\" was added to table \"users\""
        }
      ]
    }
  ]
}

POST /api/v1/system/contract/{serviceName}/

Lock a single table (create or replace its contract).

GET /api/v1/system/contract/{serviceName}/

Get a single contract. Add ?check=true to include live drift comparison.

DELETE /api/v1/system/contract/{serviceName}/

Unlock a single table (remove its contract).

POST /api/v1/system/contract/{serviceName}/{tableName}/promote

Promote a contract to match the current live schema.

---

Table CRUD Endpoints

These endpoints operate on data in connected databases. The URL pattern is:

/api/v1/{serviceName}/_table/{tableName}

Where {serviceName} is the name of a registered database service and {tableName} is a table in that database.

GET /api/v1/{serviceName}/_table

List all table names in the service's database.

Response (200):

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

GET /api/v1/{serviceName}/_table/

Query records from a table.

Query parameters:

Parameter	Type	Default	Description
filter	string	--	Filter expression (syntax)
fields	string	all	Comma-separated column names to return
order	string	--	Sort order (e.g., name ASC, created_at DESC)
limit	integer	25	Max records to return (capped at 1000)
offset	integer	0	Number of records to skip
include_count	boolean	false	Include total count in meta
ids	string	--	Comma-separated primary key values

Examples:

# Basic query
curl "http://localhost:8080/api/v1/mydb/_table/users"

# With filtering
curl "http://localhost:8080/api/v1/mydb/_table/users?filter=age%20%3E%2021%20AND%20status%20%3D%20'active'"

# Select specific fields
curl "http://localhost:8080/api/v1/mydb/_table/users?fields=id,name,email"

# With ordering and pagination
curl "http://localhost:8080/api/v1/mydb/_table/users?order=created_at%20DESC&limit=10&offset=20"

# Include total count
curl "http://localhost:8080/api/v1/mydb/_table/users?include_count=true&limit=10"

Response (200):

{
  "resource": [
    {"id": 1, "name": "Alice", "email": "alice@example.com"},
    {"id": 2, "name": "Bob", "email": "bob@example.com"}
  ],
  "meta": {
    "count": 2,
    "limit": 25,
    "offset": 0,
    "took_ms": 1.23
  }
}

NDJSON streaming: Set Accept: application/x-ndjson to receive results as newline-delimited JSON (one JSON object per line). Useful for large result sets.

POST /api/v1/{serviceName}/_table/

Insert one or more records. Accepts three body formats.

Batch mode query parameters: rollback and continue (see Batch Transaction Modes below).

Single record:

{"name": "Alice", "email": "alice@example.com"}

Array of records:

[
  {"name": "Alice", "email": "alice@example.com"},
  {"name": "Bob", "email": "bob@example.com"}
]

Resource envelope (DreamFactory-compatible):

{
  "resource": [
    {"name": "Alice", "email": "alice@example.com"},
    {"name": "Bob", "email": "bob@example.com"}
  ]
}

Response (201): For databases that support RETURNING (PostgreSQL), the created records including auto-generated fields are returned. For others, the input records plus a row count are returned.

{
  "resource": [
    {"id": 1, "name": "Alice", "email": "alice@example.com", "created_at": "2025-01-15T10:30:00Z"}
  ],
  "meta": {
    "count": 1,
    "took_ms": 2.45
  }
}

PUT /api/v1/{serviceName}/_table/

Replace records (full update). Each record in the body must include its primary key (id field) or a filter query parameter must be provided.

Batch mode query parameters: rollback and continue (see Batch Transaction Modes below).

Request body:

{
  "resource": [
    {"id": 1, "name": "Alice Updated", "email": "alice.new@example.com", "status": "active"}
  ]
}

PATCH /api/v1/{serviceName}/_table/

Partial update of records matching a filter or ID list. Only the provided fields are modified.

Query parameters: filter or ids (at least one required)

Request body:

{"status": "archived"}

Or with IDs in the body:

{
  "ids": [1, 2, 3],
  "status": "archived"
}

Examples:

# Update by filter
curl -X PATCH "http://localhost:8080/api/v1/mydb/_table/users?filter=status%20%3D%20'inactive'" \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"status": "archived"}'

# Update by IDs
curl -X PATCH "http://localhost:8080/api/v1/mydb/_table/users?ids=1,2,3" \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"status": "archived"}'

DELETE /api/v1/{serviceName}/_table/

Delete records matching a filter or ID list. A filter or IDs must be provided to prevent accidental full-table deletes.

Query parameters: filter or ids (at least one required)

Request body (alternative):

{"ids": [1, 2, 3]}

Or:

{
  "resource": [
    {"id": 1},
    {"id": 2}
  ]
}

Response (200):

{
  "meta": {
    "count": 3,
    "took_ms": 1.12
  }
}

---

Batch Transaction Modes

All write operations (POST, PUT, PATCH, DELETE) support batch transaction modes via query parameters. These control how errors are handled during batch operations.

Modes

Mode	Parameter	Behavior
Halt (default)	(none)	Stop at first error. Prior operations are already committed.
Rollback	?rollback=true	Wrap all operations in a database transaction. On any error, roll back everything. All-or-nothing.
Continue	?continue=true	Process every record independently. Collect errors and return mixed results.

If both rollback and continue are set, rollback takes precedence.

When to use each mode

• Halt — Default behavior. Good for simple operations where you want fast failure.
• Rollback — Data integrity scenarios: financial transactions, order processing, any case where partial state is worse than no change.
• Continue — Bulk data loading: importing CSVs, syncing systems, ETL jobs where you want to process everything and fix failures separately.

Rollback mode

Wraps the entire batch in a database transaction. If any operation fails, all changes are rolled back.

# All-or-nothing insert: if any record fails, none are inserted
curl -X POST "http://localhost:8080/api/v1/mydb/_table/users?rollback=true" \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '[
    {"name": "Alice", "email": "alice@example.com"},
    {"name": "Bob", "email": "bob@example.com"}
  ]'

On success, returns the standard response (201 for POST, 200 for PUT/PATCH/DELETE). On failure, returns the appropriate error status and no records are modified.

Continue mode

Processes each record independently. Returns per-record results with a summary of successes and failures. Available for POST and PUT operations.

# Insert with continue: process all records, report mixed results
curl -X POST "http://localhost:8080/api/v1/mydb/_table/users?continue=true" \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '[
    {"name": "Alice", "email": "alice@example.com"},
    {"name": "Bob", "email": "alice@example.com"},
    {"name": "Charlie", "email": "charlie@example.com"}
  ]'

Response (200 when there are errors, 201 when all succeed):

{
  "resource": [
    {"id": 1, "name": "Alice", "email": "alice@example.com"},
    {"error": {"code": 409, "message": "Insert failed: UNIQUE constraint failed: users.email"}},
    {"id": 3, "name": "Charlie", "email": "charlie@example.com"}
  ],
  "meta": {
    "count": 3,
    "succeeded": 2,
    "failed": 1,
    "errors": [1],
    "took_ms": 5.2
  }
}

Response fields:

Field	Type	Description
meta.count	integer	Total number of operations attempted
meta.succeeded	integer	Number of successful operations
meta.failed	integer	Number of failed operations
meta.errors	array	Zero-indexed positions of failed operations in the resource array
meta.took_ms	number	Total execution time in milliseconds

Each entry in the resource array is either the result of a successful operation (the created/updated record) or an error object with error.code and error.message.

Database support

All five supported databases (PostgreSQL, MySQL, SQL Server, SQLite, Snowflake) support transactions and work with all batch modes.

---

Schema Endpoints

Introspect and modify database schemas.

GET /api/v1/{serviceName}/_schema

Get the full schema for a service, including all tables, views, columns, primary keys, foreign keys, and indexes.

GET /api/v1/{serviceName}/_schema/

Get the detailed schema for a single table.

Response (200):

{
  "name": "users",
  "type": "table",
  "primary_key": ["id"],
  "columns": [
    {
      "name": "id",
      "position": 1,
      "db_type": "integer",
      "go_type": "int32",
      "json_type": "integer",
      "nullable": false,
      "is_primary_key": true,
      "is_auto_increment": true,
      "is_unique": false,
      "default": "nextval('users_id_seq'::regclass)"
    },
    {
      "name": "name",
      "position": 2,
      "db_type": "character varying",
      "go_type": "string",
      "json_type": "string",
      "nullable": false,
      "max_length": 255,
      "is_primary_key": false,
      "is_auto_increment": false,
      "is_unique": false
    },
    {
      "name": "email",
      "position": 3,
      "db_type": "character varying",
      "go_type": "string",
      "json_type": "string",
      "nullable": true,
      "max_length": 255,
      "is_primary_key": false,
      "is_auto_increment": false,
      "is_unique": true
    }
  ],
  "foreign_keys": [],
  "indexes": []
}

POST /api/v1/{serviceName}/_schema

Create a new table.

Request body:

{
  "name": "products",
  "columns": [
    {"name": "id", "type": "serial", "is_primary_key": true},
    {"name": "name", "type": "varchar(255)", "is_nullable": false},
    {"name": "price", "type": "decimal(10,2)"},
    {"name": "created_at", "type": "timestamp", "default": "now()"}
  ]
}

Returns the schema of the newly created table (201).

PUT /api/v1/{serviceName}/_schema/

Alter an existing table. Provide a list of schema changes.

Request body:

{
  "changes": [
    {
      "type": "add_column",
      "column": "description",
      "definition": {"name": "description", "type": "text", "is_nullable": true}
    },
    {
      "type": "drop_column",
      "column": "old_field"
    },
    {
      "type": "rename_column",
      "column": "name",
      "new_name": "title"
    }
  ]
}

Supported change types: add_column, drop_column, rename_column, modify_column

DELETE /api/v1/{serviceName}/_schema/

Drop a table. This is irreversible.

Response (200):

{
  "success": true,
  "message": "Table 'products' dropped successfully"
}

---

Stored Procedure Endpoints

GET /api/v1/{serviceName}/_proc

List all stored procedures and functions for a service.

Response (200):

{
  "resource": [
    {
      "name": "calculate_total",
      "type": "function",
      "return_type": "numeric",
      "parameters": [
        {"name": "order_id", "type": "integer"}
      ]
    }
  ],
  "meta": {"count": 1}
}

POST /api/v1/{serviceName}/_proc/

Call a stored procedure with parameters.

Request body:

{
  "order_id": 42
}

Query parameters are also accepted and merged into the parameter map. This allows simple calls like:

curl -X POST "http://localhost:8080/api/v1/mydb/_proc/calculate_total?order_id=42" \
  -H "Authorization: Bearer $TOKEN"

Response (200):

{
  "resource": [
    {"total": 299.99}
  ],
  "meta": {
    "count": 1,
    "took_ms": 5.67
  }
}

---

Per-Service OpenAPI Spec

GET /api/v1/{serviceName}/_doc

Returns an OpenAPI 3.1 specification for a single service, including schemas derived from actual table definitions.

---

CORS

Faucet enables CORS with the following defaults:

• Allowed origins: *
• Allowed methods: GET, POST, PUT, PATCH, DELETE, OPTIONS
• Allowed headers: Accept, Authorization, Content-Type, X-API-Key, X-Requested-With
• Exposed headers: X-Total-Count, X-Request-ID, Link
• Max age: 300 seconds

---

Rate Limiting

Request rate limiting middleware is available. See Deployment for configuration.

---

Request Size Limit

The maximum request body size is 10 MB by default.