In database management, well-formatted SQL code is easier to read and a vital aspect of maintaining clean, efficient, and error-free databases. PostgreSQL developers and database administrators often deal with complex SQL queries, making code readability a critical factor for productivity. A powerful tool designed to beautify and standardize SQL code is pgFormatter. This guide to pgFormatter explores its features, installation, and how it can revolutionize the SQL coding experience.
What is pgFormatter?
pgFormatter is an open source utility that takes SQL code as input and outputs beautifully formatted, indented, and aligned SQL code following a set of predefined rules. It not only enhances code readability but also enforces best practices and SQL style conventions. As a result, developers can focus more on writing logic and less on code presentation.
Key features of pgFormatter
SQL code beautification: pgFormatter transforms complex and unformatted SQL code into well-structured, visually appealing statements, making it easier to comprehend and maintain.
Indentation and alignment: The tool ensures consistent indentation and alignment of SQL keywords, clauses, and expressions, providing a clear visual code hierarchy.
Standard SQL style: pgFormatter follows standard SQL style guidelines, promoting code consistency across your projects and team members.
Configurable rules: Users can customize formatting rules according to their preferences, adapting pgFormatter to specific project requirements.
Installation and setup of pgFormatter
Don’t forget to update the repos before starting the installation:
1 2 3 | yum update or sudo apt-get update |
Perl-CGI module needs to be installed as a prerequisite to install pgFormatter:
1 2 3 | yum install perl-cgi or apt-get install libcgi-pm-perl |
This tool/utility can be installed using the below:
1 | sudo apt-get install pgformatter |
Using pgFormatter
Basic usage: Once pg_format is installed, one can use the below command to get the beautified SQL query output.
1 2 3 4 5 | cat samples/ex1.sql | /usr/bin/pg_format - or /usr/bin/pg_format -n samples/ex1.sql or /usr/bin/pg_format -o result.sql samples/ex1.sql |
There are multiple ways to use pg_format; above are a few of the common ways majorly used. More ways could be checked by using pg_format --help
For example:
We have the below unformatted query, which is difficult to read:
1 2 3 4 5 6 7 8 | ubuntu@ip-172-31-95-64:/tmp$ cat Query.sql SELECT c.customer_id, c.first_name, c.last_name, COUNT(o.order_id) AS total_orders FROM customers c JOIN orders o ON c.customer_id = o.customer_id WHERE c.country IN ('USA', 'Canada','India', 'UK') AND o.order_date BETWEEN '2023-01-01' AND '2023-05-31' GROUP BY c.customer_id, c.first_name, c.last_name HAVING COUNT(o.order_id) > 5 ORDER BY total_orders DESC, c.last_name ASC; |
Let’s try to use pgFormatter and beautify the query to make it readable for easy understandability:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | ubuntu@ip-172-31-95-64:/tmp$ /usr/bin/pg_format Query.sql SELECT c.customer_id, c.first_name, c.last_name, COUNT(o.order_id) AS total_orders FROM customers c JOIN orders o ON c.customer_id = o.customer_id WHERE c.country IN ('USA', 'Canada', 'India', 'UK') AND o.order_date BETWEEN '2023-01-01' AND '2023-05-31' GROUP BY c.customer_id, c.first_name, c.last_name HAVING COUNT(o.order_id) > 5 ORDER BY total_orders DESC, c.last_name ASC; |
As mentioned earlier, multiple attributes can be used along with pg_format depending on the individual’s requirement.
pgFormatter web interface: The web-based version of pgFormatter allows to format SQL code conveniently from the browser using https://sqlformat.darold.net/.
Please find the unformatted query below:
Please find the query which got formatted after using the “Format my code” button at the bottom left corner:
Note: Please refrain from using the web interface if you are dealing with sensitive queries that might expose any critical data on the internet.
Best practices and benefits
Debugging and troubleshooting: When developers need to inspect SQL code during debugging or troubleshooting manually, well-formatted code is easier to navigate and understand. pgFormatter’s output presents code in a structured manner, helping developers quickly locate potential issues.
Collaborative projects: In a team of developers working on a collaborative PostgreSQL project, each team member may have their own coding style and formatting preferences. Without a tool like pgFormatter, the SQL codebase can become inconsistent and challenging to read. By integrating pgFormatter into their code editors or using it from the command line, team members can ensure that all SQL code follows a standardized format, promoting better collaboration and code reviews.
SQL code reviews: During code reviews, it’s essential to focus on the logic and functionality of the SQL code rather than its presentation. However, poorly formatted code can distract and make it harder to spot actual issues. By running pgFormatter before submitting SQL code for review, developers can present their queries neatly and organized, making it easier for reviewers to focus on the code’s correctness.
Query optimization: In complex SQL queries, code organization can significantly impact database performance. pgFormatter ensures that queries are properly indented and aligned, making it easier for developers to identify areas for optimization. Formatted code enables them to focus on the logical structure and improve query performance efficiently.
Migration and deployment: When deploying a PostgreSQL database schema or migrating it to a new environment, having properly formatted SQL scripts is crucial. pgFormatter helps ensure that schema scripts are consistently formatted, reducing the risk of errors during migrations and deployments.
Adherence to coding standards: Many organizations have their own SQL coding standards and best practices. pgFormatter allows developers to configure formatting rules to match their specific coding guidelines, ensuring adherence to the established standards across the entire codebase.
Useful links
- Please download using http://sourceforge.net/projects/pgformatter/
- Keep following GitHub for more information
Conclusion
In conclusion, pgFormatter is a valuable tool for PostgreSQL developers and administrators seeking to enhance their SQL coding experience. By incorporating pgFormatter into your workflow, you can achieve consistently formatted, clean, and readable SQL code, improving productivity and reducing the risk of errors. Whether you’re working on complex database queries or maintaining a large codebase, pgFormatter is the ideal companion for elevating your SQL coding journey. So, dive into the world of beautifully formatted SQL with pgFormatter, and elevate your PostgreSQL development to new heights.
Percona Distribution for PostgreSQL provides the best and most critical enterprise components from the open-source community in a single distribution, designed and tested to work together.
Download Percona Distribution for PostgreSQL Today!
