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 |
|---|---|
| Total Go source | 28,641 lines |
| Test code | 11,822 lines (41% of codebase) |
| Test files | 23 |
| Test cases | 849 |
| Packages tested | 16 (all passing) |
| Binary size | ~47 MB (stripped, CGO_ENABLED=0) |
| Databases supported | 6 (PostgreSQL, MySQL, SQL Server, Oracle, SQLite, Snowflake) |
| External dependencies | 115 |
| Lines of ORM code | 0 |
The test-to-source ratio matters. When your query builder has to produce correct SQL across six dialects 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:
-- 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 = $1These 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:
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
This is the core abstraction. Every database driver implements 20+ methods:
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:
| Driver | Param style | RETURNING | Identifier quoting | Special |
|---|---|---|---|---|
| PostgreSQL | $1, $2, $3 | Yes | "double quotes" | Full RETURNING * on INSERT/UPDATE |
| MySQL | ?, ?, ? | 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=50filter=age>21passes through a handwritten recursive-descent parser (internal/query/parser.go, 826 lines). 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 ?, ?.
-
Column names are validated by
ValidateIdentifier()inquery/sanitizer.go— a strict allowlist of safe characters. SQL reserved words likeSELECT,DROP, andUNIONare rejected outright. -
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.
-
conn.BuildSelect()produces dialect-specific SQL viastrings.Builder, the connector’sDB().QueryxContext()executes it against the connection pool, and rows are scanned viarows.MapScan()intomap[string]interface{}.
The query parser alone has 153 test cases 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:
- API Key:
X-API-Keyheader → SHA-256 hash → lookup in SQLiteapi_keystable. The raw key is never stored. The lookup returns aRoleIDwhich controls access. - JWT:
Authorization: Bearer <token>→ validated withgolang-jwt/jwt/v5. JWT carriesadmin_idin claims. Signed with a configurable secret (startup warns if using the default).
RBAC is not table-level on/off — it’s a bitmask system:
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
}Row-level filters are injected into query building. If a role’s access rule includes Filters: [{column: "org_id", operator: "=", value: "42"}], that condition is AND’d into every query made with that API key. Multi-tenant isolation without application code.
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: 849 tests across 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.
Test distribution:
| Package | Test cases | What’s covered |
|---|---|---|
connector/... | 303 | Query building for all 6 dialects, registry, Snowflake JWT auth |
query/ | 153 | Filter parser, order parser, sanitizer, builder |
handler/ | 113 | Full HTTP request/response, batch operations, system endpoints |
server/ | 47 | Router setup, middleware chain |
| Other | 233 | 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 7 tools. Two transports: faucet mcp for stdio (Claude Desktop, VS Code) and POST /mcp for Streamable HTTP on the same port as the REST API.
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 filterThe “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.
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).
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 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
# Install
brew install faucetdb/tap/faucet
# Start server + add a database
faucet serve
faucet db add --name mydb --driver postgres \
--dsn "postgres://user:pass@localhost:5432/mydb"
# Your API is live
curl http://localhost:8080/api/v1/mydb/_table/users?limit=10
# OpenAPI spec
curl http://localhost:8080/openapi.json
# MCP for AI agents
faucet mcp # stdio mode for Claude DesktopThe source is on GitHub. Star the repo, file issues, send PRs.