SQL query builder in 150 lines of Python

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:

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 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).

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

This is part of a series: