The Art of PostgreSQL/

...

/

SQL Style Guidelines

SQL Style Guidelines

Look at a few examples to understand the SQL style guidelines.

We'll cover the following...

The first step here is to realize that your database engine is actually part of your application logic. Any SQL statement you write, even the simplest possible, does embed some logic; you are projecting a particular set of columns, filtering the result to only a part of the available dataset (thanks to the where clause), and you want to receive the result in a known ordering. That is already business logic. Application code is written in SQL.

Code style is mainly about following the “principle of least astonishment” rule. That’s why having a clear internal style guide that every developer follows is important in larger teams. We’ll cover several aspects of SQL code style here, from indentation to alias names.

Indentation

Indenting is a tool aimed at making it easy to read the code. Let’s face it, we spend more time reading code than writing it, so we should always optimize for easy-to-read code. SQL is code, so it needs to be properly indented.

Good vs. bad writing styles

Let’s see a few examples of bad and good styles so you can decide about your local guidelines.

Press + to interact
SELECT title, name FROM album LEFT JOIN track USING(albumid) WHERE albumid = 1 ORDER BY 2;

Here, we have a runaway query all on the same line, making it more difficult than it should be for a reader to grasp what the query is about. Also, the query uses the old habit of all-caps SQL keywords. While it’s true that SQL started a long time ago, we now have color screens and syntax highlighting, and we don’t write all-caps code anymore, not even in SQL.

Our advice is to right align top-level SQL clauses and put them on new lines:

Press + to interact
  select title, name
    from album left join track using(albumid)
   where albumid = 1
order by 2;

Now it’s easier to understand the structure of this query at a glance and to realize ...