Enhancing query languages

In this post I want to go over my journey that got me to create Composable queries.

For the people that just want the highlights:

• The library is using enumerations to create a divide between the code and the query container and fields, and at the same time create a single source of truth in your application.
• I'm using functions instead objects so you can pick and choose which parts you want to use in your application.

How I took the first steps

For a long time I had the feeling ORM solutions don't fit in the current database landscape. There are databases that use SQL but the relational part is less important, for example ClickHouse, and the way ORM solutions build queries can even be harmful for those databases.

So I started with a branch of the Laravel database repository. At first my focus was to make local scopes reusable without using traits. But that spiraled into wanting to rewrite the whole thing. Of course with all the existing functionality it was an overload of work. The main thing that I learned from that endeavor is that there are a lot of great patterns hidden in the code. I invite everyone that uses the query builder or Eloquent to check the code out.

After giving up, I needed a reset and I started learning more about the possibilities that SQL provides, and exploring databases I haven't worked with before.

Fast forward to last month, where I rediscovered the beauty of DQL. This is the moment the seed of the Composable queries library started to sprout.

The birth of the library

The idea that started it was simply, what if query language strings can use a centralized name for the containers and the fields without the bagage of an ORM model.

The choice for enumerations as the main driver for this idea was easy. An enumeration is at its core nothing more than an identifier. It can hold more information and behavior, but I leave that up to your consideration.
Backed enumerations hit the sweet spot between living by library rules and being in control for me.

The first task was to process the query language strings to replace the enumerations with container name, field names. I don't need to build a lexer and parser, because I only care about the enumeration placeholders in the strings.
In a very early stage I used the container and field name with a tilde in front of it, but is realized soon this will cause a conflict as soon as two enumerations have the same case but a different value.

So I reached back to DQL where the full or a shortened notation of the model class is used. To make the shortened version work, I added a BaseNamespacesCollection that can be used as a function argument.
I just reviewed the code and it is just stupid with the foreach. But the main idea behind creating a type of what is basically an array, is to give it a specific purpose.

I split the functionality into the collectPlaceholders and replacePlaceholders functions.
The functions accept an $overrides collection, as the name suggests when an enumeration is found in the collection the placeholder will be replaced with that value instead of the value from the enumeration.

The next task was to have a mechanism to identify value placeholders and make it possible to insert them in a safe way.
That is where the collectQueryParameters and PDO\replaceParameters come into play.
The collectQueryParameters function is almost identical with the collectPlaceholders function. The biggest difference is that the collectQueryParameters can accept an array placeholder. In the replaceParameters function the array will be broken up into individual placeholders with their respective values, and the query string placeholder will be replaced with the multiple placeholders.

Now the query string and the possible values are ready to be passed on to the database drivers.
I choose to create a namespace per driver, and at the moment I focused on the PDO driver.
Because most of the work has been done by the previous functions, the driver specific functions are not very exciting to look at.

The cherry on top is the createMapFromArray function. This takes the database result and uses the enumeration as a key. It allows you to use the enumerations not only as query string placeholders but also as identifiers in your application code.
I already see the same problem as with the early placeholder replacements, but it is a early version. I just added it last night.

As a result you can use the library as follows.

// Users.php
namespace Test\Unit;


use Xwero\ComposableQueries\ReplacementInterface;

enum Users implements ReplacementInterface
{
    case Users;
    case Name;
    case Email;
}

// somewhere in your application, with PHP 8.5 (pipe operator)
$query = 'SELECT ~Users:Name FROM ~Users:Users WHERE ~Users:Name = :Users:Name';
$map = getStatement(new PDOConnection(new PDO(getenv('PDO_DSN'))), 
                    $query, 
                   new QueryParametersCollection(Users::Name, 'me'), 
                   new OverrideCollection('Test\Unit'))
       |> getRow(...)
       |> fn($result) => createMapFromArray(Users::Users, $result);

echo 'hello my name is ' . $map[Users::Name];

Next steps

At the moment it is POC level code, so my next task is to make it production worthy. Starting with cleaning up the code and writing real world query string tests.
Once that is done I'm going to add other database drivers.

I'm glad I got to this stage, but this is just the beginning.
It is very likely the links to the code will point to other locations as I move along in the process. I will try to remember to update the post, but that could be on a delay.

What do you think of the idea? What is your experience when you test it?