MySQL Formatter: How to Make Beautiful Code and Why You Need It

A MySQL formatter makes code beautiful. But have you ever wondered what beautiful code looks like? 

You might think that this is not so important. As long as it works as intended, why make a case about it? 

Now, hear me out. The difference between beautiful MySQL code and plain code is like night and day. From coding to debugging to maintaining MySQL code, the beautiful one always wins. 

Know why it matters in this article. Learn how it works and check out the hand-picked tools for your MySQL code’s beauty needs. 

Let’s begin. 

Understanding MySQL Code Formatting

MySQL coding involves working on scripts, stored procedures, functions, and more. One way or another, you will work in one or more of these during your active database career. So, beautifully formatting your code now will make your coding life easier. 

But what can you expect from a formatted MySQL code? 

Formatting MySQL code is like arranging furniture and other stuff in your home. What will make it inviting? How will you place furniture and appliances so that you will want to go home after work? 

You can imagine aligned chairs, evenly spaced tables, and cabinets. Even more, the entire space is properly utilized, and decorations are placed thoughtfully. It’s easy to navigate from one room to the next. Even the kitchen gives you a workflow where cooking is an enjoyable experience. 

Formatting MySQL code is the same. You make it easier to read for your sake and the other person who will read your code. Your indentations should be well thought out, your spacing is even, and your capitalizations are consistent. 

MySQL formatters are like your trusty interior decorators that will do just that. 

Formatted vs. Unformatted MySQL Code

Consider the following unformatted code. 

Let me tell you that the above code works. The first SELECT statement returns the total media rental amount a customer has. Then, the second SELECT returns the number of days from the rental date to the return date. Finally, the last SELECT statement returns how much payment the same customer has. 

But did you notice something? 

The code is an eyesore. It’s hard to look at. There’s no standard spacing, capitalization, or line breaks. In the end, you need some effort to decipher it, especially the second SELECT. It’s hard to connect the parentheses if it’s for the IFNULL part, SUM, or IF. 

But what if the same code is beautified? 

Check it out below. 

A lot better, right? 

Capitalization is consistent. Line breaks and spacing are nice too. In the second SELECT, the closing parenthesis for the SUM function is aligned with the open parenthesis. So, it’s easy for the eyes. Maintaining this kind of code is a joy to do. 

In the end, formatting your MySQL code will benefit you, me, and the rest of us who code for fun and a living. 

But do you need a MySQL formatter or will you just do it yourself? Consider what’s in store for you in the following section. 

What a MySQL Formatter Can Give You 

Like an inviting house, you will desire to go home. Beautiful code makes you feel good. It’s not only because it does something cool, but because it’s easier for the eyes. 

Having a MySQL formatter that will beautify things for you is like hiring an interior decorator for your home. Designing it by yourself may not make things easy and beautiful. You can even do some wrong moves that will make workflows at home even worse. 

Same thing, using a MySQL formatter can make formatting consistent across the entire database. Coding is software-assisted, and it’s the key that takes formatting a step further. It ensures the following benefits are always realized: 

Readability 

Grasping your query’s logic and structure becomes easier. Even more, looking at stored procedures and functions is not like reading ancient Egyptian hieroglyphics. 

Maintainability 

Spotting problems during development and pinpointing the causes of errors is easier to do when it’s formatted. It will also reduce the chances of introducing bugs in your queries and scripts. 

Collaboration 

Working in an elite team with standard code formatting makes formatters even more important. It ensures the standards are in place across the entire database and scripts associated with it. And this makes code collaboration easier. The same format used by the team will reduce confusion and disagreements during discussions. 

Efficiency 

Formatted MySQL code makes the team more productive during code reviews, debugging, and troubleshooting tasks. With MySQL formatters, you focus on the more important tasks and less on formatting code. 

Professionalism 

Consistent formatting in code reflects positively on the developer, the team, and the organization. Using formatters shows attention to detail, professionalism, and respect for coding standards. 

How Does a MySQL Formatter Work? 

MySQL formatters automate the process of beautifying your code. It is a feature included in MySQL integrated development environments (IDEs). Later, you will see some of the best IDEs and how they format code. 

Basically, automated formatting works like this: 

