Whilst I do most of my SQL editing using using an Open Source SQL editing tool, there are some online tools I use for SQL related tasks. I always keep privacy in mind so I never use these tools with sensitive data. Reasons for using these tools include:
- When I am not working on my PC.
- Web application provides a niche function better than the Open Source SQL editor tool I am using.
- The tool improves my productivity by making it quicker to perform a usually tedious task.
- I always have a browser window open and sometimes it is quicker to use the online tool on a second screen and leave my default SQL editor running a long running script.
Online SQL Tools
Suffice to say many web based utilities that provide a multitude of features for SQL editing. I am only including the ones I regularly use and rate highly:
- Poor SQL T-SQL Formatting
- SQL Fiddle
- CSV To SQL Converter
- Snowflake Joins
- Honourable mention PhpMyAdmin
Poor SQL T-SQL Formatting
When I write SQL I follow these formatting guidelines which are important in producing not only accurate results but understandable and debug friendly SQL. PoorSQL is particularly invaluable when I have to format SQL writtent by other people as it enables me to format the SQL to my prefered style. PoorSQL has more SQL formatting options than any of the editors I use.
I use SQL Fiddle when I want to test some SQL syntax without having to install the targeted database server. Say I am working in MySQL and want to check if the SQL statement I am working on will run in SQL Server. I use SQL Fiddle. At the time of writing SQL Fiddle supports the following databases:
- MySQL 5.1, 5.6
- Oracle 11g R2
- PostgreSQL 9.2, 9.3
- SQLite (WebSQL)
- SQLite (SQL.js)
- MS SQL Server 2014 I have to repeat mysql, SQL Fiddle works best for checking syntax. You create a schema with some sample data and run your SQL against this schema. I typically create one or two tables with less than 100 rows of data. You will have serious performance issues if you try create a big database with lots of data.
CSV To SQL Converter
Importing data from a spreadsheet is something I do infrequently enough to forget how I did it the last time every time I do it again. I am aware of utilities such as ETL tools to smooth the process but I often find that I get the following errors:
- DDL statement usually needs to be changed because field lengths were not accurate.
- Errors due to some character encoding issue.
- Error due to date formats/number not being consistent.
When using an ETL tool or data import utility built into the SQL editor and I get an import error. I have to now work out if the error is from the software, OS or with the data.
To use CSV To SQL Converter you copy the data from your spreadsheet or CSV file. You then set a few options, most are optional and have sensible defaults:
- The delimiter in your data
- Table name for the INSERT statement to be created
- Change the suggested data types for the table columns
- Change the suggested columns names
- Decide on the type of INSERT statement to generate
When working with the SQL generated by the CSV To SQL Converter I know that the problem is the SQL and the error messages I get are more specific. A typical error is goes like, “cannot insert row 104” and this is easier to debug.
A picture is worth a thousand words. Visualising SQL statements can help you understand complex SQL statements particularly when discussing the query with a colleague. Snowflake Joins creates a diagram from your SQL SELECT statement.
Note: Snowflake Joins is an Open Source application that you can install on your computer if you don’t want to use the online version. The free online version is limited to 800 character SQL queries.
Online SQL utilities will never replace the traditional SQL client. Privacy and performance being the the foremost reasons. Having said that, they are many online SQL services that provide niche features that speed up everyday SQL tasks.
Tags learn sql/online sql/sql/sql tools/
Related PostsFormatting SQL to increase productivity
KDE Kate Text Editor, a Distraction Free SQL Client