TextMate Sql Formatter Command

Oct 27 2009

Recently, I am doing some heavy database migrations, so I spend a lot of time on playing with SQL again. It’s fun as always. But there are some SQL files are quite long and messy. There is no problem on running, but it’s really painful to look at and do any changes.

So I went out and tried to find any SQL Formatters. There are quite a few and I’ve tasted them as many as I can.

At the end, I found myself really enjoy using Instant SQL Formatter from Gudu Software. It has a free online sql tidy tool and it’s very powerful:

Instant SQL Formatter is a free online sql tidy tool, actually, it not only can beautify your sql but also can turn your formatted sql into html code, so you can post coloured sql code in your blog, forum,wiki and any website easily. In addition to beautifying SQL code, this sql tool can translate SQL code into C#, Java, PHP, DELPHI and other program languages. Another useful feature is find out all database objects such as table, column, function in sql by selecting output format to list database object.

Here is the Free online Tool and here are some examples you can see. Quite impressive! To format your SQL, you just need paste the sql in the textarea, choose the database and output format then press “Format SQL”, you’ll get the result right away.

Every SQL file looks great now. But after tens of copy & paste and copy & paste between TextMate and browser, it feels not as smooth as I want. I think I should find a better way.

Gudu software does provide some desktop version even add-ins, sadly there are all Windows based. It’s not a option for me.

After reading the page source of their free online tool, here is the result:

#!/usr/bin/env ruby

require 'net/http'

url = 'http://www.dpriver.com/cgi-bin/ppserver'
url = URI.parse(url)
http = Net::HTTP.new(url.host, url.port)

query = "<sqlpp_request><dbvendor>mysql</dbvendor><outputfmt>SQL</outputfmt><inputsql>#{ENV['TM_SELECTED_TEXT']}</inputsql><formatoptions><keywordcs>Uppercase</keywordcs><identifiercs>Lowercase</identifiercs><functioncs>InitCap</functioncs><lnbrwithcomma>after</lnbrwithcomma><liststyle>stack</liststyle><salign>sleft</salign><quotechar>\"</quotechar></formatoptions></sqlpp_request>"

header = {
  'Referer' => 'http://www.dpriver.com/pp/sqlformat.htm',
  'User-Agent' => 'Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.5; en-US; rv:1.9.0.1) Firefox/3.0.1'
}

resp, data = http.post(url.path, query, header)

puts resp.error! unless data
formatted_sql = data[/<formattedsql>.*<\/formattedsql>/m].gsub(/<\/?.*>/, '')

puts formatted_sql

Download TextMate SqlFormatter Command

How to install?

After save to your own disk, unzip it, just open those tmCommand files and add them into your TextMate. The shortcut key has been set to Command+Shift+F for now.

As you can see there are two tmCommand files. The “SQL Formatter – Stack” will format you sql with every fields has its own line, the “No Stack” one will put all the fields into one line. Try them and you’ll see the difference.

How to use it?

Open TextMate,  select the SQL query you want to format and press Command+Shift+F. The beautiful formatted SQL will replace the selected SQL “instantly”!

Requirement?

The command is using free online Instant SQL Formatter, so the internet is required.

What’s next?

As I said before, SQL Pretty Printer is really powerful. I really should create a complete TextMate Bundle instead of just one command. But before doing this, I should get the permission from them now.

Any update will be posted here, hopefully soon.

Enjoy the SQL Formatter Command and stay tuned for more.

10 responses so far

  1. Just wanted to say thank you for this! Just started going more SQL on my Mac instead of windows and this will help a lot.

  2. Glad to hear it’s helpful, Jason. You are more than welcome!

    I’ve just got the email from James Wang, who is the developer of Instant SQL Formatter, so I can work on the TextMate Bundler now. Hopefully will release a full function bundle soon.

    Thanks!

  3. Hi Libin,

    Very nice utility.

    I have slight problem while using it Oracle based sql.

    Example:
    select * from v$session where username=’SYSTEM’;

    It formats to:
    SELECT *
    FROM v
    WHERE username = ‘SYSTEM’;

    basically, it’s truncating characters after “$” sign. I edited the bundle and replace “mysql” to “oracle”, but didn’t work.

    Any inputs ?

    thanks
    Nikhil

  4. The web services works fine. It’s the TextMate eats strings with a ‘$’. Please change the Output to “Replace Selected Text” for this command. I’ll try to find a better way to do this.

    Thanks!

  5. I tried installing this and it just erases everything in the file I want formatted. help?

  6. Looks like the web service used is no longer free. So, it just erases everything (i.e. replaces SQL with a blank)

  7. Hi Libin,

    This looks great and I was so happy to find it, but it is no longer working! :( The website at dpriver is still up and seems to work.

    Any progress on a TextMate bundle for this?

    Thanks!

  8. Any news on this?

  9. Nobody cares? This is strange…

  10. Hi,
    got the same problem…it’s not working =(

Leave a Reply