Query Builders

Let's imagine that we have an API endpoint that looks like the following:

GET /dogs?breed={breed}&size={size}

Both of the parameters are optional, and it maps to a database table called dogs. In the first variation no filters are provided:

SELECT * FROM "dogs"

In another variation one filter is provided:

SELECT * FROM "dogs" WHERE "breed" = ?

And in yet another variation two filters are provided:

SELECT * FROM "dogs" WHERE "breed" = ? AND "size" = ?

We'll now explore some different ways we can build these queries.

One way to build the queries would be with raw SQL:

SELECT * FROM "dogs"
WHERE (@filter_breed = 0 OR "breed" = ?)
AND (@filter_size = 0 OR "size" = ?)

For something simple this kind of works, but this is a contrived example. In the real world queries would have myriad possible clauses, CTEs, sub-queries, optional joins, etc. While the simplicity seems appealing at first, this quickly becomes a maintainability nightmare.

SQL isn't a general purpose programming language and it does especially poorly when you want to add branching logic and start passing incomplete queries around for reuse.

Another approach is to use an ORM. In a true ORM you don't write SQL, but instead you write queries in a different DSL that can compile down to SQL. How close to SQL that DSL is will depend on the library. You will also have to a fair bit of setup to get things going that we will skip here. Here is how it might look with an ORM:

async Task<IEnumerable<Dog>> QueryDogs(string breed, string weight)
{
  var dogs = ctx.Dogs;
  if (!string.IsNullOrEmpty(breed))
  {
    dogs = dogs.Where(dog => dog.Breed == breed);
  }
  if (!string.IsNullOrEmpty(breed))
  {
    dogs = dogs.Where(dog => dog.Weight == weight);
  }

  return await dogs.ToListAsync();
}

This example is in an excellent ORM: Entity Framework. Here the dogs variable represents a query that can be passed around, and it's trivial to add branching logic to it. This scales up to more complicated queries pretty well, but it has a couple of problems.

The first problem is that you are writing something so different from SQL that you don't know what the generated SQL is going to look like until the DSL gets compiled down to SQL. Perhaps the generated SQL is what you intended, but perhaps it isn't. The more complex the query gets the greater the likelihood is that the ORM will generate a query in way that isn't performant.

The second problem is that the DSL might not be as expressive as SQL. More specifically there might be queries that you could easily write in SQL that you cannot in the DSL.

While this example might not seem that different from the others, the more complex the queries get the more they will deviate from SQL.

There is a third level of abstraction here: the query builder. You write your queries in your programming language rather than SQL, but the query builders semantics maps closely to SQLs. Here is how it looks with query builder:

async function queryDogs(breed?: string, weight?: string): Promise<Dog[]> {
  let query = db.selectFrom('dogs');
  if (breed) {
    query = query.where('breed', '=', breed); 
  }
  if (weight) {
    query = query.where(weight, '=', weight); 
  }

  return await query.selectAll().execute();
}

This is with an excellent query builder called Kysely. Here we get the things we want from the ORM:

• Type safety and editor integration
• Easy branching logic
• Easy to pass queries around
• Remains maintainable as queries grow in complexity

And we lost a of the disadvantages that the ORM has:

• Easy to tell what the resulting query will look like
• As expressive as SQL

While raw SQL is not abstract enough for back-end services I feel that full ORMS are often too abstract for them. Query builders offer a pleasant middle ground.