# How Faucet Turns Your Database Into a REST API

> Pure Go, zero CGO, no code generation. How Faucet introspects your schema at runtime and serves a REST API and MCP server across seven databases.

Source: https://faucetdb.ai/blog/how-faucet-turns-databases-into-apis/

Faucet Team · February 25, 2026 · Updated October 8, 2026 · 11 min read

Tags: engineering, architecture, go, deep-dive

Most database-to-API tools generate code you have to deploy. Faucet doesn’t. It reads your schema at startup and serves typed, parameterized, OpenAPI-documented endpoints in real time. No scaffolding, no templates, no intermediate representations. This post walks through exactly how that works — from the SQL query that reads `information_schema` to the `strings.Builder` call that writes your response.

## By the numbers

Before we get into architecture, here’s what we’re working with:

| Metric | Value |
| --- | --- |
| Binary | single ~57 MB static binary (~16 MB download), stripped, `CGO_ENABLED=0` |
| Databases supported | 7 (PostgreSQL, MySQL, MariaDB, SQL Server, Oracle, SQLite, Snowflake): 6 SQL dialects |
| Platforms | Linux, macOS, Windows (amd64 + arm64) |
| Lines of ORM code | 0 |

Test coverage matters. When your query builder has to produce correct SQL across six SQL dialects and seven databases with different parameter styles, quoting rules, and feature sets, coverage isn’t optional.

## The startup sequence

When `faucet serve` starts, four things happen in order:

**1\. Config store opens.** A SQLite database at `~/.faucet/faucet.db` (WAL mode, single connection) holds every piece of runtime state — service DSNs, roles, API keys, admin accounts. Self-migrating: startup runs idempotent `ALTER TABLE` statements and silently swallows “column already exists” errors. No migration framework needed.

**2\. Connector registry initializes.** A `sync.RWMutex`\-protected map loads each active service from the config store, calls the appropriate driver factory, and establishes a connection pool. The registry is the architectural keystone — every HTTP request resolves its database connector with a read-lock map lookup that returns in nanoseconds.

**3\. Router registers.** Chi router mounts all routes statically. Dynamic dispatch happens at request time via `{serviceName}` URL params, not at route registration. This means adding a new database at runtime (`POST /api/v1/system/service`) works without restarting the server.

**4\. HTTP server starts.** `ReadTimeout: 15s`, `WriteTimeout: 60s`, `IdleTimeout: 120s`. Graceful shutdown with a 30-second drain window. After drain, `registry.CloseAll()` tears down every connection pool.

## Schema introspection: how we learn your database

Every connector implements `IntrospectSchema(ctx) (*model.Schema, error)`. The PostgreSQL implementation runs five independent queries against `information_schema`:

```sql
-- 1. Tables
SELECT table_name, table_type
FROM information_schema.tables
WHERE table_schema = $1

-- 2. Columns (note: udt_name, not data_type — more accurate for arrays/enums)
SELECT table_name, column_name, ordinal_position, column_default,
       is_nullable, udt_name, character_maximum_length,
       numeric_precision, numeric_scale, is_identity
FROM information_schema.columns
WHERE table_schema = $1

-- 3. Primary keys
SELECT tc.table_name, kcu.column_name
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu ON ...
WHERE tc.constraint_type = 'PRIMARY KEY'

-- 4. Foreign keys (four-table join)
SELECT tc.table_name, kcu.column_name,
       ccu.table_name AS ref_table, ccu.column_name AS ref_column,
       rc.update_rule, rc.delete_rule
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu ON ...
JOIN information_schema.constraint_column_usage ccu ON ...
JOIN information_schema.referential_constraints rc ON ...

-- 5. Stored procedures
SELECT routine_name, routine_type, data_type
FROM information_schema.routines
WHERE routine_schema = $1
```

These run independently (not in a transaction — introspection is a read-only snapshot, not a consistency point), then are assembled in-memory into a `*model.Schema` struct.

Type mapping handles 30+ PostgreSQL types. The interesting cases:

