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

51–60 of 76 posts

Re: SQL style guide by Simon Holywell

#51

Post by thiht »

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.

The fact the "USING" keyword exists would disagree with you.

I also use "id" but I would say SQL was designed with the opinion that ids should be prefixed with the table name.

Re: SQL style guide by Simon Holywell

#52

Post by wcrossbow »

thiht wrote: post #49
dagss wrote: post #43

Earlier quoted context omitted.

Why do you not make that argument for "if" and "else" in Go/Java/...? Editors highlight syntax.

Editors don’t syntax highlight SQL queries written as strings. That’s the main reason I write my queries with uppercase keywords in my Go programs

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

Re: SQL style guide by Simon Holywell

#53

Post by prudentpomelo »

Please don't make me write uppercase keywords. They make my eyes bleed and hurt my hands. Why not let the syntax highlighter do the heavy lifting for you? Dress your keywords with mauve or a nice butterscotch. Don't shout at the database. Write queries as if you are texting your best friend—all lowercase. Your friend understands and so does the database.

  select      e.first_name,
              e.last_name,
              s.amount
  from        employee as e
  inner join  salary as s
              on e.id = s.employee_id;

Re: SQL style guide by Simon Holywell

#54

Post by steve_gh »

seer wrote: post #26
harterrt wrote: post #13

For comparison, here’s Mozilla’s SQL style guide: https://docs.telemetry.mozilla.org/concepts/sql_style

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 Can be different than FROM a JOIN (b JOIN C) Apart from that I think I came up independently to the exact same rules…

SQLis based on set theory, which is asdociative. So (a JOIN b) JOIN c = a JOIN (b JOIN c)

Your DB's query planner should optimise given the available indices.

Re: SQL style guide by Simon Holywell

#55

Post by agubelu »

> Try to only use standard SQL functions instead of vendor-specific functions for reasons of portability.

Hard disagree here. "Let's do/not do this, in case we decide to change databases in the future" is one of the greatest lies we tell ourselves. You're just making your life harder now and in the near future, for the nebulous promise of "seamlessly replacing your database backend if needed".

In 95% of cases, it's not needed, and you're getting all of the downsides for no benefit. And if it's needed in a late stage of your application's life, changing a bunch of SQL functions will be just one tiny problem among many bigger ones.

Re: SQL style guide by Simon Holywell

#56

Post by tankenmate »

jd3 wrote: post #44
bob1029 wrote: post #39

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

A week before being laid off last month, I solved a decade+ old open problem at our company which first occurred since Django doesn't natively support CTE's, leading to years of technical debt from the ersatz sql/query plans produced by our fragile queries. I ended up manually overloading get_extra_restriction on a custom ForeignKey class (we couldn't use FilteredRelation b/c we were still on django 1.11), which ensu…

Good call.

As an aside why not use postgres's mysql foreign data wrapper instead of syncing with the mysql database?

Re: SQL style guide by Simon Holywell

#57

Post by vlvdus »

dagss wrote: post #43
y42 wrote: post #40

Earlier quoted context omitted.

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

Why do you not make that argument for "if" and "else" in Go/Java/...? Editors highlight syntax.

That would be a nice change too. Also THEN, BEGIN, END to replace varous brackets.

Re: SQL style guide by Simon Holywell

#58

Post by mijamo »

wcrossbow wrote: post #52
thiht wrote: post #49

Earlier quoted context omitted.

Editors don’t syntax highlight SQL queries written as strings. That’s the main reason I write my queries with uppercase keywords in my Go programs

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.

Re: SQL style guide by Simon Holywell

#59

Post by Izkata »

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…

I find splitting out over lines like that harder to read because the table-like columns now overlap with each other and aren't aligned with the keyword they belong to.

Re: SQL style guide by Simon Holywell

#60

Post by kmoser »

beachy wrote: post #34
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.

Automatic formatters and pretty printers never seem to be able to make the exceptions necessary for me to use them. For example, I want the contents of all my HTML tags to be formatted as one long line (think of

tags), except when they happen to contain a SQL statement which I want to remain formatted exactly as written.

Post reply on HN