Re:Write an SQL query builder in 150 lines of Python
by emidln on Friday August 27, 2021 @02:36PM
(#28331449)
Parameters have zero bearing on whether you should dynamically construct SQL strings. If parameters can solve all of your problems, you don't have a dynamic SQL query, you have user-submitted values in a WHERE clause.
What can be parameterized depends wildly on the SQL database in question. I haven't used one that could parameterize table names (for use in dynamic JOINs or CTEs) and many cannot parameterize values outside of a WHERE clause. Dynamically selecting which function to call, clauses to add or subtract, and sort orders are just a slice of places parameters don't help.
In short, parameters alone do no eliminate the need for a query builder. A good query builder should appropriately parameterize values as the underlying database supports and hopefully uses a type system or validation to constrict the domain of values it uses to construct parts of the expression that cannot be parameterized.
What can be parameterized depends wildly on the SQL database in question. I haven't used one that could parameterize table names (for use in dynamic JOINs or CTEs) and many cannot parameterize values outside of a WHERE clause. Dynamically selecting which function to call, clauses to add or subtract, and sort orders are just a slice of places parameters don't help.
In short, parameters alone do no eliminate the need for a query builder. A good query builder should appropriately parameterize values as the underlying database supports and hopefully uses a type system or validation to constrict the domain of values it uses to construct parts of the expression that cannot be parameterized.