Database Tools and SQL Editor Changes in PhpStorm 7

Always switching between the IDE and a separate database client? There’s no need to juggle apps! Using the built-in Database Tools with PhpStorm, we can setup a database connection and start creating and editing tables, views, work with data, generate a database diagram and so on.

Not only that: when a data source is added, our IDE will also help with writing SQL in our application. Code completion, inspections and quick-fixes are available.

clip_image001

PhpStorm 7 adds some new database features to our toolset which will make developing software backed by a database more fun. Let’s explore!

This functionality is available in IntelliJ IDEA, PyCharm, RubyMine and PhpStorm.

clip_image002

Database support can be found on the right-hand side of the IDE or by pressing Ctrl+Shift+A (Cmd+Shift+A on Mac) and searching for “Database”.

Building queries with the Table Editor

In the Table Editor, we’ve added support for building queries. From the toolbar we can open a window where we can select the columns to be displayed (tick/clear the checkboxes), specify the ORDER BY clause by using the dropdowns, and provide filters for a given column.

clip_image003

Want to see what’s going on behind the scenes? Or simply have the query available to copy/paste it into your code? When running a query that was built using the IDE, we can have a look at the generated SQL.

clip_image004

Better Code Completion for join conditions

When working in the database console, we can now use smart code completion (Ctrl+Shift+Space) and let the IDE automatically complete our join condition based on analyzing foreign key constraints. When multiple join conditions are possible, PhpStorm will display a list of options from which we can choose.

Here’s a short video showing the new smart code completion for join conditions in SQL statements in action.

Database Console Variable Substitution

When building queries in the Database Console, we can now make use of variables and substitute them when running the query. By default, variables are recognized as strings similar to ?, :variable, @variable, #variable# or $variable$. In the example below, I’m querying people filtered on a given country. By using variables, PhpStorm will prompt for the values to use in the query during execution.

clip_image005

We can configure recognition of variable patterns through IDE Settings | Database | Console by enabling the Explicit parameter patterns checkbox. If we add a pattern like \$\w+, PhpStorm will recognize PHP-style variables like it did in the example above.

clip_image006

Exporting Data

The Table Editor as well as the Database Console support exporting data to the clipboard or a file. From the toolbar, we can select the desired output format, such as Comma-Separated Values (CSV), XML or JSON.

clip_image007

We can also export results as SQL statements for inserting or updating data. This makes it very easy to generate a script which we can use to export data from one database to another. Need to copy those 500 records from staging to development to work on a bugfix? Export them as SQL Insert statements and simply run them against development.

clip_image008

Value View / Single Record Transpose View

When browsing a table, often we want to see an example of where the selected value is referenced. In my database here, I have a table country and want to see how it is linked to from other tables. Rather than generating a database diagram (Ctrl+Alt+Shift+U or Shift+Alt+Cmd+U on Mac OS X), we can instead press Ctrl+Q (Ctrl+J on Mac OS X) and get an example of how a given row is used throughout the database.

clip_image009

When we press Ctrl+Q (Ctrl+J on Mac OS X) again, we get a transposed view of the selected row. When working with a table that has many fields and requires vertical scrolling, the Single Record Transpose View makes it easier to view the data by flipping the column and row axis.

clip_image010

User Interface enhancements

When working with multiple data sources or data sources that have multiple schemas, we can now assign different colors to each of them. For example, when working with a development and production database at the same time, it can be useful to give both a different color to have a visual clue about the database we are currently working with. I wouldn’t even dare delete a table from that orange-colored data source!

clip_image011

Colors can be enabled and assigned by using the context menu’s Color Settings on a data source, schema or object. Why not make the tables that you are working on display in green to quickly recognize them?

clip_image012

When tab coloring is enabled, we can clearly distinguish the data source from the tab header by its color. Oh, and PhpStorm now also displays the current database schema in the tab title.

clip_image013

Read-Only Data Sources

No developer ever has deleted data from the production database by accident, right? When creating or editing Data Source properties, we can mark it as read-only. This will prevent us from accidentally editing or removing data or elements from our schema and only provides read access to the data source, even if the user connecting to the database has write privileges.

clip_image014

Give it a try and let us know how it goes. We’re looking forward to hearing your thoughts through the issue tracker, through the comments below or in our forums!

Develop with pleasure!
– JetBrains PhpStorm Team

About Maarten Balliauw

Maarten Balliauw is a Developer Advocate at JetBrains, working on .NET tools. He focuses on .NET, Azure, web technologies and application performance. Maarten is a frequent speaker at various national and international events. In his free time, he brews his own beer. Follow him on Twitter or check his personal blog.
This entry was posted in Cool Feature and tagged , , , , , . Bookmark the permalink.