Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Writing more legible SQL

craigkerstiens.com

Post reply on HN
Print view View original

41–50 of 168 posts

Re: Writing more legible SQL

#41

Post by gregn610 »

ManlyBread wrote: post #25
maurycy wrote: post #14

Is there indent for SQL?

Poor Man's SQL Formatter for Notepad++ can do it, it leaves a lot to be desired though in terms of customization. It has saved me a lot of times, since I'd rather read Poor Man's code than the code of the other people.

Another vote for Poor Man's.

But I think it's an important caveat that the whole team use the same formatter and the same settings.

Otherwise diff's turn to useless noise* because Cubicle Bob has personalized his settings.

*Yes, I've tried the Winmerge plugin. It's helpful but not ideal.

Re: Writing more legible SQL

#42

Post by berntb »

rubyfan wrote: post #11
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 go back and forth with conjunction at the end or beginning of the line. At the end you get to line up column names, at the beginning it makes it easier to comment out or remove the line and basically contains the intended logic on a single line. I think I usually end up with the later because I think it makes more sense.

Why not this?

  WHERE A
    AND B
    AND C
Or better, this? You can comment out any part easily.

  WHERE 1
    AND A
    AND B
    AND C
(With keywords capitalized, as g-d intended. :-) Or not.)

Re: Writing more legible SQL

#43

Post by ksri »

I'd love to get HN feedback on a library I wrote to make SQL more manageable - https://github.com/hashedin/jinjasql.

The library is built on the Jinja template engine. You can create macros, functions and other reusable sql fragments, and then use them to create the query. You can also write loops and conditionals, so you can do all the things you routinely do to generate HTML.

The library tracks bind parameters, and doesn't let user input into the query. At the end of the day, you get the generated SQL and an array of bind parameters. You can use these two to execute the query using whatever database & driver combination you like.

Re: Writing more legible SQL

#44

Post by wiz21c »

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)

Re: Writing more legible SQL

#45

Post by bhrgunatha »

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'

That covers the simplest case.

What would you do when you're selecting 20 columns from 5 different tables joined together?

Re: Writing more legible SQL

#47

Post by dspillett »

> CTEs: First, yes they can be an optimisation boundary.

Note that this varies by database engine. They are an optimisation fence in postgres, they generally aren't in MS SQL Server, I'm not sure about Oracle or DB2 but I think they can optimise predicate application over CTE boundaries too.

Re: Writing more legible SQL

#48

Post by the_ez »

sqlparse comes to mind - https://github.com/andialbrecht/sqlparse

I know the maintainer, they also run a service at https://sqlformat.org/

You can try it on the command line e.g. on Fedora:

  $ sudo dnf install python3-sqlparse
  $ echo "select c1, c2, c3 from table1 join table2 t2 on t1.col2 = t2.col1 where 1=2 order by col2 limit 100" | sqlformat -k upper -i lower -sa -
  SELECT c1,
         c2,
         c3
    FROM table1
    JOIN table2 t2
      ON t1.col2 = t2.col1
   WHERE 1 = 2
   ORDER BY col2
   LIMIT 100

Re: Writing more legible SQL

#49

Post by mianos »

maurycy wrote: post #14

Is there indent for SQL?

I am using Jetbrains Datagrip to write SQL all day, every day. It can auto-format SQL just like all their other tools can auto-format the language in use. It also has a bunch of other handy stuff like symbol completion from the current database.

Re: Writing more legible SQL

#50

Post by jgilfillan »

This is how I write SQL:

  select
     col1
    ,col2
    ,col3
  from table1 a
  
  left join table 2 b
    on a.col1 = b.col2
  
  where 1=1
    and col1 = 'condition'
    and col2 = 'condition2'
  ;


 * everything lower case (except strings)
 * leading commas
 * conditions indented by two spaces
 * select columns indented by two spaces, except the first colunm which is indented by three.
 * where 1=1 for easier commenting/uncommenting of conditions
Post reply on HN