In this article, formatting MySQL scripts using built-in profiles in ApexSQL Database Power Tools for VS Code extension will be explained
Writing a MySQL or MariaDB code can be challenging for every database developer. Spending hours and hours to write a perfect code for creating a MySQL or MariaDB database is already hard enough but reading a code that is not formatted is much harder.
The newly added feature for formatting MySQL scripts using predefined profiles can help a lot in this situation.
As the ApexSQL has a great experience in SQL code formatting with ApexSQL Refactor, that experience has been used to create predefined formatting profiles for MySQL and MariaDB scripts.
ApexSQL Database Power Tools for VS Code extension provides three formatting profiles which can be used:
Before getting acquainted with the new feature, let’s start with the installation process.
In the ApexSQL Database Power Tools for VS Code for a first-time user page, it’s explained how to install the extension.
When the Visual Studio Code is started, to access the built-in profiles option, click the Manage button, and select the Settings option from the menu that will pop-up.
After that, expand the Extension section, select the ApexSQL Database Power Tools for VS Code tab.
From the drop-down list under the Formatting: Profile section, select the desired formatting MySQL scripts profile. By default, the ApexSQL built-in profile is set:
As ApexSQL Database Power Tools for VS Code supports MySQL and MariaDB databases, in demonstration purpose will be used, custom created code, which contains some of the basic SQL statements:
The ApexSQL built-in profile
The ApexSQL built-in profile is defined in the following way:
- Space will be used as a basic unit for defining indents
- The spacing will be added before and after assigned operators (=, >, <, >=, <=, <>, !=) and after comas
- Empty lines will be removed, but empty lines before/after comments will be saved
- The data types are left align (CHAR, VARCHAR, DATE, INT, etc..)
- The upper case will be used for keywords and data types (CHAR, VARCHAR, DATE, INT, etc..)
- FROM, WHERE, ORDER BY, SET statements will be moved to a new line
- For the INSERT statements, the INTO keyword will be added
The Compact built-in profile
The Compact profile is defended in that way that has zero spaces set to indentations, and there are no empty lines in the query, also will move open/closed parentheses to a new line:
The Extended built-in profile
By using the Extended built-in profile, the MySQL code will be expanded with additional empty lines (like before and after every statement, before and after every comment). This built-in formatting profile creates additional space around operators, commas, and parentheses.
Q. Can I access to built-in profiles from a query editor?
A. Yes. From a query editor right-click menu, choose the Format script by profile command, and from the drop-down list, select the desired built-in profile: