SQL style guide, used by the Medium Product Science team

sql_style_guide.md

SQL Style

Naming

SQL keywords should be capitalized. Names should be lowercase.

-- Right
SELECT name, COUNT(track) FROM radiohead_albums

-- Wrong
select Name, count(TRACK) from radioheadAlbums

Names should be underscore-separated.

-- Right
SELECT track_length FROM radiohead_albums

-- Wrong
SELECT tracklength FROM radioheadAlbums

Table names should be plurals. Field names should usually be singular — an exception is made when the field legitimately contains multiple values, like a JSON array of items.

-- Right
CREATE TABLE radiohead_members (name varchar(16))

-- Wrong
CREATE TABLE radiohead_member (names varchar(16))

Name references should not use (or require) backticks.

-- Right
SELECT lyrics FROM radiohead_albums

-- Wrong
SELECT `lyrics` FROM radiohead_albums

-- Also wrong
CREATE TABLE radiohead_members (`user` varchar(16))

Aliases should always use the AS keyword. Aliased names should be meaningfully descriptive. It's often tempting to use one-letter aliases, like aliasing collections to c. But col is almost as short, and much more readable. Have a good argument for using c if you choose to.

-- Right
SELECT char_length(lyrics) AS letters
FROM radiohead_albums

-- Wrong
SELECT char_length(lyrics) AS l
FROM radiohead_albums

Formatting

Queries that don't fit comfortably on one line should use newlines for each new clause. Examples of newline clauses are SELECT, FROM JOIN, WHERE, HAVING, etc.

-- Right
SELECT lyrics
FROM radiohead_albums
WHERE name = 'The Bends'
ORDER BY track_number

-- Wrong
SELECT lyrics FROM radiohead WHERE name = 'The Bends' ORDER BY track_number

All keywords from a given query level should be left-aligned.

-- Right
SELECT lyrics
FROM radiohead_albums
WHERE name = 'The Bends'
ORDER BY track_number

-- Wrong
SELECT lyrics
  FROM radiohead_albums
  WHERE name = 'The Bends'
  ORDER BY track_number

-- Also wrong (right-aligned)
  SELECT lyrics
    FROM radiohead_albums
   WHERE name = 'The Bends'
ORDER BY track_number

Subqueries should be indented one level from their parent query. Surrounding parentheses should use the parent query's indentation and should not open on new lines. Indentations should use 2 spaces. Never indent with tabs.

-- Right
SELECT lyrics
FROM radiohead_albums
WHERE name IN (
  SELECT name
  FROM radiohead_albums
  WHERE album_name LIKE '%The%'
)

-- Wrong
SELECT lyrics
FROM radiohead_albums
WHERE name IN (SELECT name
FROM radiohead_albums
WHERE album_name LIKE '%The%')

-- Also wrong
SELECT lyrics
FROM radiohead_albums
WHERE name IN
(
  SELECT name
  FROM radiohead_albums
  WHERE album_name LIKE '%The%'
)

Queries that reference multiple tables should always include the table name (or alias) when referencing fields, even if it is not ambiguous.

-- Right
SELECT rh.lyrics
FROM radiohead_albums AS rh
JOIN other_albums AS other ON rh.writer = other.writer

-- Wrong
SELECT lyrics
FROM radiohead_albums AS rh
JOIN other_albums AS other ON rh.writer = other.writer

Output

Weeks start on Sunday and end on Saturday, but Postgres' weeks start on Monday and end on Sunday. You may have to do some math to get proper weeks.

-- Right
SELECT DATE_TRUNC('week', release_date + interval '1 day') - interval '1 day' AS rel_week
FROM radiohead_albums

-- Wrong
SELECT DATE_TRUNC('week', release_date) AS rel_week
FROM radiohead_albums

Comments

Always double hyphen (--) comments, even if the database supports other formats. This means you don't have to worry about nesting or accidentally forgetting to close a comment block, which can be difficult to catch because SQL syntax highlighting isn't always the best.

-- Right
-- This is still a comment

/* Wrong */
/* Don't do this */