rockdai / sql-bricks

Transparent, Schemaless SQL Generation
http://csnw.github.io/sql-bricks
MIT License
213 stars 25 forks source link

Params support to sql() #74

Closed Suor closed 9 years ago

Suor commented 9 years ago

sql() can now be used in various places to insert raw sql fragments. But using this we use easy value conversion we have with diffrent ops. So I suggest this syntax:

sql('col1 @> ? and col2 > now() - ?::interval', val1, val2)

Here first argument is arbitrary sql with placeholders and then follow values to fill in placeholders. Values will be converted with sql.convert() call. This is backwards compatable, since now sql() is single argument. Placeholder char/string is debatable as well as passing values as array.

The motivation for this is complex scenarios like function calls, type casts and custom operators. Implementing this you will provide easy to use workaround for many future feature requests (at least for postgresql extension).

prust commented 9 years ago

@Suor:

This is backwards compatible, since now sql() is single argument

Actually, sql() was just changed to be multi-argument a couple weeks ago when #61 was (finally!) merged: https://github.com/CSNW/sql-bricks/blob/master/sql-bricks.js#L19. All additional arguments (after the SQL string) are set to this.vals which are, in turn processed in sql().toString() if it's being run in parameterized mode: https://github.com/CSNW/sql-bricks/blob/master/sql-bricks.js#L24. This processing adds the values to the parameter values set and converts plain ? and $ placeholders to ?1 and $1 placeholders.

So I think maybe it already does a lot of what you're requesting? I'm not sure if the ::interval in your example is something used directly by postgres SQL or is something intended to be consumed by sql-bricks, to "hint" at what type the value sent to convert() is, in cases where it isn't clear by the value itself?

Suor commented 9 years ago

Or that makes it and I can just upgrade a dependency. Thanks.