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

61–70 of 76 posts

Re: SQL style guide by Simon Holywell

#61

Post by Izkata »

otteromkram wrote: post #28
jkubicek wrote: post #3

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;

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;

Without aliases makes each part understandable without having to jump around and hold extra context in your head:

  SELECT
      staff.first_name
  FROM staff
  INNER JOIN students ON student.mentor_id = staff.staff_num;
Whether or not you agree on the exact number [0], humans have limited working memory and aliases that don't group up concepts use it up unnecessarily.

[0] https://en.m.wikipedia.org/wiki/The_Magical_Number_Seven,_Pl...

Re: SQL style guide by Simon Holywell

#62

Post by jkubicek »

Sn0wCoder wrote: post #6
jkubicek wrote: post #3

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.

Actually, what I should have said was, "I've stopped using alias by default for every table reference".

I'm not opposed to aliases, it's just no longer something I do by default.

Re: SQL style guide by Simon Holywell

#63

Post by harterrt »

yen223 wrote: post #22
harterrt wrote: post #13

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.

Yes! I can’t see the point of enforcing fussing with indents to get a river.

Re: SQL style guide by Simon Holywell

#65

Post by harterrt »

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…

Agreed. Fwiw, Mozilla’s style guide prohibits rivers like this.

https://docs.telemetry.mozilla.org/concepts/sql_style

Re: SQL style guide by Simon Holywell

#66

Post by pragmatic »

The obsession with portabilty is a red herring.

Are you writing C code that's portable to Java, C++ an C#?

No that would be stupid. Same here. Any heavy loaded, heavily used db is going toneed optimization sober or later.

The In vs Or is dumb bc ORs blow up query plans and your better off with unions instead.

Re: SQL style guide by Simon Holywell

#68

Post by psadri »

otteromkram wrote: post #25
psadri wrote: post #9

Earlier quoted context omitted.

I have found that naming ids as _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.

Most of the schemas I have worked with used “id”. I agree it’s the default. But aliasing was inconsistent and made it hard to figure out which table id is being referred to later in the code.

I know it is a matter of discipline but any tool that encourages consistency helps. A database schema is definitely one of those tools.

One unrelated idea is to include the entity in the id itself. I have never done this but I’d imagine it would help with things like logging / observability. It would not play nice with indices though.

Re: SQL style guide by Simon Holywell

#69

Post by eddd-ddde »

mijamo wrote: post #58
wcrossbow wrote: post #52

Earlier quoted context omitted.

Editors can highlight SQL queries embedded as strings. Neovim can do it, and I'm pretty confident it's not going to be alone in that respect. edit: Not the editor I use but thought it might be helpful. Here is an extension, which I haven't tested, to do this in VSCode: https://marketplace.visualstudio.com/items?itemName=iuyoy.hi...

I have never seen a syntax highlighter for SQL that actually covers the real deal from Postgres dialect. Basic stuff is covered and then suddenly you use a combination that isn't covered and the colors are all wrong. This is even true for pgadmin, which is ironic. Unlike most programming languages, SQL built in syntax is huuuuuge and it is very hard to cover it all, especially as it varies with the dialect.

Any jetbrains IDE with sql tools will work perfectly in my experience.

Re: SQL style guide by Simon Holywell

#70

Post by eddd-ddde »

switch007 wrote: post #46
pqwEfkvjs wrote: post #37

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

Old school SQL gurus will tell you the ORM is wrong and just to override it. It breaks USING

Exactly, USING shines when IDs are employee_id or task_id in every table.
Post reply on HN