Writing more legible SQL • Hacker News Board

Re: Writing more legible SQL

#81

Post by shubb » Thu, Nov 03, 2016, 1:12 PM UTC

wiz21c wrote: post #44

Dunno if it's the quality of the posts here, but I find interesting to see that when we talk about SQL nobody complains about it : nobody says there are better alternative, nobody talks about the great schism between the last 2 major revisions of the SQL standard,... (IMHO, SQL is one of the oldest languages and it is still super powerful and used like hell in production environment, but that's just a point of view)

In Enterprise Land a lot of people use ETL tooling as an alternative / in addition to writing SQL.

Databases, data interchange formats, and the concepts they represent are mapped out in graphical tools like ER Studio.

The SQL operations you do to load in data from various sources are done in graphical ETL tools like Microsoft SSIS, Talend etc let you draw diagrams which replace SQL. They are more properly a way of programming an engine that runs the SQL but also does things like monitor for incoming files, query webservices etc. Some also provide a technology neutral way to express transforms that can be run in SQL but also on Hadoop or whatever. That said, you sometimes need to go in and write the SQL for optimization.

Enterprise Service Bus sit between multiple databases and multiple data sources and consumers, and the interfaces these provide replace SQL queries. Typically graphical tools or configuration files are used to configure the relationship between the endpoints they provide and databases.

Even outside enterprise land, people rarely write SQL when defining and interfacing with databases. They use an ORM, and a lot of popular ORMs will create a database and provide a way of accessing it so that you never write SQL.

Technologies like OData mean that applications can query data by proxy without needing to know the model it is stored as, and the mapping between the model OData presents and the database is typically implemented using an ORM.

Cloud computing means people with very large databases increasingly want to spread the processing across many slower machines rather than spending the budget on one specialist data processing server with very fast IO. SQL is replaced by other languages in this case (although SQL like languages are making a bit of a comeback).

Finally, in academia and certain very data datacentric industries, SPARQL and successors are still a thing.

I think there is a big divide between enterprise development and what we learn in school / hackernews here. A lot of people outside the industry are not aware of these ecosystems of applications.

I wouldn't recommend any of these over SQL in all cases. They are technologies that solve a problem and you should consider them if you have that problem.

Re: Writing more legible SQL

#82

Post by specialist » Thu, Nov 03, 2016, 1:12 PM UTC

lost_my_pwd wrote: post #59

combatentropy wrote: post #10

Here is how I write SQL: select t1.col1, t2.col2, t3.col3 from table1 t1 join table2 t2 on t1.col2 = t2.col1 join table3 t3 on t1.col3 = t3.col1 and t3.col2 = something_else where t1.col1 > 0 and t2.col2 t1.col4 order by col2 limit 100 So: 1. SQL capitalization is not sacred. I lowercase everything. 2. I just indent subclauses, with four spaces, like I indent other languages. I don't go out of my way to line up thing…

That matches what I do pretty much with the exception of capitalization. I agree, it's not totally necessary, but it does provide a visual delineation of each section/component of the the statement, which, for large statements, can be very helpful in quickly scanning what it does: SELECT t1.col1, t2.col2, t3.col3 FROM table1 t1 join table2 t2 on t1.col2 = t2.col1 join table3 t3 on t1.col3 = t3.col1 and t3.col2 = some…

+1 Vertical whitespace.

Re: Writing more legible SQL

#83

Post by logingone » Thu, Nov 03, 2016, 1:21 PM UTC

combatentropy wrote: post #10

Here is how I write SQL: select t1.col1, t2.col2, t3.col3 from table1 t1 join table2 t2 on t1.col2 = t2.col1 join table3 t3 on t1.col3 = t3.col1 and t3.col2 = something_else where t1.col1 > 0 and t2.col2 t1.col4 order by col2 limit 100 So: 1. SQL capitalization is not sacred. I lowercase everything. 2. I just indent subclauses, with four spaces, like I indent other languages. I don't go out of my way to line up thing…

I read somewhere, at some point, couldn't provide a ref, that the caps were used for clarity back in the days of monochrome screens. Now, with colour syntax highlighting it's no longer as useful.

Re: Writing more legible SQL

#84

Post by jdwyah » Thu, Nov 03, 2016, 1:24 PM UTC

