11 ms·

Write an SQL query builder in 150 lines of Python

141 points by genericlemon24 5y ago

mynameismon 5y ago
Ah, good ol' SQL injection attacks... If the author is reading, please do add a disclaimer mentioning that the code would be vulnerable to SQL injection attacks. From a purely learning point of view, it is indeed an interesting read, however.
- genericlemon24 5y ago
  (author here) Yup, good thing to note, thank you! I've kinda assumed everyone knows about the named parameters some bindings offer, but that's probably not true. Will add a disclaimer. In the previous articles, I do talk about how this is an upgrade from plain SQL, not from regular full-blown query builders / ORMs.
  - quietbritishjim 5y ago
    > I do talk about how this is an upgrade from plain SQL This statement is about functionality / ease of use, which is fairly orthogonal from (preventing) SQL injection: with plain SQL it's perfectly possible to avoid injection attacks, in fact that's probably the most common and easiest way to do it. In that sense, if anything this is a downgrade from regular SQL.
    - genericlemon24 5y ago
      > In that sense, if anything this is a downgrade from regular SQL. It likely is. Ideally, instead of dynamically building queries, one would use stored procedures. I'm using this with SQLite, which doesn't have stored procedures, so it's an acceptable downgrade for me. Appending strings to lists gets messy quickly, example: https://death.andgravity.com/query-builder-why#preventing-combinatorial-explosion https://death.andgravity.com/query-builder-why#preventing-co...
  - em500 5y ago
    Definitely not true, I've encountered plenty of SQL users at work who never heard of injection attacks or parameterized queries. Some of them even built some ad-hoc query builders to replace some of their own repetitive queries. (Note that parameterized queries alone are not sufficient: often people would try to parameterize table or column names.)
mcdonje 5y ago
SQL is an absolutely amazing domain specific language, but people keep building ORMs & other SQL abstractors. I get building one as a learning or resume project. I don't see how it's useful in most scenarios. Like, there's some difficulty breaking up the queries in a logical way (which leads to duplication), so the solution is another library that you need to write or install and learn how to use? Even if it's easy to use, I can't imagine it'd take less time to read the documentation than to write some SQL. And then there's another dependency; Another thing to check if there's performance issues, another attack vector.
- genericlemon24 5y ago
  Sometimes you need dynamic SQL, because the DB doesn't have stored procedures you can use for the same purpose (e.g. SQLite). I talk more about this use case here: https://death.andgravity.com/query-builder-why#the-problem https://death.andgravity.com/query-builder-why#the-problem
- laszlokorte 5y ago
  The issue with plain SQL is simply that you can not compose queries at runtime. For example let user decide which columns to select, in which order to fetch the rows, which table to query etc. Or not even let the user decide but decide based on some config file or the system state. You end up with a bunch of string manipulation that is fragile and does not compose well. What solution is there except from simulating SQL via your languages nestable data structures?
  - genericlemon24 5y ago
    Someone on Reddit suggested stored procedures, which seems like a good alternative. Alas, SQLite doesn't have them, so query building it is.
    - musingsole 5y ago
      Stored procedures are a nightmare that shepherd your application into an illegible, unmanageable monstrosity. Stored procedures are the slipperiest slope I've seen as a developer.
hownottowrite 5y ago
Or just use sqlalchemy...
- genericlemon24 5y ago
  I wrote an entire article about why I didn't do that: https://death.andgravity.com/own-query-builder#sqlalchemy-core-peewee-query-builder https://death.andgravity.com/own-query-builder#sqlalchemy-co... In general, SQLAlchemy is an excellent choice; in my specific case (single dev with limited time), the overhead would be too much (even with my previous experience with it).
  - mynameismon 5y ago
    A curious question: why not Python's built in Sqlite3 package [0]? [0] https://docs.python.org/3/library/sqlite3.html https://docs.python.org/3/library/sqlite3.html
    - genericlemon24 5y ago
      I am using exactly that; the query builder goes on top of it :)
  - hownottowrite 5y ago
    That’s a great article and a good way to go.
    - genericlemon24 5y ago
      Thank you!
- chooseaname 5y ago
  Exactly. Because nobody should ever use their dev skills to build up their understanding of a tool by recreating some aspect of it.
  - 5y ago
