Bojan Petrovic
Executed SQL script in Visual Studio Code with a Query results pane

Visual Studio Code for MySQL and MariaDB development

August 13, 2020 by

In this article, I’ll walk you through setting up Visual Studio Code for MySQL and MariaDB development using a third-party VS Code extension and give an overview of the basic features.

VS Code has been a very popular code editor because it has support for almost every popular programming language. For the majority of programming languages, support ships in the box. However, some common programming languages are not supported out-of-the-box, but support can be easily added by installing extensions from the VS Code Marketplace.

So, let’s see how we can customize Visual Studio Code to suit your MySQL and MariaDB development and management needs by installing an extension from Marketplace, ApexSQL Database Power Tools for VS Code.

Introduction

Created to meet the demands of developers by the team who’s responsible for providing free killer tools for SQL Server, this recently published VS Code extension enables users to connect to MySQL and MariaDB servers in the code editor, allowing them to run queries, perform object searches, export query results, and more.

Judging by the public roadmap, the extension is on an aggressive release schedule with lots of features coming up soon. With that in mind, let’s fire up the code editor, install the extension, and see what it can do.

Connecting to MySQL or MariaDB instances

The extension can be installed either from Marketplace, integrated extension manager inside Visual Studio Code, or using the VSIX installation file available as a download on this page. I’m gonna go with the integrated extension manager, but feel free to use any of the other two methods.

Bring up the Extensions view by clicking on the Extensions icon in the Activity Bar on the left side of code editor or go to View | Extensions from the main menu:

Extensions option from the main menu in Visual Studio Code

Start typing the name of the VS Code extension, and it should pop-up in the search results. At the moment of writing this article, the official product version is 2020.3.19. To install the extension, click the Install button shown below:

Database Power Tools for VS Code extension from VS Code Marketplace

Once the installation is complete, you’ll see one additional icon in the Activity Bar. Clicking on the icon will bring up ApexSQL server explorer which is used for connecting to MySQL or MariaDB instances using TCP/IP or a local socket or pipe:

ApexSQL server explorer in Visual Studio Code

I’ve already added a few instances, as can be seen above, but let’s add another one by clicking on the plus (+) sign on the top right of connection explorer. This action will open the Connect to server tab in which I’ll enter needed information to connect to MySQL Server using TCP/IP over an SSH connection:

Connect to server dialog in Visual Studio Code

Once everything is set up, just click Connect, and if connection parameters are correct, you’ll see a message in the bottom right corner of Visual Studio Code saying “Connection successful”. The Connect to server tab closes, and the focus goes to ApexSQL server explorer in which you’ll find the newly added instance of MySQL Server:

Highlighted MySQL Server in connection explorer

Executing queries and creating statements

How that we’ve seen how to add servers, let’s move on to the next step, and see what we can do from here. Connection explorer provides a user interface to view and manage the objects in each instance of MySQL and MariaDB servers directly from Visual Studio Code.

For example, we can delete the sakila database by right-clicking on it and choosing the Show DROP script option. This will open a new query editor and place the syntax of the DROP DATABASE statement:

Show DROP script option from the right-click context menu in connection explorer

To complete the operation, click the Execute button (also available from the right-click context menu) in the top left corner of the query editor. We just deleted all tables in the sakila database and database itself permanently. But don’t worry – this is just a sample database, and we can recreate it anytime. Let’s see how we can do that.

Back in ApexSQL server explorer, right-click our instance and choose New query:

New query option in connection explorer

In the newly opened query editor, I’ll just paste the SQL script for creating the sample database and hit the Execute button to recreate the database:

Executed SQL script for creating schemas of Sakila sample database

  • Note: The Sakila sample database is available from this page.

If the connection is remote, give it a few seconds. There’s always a slight delay when dealing with remote locations. It also depends on the complexity of the script that you’re running, the number of batches, etc.

I’ll run another long script to populate the tables with sample data:

Executed SQL script for populating Sakila sample database with data

You can always verify your connection and status of a query in the lower status bar:

The status bar of Visual Studio Code

Once the script is executed successfully, head over to connection explorer, right-click Databases and select Refresh to get latest changes, and you’ll see that the objects are recreated:

Refresh option from the connection explorer

Now that we have some actual data in the sample database, let’s see how the results of queries can be viewed in Visual Studio Code directly in a spreadsheet-like grid.

Fetching data from a table, of course, requires a SELECT statement to be executed. So, open a new query, paste the code from below, and execute it:

Query results are displayed right below the SQL editor:

Executed SQL script in Visual Studio Code with a Query results pane

Aside from executing queries, the VS Code extension also enables users to generate DDL and DML scripts using the integrated SQL editor.

To generate DDL and DML scripts, right-click a supported object from connection explorer and choose an appropriate option. Clicking either of those options will automatically open a new query editor and generate script, ready for execution:

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

Saving query results

The query results can be exported and saved to Excel, CSV, JSON, and HTML file formats. This can be done in just a few clicks.

Once you’re satisfied with the result set, use the export icons in the upper right corner of the query results to export data:

Query results exporting options

I’ve exported result set from above to Excel and here’s how it looks when opened:

Query results in an Excel spreadsheet

Exporting results to other supporter file formats works pretty much the same way.

Searching for objects in databases

The next handy database feature is the ability to search for objects (like internet searches), especially on databases with thousands of objects. Searching for objects in database object definitions has never been easier using this VS Code extension.

To search for objects in MySQL and MariaDB databases, right-click an instance or a database from connection explorer and choose the Object search option:

Object search option in connection explorer from VS Code extension

In the newly opened Object search tab, enter a keyword in the Search phrase field and click the Find button to initiate a search. Make sure that targeted server, as well as database, is selected:

Object search tab in VS Code extension

Search scope can be customized by choosing types of objects that should be included in the search on the right. You can play with those to narrow down a search and find objects faster. Furthermore, you can jump to any found object in connection explorer by clicking on the blue hyperlink under the Name column.

What’s next in VS Code extension

I’m particularly looking forward to the upcoming formatting feature in this VS Code extension planned for the 2020 R4 version. It will allow users to format MySQL and MariaDB scripts using a predefined profile. ApexSQL has been well known for its SQL formatter tool for SQL Server.

Looking at the roadmap, each new version of the VS Code extension will bring something to the table. It’s nice to see that they are slowly expanding MySQL front by adding features from popular MS SQL tools to this product as well.

This means that a data search is also coming up, and it will allow users to quickly find data in MySQL and MariaDB databases. However, I’m more excited about the browsable visual hints list while typing AKA IntelliSense. This will speed coding up by inserting keywords, databases, schemas, objects, parameters, variable names, etc. So, stay tuned, and for more information, visit the ApexSQL Database Power Tools for VS Code extension product page.

Conclusion

For database developers who prefer working in Visual Studio Code, an extension that adds database support for MySQL and MariaDB is much needed. ApexSQL Database Power Tools for VS Code allows users to perform simple connection configuration, writing statements and running queries, searching for database objects, and more to come. In other words, it provides extensive database development and management tools in Visual Studio Code.

I hope this article has been informative for you and I thank you for reading it.

Bojan Petrovic
Development, MySQL, Visual Studio Code

About Bojan Petrovic

Experienced QA Engineer with a demonstrated history of working in the computer software industry. Skilled in network technologies, technical support, Windows SQL Server, etc. Strong information technology professional with an AP graduate in IT Technology focused on Networks and electronic technology from the Copenhagen School of Design and Technology.

1,444 Views