0sql Review: Making AI Agent SQL Generation More Deterministic and Secure

When AI Agents generate SQL directly from natural language, you face two problems: non-deterministic query construction, and direct exposure of the database. 0sql positions itself as a semantic layer sitting between the Agent and the data warehouse, translating business term specifications into governed SQL statements.

According to 0sql's official documentation and discussions on Hacker News, its core idea is: rather than letting Claude or Copilot write raw SQL directly, have the Agent call a plan_query interface, passing in a specification like {projections: [{field: "Web Net Paid"}]}. The engine returns a SQL statement planned against the governance model. The same specification always returns the same statement.

How Pure Semantic-to-SQL Works

0sql does not parse natural language directly. It expects structured input: a JSON specification that references fields, filters, and aggregations by semantic name. The Agent's job is to map user intent onto this specification; the engine's job is to compile the specification into SQL.

The full workflow is as follows:

1. The Agent receives the user query: "show emea profit last 30 days"
2. The Agent calls the search_fields tool to find matching measures and dimensions
3. The Agent constructs the specification: {projections: [{field: "Profit"}], filters: [{field: "Region", operator: "=", value: "EMEA"}, {field: "Date", operator: ">=", value: "30d"}]}
4. The Agent calls plan_query with that specification
5. The engine returns SQL planned against the semantic model

The semantic model defines measures (aggregations), dimensions (grouping keys), and join relationships. The engine translates the specification into SQL statements that respect these definitions. If the model specifies that "Profit" is sum(sales.profit) joined to region.name, the engine generates the corresponding SELECT, JOIN, and WHERE clauses.

Semantic Model is the core building block of 0sql, essentially a business semantic contract described in YAML or JSON. It maps the physical tables and columns scattered across the data warehouse into measures, dimensions, joins, and filter rules familiar to business users. Typical definitions include: the aggregation expression for a measure (such as sum(sales.profit)), the source column for a dimension (such as region.name), join keys between tables, and row-level security conditions that can be attached to queries. Models are typically version-controlled and stored in Git repositories, so every SQL plan can be traced back to a specific model version. The immediate benefit is that business judgments like "what is profit" and "which region does profit belong to" are pulled out of the Agent's prompt and solidified into a reviewable, testable model—avoiding the same term being interpreted as different SQL across different conversations.

Three Layers of Security Boundaries

The security model has three layers, which is 0sql's most noteworthy design.

Model-level governance: The semantic model defines which fields exist and how they aggregate. If a field is not in the model, the Agent cannot query it. This prevents the Agent from accessing raw columns or constructing arbitrary joins.

Parameterized output: In the SQL returned by the engine, user-supplied values exist as placeholders. 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 can only see data from their own region, the model includes that constraint in every query. The Agent doesn't need to know these rules exist.

Parameterized Query refers to SQL statements where user input is not directly concatenated into the string, but instead appears as placeholders (such as ? or $1) in the statement template, with actual values passed separately by the database driver at execution time. Traditional SQL injection works because an attacker can close quotes and append fragments like OR 1=1 to change the statement structure; once parameterized, values are always parsed as data rather than code, the structure is fixed by the planner, and the injection surface is eliminated. 0sql performs this transformation internally within the engine: the Agent's value: "EMEA" only ends up in bound parameters and never appears in the returned SQL text, so even if the Agent is induced by a malicious prompt, it cannot rewrite query semantics through the value field.

The Engineering Value of Determinism and Caching

The engine guarantees that the same specification produces the same SQL. This matters in three scenarios.

Debugging: If the Agent generated a bad query, you can reproduce it by re-running the specification—no need to replay the entire LLM conversation.

Caching: You can cache SQL statements by specification hash. If the Agent asks the same question twice, you can skip planning and return the cached statement directly.

Auditing: You can log specifications instead of SQL. Specifications are smaller, more readable, and don't leak schema details.

Planning is fast. In the example, a three-field query took 70 microseconds—fast enough to sit on the hot path of the Agent loop.

Comparison with Cube and MetricFlow

0sql faces two semantic layer competitors: Cube and MetricFlow. The key difference is positioning.

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 cache)	Implicit (macro expansion)
Security model	Model-level + row-level	Data access policies	dbt exposures + grants
Agent integration	CLI + HTTP API tool calls	REST API (requires wrapping)	SQL interface (no tool boundary)
Expressiveness	Full SQL feature set	Limited by GraphQL schema	Limited by dbt macro system