```go
case "timestamptz", "timestamp with time zone":
    return "time.Time", "string(date-time)"
case "jsonb", "json":
    return "interface{}", "object"
case "int8", "bigint", "bigserial":
    return "int64", "integer"
```

Each database has its quirks. MySQL reports `tinyint(1)` — Faucet detects this specifically and maps it to `bool`, while all other `tinyint` widths map to `int32`. Snowflake supports JWT key-pair authentication via RSA PEM files, handling both PKCS#1 and PKCS#8 formats. SQL Server quotes identifiers with `[brackets]` instead of `"quotes"`.

## The Connector interface: one contract, six implementations, seven databases

This is the core abstraction. Every database driver implements 20+ methods:

```go
type Connector interface {
    Connect(cfg ConnectionConfig) error
    Disconnect() error
    Ping(ctx context.Context) error
    DB() *sqlx.DB

    // Schema discovery
    IntrospectSchema(ctx context.Context) (*model.Schema, error)
    IntrospectTable(ctx context.Context, tableName string) (*model.TableSchema, error)
    GetTableNames(ctx context.Context) ([]string, error)
    GetStoredProcedures(ctx context.Context) ([]model.StoredProcedure, error)

    // Query building — each returns parameterized SQL + bind slice
    BuildSelect(ctx context.Context, req SelectRequest) (string, []interface{}, error)
    BuildInsert(ctx context.Context, req InsertRequest) (string, []interface{}, error)
    BuildUpdate(ctx context.Context, req UpdateRequest) (string, []interface{}, error)
    BuildDelete(ctx context.Context, req DeleteRequest) (string, []interface{}, error)

    // Dialect-specific details
    QuoteIdentifier(name string) string
    SupportsReturning() bool
    ParameterPlaceholder(index int) string
    // ... and more
}
```

The key dialect differences (MariaDB is served by the MySQL driver, so it shares the MySQL row):

| Driver | Param style | `RETURNING` | Identifier quoting | Special |
| --- | --- | --- | --- | --- |
| PostgreSQL | `$1, $2, $3` | Yes | `"double quotes"` | Full `RETURNING *` on INSERT/UPDATE |
| MySQL / MariaDB | `?, ?, ?` | No | `` `backticks` `` | `tinyint(1)` → bool detection |
| SQL Server | `@p1, @p2, @p3` | No | `[brackets]` | `OUTPUT INSERTED.*` pattern |
| SQLite | `?` | Yes (3.35+) | `"double quotes"` | Pure Go, zero CGO |
| Oracle | `:1, :2, :3` | No | `"double quotes"` | `OFFSET/FETCH` pagination (12c+) |
| Snowflake | `?, ?, ?` | No | `"double quotes"` | JWT/RSA key-pair auth |

The SQLite choice deserves a callout: we use `modernc.org/sqlite`, a pure-Go transpilation of SQLite’s C source. This is what makes `CGO_ENABLED=0` possible across the entire binary. No shared libraries, no build toolchain requirements, no cross-compilation headaches. The same SQLite implementation backs both user databases and Faucet’s internal config store.

## Query building: handwritten SQL, no ORM

Every connector has its own `query_builder.go`. All six use `strings.Builder` for zero-allocation string construction — no `fmt.Sprintf` in the hot path.

Here’s the mental model for a SELECT:

```
GET /api/v1/mydb/_table/users?filter=age>21&order=name+ASC&limit=25&offset=50
```

1. `filter=age>21` passes through a **handwritten recursive-descent parser** (`internal/query/parser.go`). It tokenizes character-by-character (no regex), then parses:

```
expression → or_expr
or_expr    → and_expr ( "OR" and_expr )*
and_expr   → not_expr ( "AND" not_expr )*
not_expr   → "NOT" not_expr | primary_expr
primary     → "(" expression ")" | comparison
comparison → identifier operator value
           | identifier IS [NOT] NULL
           | identifier [NOT] IN (...)
           | identifier [NOT] BETWEEN v AND v
           | identifier CONTAINS v       → LIKE '%v%'
           | identifier STARTS WITH v    → LIKE 'v%'
           | identifier ENDS WITH v      → LIKE '%v'
```

