Mapping a subset DSL to SQL doesn’t sound too difficult since SQL already has those boolean expressions and such. The database/sql library uses context for cancellation, so queries that take too long could be cancelled by a timer goroutine.
One thing for me that would be helped by a library is keeping track of the argument placeholders ($1 $2 $3) and their indexing in the slice input to database/sql for the variadic part. Adding FOR UPDATE sometimes is also kind of ugly. Here’s a case where I dynamically constructed a query: https://github.com/pciet/wichess/blob/master/game.go#L209 Matt On Friday, March 2, 2018 at 12:38:23 PM UTC-6, Benjamin Thomas wrote: > > I believe correctly used database/sql (with the argument placeholders) >> protects against SQL injection >> > > Yeah I badly explained this, an SQL builder solves security *AND* > flexibility for me. Standard database/sql placeholders are too painful when > the user params are too complex, and I can't just pass around SQL fragments. > > It sounds like you are reinventing SQL. Why do you need a DSL? >> > > Yes, in a way SQL would be awesome, but way too powerful (and too > verbose). You can potentially access data from other tables, update/delete > data, etc. > > And how could you pass along raw SQL securely? > > I guess restricting data access could solve some issues. But let's say I'd > like to give access to a regex filter for some columns, but not others (for > performance reasons). Not sure if this would be possible at all via db > policies. > > I guess I'm looking for a "dumbed down" query language. > > In other words, as a programmer I've always been frustrated by search > forms I've developed. As a user, same thing, I always find them too > restrictive. > > I feel access to a DSL could be interesting for a power user, rather than > trying to anticipate every combination of search params a user would want > to perform. > > Look at github for example, their advanced search form is interesting, and > love how readable the url can be: > https://github.com/search?q=language:Go+stars:<100+forks:>500 > > However every params seems to be ANDed, so let's say you'd like to search > golang repos with less than 100 stars OR forks greater than 500, you can't > do it. > > Also if you pass invalid input, you seem to get garbage > : /search?q=language:whatever+stars:<100, so I feel that overall the user > experience is not that great. > > You see my point? > > > Le vendredi 2 mars 2018 15:11:19 UTC+1, matthe...@gmail.com a écrit : >> >> To prevent SQL injection and for flexibility, I'm set on using an sql >>> builder library. >> >> >> I believe correctly used database/sql (with the argument placeholders) >> protects against SQL injection. >> >> There’s a query builder for postgres with MIT license posted here a few >> days ago: https://groups.google.com/forum/#!topic/golang-nuts/Mtqvr1N1zAI >> >> Otherwise strings.Builder (or bytes.Buffer pre-1.10), + string >> concatenation, or fmt.Sprintf can do it. >> >> ## First, create a solid CLI app. Then port it to the web via a JSON API, >>> that would simply consume the query string. >> >> >> In Go this might be best done as a non-main package with a cmd folder >> that has a folder for the server and a folder for the CLI app. >> >> I'm thinking of implementing a lexer/parser for this, but first I'd like >>> to make sure I'm not going to reinvent the wheel :) >> >> >> It sounds like you are reinventing SQL. Why do you need a DSL? >> >> Matt >> >> On Friday, March 2, 2018 at 7:45:19 AM UTC-6, Benjamin Thomas wrote: >>> >>> Hello gophers, >>> >>> Sorry if this is considered noise to some, as I have a question which is >>> not specifically go related. >>> >>> I have a personal project in which I'd like to use go though. >>> >>> Basically, I'd like to create a complex search form, returning data >>> backed by an SQL database. >>> >>> To prevent SQL injection and for flexibility, I'm set on using an sql >>> builder library. >>> >>> However I'm not sure how to go about querying the data itself, via query >>> params, without creating lots of boiler plate and duplication. >>> >>> I'm wondering if a solution similar to what I'm looking for exists, as >>> I've never stumbled upon one... >>> >>> I'm submitting my thoughts below, and would greatly appreciate feedback >>> :) >>> >>> ===NOTES_START=== >>> # Idea for query params, for a search form. >>> >>> Upon UI changes, javascript would generate the appropriate final query >>> string >>> >>> A query string could be typed in by a power user, to handle cases not >>> covered by a simpler UI (via the url or text input) >>> >>> ## First, create a solid CLI app. Then port it to the web via a JSON >>> API, that would simply consume the query string. >>> >>> ``` >>> go run ./cmd/query/main.go QUERY_STRING >>> ``` >>> >>> ## Query string format would follow this principle >>> >>> PARAM_NAME : VALUE : OPERATOR >>> >>> ``` >>> # Commands >>> columns:posted_on,short_descr:eq >>> columns:posted_on,short_descr:hide >>> columns:posting_id,posted_on,short_descr:show >>> >>> limit:10:eq >>> limit:10 # would default to `eq`? >>> >>> page:1 >>> page:2 >>> offset:20 >>> >>> # Filtering >>> euros:11.94 # would default to `eq`? >>> euros:11.94:eq >>> euros:100:lt >>> euros:100:lte >>> >>> comment:FIXME # would default to `eq`? >>> comment:FIXME:eq >>> comment:NULL:eq >>> comment:NULL:ne >>> comment:%tickets%:like >>> comment:%Tickets%:ilike >>> >>> payee:Amazon|Google:re # regex >>> payee:AMAZON|Google:rei # regex, case insensitive >>> >>> ``` >>> >>> ## Question: how would I chain commands? I cannot use & in urls. >>> >>> ### Maybe with a pipe char >>> >>> QUERY_STRING | QUERY_STRING | QUERY_STRING >>> >>> ### Or via AND, OR keywords >>> >>> ``` >>> qs=QUERY_STRING >>> >>> qs AND qs OR qs >>> ``` >>> >>> ### Boolean logic, force the use of parentheses? >>> >>> ``` >>> qs=QUERY_STRING >>> >>> (qs AND qs) OR (qs OR qs) >>> ``` >>> ===NOTES_END=== >>> >>> Basically, I guess I'm looking for some kind of DSL. >>> >>> I'm thinking of implementing a lexer/parser for this, but first I'd like >>> to make sure I'm not going to reinvent the wheel :) >>> >>> Thanks for your interest and input! >>> >> -- You received this message because you are subscribed to the Google Groups "golang-nuts" group. To unsubscribe from this group and stop receiving emails from it, send an email to golang-nuts+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
