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!

Read on for a preview.

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:

  • why I needed a query builder in the first place
  • why I decided to write my own
    • what alternatives I considered, and why I didn't use an existing library
    • how I knew it wouldn't become too big and/or a maintenance burden
  • how I modeled the problem, and how I got the idea for it

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 0x7fad40935a30>
>>> print(query)

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

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

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)')))
    long(expression) AS alias
>>> print(Query().WHERE('condition', 'another condition'))
    condition AND
    another condition
>>> print(Query().FROM('first').LEFT_JOIN('second USING (column)'))
    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: