Plugin for analyzing PostgreSQL plans in VS Code, and its development

VisualStudio Code extension

VisualStudio Code extension

Plan analysis and PostgreSQL query formatting can be done in VS Code using explain.tensor.ru and a plugin, which will be discussed below.

Installation

installing extensions

installing extensions

To perform requests, our plugin uses another popular extension – SQLTools so first you need to configure a connection to PostgreSQL in it and then install Explain PostgreSQL from the application or command line:

code --install-extension TensorCompanyLtd.explain-postgresql

Formatting a request

In the SQL Editor context menu or in the list Code Actions choose Format SQL

request formatting

request formatting

Query analysis

In the SQL Editor context menu or in the list Code Actions choose Explain Analyze in this case the request will be executed on the PG server (configured in SQLTools) and the resulting plan will be sent to the service for analysis explain.tensor.ru through public API the result will open in a new window

query analysis

query analysis

Settings

In the settings you can specify the site address and analysis options:

setting

setting

Development

To create the plugin framework, install the utilities according to instructions set the parameters and install the recommended extensions:

npx --package yo --package generator-code -- yo code

? What type of extension do you want to create? New Extension (TypeScript)
? What's the name of your extension? Explain PostgreSQL
? What's the identifier of your extension? explain-postgresql
? What's the description of your extension? Analyzes EXPLAIN plan from PostgreSQL
? Initialize a git repository? Yes
? Bundle the source code with webpack? Yes
? Which package manager to use? npm

code explain-postgresql

We check – press F5 and enter the command in a new window Ctrl-Shift-P > Hello World a message window should appear:

Hello World

Hello World

The main file of the plugin is its manifesto – package.json, let’s add in it a dependency on the SQLTools plugin to install it along with our plugin:

  "extensionDependencies": [
    "mtxr.sqltools"
  ]

in the parameter activationEvents We define the condition for launching the plugin, in our case editing SQL files:

  "activationEvents": [
    "onLanguage:sql"
  ]

in the parameter contributes We are expanding the functionality of VSCode – adding commands, menu items, settings and others. For example, let's add three commands – text formatting, query plan analysis and website opening:

"commands": [
  {
    "command": "explain-postgresql.formatsql",
    "title": "Format SQL",
    "icon": "icons/EP.png"
  },
  {
    "command": "explain-postgresql.explainanalyze",
    "title": "Explain Analyze",
    "icon": "icons/EP.png"
  },
  {
    "command": "explain-postgresql.explainsite",
    "title": "Explain PostgreSQL",
    "icon": "icons/EP.png"
  }
]

Let's add two items to the editor context menu – Format SQL And Explain Analyzeas well as the button to open the site on the title panel, the display condition will be set to SQL language, and the group to navigation for placement in the top and 1_modification to place our commands first in the list:

"menus": {
  "editor/title": [
    {
      "when": "resourceLangId == sql",
      "command": "explain-postgresql.explainsite",
      "group": "navigation",
      "icon": "./icons/EP.png"
    }
  ],
  "editor/context": [
    {
      "when": "resourceLangId == sql",
      "command": "explain-postgresql.formatsql",
      "group": "1_modification",
      "icon": "./icons/EP.png"
    },
    {
      "when": "resourceLangId == sql",
      "command": "explain-postgresql.explainanalyze",
      "group": "1_modification",
      "icon": "./icons/EP.png"
    }
  ]
}

We store the plugin settings as an object, for example the site URL:

"configuration": {
  "type": "object",
  "title": "Explain PostgreSQL",
  "properties": {
    "explain-postgresql.url": {
      "scope": "resource",
      "type": "string",
      "default": "https://explain.tensor.ru",
      "description": "API site",
      "order": 1
    }
  }
}

To access the settings we use the function:

const url = vscode.workspace.getConfiguration('explain-postgresql').get('url');

In the parameter main manifest specifies the entry point to the plugin file extension.js which exports the input function activate. It receives as a parameter context which stores the private parameters of the plugin, for example the path to its directory extensionUri which we use to get the path to the panel icon:

panel.iconPath = vscode.Uri.joinPath(Context.extensionUri, "icons/EP.png");

In this function we register the commands that were specified in the manifest:

vscode.commands.registerCommand('explain-postgresql.formatsql', formatCommand);
vscode.commands.registerCommand('explain-postgresql.explainanalyze', explainCommand);
vscode.commands.registerCommand('explain-postgresql.explainsite', explainsiteCommand);

In the formatting command code, we take the selected area or the entire text and format it using API explain.tensor.ru and replace the text:

export async function formatCommand(): Promise<void> {
    let editor = vscode.window.activeTextEditor;
    if (editor) {
        let validFullRange: vscode.Range;
        let selection = editor.selection;
        let text = editor.document.getText(selection);
        if (!text) {
            text = editor.document.getText();
            let invalidRange = new vscode.Range(0, 0, editor.document.lineCount, 0);
            validFullRange = editor.document.validateRange(invalidRange);
        }
        try {
            let formatted = await beautifier(text); // получаем форматированный текст
            editor.edit(editBuilder => {
                editBuilder.replace(validFullRange || selection, formatted);
            })
        } catch (e) {
            vscode.window.showErrorMessage(`${e}`, {modal: true});
        }
    }
}

