Add a mark to finish query

[robert-zaremba]

Let's say we use comments to group sql queries

Example

-- file user.sql

--- UPDATE QUERIES ---

--name: EmailUpdate
UPDATE users SET email = $1 where user_id = $2

--name: LevelUpdate
UPDATE users SET level = $1 where user_id = $2

---  REPORTS ---

...

In current implementation --- REPORTS--- comment belongs to LevelUpdate query. Problem is when LevelUpdate is not complete and we update the query on the program side. Let's imagine t hat we have the following:

--name: LevelUpdate
UPDATE users SET level = $1 where user_id IN

and in the application:

q := queries.LevelUpdate + fmt.Sprintf(" (%s)", strings.Join(userIDs, ","))

This will fail: ERROR: syntax error at end of input (SQLSTATE 42601) - because queries.LevelUpdate is finishing with a comment. I know that we can bind here the sqlx solution, however this is just a simple example.

We need a phrase to end the query, eg: --end

[qustavo]

Good question, a simpler solution would be strip all the comments when parsing the file, so you don't have to write specific annotations.
Does that make sense to you?

[robert-zaremba]

Yes, this is a good idea.

[hxzhao527]

emmmm, is there a risk of sql-injection?

I know that we can bind here the sqlx solution,

sqlx.In maybe better.

[vagra]

i have many sql in dot.sql, i want using comment to splite these lines, but i can't using ---------------- or /**************/

[vagra]

comment must start as -- name: now. example:

-- name: ----------------------------------------
-- this is general sql comments
--------------------------------------------------

-- name: -------- update sql for files ----------

[vagra]

refer to my current dot.sql , it’s a bit embarrassing to write comments like this, I hope it can be improved.

https://github.com/vagra/botools/blob/c6ab0c0a5e4ccba302caf261100cf9cc890cf6f4/dot.sql