I think my #1 rule for SQL these days is to abuse common table expressions as much as possible. No amount of whitespace cleanliness can compensate for a poorly organized problem. There is (in my mind) no longer an excuse for trying to join 10+ tables all at once in a single heroic attempt. Decompose the problem and let the query planner figure that shit out for you, just as you would with a compiler and code.
With CTEs you can offload sophisticated joins and constraints in such a way that less experienced developers can follow behind more easily.
Once you find multiple queries using the same WITH clauses, you can create more permanent views that further centralize and optimize these concerns.
> Spaces should be used to line up the code so that the root keywords all end on the same character boundary.
SELECT file_hash
FROM file_system
WHERE file_name = '.vimrc';
SELECT
file_hash
FROM
file_system
WHERE
file_name = '.vimrc';IMO in the modern day there is no place for any indentation styling that can't be achieved automatically via a pretty printer such as golang has.
This. Relying on developers manually trying to follow a style guide is a recipe for not having a consistent style. Instead something like pgFormatter should be used. I'm not sure what the state of SQL formatters and IDE support is these days. Not sure how many command based options there are.
And people who use things like Datagrip or other IDEs will probably format with their IDE's preferences unless there is a plugin for things like pgFormatter. This works well if there is a company mandated editor/IDE, but not so well when you have developers across various editors and IDEs.
Also, could uppercase go away and never come back? Please?
but why? it's a quick and easy way to distinguish commands from arguments
I think that API URI naming conventions have impacted SQL table names.
It's common to have endpoints like `/customers` and `/customers/{id}` which has a tendency to move SQL table names to match the API endpoint.
I tend to name tables in the singular form if writing applications that interact directly with SQL tables, but where standing up a API in front of a SQL database, I tend to move to the plural naming of the table.
It helps to have the table 'customers' be associated with an API endpoint of the same name.
> Where possible avoid simply using id as the primary identifier for the table.
I've found the opposite true in my limited experience, at least when doing any sort of ORM, then having id implicitly as the primary key makes life so much easier.
SQL is around 40 and there is still no reliable tool for SQL formatting comparable to Prettier or gofmt. At least not for Postgres. There are several formatters but they break on advanced features like stored procedures. If someone is looking for an open source project to contribute to, a tool like this would be greatly appreciated.
I’m probably alone in this, but I dislike naming tables in plural.
IMO, reading “SELECT employee.first_name” makes much more sense than “SELECT staff.first_name”.
Back In The Day at uni in the early 2000s, we were taught that table names should always be singular, and that's mostly what I've seen in the real world since.
I also think the advice around join table naming is a bit silly. Calling a join table between `cars` and `mechanics` `services` (rather than `cars_mechanics`) does not make the relationship clearer, and only works when the relationships maps to a the real world where the relationship has a commonly used name.
The more I read of this guide, the uglier the SQL is.
You are not alone at all, I also prefer singular names for the same reason. I reserve plural names for the rare cases where the single row of a table actually contains information about more than one item, which is usually when I'm doing something denormalized or non-relational e.g. CREATE TABLE user_settings ( user_id INT, settings_data JSON)
You can always alias to a singular … like
join users as user on user….
Then do as you please without the that if you are dealing with a user or leave it plural if multiple…
And if we’re talking personal preference I really dislike caps in reserved words in sql, even before highlighting was everywhere it still just feels archaic for no good reason
You left out the where.
SELECT employee.name where role = 'developer'
Vs
SELECT staff.name” where role = 'developer'
Then the plural one reads better
I don't think it does, because `role` is an attribute of an employee.
SELECT employee.Name
FROM employee
WHERE employee.Role = 'developer'
SELECT employees.Name
FROM employees
WHERE employees.Role = 'developer'According to the guide it would be e.first_name or s.first_name.
Yes, you are indeed alone in this
For comparison, here’s Mozilla’s SQL style guide: https://docs.telemetry.mozilla.org/concepts/sql_style
Thanks for sharing this!
It looks so much cleaner in my eyes.
Plus it uses constant-sized indents, which means less futzing about with spaces and all that.
Also means you can comment out the first select item, something you can't do with the article's approach.
I am definitely not a fan of that style. Wastes too much vertical space without much benefit.
I think this guide misses the point that “JOIN” is not a root keyword but a modification on “FROM”. It is more akin to logical “AND”, “OR”, etc.
And this stacks much better once you start doing complex joins especially when you can add parentheses to change where you actually join
FROM a JOIN b JOIN c
FROM a JOIN (b JOIN C)
Page title: SQL Style Guide, discussions in:
2018 (59 points, 16 comments) https://news.ycombinator.com/item?id=17924917
2016 (257 points, 147 comments) https://news.ycombinator.com/item?id=12671667
2015 (16 points, 10 comments) https://news.ycombinator.com/item?id=9941150
Not bad advice. The one about “where possible avoid simply using id as the primary identifier for the table” stood out to me. In the past with multiple ORMs (ya, ya, we all hate them) the default was to map to a column named id. Also when doing joins its cleaner to use the table_name.id or alias.id then table_name.table_name_id or alias.table_name_id or whatever else besides id is used. The best is when multiple people have worked on the project over the years and the columns are a combo of camel, snake, camel_snake, all UPPER / lower. Must look at the table definitions or ERD every time you want to write some non-trivial query. So having a consistent style guide is better than having any one specific style guide. This would be a good starting point and adjust with your team as needed.
> Also when doing joins its cleaner to use the table_name.id or alias.id then table_name.table_name_id or alias.table_name_id or whatever else besides id is used However, using 'table_name.table_name_id' and then having another table with an FK that references it with the same name i.e. 'table_2.table_name_id' allows you to use a shorthand 'USING' clause instead of 'ON' in databases that support it.
Great point thanks for calling USING out. Since you end up putting table_name_id for FKs it totally makes sense to just use that in the main table. Seems I am just so accustomed to having id as the default PK over the years it become habit (my DB professor was an old time IBM-er who preached all tables will have an ID). With auto complete in just about every tool these days and ORM limitations improving will need to update my thinking on this reality. 95% of the time living in the MSSQL world so USING is not something that can even be used (I don’t think).
I have found that naming ids as <thing>_id helps downstream code when trying to figure out which thing's id you are dealing with. It also helps with avoiding renaming fields when a structure contains multiple ids.
I do agree it makes joins more verbose.
This isn't a great idea. Maybe it works for you, but you can alias it, too. The main identification column of a table should just be id. Any foreign keys can have a table prefix.
Please don't prefix the main table id with the table name.
I've stopped using aliases in the SQL I write and it's dramatically increased the clarify.
From this style guide, the aliases section would look like this in my style guide:
SELECT first_name
FROM staff
JOIN students
ON students.mentor_id = staff.staff_num;If the table names are all short one word like that, they are already basically aliases. What do you do when you end up on some legacy project (you did not make the schema) where_the_table_names_look_like_this? Seems some sort of alias might be more appropriate. Also, when you are writing longer / sub queries or using Common Table Expressions it’s impossible to not use an alias.
How do you know which table first_name came from?
Also aliasing can be as much for legibility as anything.
SELECT
ZZ.first_name
FROM staff AS ZZ
INNER JOIN students AS Q ON Q.mentor_id = ZZ.staff_num;I‘m not a fan of upper case keywords especially when there is also syntax highlighting that gives them a unique color.
Shifts my focus away from the rest of the query.
SQL keywords have been upper case for decades. I prefer it because it is faster to match visually.
Just like I don’t like uppercase paragraphs because of the same reason
typing them is such a pain though.
This is really good advice and the coding style (alignment) matches what I came to without any real guidance when I was learning SQL 20+ years ago. The only thing I slack on, is uppercasing the keywords. I hate switching case so much. But, I will fit the coding style of the codebase I am working on when it comes to that.
What'the best formatter for sql?
Great document to feed to GPT while ensuring it writes code :)