globular-toast 5y ago
> query.SELECT('one').FROM('table') I don't really like this API. SQL is weird because it's written backwards. What does `query.SELECT("one")` on its own represent? A query on any table that happens to have a field called "one"? I know you're not trying to build an ORM, but `Table("table").select("one")` makes a lot more sense for an API since the object `Table("table")` actually has a purpose and would make sense to pass around etc.
- blackbear_ 5y ago
  I concur, despite the downvotes. This is also why the select is last LINQ to SQL [1] queries: var companyNameQuery = from cust in nw.Customers where cust.City == "London" select cust.CompanyName; [1] https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/linq/getting-started https://docs.microsoft.com/en-us/dotnet/framework/data/adone...
- genericlemon24 5y ago
  > What does `query.SELECT("one")` on its own represent? data['SELECT'].append('one') I'm intentionally trying to not depart too much from SQL / list-of-strings-for-each-clause model, since I'd have to invent/learn another one. For a full-blown query builder, I agree Table("table").select("one") is better.
- gpderetta 5y ago
  also select is really project (and where is select), but that's the battle for another day.
mikewarot 5y ago
I've built SQL query builders to get things done behind the scenes a few times. Users just love being able to search any field, and get their results.
krosaen 5y ago
Interesting to see what goes into an ORM library - but as others note, in my experience learning SQL ends up being better. The things that ORMs make easy are already pretty straightforward, and when you get to more advanced queries, the ORM ends up getting in the way and/or in order to use the ORM properly you have to understand SQL deeply anyways. For learning SQL, my favorite resource to get started: Become a SELECT star! https://wizardzines.com/zines/sql/ https://wizardzines.com/zines/sql/ followed by The Best Medium-Hard Data Analyst SQL Interview Questions https://quip.com/2gwZArKuWk7W https://quip.com/2gwZArKuWk7W
greenie_beans 5y ago
this is a good guide for an intermediate-level python developer. thanks.
- genericlemon24 5y ago
  Glad you liked it!
chrisofspades 5y ago
What are you doing to handle more complicated WHERE clauses, such as WHERE last_name = 'Doe' AND (first_name = 'Jane' OR first_name = 'John')?
- genericlemon24 5y ago
  That's what the fancier __init__() at the end of the article[1] is for :) Here's the tl;dr of a real-world example[2]: query = Query().SELECT(...).WHERE(...) for subtags in tags: tag_query = BaseQuery({'(': [], ')': ['']}, {'(': 'OR'}) tag_add = partial(tag_query.add, '(') for subtag in subtags: tag_add(subtag) query.WHERE(str(tag_query)) It can be shortened by making a special subclass, but I only needed this once or twice so I didn't bother yet: for subtags in tags: tag_query = QList('OR') for subtag in subtags: tag_query.append(subtag) query.WHERE(str(tag_query)) [1]: https://death.andgravity.com/query-builder-how#more-init https://death.andgravity.com/query-builder-how#more-init [2]: https://github.com/lemon24/reader/blob/10ccabb9186f531da04db91ee24ba63abc9e4318/src/reader/_storage.py#L1311-L1356 https://github.com/lemon24/reader/blob/10ccabb9186f531da04db...
thangalin 5y ago
Many SQL abstractions substitute SQL keywords (e.g., "SELECT") for a fluent interface function (e.g., "select"). The query builder and output tend to be language-specific. Several years ago, I prototyped a relational mapping language based on XPath expressions. The result is a database-, language-, and output-agnostic mechanism to generate structured documents from flat table structurse. Although the prototype uses XML, other formats are possible by changing the RXM library. The queries might be bidirectional. Given a document and the RXM query that generates the document, it may be possible to update the database using the same query that produced the document. I haven't tried, though. https://bitbucket.org/djarvis/rxm/src/master/ https://bitbucket.org/djarvis/rxm/src/master/
zer0faith 5y ago
Please God no...don't do this.. This is exactly how security vulnerabilities are introduced. Validate input and use parametrized queries over something like this...
- genericlemon24 5y ago
  It's not either-or; I'm using the query builder to build _parametrized_ queries. Here are two examples: https://death.andgravity.com/query-builder-why#introspection https://death.andgravity.com/query-builder-why#introspection https://github.com/lemon24/reader/blob/10ccabb9186f531da04db91ee24ba63abc9e4318/src/reader/_storage.py#L1272-L1308 https://github.com/lemon24/reader/blob/10ccabb9186f531da04db...
