Show HN: filtersql – a dependency-free JSON-to-SQL compiler for LLMs and WebAPIs

1 comments

• fthiella 6 hours ago

  I'm Federico. I built filtersql (https://github.com/fthiella/filtersql), a lightweight Python library that takes a declarative JSON payload and compiles it into safe, parameterized SQL strings and values.

  Years ago I tried to build a "Microsoft Access-style" web framework in Perl / HTML::Mason, with forms, dependent dropdowns and master-detail subtables that were entirely generated by declarative JSON configs. I love declarative configs and I still believe that the idea was good! But it was also too complex for a solo developer, especially the frontend, so I stopped the project.

  Anyway, some of the components were good and I couldn't throw them away, and I have been using them for years.

  In particular, the backend JSON-to-SQL compiler was somehow solid, so I decided to port it to Python, and kept using it internally for DataTables backends.

  Recently, while building a custom enterprise RAG, I realized that LLMs are bad and insecure at generating SQL, but are amazing at generating declarative JSON.

  My old engine was exactly what the AI needed to safely query Postgresql metadata before doing vector searches. Before open-sourcing it as a standalone library, I fixed it up, in particular I tried to make the JSON specs as elegant as possible.

    payload = {
      "action": "select",
      "source": "users",
      "filters": [
        {"field": "name", "operator": "icontains", "value": "john"}
      ]
    }
  
    query, values = filtersql(payload, dbms='Pg', placeholder='?')
  

  compiles to:

    select * from "users" where "name" ilike '%' || ? || '%'
  

  with values:

    ["john"]
  

  I’d love to hear your feedback.

  GitHub: https://github.com/fthiella/filtersql Docs: https://filtersql.org

  Thanks, Federico