When an agent generates SQL from natural language, you face two problems: non-deterministic query construction and direct database exposure. 0sql positions itself as a semantic layer that sits between the agent and your warehouse, translating business-term specifications into governed SQL statements.
The pitch is simple. Instead of letting Claude or Copilot write raw SQL, the agent calls a plan_query endpoint with a spec like {projections: [{field: "Web Net Paid"}]}. The engine returns a single SQL statement planned against a governed model. Same spec, same statement, every time.
What Pure Semantics-to-SQL Means
0sql does not parse natural language directly. It expects structured input: a JSON spec that references fields, filters, and aggregations by their semantic names. The agent's job is to map user intent to this spec. The engine's job is to compile that spec into SQL.
The workflow:
- Agent receives user query: "show emea profit last 30 days"
- Agent calls
search_fieldstool to find matching measures and dimensions - Agent constructs a spec:
{projections: [{field: "Profit"}], filters: [{field: "Region", operator: "=", value: "EMEA"}, {field: "Date", operator: ">=", value: "30d"}]} - Agent calls
plan_querywith the spec - Engine returns SQL planned against the semantic model
The semantic model defines measures (aggregations), dimensions (grouping keys), and joins. The engine translates the spec into a SQL statement that respects those definitions. If the model says "Profit" is sum(sales.profit) joined to region.name, the engine generates the appropriate SELECT, JOIN, and WHERE clauses.
Security Boundaries
The security model has three layers:
Model-level governance: The semantic model defines what fields exist and how they aggregate. If a field is not in the model, the agent cannot query it. This prevents agents from accessing raw columns or constructing arbitrary joins.
Parameterized output: The engine returns SQL with placeholders for user-supplied values. The agent never interpolates strings into SQL. This eliminates injection risk at the query construction layer.
Row-level security: The model can encode row-level filters. If a user should only see their own region's data, the model includes that constraint in every query. The agent does not need to know about these rules.
Determinism and Caching
The engine guarantees that the same spec produces the same SQL. This matters for three reasons:
Debugging: If an agent generates a bad query, you can reproduce it by re-running the spec. You do not need to replay the LLM conversation.
Caching: You can cache SQL statements by spec hash. If the agent asks the same question twice, you skip planning and return the cached statement.
Auditing: You can log specs instead of SQL. The spec is smaller, more readable, and does not leak schema details.
The planning time is fast. The example shows 70 microseconds for a three-field query. This is fast enough to sit in the hot path of an agent loop.
CLI Integration Pattern
0sql ships with a CLI (zsql) that wraps the HTTP API. This is designed for coding agents that can execute shell commands.
zsql sql --expr "web net paid, web sold date, net paid"
The CLI returns SQL to stdout. The agent can pipe this into a query executor or log it for review.
For agents that use function calling (OpenAI, Anthropic), you expose the HTTP API as a tool. The agent calls search_fields to discover available fields, then plan_query to generate SQL.
{
"name": "plan_query",
"description": "Generate SQL from a business-term specification",
"parameters": {
"type": "object",
"properties": {
"spec": {
"type": "object",
"properties": {
"projections": {"type": "array"},
"filters": {"type": "array"}
}
}
}
}
}
The agent constructs the spec, calls the tool, and receives SQL. It never writes SQL directly.
Comparison to Cube and MetricFlow
0sql competes with Cube and MetricFlow, both of which define semantic layers for BI tools. The key differences:
| Feature | 0sql | Cube | MetricFlow |
|---|---|---|---|
| Primary use case | Agent-driven queries | Embedded BI dashboards | dbt-native metrics |
| Query interface | JSON spec → SQL | GraphQL, REST, SQL | SQL with metric references |
| Determinism guarantee | Explicit (same spec = same SQL) | Implicit (query plan caching) | Implicit (macro expansion) |
| Security model | Model-level + row-level filters | Data access policies | dbt exposures + grants |
| Agent integration | CLI + HTTP API for tool calling | REST API (requires wrapper) | SQL interface (no tool boundary) |
| Expressiveness | Full SQL feature set | Limited by GraphQL schema | Limited by dbt macro system |
0sql is more expressive because it does not constrain the query shape. Cube's GraphQL schema limits what you can ask for. MetricFlow's macro system limits how metrics compose. 0sql compiles arbitrary specs into SQL, as long as the fields exist in the model.
Architecture Shape
The engine is stateless. It reads the semantic model from a Git branch (stored in the 0sql cloud) and plans queries on demand. You can self-host by pointing the CLI at your own model repository.
┌─────────────────┐
│ Agent (Claude) │
└────────┬────────┘
│ 1. search_fields("profit")
▼
┌─────────────────┐
│ 0sql API │
│ (stateless) │
└────────┬────────┘
│ 2. Load semantic model from Git
▼
┌─────────────────┐
│ Model Repo │
│ (YAML/JSON) │
└─────────────────┘
│ 3. plan_query(spec)
▼
┌─────────────────┐
│ SQL Generator │
└────────┬────────┘
│ 4. Return SQL
▼
┌─────────────────┐
│ Agent │
│ (executes SQL) │
└─────────────────┘
The semantic model defines:
-
Measures: Aggregations like
sum(profit),count(orders) -
Dimensions: Grouping keys like
region.name,date - Joins: How tables connect
- Filters: Row-level security rules
The engine compiles these into SQL. It does not execute queries. The agent is responsible for running the SQL against the warehouse.
Failure Modes
Model drift: If the semantic model falls out of sync with the warehouse schema, the engine generates invalid SQL. You need a CI pipeline that validates the model against the warehouse.
Ambiguous field names: If the model defines two fields with similar names ("Profit" and "Net Profit"), the agent may pick the wrong one. The search_fields tool returns multiple matches, but the agent must choose. You need clear naming conventions.
Complex queries: The engine generates SQL for arbitrary specs, but complex queries (window functions, recursive CTEs) may not map cleanly to the semantic model. You may need to define custom measures or fallback to raw SQL.
Rate limits: The HTTP API is hosted by 0sql. If you exceed rate limits, queries fail. You need retry logic or a self-hosted deployment.
Code Example: Agent Tool Integration
Here is how you expose 0sql as a tool in an OpenAI function-calling agent:
import openai
import requests
ZSQL_API = "https://app.0sql.io/projects/tpcds/branches/main/sql"
ZSQL_KEY = "zqk_..."
tools = [
{
"type": "function",
"function": {
"name": "plan_query",
"description": "Generate SQL from business terms",
"parameters": {
"type": "object",
"properties": {
"projections": {
"type": "array",
"items": {"type": "string"},
"description": "Fields to select"
},
"filters": {
"type": "array",
"items": {
"type": "object",
"properties": {
"field": {"type": "string"},
"operator": {"type": "string"},
"value": {"type": "string"}
}
}
}
}
}
}
}
]
def plan_query(projections, filters):
spec = {
"projections": [{"field": f} for f in projections],
"filters": filters
}
resp = requests.post(
ZSQL_API,
headers={"Authorization": f"Bearer {ZSQL_KEY}"},
json={"spec": spec}
)
return resp.json()["sql"]
messages = [{"role": "user", "content": "Show EMEA profit for last 30 days"}]
response = openai.chat.completions.create(
model="gpt-4",
messages=messages,
tools=tools
)
tool_call = response.choices[0].message.tool_calls[0]
sql = plan_query(
projections=["Profit"],
filters=[
{"field": "Region", "operator": "=", "value": "EMEA"},
{"field": "Date", "operator": ">=", "value": "30d"}
]
)
print(sql)
The agent constructs the spec from user intent, calls the tool, and receives SQL. It never writes SQL directly.
Technical Verdict
Use 0sql when:
- You are building agent-driven analytics and need to prevent SQL injection
- You want deterministic query generation for caching and debugging
- You need row-level security enforced at the semantic layer
- You are integrating with coding agents that can call HTTP APIs or shell commands
Avoid 0sql when:
- You need a full BI platform with dashboards and visualizations (use Cube or Metabase)
- Your queries are too complex to express in a semantic model (use raw SQL with parameterization)
- You need sub-millisecond query planning (the HTTP round-trip adds latency)
- You want to self-host without managing a model repository
The core value is determinism and security at the query construction layer. If your agent writes SQL directly, you have injection risk and non-reproducible queries. If your agent uses 0sql, you get governed SQL with a clear audit trail. The trade-off is expressiveness: you can only query what the semantic model defines.
Top comments (0)