bluehark 5y ago
I highly recommend pypika by Kayak: https://github.com/kayak/pypika https://github.com/kayak/pypika Have used in multiple projects and have found it's the right balance between ORMs and writing raw SQL. It's also easily extensible and takes care of the many edge cases and nuances of rolling your own SQL generator.
- orangepanda 5y ago
  > and takes care of the many edge cases and nuances of rolling your own SQL generator Want to elaborate?
  - genericlemon24 5y ago
    For one, it can output more than one flavor of SQL: https://pypika.readthedocs.io/en/latest/3_advanced.html#handling-different-database-platforms https://pypika.readthedocs.io/en/latest/3_advanced.html#hand... Since SQL is ever-so-slightly different across databases, I imagine trying to cover all of them as a single dev is a nightmare (especially if that's not the problem you're trying to solve). I wrote my own query builder because I know for sure I'm only targeting SQLite. The second I need my feed reader library to work with another database engine I'm dumping my own for something more serious – either a full blown database abstraction layer like SQLAlchemy or Peewee (likely without the ORM part), or something simpler like PyPika or python-sql.[1] [1]: I talk more about them here: https://death.andgravity.com/own-query-builder#sqlbuilder-pypika-python-sql https://death.andgravity.com/own-query-builder#sqlbuilder-py...
- genericlemon24 5y ago
  Yup, after my own initial research I found it as well, and I like it a lot. I talk more about it here: https://death.andgravity.com/own-query-builder#sqlbuilder-pypika-python-sql https://death.andgravity.com/own-query-builder#sqlbuilder-py...
Asm2D 5y ago
This reminds me my older project called xql, for node.js: https://github.com/jsstuff/xql https://github.com/jsstuff/xql and fiddle: https://kobalicek.com/experiments/fiddle-xql.html https://kobalicek.com/experiments/fiddle-xql.html Interesting how all these builders look the same :)

Ah, good ol' SQL injection attacks... If the author is reading, please do add a disclaimer mentioning that the code would be vulnerable to SQL injection attacks. From a purely learning point of view, it is indeed an interesting read, however.

genericlemon24 5y ago
(author here) Yup, good thing to note, thank you! I've kinda assumed everyone knows about the named parameters some bindings offer, but that's probably not true. Will add a disclaimer. In the previous articles, I do talk about how this is an upgrade from plain SQL, not from regular full-blown query builders / ORMs.
- quietbritishjim 5y ago
  > I do talk about how this is an upgrade from plain SQL This statement is about functionality / ease of use, which is fairly orthogonal from (preventing) SQL injection: with plain SQL it's perfectly possible to avoid injection attacks, in fact that's probably the most common and easiest way to do it. In that sense, if anything this is a downgrade from regular SQL.
  - genericlemon24 5y ago
    > In that sense, if anything this is a downgrade from regular SQL. It likely is. Ideally, instead of dynamically building queries, one would use stored procedures. I'm using this with SQLite, which doesn't have stored procedures, so it's an acceptable downgrade for me. Appending strings to lists gets messy quickly, example: https://death.andgravity.com/query-builder-why#preventing-combinatorial-explosion https://death.andgravity.com/query-builder-why#preventing-co...
- em500 5y ago
  Definitely not true, I've encountered plenty of SQL users at work who never heard of injection attacks or parameterized queries. Some of them even built some ad-hoc query builders to replace some of their own repetitive queries. (Note that parameterized queries alone are not sufficient: often people would try to parameterize table or column names.)

mcdonje 5y ago

SQL is an absolutely amazing domain specific language, but people keep building ORMs & other SQL abstractors. I get building one as a learning or resume project. I don't see how it's useful in most scenarios. Like, there's some difficulty breaking up the queries in a logical way (which leads to duplication), so the solution is another library that you need to write or install and learn how to use? Even if it's easy to use, I can't imagine it'd take less time to read the documentation than to write some SQL. And then there's another dependency; Another thing to check if there's performance issues, another attack vector.

