Show HN: filtersql – A Dependency-Free JSON-to-SQL Compiler for LLMs and WebAPIs

Filtersql, a dependency-free JSON-to-SQL compiler for LLMs and web APIs, converts structured JSON payloads into safe, parameterized SQL queries. Designed for DataTables backends, cursor-based pagination, and deterministic LLM pipelines, it supports PostgreSQL, SQLite, MySQL, and Oracle while protecting against SQL injection. The tool is stateless, driver-agnostic, and available via pip install filtersql.

Frontends, REST APIs and LLMs naturally produce structured JSON - not SQL. filtersql defines a declarative JSON query language and compiles it into safe, parameterized SQL. JSON payload or Python dicts → filtersql → SQL string + values list It is intentionally not an ORM or a connection manager. It builds the query and returns it. Query execution remains the responsibility of the caller, making filtersql compatible with any Python DB driver. Designed for three use cases: DataTables server-side backends Cursor-based pagination Access-style, no OFFSET Deterministic LLM pipelines - the LLM generates JSON, filtersql compiles it to safe SQL Supports PostgreSQL, SQLite, MySQL, and Oracle. Secure by default - Fully parameterized queries, protected against SQL injection Multi-database support - PostgreSQL, SQLite, MySQL, and Oracle Lightweight - No ORM required. Works with any database driver psycopg2, sqlite3, mysql-connector, cx Oracle, etc. Language-agnostic - Clean JSON protocol, perfect for REST APIs and frontend applications High-performance pagination - Keyset cursor-based pagination, avoiding slow OFFSET queries AI/LLM friendly - Designed for structured output from large language models filtersql is intentionally: - stateless - deterministic - driver agnostic - parameterized - declarative - portable - JSON-first - LLM friendly Applications describe what they want. filtersql decides how to express it in SQL. From JSON: { "action": "select", "source": "users", "filters": { "field": "name", "operator": "icontains", "value": "john" } } to SQL: select from "users" where "name" ilike '%' || ? || '%' with values: "john" pip install filtersql python import filtersql ds = filtersql.Datasource source = 'users', dbms = 'Pg', placeholder = '%s', query, values = ds.select columns = {'field': 'id'}, {'field': 'first name'}, {'field': 'last name'}, , filters = {'field': 'first name', 'operator': 'icontains', 'value': 'John'}, {'field': 'last name', 'operator': 'icontains', 'value': 'Smith'}, , order = {'field': 'first name', 'order': 'asc'} , limit = {'start': 0, 'length': 10}, print query select "id", "first name", "last name" from "users" where "first name" ilike '%' || %s || '%' and "last name" ilike '%' || %s || '%' order by "first name" asc limit 10 offset 0 print values 'John', 'Smith' cursor.execute query, values rows = cursor.fetchall Every filter is a dict with three keys: {'field': 'status', 'operator': '=', 'value': 'active'} field - column name, JSONB path attributes- amount , or schema-prefixed m.doc type operator - the comparison operator see table below value - the value to compare against A flat list of filters is joined with AND: filters = {'field': 'status', 'operator': '=', 'value': 'active'}, {'field': 'doc date', 'operator': ' =', 'value': '2025-01-01'}, {'field': 'title', 'operator': 'icontains', 'value': 'oxygen'}, Some operators don't need a value: {'field': 'deleted at', 'operator': 'null'} {'field': 'deleted at', 'operator': 'notnull'} Some operators take a list: {'field': 'status', 'operator': 'in', 'value': 'active', 'pending' } {'field': 'doc date', 'operator': 'between', 'value': '2025-01-01', '2025-12-31' } Wrap filters in a dict with key 'or' : filters = {'field': 'status', 'operator': '=', 'value': 'active'}, {'or': {'field': 'first name', 'operator': 'icontains', 'value': 'john'}, {'field': 'last name', 'operator': 'icontains', 'value': 'john'}, }, → "status" = ? AND "first name" like '%'||?||'%' OR "last name" like '%'||?||'%' OR groups can contain AND groups and vice versa: filters = {'or': {'field': 'doc type', 'operator': '=', 'value': 'CONTRACT'}, {'and': {'field': 'doc type', 'operator': '=', 'value': 'ORDER'}, {'field': 'amount', 'operator': ' =', 'value': '10000'}, }, }, → "doc type" = ? OR "doc type" = ? AND "amount" = ? scope is a dict of fixed field: value pairs applied as = filters to every operation on the Datasource - select, where, update, delete, and insert. ds = filtersql.Datasource source = 'documents', dbms = 'Pg', placeholder = '%s', scope = {'tenant id': 42}, scope is always added query, values = ds.select columns=columns, filters=user filters → ... WHERE "tenant id" = %s AND ... query, values = ds.delete id={'id': 99} → ... WHERE "id" = %s AND "tenant id" = %s query, values = ds.insert values={'title': 'New doc'} → INSERT INTO "documents" "title", "tenant id" VALUES %s, %s Columns are passed per call to select , not at construction time - they can be computed at runtime. Each column is a dict with a field key. Any extra keys label , visible , editable etc. are ignored by filtersql and can be used by your frontend layer: columns = {'field': 'id', 'label': 'ID', 'visible': True, 'editable': False}, {'field': 'first name', 'label': 'First Name', 'visible': True, 'editable': True}, {'field': 'last name', 'label': 'Last Name', 'visible': True, 'editable': True}, Plain strings also work: columns = 'id', 'first name', 'last name' Raw expressions with raw=True : columns = {'field': 'COUNT as total', 'raw': True}, {'field': 'MAX age as max age', 'raw': True}, All three return query, values like select . insert query, values = ds.insert values = {'first name': 'John', 'last name': 'Smith'}, insert with returning PostgreSQL only query, values = ds.insert values = {'first name': 'John'}, returning = 'id', update - id can be single or composite key query, values = ds.update id = {'id': 42}, values = {'first name': 'John', 'status': 'active'}, values order: SET values first, then WHERE values → 'John', 'active', 42 delete query, values = ds.delete id={'id': 42} When you want to inject filters into your own handwritten query: ds = filtersql.Datasource source='documents', dbms='Pg', placeholder='%s' where clause, values = ds.where filters= {'field': 'doc type', 'operator': '=', 'value': 'CONTRACT'}, {'field': 'doc date', 'operator': ' =', 'value': '2025-01-01'}, → ' "doc type" = %s and "doc date" = %s ' → 'CONTRACT', '2025-01-01' query = f""" SELECT v.chunk, m.doc type FROM file vectors v JOIN file metadata m ON v.sha256 = m.sha256 WHERE v.model name = %s AND {where clause} ORDER BY v.embedding <= %s """ Builds any query from a single payload dict. Useful for REST APIs and AI-generated actions: python from filtersql import filtersql query, values = filtersql payload = { 'action': 'select', 'source': 'users', 'columns': {'field': 'id'}, {'field': 'first name'} , 'filters': {'field': 'status', 'operator': '=', 'value': 'active'} , 'order': {'field': 'id', 'order': 'asc'} , 'limit': {'start': 0, 'length': 10}, }, dbms = 'Pg', placeholder = '%s', Supported actions: select , insert , update , delete . Replaces placeholders with actual values for logging. Never use the output as real SQL. query, values = ds.select columns=columns, filters=filters print ds.debug query, values select "id", "first name" from "users" where "first name" ilike '%' || 'John' || '%' LLMs should never generate SQL directly. They should generate structured intent. filtersql validates that intent and compiles it into parameterized SQL. The values are always parameterized - no SQL injection risk even if the model produces unexpected output. user question → LLM → JSON → filtersql → parameterized SQL DANGEROUS the naive approach everyone starts with query = f"SELECT FROM users WHERE {llm output}" SQL injection waiting to happen with filtersql payload = json.loads llm response query, values = filtersql payload, dbms='Pg', placeholder='%s' cursor.execute query, values always parameterized, always safe The simplest version without Pydantic: python import json from google import genai from filtersql import filtersql SCHEMA = { "type": "OBJECT", "properties": { "source": {"type": "STRING", "enum": "users", "contracts" }, "filters": { "type": "ARRAY", "items": { "type": "OBJECT", "properties": { "field": {"type": "STRING"}, "operator": {"type": "STRING", "enum": "=", " =", " ", " =", "<", "<=", "icontains", "in" }, "value": {"type": "STRING"} }, "required": "field", "operator", "value" } } }, "required": "source", "filters" } response = client.models.generate content model='gemini-flash', contents=user question, config=genai.types.GenerateContentConfig system instruction="Convert user questions into SQL filter payloads.", response mime type="application/json", response schema=SCHEMA, temperature=0.0, payload = json.loads response.text query, values = filtersql payload, action='select', dbms='SQLite', placeholder='?' With Pydantic for stricter validation: python from pydantic import BaseModel, Field from typing import List, Literal class SQLFilter BaseModel : field: Literal 'doc type', 'doc date', 'author', 'title' operator: Literal '=', ' =', ' ', ' =', '<', '<=', 'icontains', 'between', 'in', 'notin' value: str class QuerySchema BaseModel : semantic query: str sql filters: List SQLFilter parsed = QuerySchema.model validate json response.text filters = f.model dump for f in parsed.sql filters ds = filtersql.Datasource source='documents', dbms='Pg', placeholder='%s' where clause, values = ds.where filters=filters php {'field': 'attributes- amount', 'operator': ' =', 'value': '10000', 'value type': 'numeric'} → "attributes"- 'amount' ::numeric = %s::numeric {'field': 'attributes- expiry', 'operator': '<=', 'value': '2025-12-31', 'value type': 'date'} → "attributes"- 'expiry' ::date <= %s::date Without the cast, '9' '10' as text gives wrong results for numeric ranges. Navigates large tables without OFFSET - no performance degradation. ds = filtersql.Datasource source = 'documents', dbms = 'Pg', order = {'field': 'id', 'order': 'asc'} , forward query, values = ds.select columns = columns, cursor = {'id': last seen id}, direction = 'next', backward query, values = ds.select columns = columns, cursor = {'id': last seen id}, direction = 'prev', jump to specific record query, values = ds.select columns = columns, cursor = {'id': 42}, direction = 'seek', direction | SQL | Use case | |---|---|---| 'next' | field last value | Forward navigation | 'prev' | field < last value | Backward navigation | 'seek' | field = value | Jump to specific record | Multi-column cursors work too: query, values = ds.select columns = columns, cursor = {'first name': last first, 'last name': last last}, direction = 'next', pre-built tsvector column {'field': 'tsv content', 'operator': 'fts', 'value': 'oxygen supply'} → "tsv content" @@ websearch to tsquery 'english', %s on the fly from a text column {'field': 'description', 'operator': 'fts query', 'value': 'oxygen supply'} → to tsvector 'english', coalesce "description", '' @@ websearch to tsquery 'english', %s ds = filtersql.Datasource ..., fts language='italian' {'field': 'content', 'operator': 'fts', 'value': 'oxygen supply'} → match "content" against ? in boolean mode | Operator | Description | |---|---| = = = < <= | Comparison | between | Range - value must be low, high | contains | Case-sensitive substring | starts with | Case-sensitive prefix | ends with | Case-sensitive suffix | not contains | Case-sensitive substring exclusion | not starts with | Case-sensitive prefix exclusion | icontains | Case-insensitive substring | istarts with | Case-insensitive prefix | iends with | Case-insensitive suffix | not icontains | Case-insensitive substring exclusion | null / notnull | NULL checks - no value needed | in / notin | List membership | reverse in | Value in a set of columns - ? IN col1, col2 | regexp | Case-sensitive regular expression | iregexp | Case-insensitive regexp Pg: ~ , Oracle: regexp like with i | fts | Full-text search on indexed column Pg, MySQL | fts query | Full-text search on text column Pg only | | DBMS | dbms value | Default placeholder | |---|---|---| | PostgreSQL | 'Pg' | %s | | SQLite | 'SQLite' | ? | | MySQL | 'mysql' | ? | | Oracle | 'Oracle' | ? | Override with any placeholder your driver expects: ds = filtersql.Datasource ..., placeholder='%s' ds = filtersql.Datasource ..., placeholder=':val' Working examples are in the /examples folder: examples/datatables/ - Flask + DataTables server-side with column filters and global search examples/pagination/ - REST API gateway with security whitelist for insert/update/delete examples/ai/ - Gemini structured output → filtersql → SQLite MIT