We give every user SQL access to a shared ClickHouse cluster

Trigger.dev unveils TRQL, a custom SQL-like DSL, to provide secure, multi-tenant analytical access to their ClickHouse backend. This deep dive explains their unique approach to security by construction, tenant isolation, and schema-driven features through a sophisticated compilation pipeline. The innovative solution sparks a lively Hacker News debate on the necessity and benefits of custom DSLs versus leveraging existing database security features.

Score

Comments

Highest Rank

on Front Page

First Seen

Mar 21, 11:00 AM

Last Seen

Mar 21, 7:00 PM

Rank Over Time

The Lowdown

Trigger.dev's recent post details TRQL (Trigger Query Language), a custom SQL-like DSL designed to offer secure, multi-tenant SQL access to a shared ClickHouse analytics cluster. The company aims to address challenges like data isolation, preventing malicious queries, and abstracting internal database complexities, making analytical queries safe and user-friendly.

Why a Custom DSL? TRQL serves as a security boundary, enforcing read-only operations through its grammar, compiler-injecting tenant isolation, hiding internal database specifics, and enabling features not natively found in ClickHouse, like virtual columns and custom functions.
ClickHouse as the Backbone: The analytical backend leverages ClickHouse for its columnar storage, performance, rich SQL features, and proven scalability in handling massive datasets.
ANTLR for Parsing: ANTLR generates a lexer and parser from a strict SQL subset grammar, producing an Abstract Syntax Tree (AST) that inherently cannot express write or administrative commands, ensuring security by design.
The Compilation Pipeline: User queries undergo a multi-stage process: parsing to AST, schema validation, tenant and time-based filter injection, value parameterization, and finally, transformation into ClickHouse-compatible SQL, executed in read-only mode.
Intelligent Schema Design: The schema defines virtual columns (e.g., total_cost expanding to cost_in_cents + base_cost_in_cents), renames internal column names to user-friendly terms, applies value transforms (e.g., stripping ID prefixes), and includes metadata for UI rendering.
Custom Functions: TRQL introduces functions like timeBucket() for automatic time granularity adjustment and prettyFormat() to attach rendering instructions (e.g., duration, currency) to computed values, enhancing user experience.
Advanced Editor Experience: The query editor utilizes a dual-parser architecture (Lezer for highlighting, ANTLR for linting) and provides schema-aware autocompletion, offering immediate feedback and guided query construction.
System Safeguards: Various limits are enforced, including concurrency, row count, time range, and query resource usage, to maintain cluster stability and prevent resource exhaustion.

In essence, TRQL provides a robust, secure, and developer-friendly interface to complex analytical data, serving as a foundational element for Trigger.dev's observability and dashboard functionalities, with plans for continued expansion.

The Gossip

DSLs vs. Database Defenses

Many commentators questioned the necessity of building a custom DSL like TRQL, arguing that existing database features such as PostgreSQL's Row Level Security (RLS) or native ClickHouse policies could achieve similar security and isolation without the added complexity. They posited that custom DSLs trade off a broad ecosystem for marginal benefits. Conversely, proponents highlighted the DSL's advantages in creating a tailored 'product experience', abstracting internal schema details, and enforcing strict read-only access for multi-tenant analytical use cases, where an added compilation step provides a stronger security boundary.

Alternative Analytical Approaches

The discussion branched into various alternative strategies for providing secure, tenant-isolated query access. Some shared experiences with MotherDuck, which offers isolated storage and compute per tenant, simplifying access control. Another approach involved creating temporary 'data ponds' of tenant-isolated data in S3 parquet files, queried efficiently with DuckDB and secured via STS tokens, highlighting a diverse landscape of solutions to similar data access challenges.

Multi-Tenant Maneuvers

A core debate emerged around the distinct challenges of multi-tenancy versus single-tenancy security. Commenters emphasized that solutions like RLS, while effective for internal employee access on a single-tenant database, don't directly address the complexities of providing arbitrary SQL access to external users in a multi-tenant environment, especially regarding cross-tenant data leaks and unbounded queries. The trade-offs between managing many isolated database instances versus a single, multi-tenant cluster were also discussed, with the latter often preferred for operational simplicity and granular permission control within a customer's projects.

Agent-Assisted Analytics

A speculative but intriguing theme suggested that the next evolution in data interaction would involve AI agents allowing users to 'chat with data' instead of writing SQL directly. The author confirmed that Trigger.dev's architecture already supports this, indicating a future direction where the underlying DSL could be seamlessly integrated with natural language interfaces.