genericlemon24 5y ago
Sometimes you need dynamic SQL, because the DB doesn't have stored procedures you can use for the same purpose (e.g. SQLite). I talk more about this use case here: https://death.andgravity.com/query-builder-why#the-problem https://death.andgravity.com/query-builder-why#the-problem
laszlokorte 5y ago
The issue with plain SQL is simply that you can not compose queries at runtime. For example let user decide which columns to select, in which order to fetch the rows, which table to query etc. Or not even let the user decide but decide based on some config file or the system state. You end up with a bunch of string manipulation that is fragile and does not compose well. What solution is there except from simulating SQL via your languages nestable data structures?
- genericlemon24 5y ago
  Someone on Reddit suggested stored procedures, which seems like a good alternative. Alas, SQLite doesn't have them, so query building it is.
  - musingsole 5y ago
    Stored procedures are a nightmare that shepherd your application into an illegible, unmanageable monstrosity. Stored procedures are the slipperiest slope I've seen as a developer.

hownottowrite 5y ago

Or just use sqlalchemy...

genericlemon24 5y ago
I wrote an entire article about why I didn't do that: https://death.andgravity.com/own-query-builder#sqlalchemy-core-peewee-query-builder https://death.andgravity.com/own-query-builder#sqlalchemy-co... In general, SQLAlchemy is an excellent choice; in my specific case (single dev with limited time), the overhead would be too much (even with my previous experience with it).
- mynameismon 5y ago
  A curious question: why not Python's built in Sqlite3 package [0]? [0] https://docs.python.org/3/library/sqlite3.html https://docs.python.org/3/library/sqlite3.html
  - genericlemon24 5y ago
    I am using exactly that; the query builder goes on top of it :)
- hownottowrite 5y ago
  That’s a great article and a good way to go.
  - genericlemon24 5y ago
    Thank you!
chooseaname 5y ago
Exactly. Because nobody should ever use their dev skills to build up their understanding of a tool by recreating some aspect of it.
- 5y ago

globular-toast 5y ago

> query.SELECT('one').FROM('table') I don't really like this API. SQL is weird because it's written backwards. What does `query.SELECT("one")` on its own represent? A query on any table that happens to have a field called "one"? I know you're not trying to build an ORM, but `Table("table").select("one")` makes a lot more sense for an API since the object `Table("table")` actually has a purpose and would make sense to pass around etc.

blackbear_ 5y ago
I concur, despite the downvotes. This is also why the select is last LINQ to SQL [1] queries: var companyNameQuery = from cust in nw.Customers where cust.City == "London" select cust.CompanyName; [1] https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/linq/getting-started https://docs.microsoft.com/en-us/dotnet/framework/data/adone...
genericlemon24 5y ago
> What does `query.SELECT("one")` on its own represent? data['SELECT'].append('one') I'm intentionally trying to not depart too much from SQL / list-of-strings-for-each-clause model, since I'd have to invent/learn another one. For a full-blown query builder, I agree Table("table").select("one") is better.
gpderetta 5y ago
also select is really project (and where is select), but that's the battle for another day.

mikewarot 5y ago

I've built SQL query builders to get things done behind the scenes a few times. Users just love being able to search any field, and get their results.

krosaen 5y ago

Interesting to see what goes into an ORM library - but as others note, in my experience learning SQL ends up being better. The things that ORMs make easy are already pretty straightforward, and when you get to more advanced queries, the ORM ends up getting in the way and/or in order to use the ORM properly you have to understand SQL deeply anyways. For learning SQL, my favorite resource to get started: Become a SELECT star! https://wizardzines.com/zines/sql/ https://wizardzines.com/zines/sql/ followed by The Best Medium-Hard Data Analyst SQL Interview Questions https://quip.com/2gwZArKuWk7W https://quip.com/2gwZArKuWk7W

greenie_beans 5y ago

this is a good guide for an intermediate-level python developer. thanks.

genericlemon24 5y ago
Glad you liked it!

chrisofspades 5y ago

What are you doing to handle more complicated WHERE clauses, such as WHERE last_name = 'Doe' AND (first_name = 'Jane' OR first_name = 'John')?

