Support for parameters

[lorefnon]

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:

/* @name FindBookById */
SELECT * FROM books WHERE id = :bookId;

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 ?

[cmoog]

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

• Parameterization would be a useful feature for many notebook workflows.
• Maintaining custom parsing logic for different SQL dialects could be difficult. At present, we don't do any preprocessing of the actual SQL code before passing each block to its respective driver, this limits the surface area for bugs and general maintenance burden.
• One nice property that's been maintained so far with 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.
  • A corollary to this is that choosing to use vscode-sql-notebook does not impose anything on other team members who might work on the same source code.
• Notebooks are (more or less) stateless at the moment. In my opinion, persistent parameter state encourages bad usage patterns.

One possible design

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.

[lorefnon]

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.