How to Create an Extension: Beautify SQL

This tutorial will show you how to write an extension that beautifies the SQL you write.

Prerequisites

Before you begin, you must install:

  • TeamSQL. You must also have a TeamSQL account that you use to log into your TeamSQL account -- if you don't already have one, you'll be prompted to create an account the first time you launch TeamSQL
  • Node.js. Node.js comes with npm, but because the latter is updated more frequently, you'll want to make sure that you're using the most up-to-date version
  • TypeScript

Install the TeamSQL CLI

You can install the TeamSQL CLI by running the following command:

npm i -g @teamsqlio/teamsql-cli

Next, you'll need to log in with your TeamSQL credentials. Begin the login process by running

teamsql-cli login

You'll be prompted for your username and password. Be sure to use the same set of credentials you're using to log in to your local instance of TeamSQL.

Create Your Extension

The easiest way to begin creating an extension is to clone the Context Menu template provided as a starter for you.

Navigate to the disk location where you want to work on your extension. Run the following command to clone the Context Menu template:

teamsql-cli create

Use the up/down arrow to change your selection, and press Enter (or Return) to begin the process of cloning the Editor extension template. You'll be prompted to Please enter a name for your project's folder.

Once you receive a message that the cloning process is complete, you can begin working on your extension.

The Editor Extension Template Contents

All extension project folders must contain, at a minimum, the four following files in the root:

  • README.MD: Markdown file containing well-written description of your extension, as well as pertinent images, usage instructions, and so on. If we approve your extension and it becomes available in the TeamSQL Extension Store, this file's content will be used as the description.
  • icon.png: An icon (512px x 512px) in PNG format to be used with your extension
  • index.js: The main file of your extension (we will cover the specifics required in this file later on in this tutorial)
  • package.json: File containing configuration information about your npm package

Writing Your Extension

The code for your extension goes into the index.js file, which you will later compile into TypeScript for installation.

There are several different classes that allow you to implement functionality where your extension interacts with or takes advantage of data available in the TeamSQL environment. For the most part, you do not have to utilize these classes if not necessary. The exception to this is your main class, which must extend the Extension class and implement one of the interfaces available via the TeamSQL Extension SDK.

For information on methods related to the Monaco editor, please see the appropriate API documentation.

import { Extension } from "@teamsqlio/ts-extension-sdk/dist/source/Extension";
import { IExtensionEditor } from "@teamsqlio/ts-extension-sdk/dist/source/interfaces/groups/IExtensionEditor"
import * as path from "path";
const sqlFormatter = require('@teamsqlio/sql-formatter');

/*
    Your extensions' main class must be named TeamSQLExtension and extend the Extension class.
*/
export class TeamSQLExtension extends Extension implements IExtensionEditor {
    init() {
        let me = this;
        this.addActionToActiveEditor(
            <monaco.editor.IActionDescriptor>{
                id: 'beautify-query',
                label: 'Beautify Query',
                keybindings: [monaco.KeyMod.Alt | monaco.KeyMod.Shift | monaco.KeyCode.KEY_F],
                keybindingContext: null,
                contextMenuGroupId: '10',
                run: function (ed) {
                    me.beautifyQuery();
                    return null;
                },
                enablement: {
                    textFocus: true,
                }
            }
        )
    }

    beautifyQuery() {
        // Get the editor window to make changes
        let activeEditor = this.getActiveEditor();
        let query = activeEditor.getValue();
        query = sqlFormatter.format(query);
        let range = new monaco.Range(1, 1, activeEditor.getModel().getFullModelRange().endLineNumber,
            activeEditor.getModel().getFullModelRange().endColumn);
        this.executeEdits("formatter", query, range);
    }
}

Prepare for Installation

Now that you've created the extension, you'll need to perform the following steps to prepare it for installation.

  1. In the extension's root, install the required npm packages for this tutorial by running npm install

  2. Build the extension project with TypeScript by running tsc -p .

  3. At this point, you're ready to install the extension. You can do so by running the following command: teamsql-cli submit --install. If the installation was successful, you're ready to use your extension!

Use the Extension

After launching TeamSQL and connecting to the database of your choice, you'll open up an editor window and begin writing your SQL statement. To beautify your SQL, you can use the following shortcuts:

  • macOS: Option + Shift + F
  • Windows or Linux: Alt + Shift + F

Alternatively, you can right-click and select Beautify SQL.

As an example, here's what your SQL might look like initially:

Here's what it looks like after running Beautify SQL:

results matching ""

    No results matching ""