> 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 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.
I use Jetbrains and there is at least full coverage for MSSQL in my experience, which is a huge dialect -- not only syntax highlighting but full IDE features like autocompletion and target name refactoring etc.
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
I was not tech lead on that part of the project and did not contribute to any of its initial research/implementation, sadly; I was brought in post-hoc to help with perf issues/cleanup.
To directly answer your question, though, without going into too much detail, there were pci compliance issues with some tables/columns in the mysql db, so if I had to guess, perhaps DMS had the capability to assuage that concern more elegantly than mysql_fdw[0], though the mysql_fwd EXCEPT parameter seems to also do the same thing, so honestly, I have no idea.
By default, all views and tables existing in a particular database on the MySQL host are imported. Using this option, you can exclude specified foreign tables from the import.[1]
> 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;
> 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.
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.
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 people have worked on the project over the years and the columns are a combo of camel, snake, camel_snake, all UPPER / lower. Must look at the table definitions or ERD every time you want to write some non-trivial query. So having a consistent style guide is better than having any one specific style guide. This would be a good starting point and adjust with your team as needed.
> 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).
I have found that naming ids as <thing>_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.
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.
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.
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.
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.
Editors highlight syntax.
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...
And 10 other dialects are listed..
```sql
```
https://docs.telemetry.mozilla.org/concepts/sql_style
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?
To directly answer your question, though, without going into too much detail, there were pci compliance issues with some tables/columns in the mysql db, so if I had to guess, perhaps DMS had the capability to assuage that concern more elegantly than mysql_fdw[0], though the mysql_fwd EXCEPT parameter seems to also do the same thing, so honestly, I have no idea.
[0]: https://github.com/EnterpriseDB/mysql_fdw[1]: https://www.enterprisedb.com/docs/mysql_data_adapter/latest/...
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
SELECT employee.name where role = 'developer'
Vs
SELECT staff.name” where role = 'developer'
Then the plural one reads better
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.
Seems like a lot of work to me. And I don't know any formatter that'd do that work.
I do agree it makes joins more verbose.
Please don't prefix the main table id with the table name.
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.
I also use "id" but I would say SQL was designed with the opinion that ids should be prefixed with the table name.
From this style guide, the aliases section would look like this in my style guide:
I'm not opposed to aliases, it's just no longer something I do by default.
Aliases are the SQL equivalent of ternary operators. Are you trying to write clean code or are you trying to express to other people you know things?
Also aliasing can be as much for legibility as anything.
[0] https://en.m.wikipedia.org/wiki/The_Magical_Number_Seven,_Pl...
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.
Shifts my focus away from the rest of the query.
Just like I don’t like uppercase paragraphs because of the same reason
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.