Output: a SQL fragment with placeholders and a `[]interface{}` bind slice. **No string interpolation of user values into SQL, ever.** The parser accepts a `PlaceholderFunc` argument so PostgreSQL gets `$1, $2` and MySQL gets `?, ?`.

2. Column names are validated by `ValidateIdentifier()` in `query/sanitizer.go` — a strict allowlist of safe characters. SQL reserved words like `SELECT`, `DROP`, and `UNION` are rejected outright.
3. Column names in INSERT/UPDATE are **sorted** before building SQL. This produces deterministic query strings — critical for test assertions and potentially for database query plan caching.
4. `conn.BuildSelect()` produces dialect-specific SQL via `strings.Builder`, the connector’s `DB().QueryxContext()` executes it against the connection pool, and rows are scanned via `rows.MapScan()` into `map[string]interface{}`.

The query parser has its own test suite covering valid filters, injection attempts, edge cases, and every operator combination.

## Request lifecycle

A complete request path, annotated:

```
GET /api/v1/mydb/_table/users?filter=age>21&limit=25
│
├─ middleware.RequestID    → X-Request-ID header
├─ middleware.Logger       → structured access log
├─ middleware.Recoverer    → panic recovery
├─ middleware.RealIP       → X-Forwarded-For handling
├─ middleware.CORS         → allows *, exposes X-Total-Count
├─ middleware.Compress     → gzip level 5
├─ middleware.Authenticate → X-API-Key (SHA-256 lookup) or JWT
│
├─ handler.QueryRecords
│  ├─ registry.Get("mydb")           → read-lock map lookup, nanoseconds
│  ├─ query.ParseFilter("age>21")    → ("age > $1", [21])
│  ├─ query.ParseOrderClause(...)    → validated ORDER BY
│  ├─ conn.BuildSelect(ctx, req)     → full SQL via strings.Builder
│  ├─ db.QueryxContext(ctx, sql, args...)  → sqlx against connection pool
│  ├─ rows.MapScan(row)              → map[string]interface{}
│  └─ cleanMapValues()               → []byte → string (MySQL/sqlx quirk)
│
└─ Response: {"resource": [...], "meta": {"count": 25, "limit": 25, "took_ms": 1.234}}
```

Every response includes `took_ms` with microsecond precision: `float64(took.Microseconds()) / 1000.0`. For large result sets, the handler detects `Accept: application/x-ndjson` and switches to streaming — each row is written as a newline-delimited JSON object directly to the `http.ResponseWriter`, bypassing the full-buffer-then-serialize path.

## Security: layers, not a single gate

**Authentication** checks two methods in order:

1. **API Key**: `X-API-Key` header → SHA-256 hash → lookup in SQLite `api_keys` table. The raw key is never stored. The lookup returns a `RoleID` which controls access.
2. **JWT**: `Authorization: Bearer <token>` → validated with `golang-jwt/jwt/v5`. JWT carries `admin_id` in claims. Signed with a configurable secret (startup warns if using the default).

**RBAC** is not table-level on/off — it’s a bitmask system:

```go
type RoleAccess struct {
    ServiceName   string    // which database (or * for all)
    Component     string    // which table
    VerbMask      int       // GET=1, POST=2, PUT=4, PATCH=8, DELETE=16
    RequestorMask int       // API=1, Script=2, Admin=4
    Filters       []Filter  // row-level filter conditions (stored as JSON)
    FilterOp      string    // AND or OR
}
```

Enforcement today is the verb mask: per service, per table, per HTTP verb, and fail-closed — if no rule grants a verb, the request is denied. The `Filters` field is stored with each rule, but row-level filters are not yet applied to queries, so don’t rely on them for multi-tenant isolation.

**SQL injection prevention**: All user values go through parameterized queries. The filter parser produces `(SQL, []interface{})` — SQL contains only placeholders. Column names are validated against a strict allowlist. The sanitizer rejects identifiers containing semicolons, quotes, or SQL reserved words.