In the query analysis command, we also take the query text from the current editor and add EXPLAIN to it with a list of options specified in the plugin settings.
Then we run the command executeQuery from the plugin SQLToolswhich will execute the query on the connected server and return the query plan.
We send the request text along with its execution plan for analysis to API explain.tensor.ru and open the result in a new panel:

export async function explainCommand(): Promise<void> {
    let editor = vscode.window.activeTextEditor;
    let selection = editor.selection;
    let query = editor.document.getText(selection);
    if (!query) {
        query = editor.document.getText();
    }			

    const explainSettings = vscode.workspace.getConfiguration('explain-postgresql').get('explain');
    let options = Object.keys(explainSettings).filter(key => explainSettings[key]).join(',');
    let plan = await vscode.commands.executeCommand(`sqltools.executeQuery`, `EXPLAIN (${options}) ${query}`);

    try {
        let url = await explain(plan, query); // отправляем план на анализ в API explain
        const apiUrl = vscode.workspace.getConfiguration('explain-postgresql').get('url');
        await createView(`${apiUrl}${url}`);
    } catch(e) {
        vscode.window.showErrorMessage(`${e}`, {modal: true});
    }
}

To create a panel with analysis results, use Webviewwhich is actually an iframe element so we can place our HTML in it.
Let's add our own iframe to the panel, in the src attribute we indicate the URL of the document with the analysis result:

async function getHtml (url: string) {
    return `<!DOCTYPE html><html><head></head>
		<body style="margin:0px;padding:0px;overflow:hidden">
		<div>
            <iframe sandbox="allow-scripts allow-forms allow-same-origin allow-downloads" src=${url} style="width:100vw;height:100vh;border: none;display: block;"></iframe>
		</div>
		</body></html>`;
}

export default async function createView(url: string) {
    let panel = vscode.window.createWebviewPanel(
        'explainAnalyze',
        'Explain Analyze',
        vscode.ViewColumn.Active,
            {
            enableScripts: true,
            retainContextWhenHidden: true,
        }
    );
    panel.webview.html = getHtml(url);   
    panel.iconPath = vscode.Uri.joinPath(Context.extensionUri, "icons/EP.png");
}

For creating Code Actions we use CodeActionProvider :

export class Actions implements vscode.CodeActionProvider {
	public static readonly providedCodeActionKinds = [
		vscode.CodeActionKind.Refactor,
	];

	provideCodeActions(document: vscode.TextDocument, range: vscode.Range | vscode.Selection, context: vscode.CodeActionContext, token: vscode.CancellationToken): vscode.ProviderResult<(vscode.CodeAction | vscode.Command)[]> {
		const commandFormatSQL = this.createCommand('explain-postgresql.formatsql', "Format SQL", "Formatting SQL text");
		const commandExplainAnalyze = this.createCommand('explain-postgresql.explainanalyze', "Explain Analyze", "Explain query plan");
		return [
			commandFormatSQL,
			commandExplainAnalyze,
		];
	}

	private createCommand(command: string, title: string, tooltip: string): vscode.CodeAction {
		const action = new vscode.CodeAction(title, vscode.CodeActionKind.Refactor);
		action.command = {command, title, tooltip};
		return action;
	}
}

register it in a function activate along with the commands:

vscode.languages.registerCodeActionsProvider('sql', new Actions(), {
    providedCodeActionKinds: Actions.providedCodeActionKinds
})

To create a button on the status panel, create a new one StatusBarItem also in activate :

const statusBarItem = vscode.window.createStatusBarItem(vscode.StatusBarAlignment.Left, 10);
statusBarItem.command = 'explain-postgresql.explainanalyze';
statusBarItem.text="Explain Analyze";
statusBarItem.show();

Debugging

To debug panels, it is convenient to use the built-in WebView Developer Tools:

launch devtools

launch devtools

panel debugging

panel debugging

Building and publishing the plugin

To build and publish the plugin, install the utility vsce :

npm install -g @vscode/vsce

Specify the version in the parameter version V package.json and start the build:

vsce package
 Packaged: explain-postgresql/explain-postgresql-1.0.5.vsix (12 files, 319.64KB)

VS Code Extension Marketplace uses Azure DevOps and the utility vsce publishes only using personal tokens of this service, so to receive it we register on dev.azure.com and create a new organization:

creating an organization

creating an organization

In the organization settings, create a new token:

create a token

create a token

Copy the created token and go to the site Visual Studio Marketplace publisher management page with the same login that was used to create the token, and create a publication:

create publisher

create publisher

When creating, you will need to specify a unique ID, which we will write in package.json in the parameter publisher:

"publisher": "TensorCompanyLtd"

Checking the publication from the utility vsce using the generated token:

vsce login TensorCompanyLtd
 https://marketplace.visualstudio.com/manage/publishers/
 Personal Access Token for publisher 'TensorCompanyLtd': ****************************************************
 The Personal Access Token verification succeeded for the publisher 'TensorCompanyLtd'.

And we publish our plugin:

vsce publish

We check – the plugin should appear on the site Visual Studio Marketplace publisher management page and after a few minutes in the list of extensions in VSCode

plugin management

plugin management

Plugin code published under the MIT license.

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *