> 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 know what's neat and still easy to modify/diff? Just indent a new line for each row.
SELECT
file_hash
FROM
file_system
WHERE
file_name = '.vimrc';
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.
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 <p> tags), except when they happen to contain a SQL statement which I want to remain formatted exactly as written.
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.
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.
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
> 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.
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.
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.
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.
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 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)
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.
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;
Readit News
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.
https://docs.telemetry.mozilla.org/concepts/sql_style
Deleted Comment
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.
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
etc. approachbefore/after query plan in depesz: https://imgur.com/a/HQbNSIL
As an aside why not use postgres's mysql foreign data wrapper instead of syncing with the mysql database?
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.
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.
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.
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.
And this stacks much better once you start doing complex joins especially when you can add parentheses to change where you actually join
Can be different than Apart from that I think I came up independently to the exact same rules when building the prettier extension for SQL a few years back.Your DB's query planner should optimise given the available indices.
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
IMO, reading “SELECT employee.first_name” makes much more sense than “SELECT staff.first_name”.
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.
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
Deleted Comment
Deleted Comment
SELECT employee.name where role = 'developer'
Vs
SELECT staff.name” where role = 'developer'
Then the plural one reads better
Deleted Comment
Dead Comment