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

41–50 of 76 posts

Re: SQL style guide by Simon Holywell

#41

Post by calrain »

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.

Re: SQL style guide by Simon Holywell

#42

Post by snorremd »

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.

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.

Re: SQL style guide by Simon Holywell

#43

Post by dagss »

y42 wrote: post #40
emmelaich wrote: post #29

Earlier quoted context omitted.

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

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.

Re: SQL style guide by Simon Holywell

#44

Post by jd3 »

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 ensured that the JOIN ON ... clause limited the tables being joined to their correct partition/schema while being accessed through a view

The view thing is a long story — it was a legacy PAC codebase from the '90s which used 13+ schemas in a mysql db that was then being synced to our postgres db through Amazon DMS. All of the tables on each view contain identical source_schema/CompanyID columns, hence the

    '%(remote_alias)s. "source_schema" = %(fk_alias)s. "source_schema" AND '
    '%(remote_alias)s. "CompanyID" = %(fk_alias)s. "CompanyID"
etc. approach

before/after query plan in depesz: https://imgur.com/a/HQbNSIL

Re: SQL style guide by Simon Holywell

#45

Post by hnlmorg »

harterrt wrote: post #13

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

This is a much better style guide in my opinion.

It’s still highly readable but also much much easier to write and modify.

Though I am biased because it’s also how I used to write SQL back when PL/SQL was my day job. Albeit I fell into this design because it proved to be the easiest way to write and maintain readable code.

Re: SQL style guide by Simon Holywell

#46

Post by switch007 »

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

Re: SQL style guide by Simon Holywell

#47

Post by mjevans »

cwbriscoe wrote: post #15
harterrt wrote: post #13

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

I am definitely not a fan of that style. Wastes too much vertical space without much benefit.

The benefit is how quickly an experienced programmer can accurately isolate portions of logic and understand / mutate them.

It also achieves that in monochrome, which is likely to be the case when an SQL query is in a shell script's <<< HEREDOC or in a string blob in a log file or source code for another language's compiler.

Re: SQL style guide by Simon Holywell

#48

Post by bartvk »

cwbriscoe wrote: post #14

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.

IMHO, not all good. In this style guide, the person says to "SELECT, FROM, etc. are all right aligned while the actual column names and implementation-specific details are left aligned."

Seems like a lot of work to me. And I don't know any formatter that'd do that work.

Re: SQL style guide by Simon Holywell

#49

Post by thiht »

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.

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

Re: SQL style guide by Simon Holywell

#50

Post by prudentpomelo »

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…

CTEs would be such a blessing. I am stuck using mysql 5.6. So many queries would just get simpler.
Post reply on HN