Usage with pg-promise template parameters?

[rkoval]

Hello!

I'm wondering if there's any support for use in queries written with named parameters from pg-promise, a nodejs PostgreSQL library. Documentation for that syntax can be found here. I realize this syntax deviates from the SQL standard, but I'm wondering if there's any kind of custom support for tweaking the query lexer here.

Thanks for a great library!

[joe-re]

@rkoval HI, thanks for your suggestion.

I don't use pg-promise usually, so I would like to hear more about use cases.

Do You usually write queries on a nodejs file? For now sql-language-server only supports .sql file, hence if we want to support pg-promise, we may need to come up with some ideas how we apply files other than .sql files. Otherwise, if we only support syntax parsing it would not make sense for it.

Thanks.

[rkoval]

In my projects, they are in separate .sql files, so I'm not concerned about use cases where raw queries are written in strings within js/ts files (for example). The main point I'm wanting to convey is that the variable interpolation that pg-promise supports is not valid SQL, so this language server is not usable in any files that use this library.

Here's a very basic example of what I mean... suppose we have a SQL file with the contents:

select id from some_table where id=${id}; -- ${id} is not valid in a SQL query

This string is then loaded from a .sql file and passed into pg-promise's library as something like:

const query = await loadTheRawQueryPlaintextFromTheSqlFile();
const row = db.oneOrNone(query, {id: 1});

Notice the {id: 1} parameter of the oneOrNone function; pg-promise takes the raw SQL plaintext and then will interpolate the variable accordingly in the where id=${id} clause doing things like type conversion or character escaping to prevent against SQL injection

Does this make sense? In probably the most basic case, it seems as though if the query parser/lexer can handle a tokenizing text with start delimiter of ${ and end delimiter of } [1] without it blowing up, this would allow users to use pg-promise with this language server.

---

[1] pg-promise does allow for different delimiters per the link I shared, but that seems like a minor detail that could be controlled by some configuration value passed to language server init or something ismilar

[joe-re]

@rkoval Hi, thanks for your sharing.

Does this make sense? In probably the most basic case, it seems as though if the query parser/lexer can handle a tokenizing text with start delimiter of ${ and end delimiter of } [1] without it blowing up, this would allow users to use pg-promise with this language server.

It makes sense to me. For now, the direction of how we handle various dialects for parser implementation is, making the parser able to parse any dialect sql. Then if we want to make some intelligence to detect using different dialect sql in the different vendor sql, we can implement it as lint rules. We're going to have the plan to implement tokenize ${} as a primitive value.

[joe-re]

@rkoval HI. We've released v1.3.1 and it includes supporting ${IDENT} syntax. Please check it out with the latest version, thanks. 🙏

[joe-re]

@rkoval If you find any problems, please feel free to raise the problem again. (Of course, your PR is always welcome also!) Thanks.

[rkoval]

Hey @joe-re ! Thank you so much for this addition! This does seem to resolve one of my issues; however, it seems like there may just generally be other issues supporting PostgreSQL in my project. I opened up a random, semi-complex query in my project, and the lang server was unable to parse due to multiple issues:

1. CTEs don't support lowercase "as" (e.g. with result as (), even with the uppercase keyword rule disabled in the sqllint config file
2. != operator does not seem to parse in a select clause? (e.g., select table.column != 'some value')
3. is operator not working in select clause? (e.g., select column is not null as column_exists)
4. (there may be more, but I stopped trying after this)

I think my project configuration is set up properly, but it's hard to tell. Perhaps adding some diagnostics to help developers troubleshoot their connection configs would help too. I really do appreciate your work here, but I unfortunately don't think it'll work for my use case. I'll definitely be checking back in the future though!