## Testing strategy: three tiers

**Unit tests** (fast, no external deps): The filter parser test suite covers normal operations, operator edge cases, nested boolean logic, `NULL` handling, `IN` lists, `BETWEEN`, `LIKE` variants, and SQL injection attempts. Per-connector `query_builder_test.go` files verify SQL generation for each dialect independently.

**Handler integration tests** (no external services): `httptest.Server` with a real SQLite connector as the backing database. These test the full HTTP path — request parsing, authentication, query building, execution, response serialization — against a real (in-memory) database. No mocking of the connector layer.

**External integration tests** (gated): `FAUCET_INTEGRATION=1` enables tests against live PostgreSQL, MySQL, and SQL Server instances. These exercise the full connector lifecycle: connect, ping, introspect, query, disconnect.

What each layer covers:

| Package | What’s covered |
| --- | --- |
| `connector/...` | Query building for all six SQL dialects, registry, Snowflake JWT auth |
| `query/` | Filter parser, order parser, sanitizer, builder |
| `handler/` | Full HTTP request/response, batch operations, system endpoints |
| `server/` | Router setup, middleware chain |
| Other | Config store, MCP tools, OpenAPI generation, auth service, model, telemetry |

The handler tests deserve a callout — they don’t mock the database layer. When a handler test makes `GET /api/v1/testdb/_table/users`, it executes against a real SQLite database with real data. This catches serialization bugs, type mapping issues, and response format problems that mocked tests would miss.

## MCP: how AI agents talk to your database

Faucet ships a native MCP (Model Context Protocol) server with 8 tools. Two transports: `faucet mcp` for stdio (Claude Desktop, Cursor, any local MCP client) and Streamable HTTP at `/mcp` on the same port as the REST API, authenticated with an API key so RBAC applies.

The tools follow a deliberate navigation pattern:

```
faucet_list_services  → discover what databases are connected
faucet_list_tables    → discover what tables exist (with column summaries)
faucet_describe_table → get full schema: columns, types, PKs, FKs, indexes
faucet_query          → read data with filter/order/pagination
faucet_insert         → write data (batch supported)
faucet_update         → update with required filter (no accidental full-table updates)
faucet_delete         → delete with required filter
faucet_raw_sql        → raw SQL, denied unless the role has all five verbs on _sql
```

The “required filter” constraint on mutations is intentional — the tools refuse to operate without a `WHERE` clause. An LLM that hallucinates an empty filter can’t accidentally wipe your table.

Error messages are designed for LLM self-correction: if a table isn’t found, the error response includes all available tables. If a service isn’t found, it lists all services. This lets the agent recover without human intervention.

