ApexSQL Database Power Tools for VS Code for a first-time user

Applies to

ApexSQL Database Power Tools for VS Code

Summary

This article is an all-in-one guidebook for everyone interested in getting started with ApexSQL Database Power Tools for VS Code extension for Visual Studio Code and exploring its features and capabilities.

Requirements

ApexSQL Database Power Tools for VS Code is a Visual Studio Code extension that allows users to connect to MySQL and MariaDB instances, execute and display queries results, export query results into several standard formats, and generate DDL and DML scripts from ApexSQL server explorer. All of this is possible on multiple computing platforms like Windows, Linux, and macOS.

First things first, before continuing any further, it’s always a good idea to check ApexSQL What’s supported and Requirements page just to verify if this extension is actually what you’re looking for, does it do the job, and to see what are the minimum system requirements that have to be met for the application to run smoothly.

It’s recommended to have the latest Visual Studio Code installed and also to keep the program always up-to-date. If you’re running the nightly Insiders build of Visual Studio Code, there is no guarantee that the extension will work properly because the extension is not being tested on any nightly builds.

Visual Studio Code itself is a lightweight application and adding ApexSQL Database Power Tools for VS Code extension to it does not affect its performance at all. So, don’t let the size fool you. The extension’s download size of > 100MB is due to its necessary components that ensure smooth operation and no additional download of any third-party software.

Installation process

Once going through the requirements is done, it’s time to increase the power of Visual Studio Code by installing ApexSQL Database Power Tools for VS Code extension.

Install from the Marketplace

The easiest way to install and manage the extension is from the Visual Studio Code MarketPlace. Go to the Extensions view by clicking on the Extensions icon in the Activity Bar on the left side of Visual Studio Code.

In the search box, start typing the name of the extension and ApexSQL Database Power Tools for VS Code should appear at the very top of search results:

Database Power Tools for VS Code extension in VS Code Marketplace

Select the extension by clicking on it to display the extension’s details page on the right where you can learn more about it:

Details page of the Database Power Tools for VS Code extension

Switch over to Feature Contributions page to see the extension’s additions to Visual Studio Code such as settings, commands, and keyboard shortcuts:

Feature Contributions page of the Database Power Tools for VS Code extension

The last page, Changelog represents extension repository CHANGELOG AKA release notes. This page describes features, enhancements, and changes made to ApexSQL Database Power Tools for VS Code:

Changelog page of the Database Power Tools for VS Code extension

Back to the installation, to add the extension to Visual Studio Code, simply click the Install button right below the five-star rating and download count in Extensions view. Once the installation is complete, an information message will be shown in the lower right corner:

Completed installing the extension ApexSQL Database Power Tools for VS Code info message

When installed, there will be a new ApexSQL extension icon in the Activity Bar at the bottom:

ApexSQL server explorer

Install from VSIX

If for some reason you are not able to access the Marketplace within Visual Studio Code, let’s say computer’s Internet access goes through a proxy server and you don’t want to configure the proxy server. Then, another way of installing the extension is downloading the VSIX installation file from the ApexSQL site and installing it manually.

Head over to ApexSQL Downloads page and scroll down until you find ApexSQL Database Power Tools for VS Code under the For Developers section, and click Free tool to download the installation file:

List of ApexSQL Developer tools available for download on ApexSQL Downloads page

Fill out the Download form on the next page, make sure to check “I have read and accepted the license agreement”, and click Submit to initiate the download.

Choose the desired location where the file should be saved and click Save:

Save As dialog on Windows 10 with download location and file name specified

Next, fire up Visual Studio Code and go to the Install from VSIX command from the Extensions view command drop-down menu:

Install from VSIX option under the Extensions view drop-down menu

In the newly opened window, navigate to where the installation file was previously saved, select it and click Install:

Install from VSIX dialog with extension location specified

In a matter of seconds, the installation will be completed and the same information message will be shown in the lower right corner as when installing it from the Marketplace.

Bear in mind that when the extension is either upgraded or deleted, the user is prompted to reload the host application in the information message on the bottom right as shown below:

Please reload Visual Studio Code to complete installing the extension ApexSQL Database Power Tools for VS Code info message

Note: Visual Studio Code checks for extension updates and installs them automatically. After an update of the ApexSQL extension, you might be prompted to reload the host application as described above.

Connecting to servers

Before using any of the extension’s features and functionalities, a connection to a server should be established first.

Standard TCP/IP connection

The very first time, users can add a server by going to ApexSQL extension icon in the Activity Bar and clicking the Add server button:

Add server option in ApexSQL server explorer

In the newly opened Connect to server tab on the right, enter server connection parameters and click Connect:

Connect to server dialog

Checking Save credentials in the connection dialog before establishing a connection will save server connection parameters making the server available in ApexSQL server explorer between sessions without a need for re-entering credentials. This is recommended for frequently used servers as it will save you a lot of typing and don’t worry – the password gets encrypted so no one can view it in plain text.

This example used a local instance of MySQL with server name specified as “localhost“ but feel free to enter the full name or IP address of the server host and appropriate TCP/IP port.

For the credentials, use the correct name and password of the user to connect with and when Connect is clicked, an information message will appear saying that the connection was successful:

Connection successful info message

Back to the ApexSQL server explorer, the newly added server will appear. Use the Connect to server button (plus sign in) to open a new connection dialog and add more servers to the list when needed:

Option for adding servers from ApexSQL server explorer

If a connection to a server fails after it has been successfully added previously, it’s most likely because the password for the user has expired. In such a case, there’s no need to re-add the instance again, just right-click it in ApexSQL server explorer and choose Edit connection. Enter new credentials and click Connect to save changes:

Option for editing servers parameters from ApexSQL server explorer

Secure types of connections

A connection can be established to MySQL and MariaDB servers choosing from the two methods under the Type drop-down menu in the connection dialog:

  • TCP/IP
  • Socket/Pipe

There is also an Advanced option in the connection dialog, on the left of the Connect button that is used for configuring SSL and SSH for establishing an encrypted link between a server and a client.

SSH

In the connection dialog, when the Advanced option is clicked it will expand the connection options and an option for configuring and adding server using SSH (Secure Shell) from the extension will be available as shown below:

Connect to server dialog with advanced SSH parameters

Make sure to fill out all the required information and know that the connection will fail if SSH is not available:

  • Host: The name of the SSH server. Do not specify the port number in the host’s name, for example, localhost:22. Instead, use the Port box to the right
  • User: The name of the SSH user to use for the connection
  • Password: The SSH password

It is recommended that an SSH key file is also used. Check the Use SSH key file option and provide the following:

  • Location: A path to the SSH key file
  • Passphrase: Passphrase for the private key

Once everything is set, hit the Connect button and the new instance will be added to the list of servers.

SSL

Switch over to the SSL tab for connection options related to establishing an SSL (Secure Sockets Layer) connection. Same as described in the previous example, the connection will fail if SSL is not available:

Connect to server dialog with advanced SSL parameters

SSL parameters are:

  • CA file: Path to the Certification Authority file for SSL
  • Certification file: Path the Certificate file for SSL
  • Key file: Path to the Key file for SSL

Once these are specified, test the connection by clicking the Connect button. If everything is in order, the instance will appear in the list of servers.

Executing queries

Once the ApexSQL server explorer is populated with servers and databases, it’s time to start writing and executing MySQL and MariaDB queries.

From ApexSQL server explorer, right-click on a database or server and choose New query from the context menu:

New query option in ApexSQL server explorer

The new SQL editor tab will be opened. Write or paste some code, right-click anywhere in the query window, and select Execute query from the menu:

Execute query option in SQL editor

If this is a SELECT query, the results of the query appear in the Query results (spreadsheet-like grid) pane on the right:

Query results pane

If you are manipulating data using an INSERT, UPDATE, and DELETE, the Query results display a message indicating how many rows were affected by the query:

Command executed successfully info message Query results pane

If there is a syntax error in the executed script, an appropriate message will be returned referring to the manual that corresponds to the server version for the right syntax, and at which line the error occurs.

Besides opening a new query, there is also Select limit 1000 command in ApexSQL server explorer that opens a new query editor with SELECT * FROM <object_name> LIMIT 1000 script snippet for the selected object (table or view) and displays query results:

Select limit 1000 option from the right-click context menu in ApexSQL server explorer

Exporting query results

Wouldn’t it be nice if there was a way to export query results? Well, there is. Query results can be exported into several standard formats. Look for the export icons in the upper right corner of the Query results pane and click one of them to save query results as:

  • Excel
  • CSV
  • JSON
  • HTML

file formats in that order from left to right:

Query results exporting options

Clicking any of those icons will open the Save As dialog in which two things should be specified: the file name and where it should be saved on the computer:

Save As dialog on Windows 10 with download location and Excel file name specified

This query result is saved as an Excel worksheet and when opened, it will be filled in by following the order of appearance of Query results:

Query results in an Excel spreadsheet

If the executed batch contains two or more SELECT statements, each SELECT statement will have its separate pane within the Query results and the results can be exported individually:

Query results pane of two separate SELECT statements

Creating statements

This feature enables users to generate DDL and DML scripts for objects in a database.

Right-click any object within ApexSQL server explorer and choose the available DDL or DML command:

DDL and DML options from the right-click context menu in ApexSQL server explorer for generating scripts

This action will open a new SQL editor and generate code for selected operation:

Generated SQL script for inserting data into a table

DDL

DDL options are:

  • Show Create script – Create script snippet for the selected object 
  • Show Drop script – Drop script snippet for the selected object

Create and drop commands are available for databases, tables, views, procedures, functions, indexes, and triggers.

DML

DML options are:

  • Show SELECT script – Select data script snippet for the selected object
  • Show INSERT script – Insert data script snippet for the selected object
  • Show UPDATE script – Update data script snippet for the selected object
  • Show DELETE script – Delete data script snippet for the selected object

DDL commands are available for objects that have data residing in database tables.

To display newly created objects in a database, use the Refresh command in ApexSQL server explorer:

Refresh options from the right-click context menu in ApexSQL server explorer

FAQ’s

The FAQs page has answers to some frequently asked questions about ApexSQL Database Power Tools for VS Code, so check those to see additional product information.