How to use environment or other variables in SQL code?

[shah]

Excellent job @cmoog - this is a fantastic extension. In my SQL I would like to use string replacements / variables coming from either the environment, file, or other VS Code location (e.g. ${env.XYZ}). Is this supported?

If it helps, please check out REST Client extension, which is kind of like a Postman or HTTP Notebook. REST Client does a great job of integrating dynamic variables for HTTP fetch testing.

[shah]

Also, it would be nice to use output of a previous SQL call in the next SQL block :-)

[cmoog]

Thanks! I'm glad you're finding it useful. Thank you for the feature request.

Regarding variable interpolation in general– I'm uneasy about adding features that stray from standard SQL syntax. Ideally, a SQL file used by one team member as a notebook can interoperate with other tools that consume SQL files. In any case, if we were to implement some kind of variable interpolation, I think we'd want to stick with the ? or $1 syntax that most SQL dialects use to ensure that we aren't encouraging unsafe usage patterns that expose unsophisticated users to SQL injection vulnerabilities.

@shah could you share a little more about your use case for environment variables?

[shah]

Good point @cmoog I would rather maintain SQL-first model as well.

My initial use case for SQL Notebook is for interactive SQL testing for QA and some non-technical users. In the SQL we would give them blocks of reusable SQL except we use direnv for maintaining the variables that we need in env vars. If I could get access to the data from the previous statement (using the $_ or _ which is common in some Notebooks) that would suffice. In case that's too hard then we could store the variables somewhere else (e.g. JSON, env, etc.).

-- 1. Get namespace record for PKC
select id, parent_id as "parentID", name, path, description, avatar from namespaces where name = 'namesp' and type = 'Group';

-- 2. Get assignments in PKC namespace (use the 'id' variable from the previous execution or store it in env var), this SQL depends on the previous SQL based on what the user types in
WITH groups_cte (id) AS (
    /* Find all children of given group ID (e.g. 'Precision Knowledge Content') */
    WITH RECURSIVE childNS AS (
        SELECT $_.id AS id           --  or SELECT ${env:DEFAULT_NAMSPACID_ID} AS id
        UNION ALL
        SELECT ns.id
        FROM namespaces AS ns
        JOIN childNS ON childNS.id = ns.parent_id
        ) SELECT id FROM childNS),
...

A generalized version might be something like the following, imagine that we had a section like /*markdown... except it was a basic JS block which exported variables like this:

/*interpolate-js
export const myValue = 100;
export const anotherValue = env.VAR_NAME;
*/

All the exported variables from the "module" would be available to all later SQL as $_.myValue or similar. The REST Client extension does a great job.

But your point is quite valid -- we should try to stay as close to regular SQL as possible - I just couldn't figure out how to use SQL syntax for variables since there's no "standard". PostgreSQL uses :variable format in psql but that's not common across all engines.

[rw100551]

Hi folks, greeting! I've using this tool to develop some sql training course and tools which is step-by-step to guide through a series of sql statement and queries to stidents, it would be nice to have something like global variables in one sql notebook that set value once and can be used anywhere of sql code cells, instead of type in the same info whenver run a code... Would be appreciated if you guys have plan to add this feature. Thanks!