intro
Let’s discover the definition of SQL lint, why it plays a critical role in writing quality SQL code, and what the best linting tools are.
A SQL linter is a software component that analyzes SQL statements to identify syntax errors and deviations from best practices. With this tool, bugs can be prevented early in the development process. That is why it is a critical technology to know and adopt.
In this article, you will dig into SQL linting, understanding how it works and what are the most popular linters for SQL. Time to dive in!
What Is an SQL Linter?
Before delving into the topic of SQL linting, it is necessary to understand what a linter is. A linter is a tool that can analyze code written in a particular language for syntax errors, performance and/or security issues, indentation inconsistencies, and violations of coding standards. The idea behind a linter is to detect potential bugs and errors before the code is actually executed. These tools provide feedback to improve the quality, readability, and maintainability of the code and may also be able to automatically fix some of the problems found.
In the context of SQL, an SQL linter specifically focuses on analyzing SQL code. In detail, it helps developers identify and address issues within SQL statements, queries, and scripts, ensuring that they are syntactically correct and adhere to best practices. Note that popular SQL linters usually support both standard SQL and popular dialects, such as MySQL, MariaDB, T-SQL, PostgreSQL, Oracle, and others.
In particular, some key features offered by an SQL lint tool are:
In other words, an SQL linter is a tool that assists developers in writing high-quality SQL code by detecting and highlighting issues related to syntax, coding standards, performance, and security.
How SQL Linting Works
SQL linting works by analyzing SQL code to identify issues and errors. Then, it reports them to the user in a detailed report. This process involves several steps. Some of them are optional, depending on the capabilities or configuration of the SQL lint tool chosen.
Let’s now take a look at all these steps:
Keep in mind that lint for SQL is generally used during local development. At the same time, some linters can be integrated into CI/CD pipelines to prevent deploys if particular errors are detected. This is great for preventing avoidable bugs!
Best SQL Linters
There is no absolute best SQL linter, as the right tool for you depends on what your goals are. Time to take a look at the top linters for SQL in software development.
Top SQL Linting Libraries
The most popular Python SQL linter is sqlfluff. This library is a dialect-flexible and configurable tool to lint SQL designed with ELT applications in mind. Its goal is to enforce a consistent and readable style across SQL statements, regardless of the specific dialect.
As of this writing, it supports ANSI SQL, Athena, BigQuery, ClickHouse, Databricks, Db2, DuckDB, Exasol, Greenplum, Hive, Materialize, MySQL, Oracle, PostgreSQL, Redshift, Snowflake, SOQL, SparkSQL, SQLite, Teradata, Transact-SQL
You can install it with:
1
pip install sqlfluff
Or play with it at SQLFluff online.
It provides two commands:
See these two commands in action. Visit the online playground and write the following PostgreSQL query:
This SQL indentation is not ideal and using the wildcard operator * together with a column is not recommended.
Click “Help me.” and this would produce:
In the gray textarea there is the result of the sqlfluff fix command, while the errors come from sqlfluff lint.
Another tool to consider is the JavaScript SQL linter sql-lint. This npm library will do sanity checks on your queries to avoid errors.
Top SQL Linting Plugins for IDEs
The most popular VSCode SQL linter extensions are:
As for IntelliJ IDEs, there is:
Top SQL Linter in a Database Client
Before writing a query in code, you typically want to test it in a database client. Now, imagine if the client had a built-in SQL liner with full functionality. That would make it easier, as you can get optimized queries before using them in the code.
You write the desired query in one of the dozens of databases supported by the client, let the built-in linter optimize it for you, and paste it into the code. Sounds like a dream, doesn't it? Well, that is exactly what a top-notch database client like DbVisualizer offers!
Download DbVisualizer for free, follow the wizard to set up a database connection, and open the SQL Commander tab.
Write the following broken query:
1
SELECT id,
2
FROM users
If you try to run it, DbVisualizer will report the syntax error:
Note that it reports the syntax issue in the log but also underlines in red the part of the query responsible for the error. Hovering over the underlined key gives the error directly in the SQL editor.
The visual feedback helps you fix the query effortlessly. Plus, you may also want to format your query. To achieve that, right-click on the SQL editor section, select “Format SQL” and then the “Format Buffer” option:
Here is what your final query looks like:
1
SELECT
2
id
3
FROM
4
users
Et voilà! You just fixed and formatted your query. Note that you can also beautify only the current selection with “Format Current.”
The next steps? Use DbVisualizer’s Explain Plan to optimize the query performance. Check out our guide to see a complete DbVisualizer workflow for query optimization.
What if you wanted to select some data without writing any SQL code? In this case, you could use Query Builder, a point-and-click interface that allows you to write queries without having to know SQL syntax in depth.
This was just a simple example but imagine what DbVisualizer helps you achieve with more complex queries!
Conclusion
SQL linters play a vital role in preventing common errors and making SQL code more readable. They can detect syntax errors, spot performance and security issues, and fix stylistic and naming convention inconsistencies.
Here, you learned how SQL lint tools work and what features they offer. You also saw that the best way to use them is directly in your database client. Luckily, there is DbVisualizer, a fully-featured database client that offers built-in linting capabilities with query optimization for dozens of databases. Try it for free today!
FAQ
Can an SQL linter prevent potential issues in SQL code?
Yes, an SQL linter helps you identify and prevent issues within SQL code. By analyzing syntax, style, and helping you adhere to best practices, it catches errors early in the development process. This minimizes the chances of bugs and SQL errors, contributing to smoother database interactions.
What is the difference between an SQL linter and an SQL beautifier?
SQL linters focus primarily on analyzing code for errors and syntax. On the other hand, an SQL beautifier focuses on formatting the code, ensuring consistent indentation, line breaks, and capitalization. Linters usually provide beautification features.
How to format SQL code?
Proper formatting of SQL code is critical for readability and maintainability. Consistent indentation with line breaks to segment logical sections promotes clarity of SQL statements, as well as appropriate capitalization of keywords and spacing around operators. Although manual formatting is possible, SQL lint tools offer more efficient and error-free formatting.
Is SQL linting fast?
The speed of lint for SQL varies depending on factors such as the complexity of the code and the dialect in use. Complex or long queries may take some time to parse, but the benefits of early error detection and improved code quality are so important that the impact on performance can be ignored.
Is it important to keep an SQL linter up to date?
Keeping an SQL lint tool up to date is critical to maintaining its effectiveness and relevance. SQL standards and best practices evolve over time, and the linter must be updated to align with these changes.