As the table shows, 0sql is more expressive because it doesn't constrain query shape. Cube's GraphQL schema limits what you can ask; MetricFlow's macro system limits how metrics can be combined; 0sql can compile any specification into SQL, as long as the fields exist in the model.

Semantic Layer is an abstraction layer between the data warehouse and upper-layer applications that defines the query interface in business terms rather than physical table columns. Its value is threefold: first, governance—centrally managing metric definitions and row/column permissions; second, reuse—keeping the same metric consistently defined across BI, Agents, and reports; third, decoupling—when the physical schema changes, only the semantic model needs adjustment, leaving upstream queries unaffected. Cube and MetricFlow belong to an earlier generation of semantic layers, primarily serving embedded analytics, BI dashboards, or dbt metric workflows, with query entry points being GraphQL, REST, or SQL with metric references. 0sql follows the same philosophy but swaps the caller from human analytics tools to AI Agents, so its interface is designed as tool-call-friendly JSON specifications with an explicit promise of deterministic output.

CLI and Agent Integration Patterns

0sql ships with a CLI (zsql) that wraps the HTTP API, 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, which the Agent can pipe to a query executor or log for review.

For Agents using function calling (OpenAI, Anthropic), you can expose the HTTP API as a tool. The Agent calls search_fields to discover available fields, then calls plan_query to generate SQL. Throughout the process, the Agent never writes SQL directly.

Architecture

The engine is stateless. It reads the semantic model from a Git branch (stored in 0sql cloud) and plans queries on demand. You can self-host by pointing the CLI at your own model repository.

The architecture chain is as follows:

Agent (Claude)
  │ 1. search_fields("profit")
  ▼
0sql API (stateless)
  │ 2. Load semantic model from Git
  ▼
Model repository (YAML/JSON)
  │ 3. plan_query(spec)
  ▼
SQL generator
  │ 4. Return SQL
  ▼
Agent (executes SQL)

The semantic model defines four categories of elements: measures (such as sum(profit), count(orders)), dimensions (such as region.name, date), join relationships, and row-level security filters. The engine only compiles these; it does not execute queries. The Agent is responsible for running the SQL against the data warehouse.

Failure Modes to Watch Out For

0sql is not a silver bullet, and the documentation explicitly lists several failure modes.

Model drift: If the semantic model falls out of sync with the warehouse schema, the engine will generate invalid SQL. You need a CI pipeline to validate model-to-warehouse consistency.

Field name ambiguity: If the model defines two similarly named fields ("Profit" and "Net Profit"), the Agent may pick the wrong one. The search_fields tool returns multiple matches, but the final choice rests with the Agent. Clear naming conventions are essential.

Complex queries: The engine generates SQL for any specification, but complex queries (window functions, recursive CTEs) may not map cleanly to the semantic model. You may need to define custom measures or fall back to raw SQL.

Rate limits: The HTTP API is hosted by 0sql. If you exceed the rate limit, queries fail. You need retry logic or a self-hosted deployment.

Model Drift refers to the gradual divergence between a semantic model's definitions and the actual data warehouse schema. Common triggers include: upstream table structure changes, column renames, aggregation definition adjustments, partition field replacements—without the semantic model being updated in sync. Drift does not error at model load time; it only surfaces as invalid SQL or incorrect results at query execution time. Defenses include incorporating model validation into CI, automatically comparing whether referenced fields exist and whether join key types match after every warehouse schema change; and logging planning failure rates and SQL execution errors in production, feeding anomalous specifications back into test cases. For Agent pipelines that depend on 0sql, model drift is the most underestimated yet frequently triggered source of failure.

Technical Judgment: When to Use 0sql

Scenarios where it fits:

• You're building Agent-driven analytics and need to prevent SQL injection
• You want deterministic query generation for caching and debugging
• You need to enforce row-level security at the semantic layer
• You're integrating coding Agents that can call HTTP APIs or shell commands

Scenarios to avoid:

• 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 but don't want to manage a model repository

The core value is determinism and security at the query construction layer. If the Agent writes SQL directly, you have injection risk and non-reproducible queries. If the Agent uses 0sql, you get governed SQL and a clear audit trail. The cost is expressiveness: you can only query what the semantic model defines.

For teams building Agent data pipelines, 0sql offers a middle path between "let the LLM write raw SQL" and "a full BI platform." Its trade-offs are clear, and it suits scenarios that put security and auditability first.

References

• 0sql.io
• Hacker News discussion