craigds wrote: post #39

Is there a good sql autoformatter? For cleaning up ORM-generated queries so I can read them. I've used python's sqlparse but it produces output that's often still unreadable.

IntelliJ's SQL editor is fabulous. Auto formatting and superb autocomplete, even with aliased tables and CTEs. If you don't want the full IntelliJ you can get Datagrip which is just the DB part. Can't say enough nice things about it.

Re: Writing more legible SQL

#85

Post by jdwyah » Thu, Nov 03, 2016, 1:24 PM UTC

craigds wrote: post #39

Is there a good sql autoformatter? For cleaning up ORM-generated queries so I can read them. I've used python's sqlparse but it produces output that's often still unreadable.

IntelliJ's SQL editor is fabulous. Auto formatting and superb autocomplete, even with aliased tables and CTEs. If you don't want the full IntelliJ you can get Datagrip which is just the DB part. Can't say enough nice things about it.

Re: Writing more legible SQL

#86

Post by __jal » Thu, Nov 03, 2016, 1:33 PM UTC

combatentropy wrote: post #10

Here is how I write SQL: select t1.col1, t2.col2, t3.col3 from table1 t1 join table2 t2 on t1.col2 = t2.col1 join table3 t3 on t1.col3 = t3.col1 and t3.col2 = something_else where t1.col1 > 0 and t2.col2 t1.col4 order by col2 limit 100 So: 1. SQL capitalization is not sacred. I lowercase everything. 2. I just indent subclauses, with four spaces, like I indent other languages. I don't go out of my way to line up thing…

Yep. Same here. I find screaming one's reserved words far more distracting than the highlighting helps.

Another SQL habit that seems to far more annoying than helpful is putting commas at the start of next line. Great, they line up. Did you know that publishers have a term for vertical patterns in blocks of text? They're called 'rivers', and one generally attempts to avoid them, because they're visually distracting.

Re: Writing more legible SQL

#87

Post by __jal » Thu, Nov 03, 2016, 1:37 PM UTC

nerdponx wrote: post #64

Am I the only one who puts commas on the next line? I think of them as an operator analogous to AND or ON: select t1.col1 as col1 , t2.col2 as col2 , t2.col3 as col3 It makes commenting out columns painless.

You're not the only one, but I wish you were, because then I'd have to read a lot less of it.

I doesn't eliminate your commenting problem, it moves your commenting problem from the last line of your list to the first.

Also, it is visually distracting as all get out.

Re: Writing more legible SQL

#88

Post by Sean1708 » Thu, Nov 03, 2016, 1:37 PM UTC

jm547ster wrote: post #16

combatentropy wrote: post #10

Here is how I write SQL: select t1.col1, t2.col2, t3.col3 from table1 t1 join table2 t2 on t1.col2 = t2.col1 join table3 t3 on t1.col3 = t3.col1 and t3.col2 = something_else where t1.col1 > 0 and t2.col2 t1.col4 order by col2 limit 100 So: 1. SQL capitalization is not sacred. I lowercase everything. 2. I just indent subclauses, with four spaces, like I indent other languages. I don't go out of my way to line up thing…

"2. I just indent subclauses, with four spaces," Surely you mean tabs? runs

"runs"

Into my arms.

Re: Writing more legible SQL

#89

Post by Sean1708 » Thu, Nov 03, 2016, 1:42 PM UTC

alistairbayley wrote: post #24

mythz wrote: post #21

I also use UPPER case for SQL Keywords to visually distinguish them from user-defined names (i.e. similar purpose to syntax coloring), but I don't collapse SELECT over multiple lines and line up each statement so the conditions line-up, e.g: SELECT foo, bar FROM baz WHERE foo > 3 AND bar = 'craig.kerstiens@gmail.com'

Re uppercase: can't afford a syntax-highlighting editor?

It's true that there are some editors which highlight SQL inside strings, but most don't.

Re: Writing more legible SQL

#90

Post by stadeschuldt » Thu, Nov 03, 2016, 1:43 PM UTC

Good post. I use one of Jetbrains IDEs and I am big fan of their auto-formatting. You select the dialect of your SQL (or connect it to the data source) and the IDE almost formats it ideally. Sometimes I enforce certain line breaks but for the most part it does a really good job.