DEV Community

david duymelinck
david duymelinck

Posted on • Edited on

Enhancing query languages

#php #database

update 8/11/2025: If you want to know the progress read

xwero

My query languages library pivot

david duymelinck ・ Nov 6

#php #database

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 createMapFromQueryResult function. This takes the database result and the query string to create a map or a MapCollection depending on the database result.

As a result you can use the library as follows.

// Users.php
namespace Test\Unit;


use Xwero\ComposableQueries\IdentifierInterface;

enum Users implements IdentifierInterface
{
    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';
$namespaces = new BaseNamespaceCollection('Test\Unit');
$map = getStatement(new PDOConnection(new PDO(getenv('PDO_DSN'))), 
                    $query, 
                   new QueryParametersCollection(Users::Name, 'me'), 
                   namespaces: $namespaces)
       |> getRow(...)
       |> fn($result) => createMapFromQueryResult($result, $query, namespaces: $namespaces);

echo 'hello my name is ' . $map[Users::Name];
Enter fullscreen mode Exit fullscreen mode

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 with a delay.

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

Update 17/10/25:

  • Refactored collectPlaceholders and collectQueryParameters to use internal logic for createMapFromQueryResult. This also made the first two functions cleaner.
  • createMapFromArray is matured to createMapFromQueryResult. The query string hold all the information about the identifiers, so why not use it.
  • The BaseNamespaceCollection arguments in the functions are renamed to create consistency.

The code feels less POC for me because of the queryToIdentifierOrPairCollection function. Having the possibility to add the regex opens up a whole new field of opportunities.
And moving the identifier check created much cleaner code in the functions that depend on that information.

Update 19/10/25:

I added the Redis functions. I used the Predis library because it is the recommended library by Redis.

I realized it will be better when the database functions are in their own projects. This means no multiple namespaces in the functions.php file, but more important not loading the database dependency if it isn't required.
At the moment I want to add one or two database functions more to make sure the base functions and classes are rock solid.

Top comments (8)

Collapse
 
homeless-coder profile image
HomelessCoder •

I've been working on a related problem - decoupling models from infrastructure, but from a different angle.

In my approach, I use backed enums to define database columns where each enum member represents a column and carries its own schema definition (e.g., App\User\Infra\Schema::UserId->getColumnDefinition()). Combined with the repository pattern, and SQL code autogeneration, it creates a type-safe layer between domain logic and database structure, keeping the single source of truth.

I haven't worked with the functional approach much, but your implementation gave me some ideas for my next post - would love to share how the idea can be extended (sorry, not ready for the functional code yet =)). Thanks for sharing this!

Collapse
 
xwero profile image
david duymelinck •

I'm curious about your solution. On the one hand I can see how what I call replacement you could call schema. On the other hand if you still use models don't you have a double configuration with the model properties?

When I adopted DDD entities I felt that having models in the infrastructure was too much. The objects are too similar not to confuse them. In the infrastructure layer I started using query languages.

For the functions, I don't have functional programming in mind. I see my library more as a utility library. Functions make it able to pick and choose what you want to use. I see three big sections; the replacing, the database drivers and the data object creation.

Collapse
 
leob profile image
leob •

You mentioned Laravel - I think the Laravel Eloquent Query Builder is already very "composable" (and enormously powerful, and flexible) - what are you trying to achieve that Eloquent and its query builder object/pattern don't/can't?

I think that Eloquent has raised the bar quite high, it would be a feat to build something better, easier, or more powerful ... but, maybe (probably) I'm just totally missing the point of what you're trying to achieve.

Collapse
 
xwero profile image
david duymelinck • • Edited

If you read the post I embedded at the top I think the goal will become clearer.

The cliff notes are:

  • Don't depend on objects to add functionality
  • Use the native query language augmented by identifiers to have a single source of truth for the database names.
  • create a common language to use for different database systems

I'm working on the first release of the separate packages so at the moment an example of the code is

use Xwero\IdableQueriesCore\IdentifierInterface;
use Xwero\IdableQueriesCore\QueryParameterCollection;
use function Xwero\IdableQueriesPdo\buildStatement as pdoBuildStatement;
use function Xwero\IdableQueriesPdo\executeStatement as pdoExecuteStatement;
use function Xwero\IdableQueriesPredis\buildStatement as redisBuildStatement;
use function Xwero\IdableQueriesPredis\executeStatement as redisExecuteStatement;
use function Xwero\IdableQueriesMongodb\buildStatement as mongoBuildStatement;
use function Xwero\IdableQueriesMongodb\executeStatement as mongoExecuteStatement;

enum Product: string implements IdentifierInterface
{
   case Product = 'products';
   case Id = 'sku';
   case Name = 'name';  
}

$pdoResult = pdoBuildStatement($pdoConnection,
   'SELECT ~Product:Id, ~Product:Name FROM ~Product:Product') 
|> fn($result) => pdoExecuteStatement($result, fn(Statement $s) => $s->fetchAll())
;

$redisResult = redisBuildStatement('get product_cache::Product:Id', 
    new QueryParametersCollection(Product:id, 'dsds-134'))
|> fn($result) => redisExecuteStatement($redisConnection, $result)
;

$mongoResult = mongoBuildStatement('{"~Product:Id" : ":Product:Id"}',
  new QueryParametersCollection(Product:id, 'dsds-134'))
|> fn($result) => mongoExecuteStatement($mongoConnection, $result, 
        fn(Collection $col, Statement $s) => $col->findOne($s))
;
Enter fullscreen mode Exit fullscreen mode

I will write more convenient executeStatement functions for the database systems that need it. But in this example I wanted to show off multiple database types and the common way to query them.
It is the first time I see it myself when there are multiple databases. because I'm working on a package per database system I only saw it in isolation.

So this goes way beyond what Eloquent is doing.

Collapse
 
leob profile image
leob •

Thanks - yeah the fact that it can work with Mongo and Redis does indeed go beyond what Eloquent does ...

Thread Thread
 
xwero profile image
david duymelinck •

If you cut down to the bone, it is a multi database wrapper. And I don't care if you want to use it without the functionality I added on top. That is where the composition comes from.

At the moment I'm working on the basics, but the goal is to be able to use the more advanced options of the different database systems. That is one of the main problems i have with ORM solutions. They only use the common functionality.

You can connect Eloquent with MongoDB, but the models are not ideal to handle with documents.

Collapse
 
igornosatov_15 profile image
Igor Nosatov •

David, I've read both of your articles about the query languages library, and unfortunately, they demonstrate critical mistakes in technical writing. Let me break it down.1. Violating the Golden Rule: "Show, Don't Tell"You talk about code but don't show it working.Bad (your approach):

"I split the functionality into the collectPlaceholders and replacePlaceholders functions."
Good (how it should be):
php// Before: Hard-coded table names
$sql = "SELECT name FROM users WHERE email = ?";

// After: Using your library
$sql = "SELECT ~Users:Name FROM ~Users:Users WHERE ~Users:Email = :Users:Email";

// Result: Now you can change database schema without touching queriesYour only code example is incomprehensible:
php$map = getStatement(new PDOConnection(new PDO(getenv('PDO_DSN'))),
$query,
new QueryParametersCollection(Users::Name, 'me'),
namespaces: $namespaces)
|> getRow(...)
|> fn($result) => createMapFromQueryResult($result, $query, namespaces: $namespaces);Problems:

Requires PHP 8.5 (unreleased!)
No context: What's the problem this solves?
No comparison: How is this better than alternatives?
Cryptic syntax: ~Users:Name - what does this mean?

  1. Complete Absence of Problem StatementYou spend paragraphs on your journey but never explain the actual problem.What you wrote:

"For a long time I had the feeling ORM solutions don't fit in the current database landscape."
What readers need:
php// THE PROBLEM: Changing from 'email' to 'email_address' breaks 47 queries

// File 1
User::where('email', $value)->first();

// File 2

DB::table('users')->select('email')->get();

// File 3
$query = "SELECT email FROM users WHERE email = ?";

// Now do a find-replace across your entire codebase and prayThen show your solution:
php// THE SOLUTION: Single source of truth with enums

enum Users implements IdentifierInterface {
case Users; // Table name
case Email; // email_address in DB
case Name;
}

// Change the enum backing value once, all queries update automaticallyYou never do this. Readers are left wondering: "Why does this exist?"3. The "Stream of Consciousness" StructureYour articles read like a personal diary, not documentation.Your structure:

"I felt ORMs don't fit..."
"I started with Laravel branch..."
"I gave up..."
"Fast forward..."
"I rediscovered DQL..."
"The birth of the library..."
Nobody cares about your journey. They care about solving their problems.Proper structure:

Problem (2 paragraphs, code example)
Why existing solutions fail (comparison table)
Your solution (code examples)
How it works (architecture diagram)
Getting started (5-minute tutorial)
Performance (benchmarks)
Tradeoffs (honest assessment)

Collapse
 
xwero profile image
david duymelinck • • Edited

Nobody cares about your journey. They care about solving their problems.

If people don't want to read it, then they don't. I don't write every posts to go viral.
Not every post needs to be about code, that I leave up to other people. I find those restrictions to cramped for my style.

But I appreciate the advise.