genericlemon24 5y ago
That's what the fancier __init__() at the end of the article[1] is for :) Here's the tl;dr of a real-world example[2]: query = Query().SELECT(...).WHERE(...) for subtags in tags: tag_query = BaseQuery({'(': [], ')': ['']}, {'(': 'OR'}) tag_add = partial(tag_query.add, '(') for subtag in subtags: tag_add(subtag) query.WHERE(str(tag_query)) It can be shortened by making a special subclass, but I only needed this once or twice so I didn't bother yet: for subtags in tags: tag_query = QList('OR') for subtag in subtags: tag_query.append(subtag) query.WHERE(str(tag_query)) [1]: https://death.andgravity.com/query-builder-how#more-init https://death.andgravity.com/query-builder-how#more-init [2]: https://github.com/lemon24/reader/blob/10ccabb9186f531da04db91ee24ba63abc9e4318/src/reader/_storage.py#L1311-L1356 https://github.com/lemon24/reader/blob/10ccabb9186f531da04db...

thangalin 5y ago

Many SQL abstractions substitute SQL keywords (e.g., "SELECT") for a fluent interface function (e.g., "select"). The query builder and output tend to be language-specific. Several years ago, I prototyped a relational mapping language based on XPath expressions. The result is a database-, language-, and output-agnostic mechanism to generate structured documents from flat table structurse. Although the prototype uses XML, other formats are possible by changing the RXM library. The queries might be bidirectional. Given a document and the RXM query that generates the document, it may be possible to update the database using the same query that produced the document. I haven't tried, though. https://bitbucket.org/djarvis/rxm/src/master/ https://bitbucket.org/djarvis/rxm/src/master/

zer0faith 5y ago

Please God no...don't do this.. This is exactly how security vulnerabilities are introduced. Validate input and use parametrized queries over something like this...

genericlemon24 5y ago
It's not either-or; I'm using the query builder to build _parametrized_ queries. Here are two examples: https://death.andgravity.com/query-builder-why#introspection https://death.andgravity.com/query-builder-why#introspection https://github.com/lemon24/reader/blob/10ccabb9186f531da04db91ee24ba63abc9e4318/src/reader/_storage.py#L1272-L1308 https://github.com/lemon24/reader/blob/10ccabb9186f531da04db...

bluehark 5y ago

I highly recommend pypika by Kayak: https://github.com/kayak/pypika https://github.com/kayak/pypika Have used in multiple projects and have found it's the right balance between ORMs and writing raw SQL. It's also easily extensible and takes care of the many edge cases and nuances of rolling your own SQL generator.

orangepanda 5y ago
> and takes care of the many edge cases and nuances of rolling your own SQL generator Want to elaborate?
- genericlemon24 5y ago
  For one, it can output more than one flavor of SQL: https://pypika.readthedocs.io/en/latest/3_advanced.html#handling-different-database-platforms https://pypika.readthedocs.io/en/latest/3_advanced.html#hand... Since SQL is ever-so-slightly different across databases, I imagine trying to cover all of them as a single dev is a nightmare (especially if that's not the problem you're trying to solve). I wrote my own query builder because I know for sure I'm only targeting SQLite. The second I need my feed reader library to work with another database engine I'm dumping my own for something more serious – either a full blown database abstraction layer like SQLAlchemy or Peewee (likely without the ORM part), or something simpler like PyPika or python-sql.[1] [1]: I talk more about them here: https://death.andgravity.com/own-query-builder#sqlbuilder-pypika-python-sql https://death.andgravity.com/own-query-builder#sqlbuilder-py...
genericlemon24 5y ago
Yup, after my own initial research I found it as well, and I like it a lot. I talk more about it here: https://death.andgravity.com/own-query-builder#sqlbuilder-pypika-python-sql https://death.andgravity.com/own-query-builder#sqlbuilder-py...

Asm2D 5y ago

This reminds me my older project called xql, for node.js: https://github.com/jsstuff/xql https://github.com/jsstuff/xql and fiddle: https://kobalicek.com/experiments/fiddle-xql.html https://kobalicek.com/experiments/fiddle-xql.html Interesting how all these builders look the same :)