Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

SQL style guide by Simon Holywell

sqlstyle.guide

Post reply on HN
Print view View original

31–40 of 76 posts

Re: SQL style guide by Simon Holywell

#31

Post by Sn0wCoder »

jpnc wrote: post #20
Sn0wCoder wrote: post #4

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 peop…

> 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).

Re: SQL style guide by Simon Holywell

#32

Post by turbojet1321 »

mnsc wrote: post #24
hcarvalhoalves wrote: post #5

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”.

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'
reads much better to me than

   SELECT employees.Name
   FROM employees
   WHERE employees.Role = 'developer'

Re: SQL style guide by Simon Holywell

#34

Post by beachy »

SPBS wrote: post #23

> 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'; This style is annoying and I wish it gained less traction. It looks neat but it puts so much burden on the query writer, especially when you modify the query and all of the sudden you need to indent multiple lines just to make them all align. You k…

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.

Re: SQL style guide by Simon Holywell

#35

Post by ivanb »

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.

Re: SQL style guide by Simon Holywell

#37

Post by pqwEfkvjs »

> 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.

Re: SQL style guide by Simon Holywell

#38

Post by [deleted] »

SPBS wrote: post #23

> 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'; This style is annoying and I wish it gained less traction. It looks neat but it puts so much burden on the query writer, especially when you modify the query and all of the sudden you need to indent multiple lines just to make them all align. You k…

[deleted]

Re: SQL style guide by Simon Holywell

#39

Post by bob1029 »

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.

Re: SQL style guide by Simon Holywell

#40

Post by y42 »

emmelaich wrote: post #29
SPBS wrote: post #23

> 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'; This style is annoying and I wish it gained less traction. It looks neat but it puts so much burden on the query writer, especially when you modify the query and all of the sudden you need to indent multiple lines just to make them all align. You k…

Also, could uppercase go away and never come back? Please?

but why? it's a quick and easy way to distinguish commands from arguments
Post reply on HN