SQL query builder in 150 lines of Python

2021-05-11 ∙ two minute read

In this series, we'll look at an SQL query builder I wrote for my feed reader library.

Yup, you read that right, the whole thing fits in 150 lines!

While the code is interesting in its own right (if for no other reason other than the size), in the first few articles we'll discuss why I wrote it, how I thought about it, and what other options I considered:

After that, we'll rewrite it from scratch, iteratively, and talk about:

  • API design
  • metaprogramming
  • worse ways of doing things
  • why I removed a bunch of features
  • trade-offs, and knowing when to be lazy

So, what does it look like? #

You call KEYWORD methods on a Query object to append text:

>>> query = Query()
>>> query.SELECT('url')
<builder.Query object at 0x7fc953e60640>
>>> print(query)
SELECT
    url

You can chain the calls for convenience (order doesn't matter):

>>> query.FROM('feeds').SELECT('title', 'updated')
<builder.Query object at 0x7fc953e60640>
>>> print(query)
SELECT
    url,
    title,
    updated
FROM
    feeds

To get the SQL, you convert the query to a string:

>>> str(query)
'SELECT\n    url,\n    title,\n    updated\nFROM\n    feeds\n'

Other common things work as well:

>>> print(Query().SELECT(('alias', 'long(expression)')))
SELECT
    long(expression) AS alias
>>> print(Query().WHERE('condition', 'another condition'))
WHERE
    condition AND
    another condition
>>> print(Query().FROM('first').LEFT_JOIN('second USING (column)'))
FROM
    first
LEFT JOIN
    second USING (column)

If you want to take a peek at the code right now, you can find the final version here and the tests here. The version used by reader is here (type-annotated, and with extra features).


New articles will be linked on this page as they get published; you can also get updates via email or Atom feed.

This is my first planned series, and still a work in progress.

This means you get a say in it. Email me your questions or comments, and I'll do my best to address them in one of the future articles.

Next: Why use a query builder in the first place?


This is part of a series: