Previously, JSFiddle was featured here as a very handy JavaScript demo and testing environ on the Web. I use it when I don’t have my own machine but want to test out a Web Page that has JavaScript code. Well now the same testing capability has been created for SQL – and its called SQLFiddle. And SQLFiddle offers an impressive set of the amjor RDBMS -Relational Database Management Systems for testing:
1)Microsoft SQL Server 2008 R2;
2)Microsoft SQL Server 2012;
3)MySQL Server 5.5.20;
4)Oracle DBMS Server 11g R2;
5)Postgres 9.1.2.13 Server;
6)SQLite (WebSQL)
7)SQLite(SQL.js)
This is an impressive list of relational databases with only IBM DB2 and SAP/Sybase Adaptive Server missing from the line-up.
And just like the case of JSFiddle, SQL Fiddle makes SQL testing easier to do.
SQL Fiddle with DDL Window, SQL Window, and Results Panel underneath them.
First, you have the latest versions of the most popular SQL databases to work with. Second, DDL Panel allows users to define their tables and provide data using INSERT commands . Third, the SQL Window allows users to try different SQL queries on the tables created. SQLFiddle can be very helpful for 2 to 4 table joins where the joins start to become tricky. Also, SQLFiddle is very useful to test out different schema coding plans for for field definitions, key constraints, index structures, and other data definition nuances. I sit on my copy of SQL in a Nutshell
SQL Fiddle also can be a GodSend when working out how to define schema with all the different DDL options and table designs available in all the SQL databases but especially in MySQL or Oracle. SQLFiddle gives crisp but infomative DDL error messaging.SQLFiddle also proved helpful in getting some MySQL DDL over to Oracle tables. The two DDL’s are quite different in many of their options, so having SQL in a Nutshell or other documentation is a must.
Also quite helpful once you get the DDL schema definition working is to fine tune the schema’s different indexing and table options for performance.Again SQLFiddle provides useful information:
SQLFiddle as shown in the Highlighted screenshot above, supplies execution time for all SQL queries plus an execution plan. However, the SQL error messages can be cryptic at times, so I stage build of the query phase by SQL phrase until the query is complete. Really, this is standard practice with all the SQL developer tools where error codes are some times confusing.
It also helps that SQLFiddle automatically saves every session, so users can click on the MyFiddle menu option and restore a previous session or send the address of that session on to a colleague to take a look at the problem and solution worked out so far.
The Wish-Fors in SQLFiddle
SQLFiddle is new and there are 4 rough spots. First, there is only one execution time number – there is no breakout of execution times in the displayed Execution Plan. This can be frustrating because I know for example, Oracle has those breakout numbers and they are very useful in deciding what schema design+SQL Query method is working most effectively. The other problem is that INSERT and UPDATE commands are not allowed in the SQL Window. You can append them to the DDL Window ; but then this means for a few minor corrections all the data has to be re-read into the database. For one problem with 50,000 rows of complex data that meant waiting 2 minutes each time the data changed. Third, there appears to be no way to test stored procedures but this is a commentary on the highly proprietary world of RDBMS Stored procedures.
Finally data in CSV format is the only way to submit data beside the INSERT command. Given that data comes in variety of formats including column delimted, Excel, and variations on CSV – this can mean a fair amount of time preparing data to be read by SQLFiddle.
Summary
Given the newness of SQL Fiddle and the many complications of SQL [just take a glance at the ANSI 2003 standard], this tool has come out swinging and can be useful in a variety of circumstances:
1) way to learn or come up to speed in SQL for a new RDBMS;
2)means of checking out the latest DDL and Schema design options in 5 major databases;
3)good tool for refining SQL queries and communicating your approach online to others in the development team;
4) a way to communicate to potential client that you have the chops to handle the database side of their app you are bidding on.
SQLFiddle, like JSFiddle, is an example how Online+Cloud resources can be used effectively in development. Congratulations to Jake Feasal the main developer and StrataScale the Online/Cloud provider for delivering such an impressive tool for database and SQL development. Having SQLite on board is a bonus.
Hey, nice writeup! A couple of minor points worth mentioning though – the limitation on DML being restricted to the schema panel is actually only true for MySQL – with the other databases, you can do pretty much anything in the query panel. This limitation is due to some of the limited capacities available within MySQL to effectively sandbox the code, so I had to resort to this restriction.
Also, you can test stored procedures, but it can be a bit tricky. You usually have to define a different query terminator, since by default a line-ending semicolon ends a query batch (and you often need line-ending semicolons within the body of a stored proc). You can change the query terminator by clicking that discrete little [ ; ] box under each of the panels, and then choosing a different one.
I will take a look at what I can do about more fine-grained timing details – if you could send me a link (admin at sqlfiddle dot com) that provides more detail about what you’d like to see, that would be helpful since honestly I’ve never seen / used that type of thing.
Thanks!
Finally – you mentioned that CSV is the only supported way to import the data into your schema – that’s actually true – the “Text to DDL” feature supports a variety of formats, so long as there is a consistent delimiter between each column.
Bah – I meant to write “actually NOT true”:
Finally – you mentioned that CSV is the only supported way to import the data into your schema – that’s actually not true – the “Text to DDL” feature supports a variety of formats, so long as there is a consistent delimiter between each column.
Thanks!
Jake – I will take a look and amend the write-up for Stored procedures and use of INSERTS and UPDATES. I stand by my original assessment, very excellent first edition ;-}