For the full setup, from `faucet db add` to a key-scoped `/mcp` endpoint, see [how to turn a SQL database into a REST API and MCP server](https://faucetdb.ai/blog/turn-database-into-rest-api-and-mcp-server/).

## Build pipeline

GoReleaser produces six platform targets: `linux/amd64`, `linux/arm64`, `darwin/amd64`, `darwin/arm64`, `windows/amd64`, `windows/arm64`. All compiled with `CGO_ENABLED=0` and stripped (`-s -w`).

```yaml
ldflags:
  - -s -w
  - -X main.version={{.Version}}
  - -X main.commit={{.Commit}}
  - -X internal/telemetry.posthogAPIKey={{.Env.POSTHOG_API_KEY}}
```

The telemetry key injection is worth noting: the PostHog API key exists only in CI secrets and is baked into release binaries via `ldflags`. Dev builds from source have an empty key, and `telemetry.New()` returns `nil` immediately — telemetry is completely inert unless you’re running an official release. Opt-out: `faucet config set telemetry.enabled false` or `FAUCET_TELEMETRY=0`.

Docker images use a minimal `Dockerfile.goreleaser` that copies the pre-built binary — no multi-stage build needed since there are no runtime dependencies. Multi-arch manifests (`amd64` + `arm64`) are published to Docker Hub on every tag.

## Why these decisions

**Why Go?** The single-binary constraint narrows the field to Go, Rust, and Zig. Go wins on ecosystem — `pgx`, `go-sql-driver/mysql`, `go-mssqldb`, `go-ora`, and `gosnowflake` are all mature, production-tested database drivers. The pure-Go SQLite port eliminates CGO entirely.

**Why no ORM?** sqlx is a thin ergonomic layer over `database/sql` — named parameters, struct scanning. The query building is hand-rolled because it has to produce six different SQL dialects (for seven databases) from the same logical request. An ORM would add an abstraction layer that we’d immediately need to bypass for dialect-specific features like `RETURNING *`, `OUTPUT INSERTED.*`, and Snowflake’s JWT auth.

**Why SQLite for config?** Runtime reconfigurability. When you call `POST /api/v1/system/service` to add a new database, it’s saved to SQLite and `registry.Connect()` is called immediately. No restart. No YAML reload. No file watching. The config store uses a single connection (`SetMaxOpenConns(1)`) and WAL mode for safe concurrent reads during request handling.

**Why `strings.Builder` over `fmt.Sprintf`?** In the query-building hot path, `fmt.Sprintf("SELECT %s FROM %s WHERE %s", ...)` allocates intermediate strings that become garbage immediately. `strings.Builder` grows a single buffer. When you’re building SQL for every inbound request, this matters.

## Get started

```bash
# 1. Install
brew install faucetdb/tap/faucet   # or: npx @faucetdb/faucet serve
# 2. Connect a database
faucet db add --name mydb --driver postgres --dsn "postgres://user:pass@localhost:5432/mydb?sslmode=disable"
# 3. Create a read-only role and an API key
faucet role create --name reader --verbs GET
faucet key create --role reader
# 4. Start the server
faucet serve
# 5. Query it
curl -H "X-API-Key: faucet_YOUR_KEY" "http://localhost:8080/api/v1/mydb/_table/users?limit=10"

# OpenAPI 3.1 spec
curl http://localhost:8080/openapi.json

# MCP for AI agents
# http://localhost:8080/mcp with an X-API-Key header (role-scoped)
# or, on your own machine: faucet mcp  (stdio, admin rights)
```

The source is on [GitHub](https://github.com/faucetdb/faucet). Star the repo, file issues, send PRs.

Turn on the data.

## Try Faucet in 60 seconds

```
brew install faucetdb/tap/faucet   # or: npx @faucetdb/faucet serve, then use the admin UI
faucet db add --name mydb --driver postgres --host localhost --user app --password-prompt --database mydb
faucet role create --name reader --verbs GET
faucet key create --role reader
faucet serve   # admin UI, REST API and MCP at http://localhost:8080
```

[Star on GitHub](https://github.com/faucetdb/faucet) [Quick start docs](https://wiki.faucetdb.ai/getting-started) [Connect Claude, ChatGPT, Cursor](https://faucetdb.ai/#mcp)

Faucet is free and open source (MIT). Works with PostgreSQL, MySQL, MariaDB, SQL Server, Oracle, SQLite and Snowflake.

## Keep reading

[The 50ms Trap: Why Agent Loops Don't Tolerate Database Latency Anymore](https://faucetdb.ai/blog/2026-05-02-50ms-database-latency-agent-loop-tax/) (May 2, 2026): A 50ms database query barely registers for a human user. Inside an agent loop that runs twenty of them, it becomes a full second of dead air — and users don't think the database is slow, they think the AI is broken. Here is why the latency math changed in 2026 and what it means for the data layer underneath your agents.

[Every Enterprise Needs an MCP Gateway — Your Database Is the First Thing Behind It](https://faucetdb.ai/blog/2026-04-10-mcp-gateway-database-agent-architecture/) (Apr 10, 2026): The MCP Dev Summit consensus is clear: gateways are the architecture for production AI agents. Uber, AWS, Docker, and Kong are all building them. But a gateway without a database layer behind it is a router with nowhere to route. Here's how the new agent architecture actually works.
