Add Generate SQL File command

This would simplify the current workflow of opening PRQL to SQL output webview, selecting and copying a text from it, and copying it over to new SQL document to run it with SQLTools, db drivers, and extensions like DuckDB Sql Tools

See the Run ▶️ added in the mockup below, next to the open PRQL document.

Clicking on the Run/Compile button will create new virtual SQL text file with the same file name as open PRQL file, and expose other options in VSCode like SQL document formatting or custom Run on active connection inline options for the generated SQL.

@max-sixty

@max-sixty package.json config update for this new command:

    "commands": [
      {
        "command": "prqlSqlOutputPanel.open",
        "title": "Open SQL Output Panel",
        "category": "PRQL",
        "icon": "$(database)"
      },
      {
        "command": "prql.CreateSQLDocument",
        "title": "Compile to SQL",
        "category": "PRQL",
        "icon": "$(run)"
      }
    ],
    "menus": {
      "editor/title": [
        {
          "command": "prqlSqlOutputPanel.open",
          "when": "resourceFilename =~ /.*(.prql)/",
          "group": "navigation"
        },
        {
          "command": "prql.CreateSQLDocument",
          "when": "resourceFilename =~ /.*(.prql)/",
          "group": "navigation"
        }
      ],
      "editor/title/context": [
        {
          "command": "prqlSqlOutputPanel.open",
          "when": "resourceFilename =~ /.*\\.(prql)/",
          "group": "navigation"
        },
        {
          "command": "prql.CreateSQLDocument",
          "when": "resourceFilename =~ /.*(.prql)/",
          "group": "navigation"
        }
      ]
    }

I can implement this feature and submit new PR after you review and merge #40 & #41.

It would be up to you and current PRQL extension users to decide if you want to keep the PRQL to SQL Output webview that refreshes generated SQL on PRQL file changes after this feature is added. We can probably do the same for the virtual SQL doc and editor too.

@RandomFractals

Thanks @RandomFractals ! (I just realized you were Taras — hi!!)

One thing that's very nice about the current PRQL to SQL Output view is that it refreshes on every keypress. I think many folks would see it as more like a preview than a "Run".

I wonder whether we keep the "Run" for doing something like an actual database query, WDYT? So something like the "Run on active connection" would work directly on the PRQL, rather than requiring two clicks to go PRQL -> SQL -> Execute? WDYT?

Max,

I think we should keep SQL Preview as PRQL extension users are probably used to it by now, and it updates on prql document changes as you type or switch prql documents.

Please review some of the proposed changes I made in #46 and #47 to make that panel more standard.

The new Compile to SQL or Create SQL Document command I suggested we add in this ticket with the Run button would generate new SQL file for each PRQL document you run compile on. So, I see both Preview and Create/Compile features very different in usage.

Preview could be used while prototyping prql. Then to save SQL document we'd use the new Create SQL Document command. It would run the same prql compile step as preview, and open generated SQL document.

I am not sure further integration to Run generated SQL can be implemented without much effort as many DB oriented VSCode extensions handle DB connections differently.

That's why I would suggest PRQL produces SQL and other extensions that have more info about DB connections can handle running generated SQL queries. It's one of the Pro ext. features I plan to add to DuckDB Pro Tools and what prompted me to submit a few changes to refine current prql ext. functionality as I started using it.

Perhaps Generate SQL Document would be a better name for that Run command.

Then we can also add prql.autoGenerateSqlDocument setting defaulting to false to let some users selectively create SQL documents from prql on .prql document changes. Default scenario would do that only on PRQL Run.

What do you think? I could implement that functionality later today or tomorrow. I think it'll be easier to visualize it that way.

Also, with those changes in place in your PRQL extension, I can inject Run on active connection into PRQL document as my extension already depends on SQLTools for now and can provide tighter integration for those DB connections. Users can then use SQL Preview just to view generated SQL, or optionally create the corresponding sql document to add them to version control.

Do note that DB connections part is not standard and many different DB extensions handle it via settings or other means. I honestly think PRQL ext. being concerned with Run SQL functionality might be too much to take on.

I think the changes in #47 seem great — it's exactly a preview — so fits very well.

Re the "Run" command — how do you see this working with an engine like DuckDBPro? I totally agree with:

note that DB connections part is not standard and many different DB extensions handle it via settings or other means. I honestly think PRQL ext. being concerned with Run SQL functionality might be too much to take on.

So I was thinking that DuckDBPro could be the "Run" provider for a .prql file when it's installed, such that executing "Run" would run the actual query with DuckDBPro. Is that how you were thinking about it too?

Perhaps Generate SQL Document would be a better name for that Run command.

