5 ms·
SQL style guide by Simon Holywell
- croes 2y agoI‘m not a fan of upper case keywords especially when there is also syntax highlighting that gives them a unique color. Shifts my focus away from the rest of the query.
- jbverschoor 2y agoSQL keywords have been upper case for decades. I prefer it because it is faster to match visually. Just like I don’t like uppercase paragraphs because of the same reason
- dopfk09320k 2y agotyping them is such a pain though.
- deleted 2y ago[deleted]
- gnabgib 2y agoPage title: SQL Style Guide, discussions in: 2018 (59 points, 16 comments) https://news.ycombinator.com/item?id=17924917 https://news.ycombinator.com/item?id=17924917 2016 (257 points, 147 comments) https://news.ycombinator.com/item?id=12671667 https://news.ycombinator.com/item?id=12671667 2015 (16 points, 10 comments) https://news.ycombinator.com/item?id=9941150 https://news.ycombinator.com/item?id=9941150
- jkubicek 2y agoI'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;
- Sn0wCoder 2y agoIf 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.
- jkubicek 2y agoActually, 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.
- otteromkram 2y agoHow 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;
- Izkata 2y agoWithout 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,_Plus_or_Minus_Two https://en.m.wikipedia.org/wiki/The_Magical_Number_Seven,_Pl...
- asdfasvea 2y agoAgree. In my experience people use aliases to use aliases. They know them but don't understand them. 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?
- Sn0wCoder 2y agoNot 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.
- psadri 2y agoI 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. I do agree it makes joins more verbose.
- otteromkram 2y agoThis 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.
- thiht 2y agoThe 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.
- psadri 2y agoMost 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.
- hcarvalhoalves 2y agoI’m probably alone in this, but I dislike naming tables in plural. IMO, reading “SELECT employee.first_name” makes much more sense than “SELECT staff.first_name”.
- hackernewds 2y agoYes, you are indeed alone in this
- deleted 2y ago[deleted]
- deleted 2y ago[deleted]
- ammojamo 2y agoYou 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)
- other_herbert 2y agoYou can always alias to a singular … like 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
- croes 2y agoAccording to the guide it would be e.first_name or s.first_name.
- mnsc 2y agoYou left out the where. SELECT employee.name where role = 'developer' Vs SELECT staff.name” where role = 'developer' Then the plural one reads better
- hackernewds 2y agoGreat document to feed to GPT while ensuring it writes code :)
- harterrt 2y agoFor comparison, here’s Mozilla’s SQL style guide: https://docs.telemetry.mozilla.org/concepts/sql_style https://docs.telemetry.mozilla.org/concepts/sql_style
- cwbriscoe 2y agoI am definitely not a fan of that style. Wastes too much vertical space without much benefit.
- mjevans 2y agoThe 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.
- harterrt 2y agoWhat would you change?
- yen223 2y agoThanks 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.
- harterrt 2y agoYes! I can’t see the point of enforcing fussing with indents to get a river.
- seer 2y agoI 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 when building the prettier extension for SQL a few years back.
- cwbriscoe 2y agoThis 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.
- bartvk 2y agoIMHO, 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.
- SPBS 2y ago> 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';
- emmelaich 2y agoAlso, could uppercase go away and never come back? Please?
- y42 2y agobut why? it's a quick and easy way to distinguish commands from arguments
- dagss 2y agoWhy do you not make that argument for "if" and "else" in Go/Java/...? Editors highlight syntax.
- thiht 2y agoEditors 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
- wcrossbow 2y agoEditors 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.highlight-string-code https://marketplace.visualstudio.com/items?itemName=iuyoy.hi...
- ivanb 2y agoSQL 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.
- mustime 2y ago[dead]
- Dkuku 2y agoWhat'the best formatter for sql?
- mustime 2y ago[dead]
- pqwEfkvjs 2y ago> 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.
- bob1029 2y agoI 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.
- jd3 2y agoA 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 https://imgur.com/a/HQbNSIL
- tankenmate 2y agoGood call. As an aside why not use postgres's mysql foreign data wrapper instead of syncing with the mysql database?
- jd3 2y agoI 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] [0]: https://github.com/EnterpriseDB/mysql_fdw https://github.com/EnterpriseDB/mysql_fdw [1]: https://www.enterprisedb.com/docs/mysql_data_adapter/latest/configuring/#parameters-4 https://www.enterprisedb.com/docs/mysql_data_adapter/latest/...
- calrain 2y agoI 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.
- prudentpomelo 2y agoPlease 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;
- agubelu 2y ago> 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.
- pragmatic 2y agoThe 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.
- pragmatic 2y agoWho is Simon Holywell and wtf do I care what they think about SQL formatting?