Write an SQL query builder in 150 lines of Python!

[u/genericlemon24]

https://death.andgravity.com/query-builder-how

[u/lelanthran]

SQL query builders are an excellent example of wrapping a clean, neat and secure API with an ugly, unintuitive, feature-incomplete, bug-ridden and exploitable wrapper.

Seriously, just use the SQL syntax so that the reader knows what is being queried. Using the wrapper means that the reader now needs to know your shitty interface as well as SQL itself.

[u/genericlemon24 → u/lelanthran]

Seriously, just use the SQL syntax so that the reader knows what is being queried.

I agree, this is better for static queries. When you need to build the query dynamically, appending strings in lists all over the place is slightly worse.

Compare:

where_snippets = []

if read is not None:
    where_snippets.append(f"{'' if read else 'NOT'} entries.read")
if important is not None:
    where_snippets.append(f"{'' if important else 'NOT'} entries.important")

if any(where_snippets):
    where_keyword = 'WHERE'
    where_snippet = ' AND\n    '.join(where_snippets)
else:
    where_keyword = ''
    where_snippet = ''

query = f"""
SELECT
    entries.id,
    entries.title
FROM entries
{where_keyword}
    {where_snippet}
"""

and:

query = (
    Query()
    .SELECT('entries.id', 'entries.title')
    .FROM('entries')
)

if read is not None:
    query.WHERE(f"{'' if read else 'NOT'} entries.read")
if important is not None:
    query.WHERE(f"{'' if important else 'NOT'} entries.important")

There are other reasons for using a query builder too, like composition, introspection, and abstraction; I talk more about this here (with examples).

the reader now needs to know your shitty interface as well as SQL itself

SQL > SQL + shitty interface.

SQL + shitty interface > SQL + ad-hoc string building.

[u/[deleted] → u/genericlemon24]

When you need to build the query dynamically, appending strings in lists all over the place is slightly worse.

When you need to build the query dynamically, you're probably doing something wrong - additionally, if you're building the query dynamically from some service - then that means that the RDBMS isn't going to be able to take advantage of execution plan caching, which is going to hinder your performance.

Writing stored procedures or functions for the data the database needs to retrieve or write should be the default point you begin with, always. It allows for separation of concerns, it allows for the RDBMS to do its job and plan around the codes execution, its inherently tied to being able to utilize all the optimizations the SQL engine can perform when it knows beforehand what operations it'll need to execute, and it makes for cleaner application code.

EDIT: I just noticed based on your post history that you mainly use SQLite. Which would be a completely different approach to development compared to that of something like optimizing for a database server.

[u/genericlemon24 → u/[deleted]]

I just noticed based on your post history that you mainly use SQLite.

Thank you for actually looking! :)

[u/lelanthran → u/genericlemon24]

Well, it's a trade-off - using dynamic/generated queries means the code is less readable, while using simple/static queries makes the code more readable.

You start off with static queries, and at some point you have too many variations of the same query so you build it dynamically. Burt when you do, you know you're starting to hurt readability.

Personally, I've not yet found a need for dynamic queries; if the query is complex enough you may as well use a Stored Procedure - that's what they're there for anyway.

Your examples are better rewritten as a SP rather than the unintuitive attempt to "perform queries in code".

[u/genericlemon24 → u/lelanthran]

if the query is complex enough you may as well use a Stored Procedure

SQLite doesn't have stored procedures, so that doesn't apply here (I do agree SPs are a better fit, when available) – the trade-off is between readable code and not having a feature at all.

You start off with static queries, and at some point you have too many variations of the same query so you build it dynamically.

I describe exactly this in a previous article: https://death.andgravity.com/query-builder-why#the-problem

[u/CatolicQuotes → u/lelanthran]

Can you explain how to do user selected filters and sorts with stored procedures?

[u/life-is-a-loop → u/[deleted]]

Reinventing the wheel is an excellent exercise to understand how things work under the hood. And it's fun!

[u/Worth_Trust_3825 → u/life-is-a-loop]

More often than not, these "builders" are much more limiting than just letting me write my own query and run it. It's all good that you reinvent something to see how it could work but then the very same solutions end up in production just because they're "inhouse"

[u/genericlemon24 → u/Worth_Trust_3825]

these "builders" are much more limiting than just letting me write my own query and run it

Good thing being able to write plain SQL was part of the requirements.

then the very same solutions end up in production just because they're "inhouse"

Some "productions" (10 full-time devs) are different than others (1 dev with 10h/month), with very different trade-offs. I was careful to make it very clear building your own doesn't work everywhere (or in most contexts, for that matter).

[u/genericlemon24 → u/life-is-a-loop]

And sometimes, you find out you didn't need a car, just a blinker, and that for your use case, an entire car is overkill.

[u/genericlemon24 → u/[deleted]]

writing a toy version of a real battle tested tool and then publishing about it in this way gives people the sense that they understand what the real battle tested tool is doing, but in fact they don't

On its own and out of the blue, I agree, this can make things look deceptively simple.

Good thing there's another article explaining the context in which the tool was made, and how the lack of features was an informed decision, taken after looking at many of the existing solutions.

[u/myusernameisokay → u/[deleted]]

One of the easiest ways to learn is by doing. Anyone could read all the code for an established project like this, much like you could read all the code for GCC. But reading a ton of code is generally pretty boring, and there’s no guarantee you’ll actually understand what you’re reading.

Doing it yourself proves you actually can make a prototype. Sometimes you’ll come across difficult problems and you’ll need to figure out how to solve them. By solving them you’re improving your problem solving skills and programming skills.

[u/genericlemon24 → u/[deleted]]

I largely agree with you, in general. However, in my specific case, it was the right choice; I write more about it here (there's another disclaimer here).

What about subqueries, functions, aliasing, CTEs, aggregations...?

All already supported in the 150 lines. There are others that aren't and can be easily added, and some I'm OK with not having; I did the due diligence of finding out what I would need, and for the past 2 years I didn't need anything new.

Seriously -- this is a security issue (SQL injection).

No more than plain SQL, and this can be easily avoided with named parameters.

Writing your own is on the same level as rolling your own crypto.

Again, in general, I mostly agree, but I wouldn't go so far. Crypto is used by definition in critical applications, and way more difficult to understand and get right (haven't seen any ORM doctorates around).

I see no issue with doing your own thing if you have clear requirements, know what effort is required to solve the problem, and are willing to expend it. In general, you use a general solution; when you know what you need, it's OK to do something different.