Yes great — being able to make a SQL file from a PRQL does seem useful — agree it's a bit different from the Preview. So totally makes sense to add that as a command.

ok. Let's do this for now and add more to it later if needed, or finalize it in a PR:

Add new PRQL: Generate SQL Document command with prql.generateSqlDocument id accessible via vscode Command Palette. This will create or update an sql document and open it in the same editor group as open prql document for review, document formatting, or running SQL on open or "known" db connections from config by other extensions.
Add new prql.autoGenerateSqlDocument setting I mentioned above that would automatically create and update SQL document on the corresponding .prql document changes. We'll default this to false as many devs prefer 3rd party extensions not create extra files without explicit requests per my experience.
Add new prql.target.dialect extension setting as outlined in Add prql.target setting and use it to compile PRQL to SQL #48, and change Sql Preview and Generate Sql Document to pass this user or workspace setting to prql js compile via sql options.

I do think prql compiler should use the prql target: sql.<db> over given vscode or project workspace setting we'll pass, if present in prql document.

Also, we'll hold off on adding Run button to the PRQL editor title context menu for now until we get some version of the above integrated with SQL Tools and DuckDB for a dry run. Later on, we can add a db provider interface or some other api for DB extensions to use with prql if we'd like to abstract that functionality and have prql extension host the UI elements to run generated SQL queries.

I'll prototype this in a new PR this week and we can review and finalize the details there.

@RandomFractals

Sounds excellent throughout! Thanks @RandomFractals .

...and we've already changed to this, though it's not in the latest release; I can make one if that'd be helpful for your work:

I do think prql compiler should use the prql target: sql.<db> over given vscode or project workspace setting we'll pass, if present in prql document.

@RandomFractals

Hi, apologies for coming rather late to the party. These all look like excellent additions. Thank you very much @RandomFractals !

Some ideas to throw into the mix since I haven't seen these mentioned already:

How about "Compile" instead of "Generate SQL Document"?
- It is invoking the prql-compiler and calling the compile method after all.
- Also, VSCode is a developer tool so the users should mostly be developers who would most likely be familiar with Compile -> Run workflows.
- That way you also keep "Run" for possible future execution against a DB backend.

I do think prql compiler should use the prql target: sql. over given vscode or project workspace setting we'll pass, if present in prql document.

This could be made a config option of the VSCode extension, something like "In the presence of conflicting prql.target settings, prefer Workspace/Document/Ask?".

There are use cases for both and my instinct was also to think of the setting in the file as the more "local" one, however what if you are reviewing some production queries from Snowflake and want to test them on your local DuckDB instance? Are you going to update each document or would it be easier to just set one workspace setting?

Renamed to Genereate SQL File command as most extensions use File when files are created and Document when documents are open, etc.

@snth

@snth we'll go with Generate SQL File command name for now, mainly because even though prql compile is what we use it doesn't specify where the output goes. Most compile tasks in vscode are done via terminal and CLIs.

Generate SQL File is more specific and denotes that we will be creating an sql file for the open prql document.

As for your prql.target setting comments, please see #48 that covers it.

@max-sixty

@max-sixty I think we should merge PR #49 as it covers the basic PRQL to SQL file gen. workflow.

I am tempted to delay implementing the extra prql.autoGenerateSqlDocument setting feature. It's probably best to move it to a separate enhancement ticket as that feature implementation might be more involved and require adding file watchers, or prql document change triggers.

We can add prql.target config and pass it to prql-js compile for #48 when those compile option changes are released.

With that, we can close this ticket when #49 is merged.

@RandomFractals

Excellent @RandomFractals . I merged #49.

I also gave you write permissions so you can merge changes you're confident about without waiting for others (though you can wait if you like!). [Here] are the guidelines for merging https://prql-lang.org/book/contributing/development.html#merges

Basic PRQL -> SQL -> Run query and view results workflow demo this feature enables for other DB tools:

Navigation Menu

Repository navigation

Add Generate SQL File command #42

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Add Generate SQL File command #42

Description

Activity

RandomFractals commented on Feb 3, 2023

max-sixty commented on Feb 3, 2023

RandomFractals commented on Feb 4, 2023

RandomFractals commented on Feb 4, 2023

max-sixty commented on Feb 5, 2023

RandomFractals commented on Feb 5, 2023

max-sixty commented on Feb 5, 2023

snth commented on Feb 6, 2023

RandomFractals commented on Feb 6, 2023

RandomFractals commented on Feb 6, 2023

RandomFractals commented on Feb 6, 2023

max-sixty commented on Feb 6, 2023

RandomFractals commented on Feb 6, 2023

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions