Open lorefnon opened 8 months ago
Thank you for including all this helpful context. I hadn't seen the DBeaver implementation before, so that's a useful source of potential solutions here. I also learned that some drivers support a version of this out of the box, such as MySQL.
Here are some disjoint thoughts on the problem space
vscode-sql-notebook
is that the SQL source files do not include any custom syntax. This creates a guarantee that any files opened and used with vscode-sql-notebook will maintain compatibility with other SQL tools. Note that this is also a distinct advantage vs. the alternative of using a Python Jupyter notebook with a SQL client– these files are a custom JSON format that isn't interoperable with other SQL tools.
vscode-sql-notebook
does not impose anything on other team members who might work on the same source code.Parse the query for standard query substitution identifiers, ?
for MySQL and $n
for PostgreSQL, then show an input box via the native VSCode text input API for each variable. Pass these values directly to the driver query execution invocation where the driver will handle escaping etc.
Hmm, ok. If we avoid any preprocessing in the notebook, I'd likely need a preprocessor at my end to convert pgtyped files to sql files for notebook. Which may not be too difficult - just an extra step.
Notebooks are (more or less) stateless at the moment. In my opinion, persistent parameter state encourages bad usage patterns.
The motivation behind this is somewhat hard for me to understand. I was thinking we could save the variable values as comments in the notebook so that they are available as examples (or sensible defaults) to someone else trying out the notebook. This could be useful esp. because we can't easily reference some external data in a sql notebook.
It would be nice to be able to have cells with parameterized expressions that can be entered by user when running the query.
In db frontends like DBeaver etc. when a SQL parameter is detected in the expression being evaluated the app shows a dialog to prompt for values.
My primary use case is to use pgtyped sql files as notebooks, which can contain parameters:
This parameter syntax is nonstandard sql but tools often allow some customization around parameter detection that enables usage of such syntax.
These values are remembered and auto populated the next time the query is executed.
Would you consider this to be within the scope of this project ?