SQL gives us rows and columns, while APIs usually need nested JSON. That mismatch is small, but it creates a lot of repetitive code. Most backends choose one of two strategies: use a join and transform the resulting rows into nested JSON, or run multiple queries (often one per table) and then match each result set back to its parent.

That code is not hard, but it is everywhere. And as Robert C. Martin reminds us in Clean Code, duplication is a design smell, even when each copy is “small”. PathQL is my attempt to remove that duplication while keeping SQL in the center.

What is PathQL?

PathQL is not a new database language or an ORM. You still write SQL. The idea is simple: assign a JSON path to each table alias and let the engine merge flat rows into a nested JSON result. In PHP this is available through pathpdo. In Go this is available through pathsqlx. Both follow the same concept.

How it works

At a high level the process has four steps:

  1. Parse the SQL query to detect tables, aliases, joins, and selected columns.
  2. Infer one-to-many or one-to-one using foreign-key metadata and join type.
  3. Build JSON paths for each selected column, optionally overridden by user-provided path hints.
  4. Merge flat rows into a nested tree, deduplicating repeated parent objects.

The important thing is that the database still does what it is good at: filtering, joining, ordering, aggregating. PathQL only handles output shaping.

A concrete example

Suppose you want posts with comments.

SELECT posts.id, posts.title, comments.id, comments.message
FROM posts
LEFT JOIN comments ON comments.post_id = posts.id
WHERE posts.id <= 2
ORDER BY posts.id, comments.id

With a path hint such as "posts": "$.posts", PathQL returns:

{
    "posts": [
        {
            "id": 1,
            "title": "Hello",
            "comments": [
                { "id": 10, "message": "nice" },
                { "id": 11, "message": "great" }
            ]
        },
        {
            "id": 2,
            "title": "World",
            "comments": [
                { "id": 12, "message": "thanks" }
            ]
        }
    ]
}

No manual grouping loops, no temporary maps keyed by ids, no custom serializer for every endpoint.

Why this is better than plain SQL for APIs

To be precise: PathQL is not better than SQL for querying data. It is better than plain SQL plus hand-written reshaping code when your output is nested JSON. Benefits include:

  • Less application code
  • Clearer endpoint code
  • No hidden query generation

In The Mythical Man-Month, Fred Brooks explains that conceptual integrity is a major source of quality. PathQL keeps one conceptual model: SQL for data, paths for shape. That is easier to reason about than mixing SQL, ORM behavior, and custom post-processing logic.

How it compares to GraphQL

PathQL and GraphQL solve a similar user need: retrieve exactly the data shape you want in a single request. But they optimize for different contexts.

GraphQL:

  • client-driven query language
  • strong typed schema and introspection
  • powerful when many clients have different data needs
  • resolver layer can become complex and can suffer from N+1 issues

PathQL:

  • SQL-driven query model
  • no extra resolver layer
  • single SQL statement can avoid N+1 by construction
  • great for server-side teams that care about predictable performance

So this is not “PathQL versus GraphQL” in absolute terms. It is a trade-off. If your product is API-first with many consumer teams, GraphQL can be the right tool. If you mainly need efficient relational data access with nested JSON output, PathQL can be much simpler.

Why a simple idea can be powerful

Many useful tools are small ideas with strong composition. PathQL is one such idea: map table aliases to JSON paths, then merge rows. That is it. Because it is small and allows you to write arbitrary SQL, it works with all advanced SQL features (even subqueries and aggregates).

In A Philosophy of Software Design, John Ousterhout argues for deep modules: simple interfaces that hide complexity. PathQL aims to be exactly that. The interface is tiny, but it removes a recurring category of accidental complexity from application code.

Getting started

PHP (pathpdo):

  • create a connection with PathPdo::create(...)
  • call $db->pathQuery($sql, $params, $paths)

Go (pathsqlx):

  • create your db
  • call db.PathQuery(sql, params, paths)

You may even want to set up a PathQL server (pathql-server).