1. Lexical analysis: In this step, the MySQL code is broken down into tokens, such as keywords, identifiers, literals, and punctuation marks. 
2. Parsing: The formatter parses the tokens to understand the syntactic structure of the MySQL code. It identifies statements, subqueries, table names, column names, conditions, and clauses like WHERE, GROUP BY, and ORDER BY. 
3. Formatting rules: Some IDEs let you pick the formatting rules to apply. And based on your choice it will apply the formatting instructions on indentation, capitalization, and more. In this step, the formatter will load your instructions. 
4. Whitespace handling: The formatter inserts or removes whitespace to ensure consistent spacing between tokens and clauses. 
5. Keyword capitalization: Some formatters let you capitalize keywords. This isolates the rest of the code and makes it readable and consistent. 
6. Indentation: Poorly indented code is hard to read. So, the formatter adds indentation to visually separate different levels of nesting within the code. 
7. Line length limitations: In some cases, formatters may limit the length of lines by breaking long statements into multiple lines. This prevents horizontal scrolling and improves code clarity. 
8. Customization: Some MySQL IDEs have formatters that allow users to customize formatting options according to their preferences. The IDE can store these preferences or formatting rules (See #3) and let the user format code using these preferences repeatedly. 
9. Error handling: Some formatters may include error-handling mechanisms to detect syntactic errors that prevent proper formatting. 

Now that you know the process IDEs do to format code, let’s have a roll call of the finest MySQL IDEs that format code. 

5 Expert-Chosen MySQL Formatters 

We handpicked MySQL formatters available as an open-source or commercial offering. 

1. MySQL Workbench 

From Oracle, the company that brought us MySQL itself, is MySQL Workbench. It is a MySQL IDE with both free and paid editions. Formatting is possible in the SQL Window and accessible in its toolbar. See below: 

The same functionality works when you click Edit -> Format -> Beautify Query from the top-level menu, or in Windows, simply press Ctrl-B. 

MySQL Workbench sets one formatting standard for us. Though you have the option to capitalize keywords or not. You also have the same option to capitalize keywords during code completion or as you type. See the formatting options available below: 

Moreover, you can highlight a portion of the code or the entire SQL script to format. 

Pros 

• Aside from color coding, you can have indented and evenly spaced code formatting. 
• Easily accessible through the toolbar, Edit menu, or keyboard shortcut. 
• Capitalization of keywords. 
• Format as you type (capitalize keyword only). 
• Capable of formatting nested functions and aligning parentheses. 

Cons 

• Only one formatting profile. 
• Keyword capitalization is the only option you can change. 

2. Navicat for MySQL 

Navicat is a versatile IDE where you can manage MySQL and other SQL and NoSQL databases. You need to pay for it, but you have a 14-day trial period. Formatting is available in its SQL window through the toolbar or the Format menu. 

Below is where you can find the Beautify button on the toolbar: 

And below is where you can access it from the top-level menu: 

Nested functions are a pain to read, and Navicat knows that. So, it aligns open and closed parentheses and adds line breaks. The result is a nicely formatted nesting of functions. You can also manually do the indentation and capitalization from the Format menu, as seen above. 

Navicat also makes sure you can see your SQL code without horizontal scrolling. So, you can set wrapping with Beautify SQL, then set the number of characters in a line. Another option is to Minify SQL, where you can remove all the whitespaces, indentations, and line breaks without affecting functionality. Minification is good for sending statements over the network as it reduces the size of the statement. The reduced size helps in the bandwidth speed of the network and the parsing speed of the database server. 

Pros 

• Aside from the color coding, it nicely formats code with capitalization, indentation, and line breaks. 
• Accessible from the toolbar and Format menu. 
• Handling parentheses is very good for nested functions. 
• Avoids horizontal scrolling. 
• Option to minify MySQL code. 

Cons 

• Only one formatting profile. 
• No options to change the indentation and line breaks. 
• Formatting features are available on Standard and Enterprise Editions only. 

3. DBeaver 

DBeaver is a universal database manager that handles MySQL and other databases in one IDE. There are open-source and commercial editions of this database IDE. 

You can format MySQL code by right-clicking the statement you want to format. You don’t need to highlight first. It will format the statement where the cursor is currently placed. See a sample below: 

You can also do the same by clicking  Edit -> Format SQL  from the top-level menu. 

If you capitalize (To Upper Case) the entire statement, table names will appear in italics. 

You have different options to format SQL code. As seen below, you can use different formatters: 

• Default formatter
• Compact formatter
• SQL workbench/J formatter
• External formatter 

Choosing one will show you a preview of how formatting will be done in the selected formatter. You can also change indent size,  insert line feeds before commas, and more. 

Pros 

• Aside from the color coding, it nicely formats code with capitalization, indentation, and line breaks. 
• Easily accessible through the toolbar, Edit menu, context menu (right-click), or keyboard shortcut. 
• No need to highlight a statement to format it. But needs to highlight the entire code to format the whole script. 
• Several formatting profiles, including an external one. 
• Option to change indentation size, line feeds, and more. 
• Formatting features are available on both open source and commercial editions. 
• Works on other SQL database flavors too. 

Cons 

• The default keyword case does not change keywords to upper or lower case but allows editing of the default values. Edit the values for consistent formatting results. 
• Nested functions remain unformatted using the Default and Compact formatters. You have to add line breaks and alignment manually. Or use an external formatter capable of doing it. 

4. HeidiSQL 

HeidiSQL is a multi-database IDE that is open source. 

Reformatting MySQL code can be accessed from the toolbar, right-click context menu, and from the  Query -> Reformat SQL top-level menu. It offers one simple reformatting, and you can’t modify it. 

You need to highlight the statement to reformat or simply click the Reformat SQL button from the toolbar to reformat the whole script. 

Check out the screenshot below showing access to the toolbar to reformat MySQL code: 

Pros 

• Aside from the color coding, it nicely formats code with capitalization, indentation, and line breaks. 
• Easily accessible through the toolbar, top-level menu, and context menu (right-click). 
• Capitalization of keywords. 
• Always free, open-source offering. 
• Works on other database flavors too. 

Cons 

• Only one formatting profile. 
• No options to modify the formatting. 
• Nested functions are still unformatted. You need to manually format them yourself. 

5. dbForge Studio for MySQL 

dbForge Studio for MySQL is a comprehensive MySQL IDE from Devart. There’s much to say about its comprehensive formatting options. 

First, you can beautify code from the toolbar, right-click context menu, the Edit -> Advanced  menu, and keyboard shortcuts. dbForge Studio separates formatting into three modes: format the current statement where the cursor is, format the currently selected statement(s), or format the whole SQL document. It provides different keyboard shortcuts and menu entries for each. So, you have several options to trigger MySQL formatting. 

It provides MySQL formatting profiles where you can choose from nine formatting styles. Even more, you can create your own formatting profile and make it the active MySQL formatter, or edit the existing ones. You can still restore the default formatting options in case you messed it up. 

But it doesn’t end there. 

It has an SQL formatter that will allow you to format one or more. SQL files from one or more folders in one go. It can even provide you with a command-line option if you need to repeat the same process in the same folder. 

And if you don’t have dbForge Studio for MySQL but you want to try its formatting powers now, you can do it online for free using any of the six formatting profiles. Note that you can’t change any of the six profiles if you use the online MySQL formatter. 

Let’s showcase these features in the following subsections. 

Accessing the MySQL Formatter in dbForge Studio for MySQL 

Check out below how you can access the MySQL formatter from the toolbar. 

From the toolbar, you can only access Format Document and Format Current Statement. 

But with the context menu and the Edit menu, you can access all three. Here’s a sample using the Edit menu. 

The above shows three options, but  Format Current Statement  is disabled when you highlight a portion of the code as shown. 

You can also change settings when typing code and paste operations in the Formatting Settings. See below: 

Then, you can also define a portion of code where formatting is disabled. And notify you in the Output window if the statement being modified has syntax errors. 

In any of the ways to trigger MySQL formatting, it will use the active formatting profile. Let’s discuss formatting profiles next. 

Choosing the Active MySQL Formatter Profile 

Each of us may have different tastes on how beautiful a MySQL code is. So, dbForge Studio lets you choose between nine pre-defined formatting styles or profiles. See it below when you right-click the SQL window. 

Shown above are  Collapsed, Commas before, Compact, Default, Extended, Indented, MSDN SQL, Right aligned, and  Stack compact. Of the nine, I chose Indented as the active formatting profile. This profile formats nested functions nicely. It is marked as checked, as seen above. It means it’s the currently active formatting profile. 

Editing the MySQL Formatter Profile 

Your chosen profile doesn’t have to be the one Devart sets for you. You can still change it by clicking  Edit Format Profiles. See the window that will be after that. 

You can open the selected profile (Indented profile was chosen above) or create a new profile. 

You can reduce the line breaks, set the keywords to lowercase, and much more in editing profiles. See a sample of the SELECT statement below. 

From the above illustration, I removed the Line break after FROM. See the red dot near the table name as a result. The best way not to mess up the default settings of the profile you’re editing is to click Save As. Then, save the changes to a different profile name. 

Visible from the illustration above is the formatting of SELECT statements. There’s more to customize, like INSERT, UPDATE, Subquery, and many more. 

You will see the new profile from your list of profiles, and you can make it the active profile. You can share the same profile with your entire team for a standardized MySQL code formatting. 

Formatting Multiple SQL Files

From the  Tools  menu, you have another  SQL Formatter  item. This one formats MySQL code inside .SQL files. You can choose one or more. SQL files to format. It may or may not be in the same folder. 

When you click the SQL Formatter menu item, a wizard will open to let you choose between files or directories. See below. 

If you choose Files, it will ask you next for the SQL files you want to format. Here’s a sample: 

You can add or remove SQL files as needed. Then, you can click  Format. Wait until the formatting process is complete. A progress bar will show the status of the formatting. 

A new SQL window will appear after the formatting, so you can see the result. 

Using the Command Line as a MySQL Formatter 

You can also click the  Save Command Line  so you can do the same formatting again. Here’s what will appear after clicking the  Save Command Line. 

The same options will appear. Do not forget the Password to the selected connection. Then, you can click  Validate  and  Save. You will save a batch file (*.bat) so you can run the same formatting again using the Terminal. 

The dbForge Online MySQL Formatter 

Apart from dbForge Studio for MySQL, Devart offers the same formatting online here. Below is a sample web page: 

Pros and Cons of MySQL Formatter in dbForge Studio for MySQL 

Pros 

• 9 pre-defined formatting styles or profiles you can choose from. 
• Create your own MySQL formatting profile. 
• Bulk formatting of .SQL files. 
• Command-line execution of MySQL formatter. 
• Online MySQL formatter. 

Cons 

• Only the basic functionality of MySQL formatter is available in the Free edition. 
• No pre-defined MySQL code minifier. 

Formatting Examples from Different MySQL IDEs 

Now that you know five of the IDEs with MySQL formatters and how to use them, let’s compare their formatting output. We will use the following MySQL code: 

SELECT IFNULL(SUM(film.rental_rate),0) FROM film INNER Join inventory oN film.film_id = inventory.film_id 

inner JOIN rental on inventory.inventory_id = rental.inventory_id where rental.rental_date <= '2005-05-24 22:54:33'  

and rental.customer_id = 459; 

 

  select ifnull(Sum(iF((tO_days(rental.return_date) - to_days(rental.rental_date)) > film.rental_duration, 

((TO_DAYS(rental.return_date) - tO_DAYS(rental.rental_date)) - film.rental_duration),0)),0) 

    from rental                inner join inventory ON inventory.inventory_id = rental.inventory_id 

INNER JOIN film on film.film_id = inventory.film_id Where rental.rental_date <= '2005-05-24 22:54:33' and rental.customer_id = 459; 

 

  SELECT IFNULL(sum(payment.amount),0) 

 FROM payment Where payment.payment_date <= '2005-05-24 22:54:33' and payment.customer_id = 459; 

This is the same unformatted code from earlier. Now, see the formatting output from the 5 MySQL IDEs. 

MySQL Workbench 

Navicat for MySQL 

DBeaver 

HeidiSQL 

dbForge Studio for MySQL

Using the Indented profile: 

Advanced MySQL Formatting Features 

In this section, we will discuss features that may be included in a MySQL formatter. These are obfuscation, minification, and error detection. 

Obfuscation 

While most MySQL formatters make code readable, having an obfuscation feature will do the opposite. Obfuscation refers to making SQL code hard to understand while retaining its functionality. In a nutshell, this feature will replace table names, columns, and identifiers with cryptic names. The purpose of obfuscation is to hide any secret algorithm within the SQL code from reverse engineering. 

Minification 

Minification refers to reducing the size of the SQL code by removing unnecessary whitespace, line breaks, and comments, but retaining the original functionality without introducing errors. The reduced size will improve performance by passing a smaller code size over the network. It will also improve the parsing speed of the database engine. We have seen Navicat having this functionality built-in. 

Error Detection 

Some MySQL formatters will just stop working if there are syntax errors in the unformatted code. But those that have one will detect problems and inform the user. 

Conclusion 

Having beautiful MySQL code improves productivity by making code readable and easy to maintain. We showcased five different IDEs and their strong points in formatting MySQL code. 

We hope that this article has convinced you of how important MySQL formatters are. Why not explore these tools to see which one fits best for your needs?