Phpstorm and Mysql Scratch Files

Workflow

Recently I have been writing a number of internal scripts that interface with one of our databases to check a few things. A lot of the time we use PhpMyAdmin or the standard Mysql command line client where necessary but there is a better and more reliable way to fire queries to the database.

Enter PHP Storm.

PHP Storm and MySQL

PHPStorm has been extended so much over the years that it can be used for fully fledged raw SQL on it’s own which can be very handy. To start off, look in to adding your database source (we use MySql but any data source supported will work too).

You can also use an SSH tunnel for your remote projects!

Data source Guide

Once you have your data source set up and configured, you are now ready to start firing queries, open a new scratch file and choose mysql or which ever data source type you are currently using. You will now have full auto completion and error highlighting out of the box and a bunch of other useful features.

Selective Statement Execution

One useful feature besides the code completion is the ability to only execute certain parts of large SQL statements with inner statements so to ensure the expected results are returned. See the screen shots for an example.

When selecting the code block, hit CTRL+Enter or the shortcut for completion in your settings and you can then cycle through which parts of the query to run with the arrow keys. As you cycle different parts of the query will have the green border to indicate this.

This feature was tested in PHP Storm 2017.1 (EAP) but should be present in 2016.*