setting up vscode to edit, format, and execute SQL++ (n1ql) queries

[randytpierce]

Making vscode handle SQL++ (N1QL)

This is a sort of guide or tutorial to explain how to setup vscode to edit, format, and execute (with a keyboard binding) n1ql queries.
This should accomplish these three things...

1. Have a better editing experience.
2. Have a better, and consistent formatting mechanism.
3. Be able to run queries from inside the editor.

Instructions

1. Install the SQLTools extension - https://marketplace.visualstudio.com/items?itemName=mtxr.sqltools

2. Associate .n1ql files with SQLTools
   To do this

   • create a simple test.n1ql in a project workspace, perhaps like this...

   echo "SELECT COUNT(*) FROM mdata WHERE type = 'dd'" > test.n1ql
   

   • Then open that file in an editor (it won't yet be the SQLTools editor)
   • Use the palette [cmd shift P] and search for "Change Language Mode". From the list select "SQL"
   • Now it will probably change to the SQL editor. If not close the file and reopen it. It is important that the file extension be .n1ql because that is how the "Change Language Mode" is making the association to the file extension.

3. Install the couchbase server

   • homebrew. - https://www.javanibble.com/how-to-install-couchbase-enterprise-edition-on-macos-using-homebrew/
   • or from the couchbase docs - https://docs.couchbase.com/server/current/install/macos-install.html
     This will give you access to the cbq utility but you will have to put the couchbase bin directory into your PATH (like in your ~/bash_profile file or something). Mine is in /Applications/Couchbase Server.app/Contents/Resources/couchbase-core/bin.

4. Create a task to execute a .n1ql file from inside the editor. This goes in a tasks.json file under the .vscode directory along with the extensions.json and the settings.json and the launch.json etc. The tasks.json file should have this content...

{
  "version": "2.0.0",
  "tasks": [{
    "label": "cbq-adb-cb1",
    "type": "shell",
    "command": "cbq",
    "args": [
      "-u",
      "'user'",
      "-p",
      "'pwd'",
      "-e",
      "http://adb-cb1.gsd.esrl.noaa.gov:8091",
      "-f=${file}"
    ],
    "presentation": {
      "reveal": "always",
      "panel": "new"
    },
    "problemMatcher": []
  }]
}

Of course if you already have a tasks.json just put this part into the existing task list...

{
    "label": "cbq-adb-cb1",
    "type": "shell",
    "command": "cbq",
    "args": [
      "-u",
      "'user'",
      "-p",
      "'pwd'",
      "-e",
      "http://adb-cb1.gsd.esrl.noaa.gov:8091",
      "-f=${file}"
    ],
    "presentation": {
      "reveal": "always",
      "panel": "new"
    },
    "problemMatcher": []
  }

1. Test that task by editing a .n1ql file (like the test.n1ql one above) by using [cnd shift P] and searching for "tasks run task" and choosing "cbq-adb-cb1". It should execute your query and show the results in the terminal window.
2. You will want to increase the history in the terminal window (many of our results are quite long). To do that open code->Preferences->Settings and search for "scrollback" and click on "terminal". Set this to 5000 or some other number that you like.

Terminal › Integrated: Scrollback
Controls the maximum amount of lines the terminal keeps in its buffer.
5000

1. Set a keybinding for this task. To do this use "code->Preferences->Keyboard Shortcuts". This is a tricky sudo graphical editor and I wouldn't use it. Instead use the "Open Keyboard Shortcuts (JSON) tool at the top right.

By clicking on this You get an editor window where you can see user keyboard bindings if any exist, or create a new one if they don't. Paste this into the editor ...

// Place your key bindings in this file to override the defaults
[
    {
        "key": "ctrl+s",
        "command": "workbench.action.tasks.runTask",
        "args": "cbq-adb-cb1"
      }
]

Now you have a keybinding [ctrl s] associated with the cbq-adb-cb1 task. Whenever you are editing a file with '.n1ql' as an extension it will use cbq to execute the n1ql and put the results into the terminal.