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.
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.
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.
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.
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.
http://www.youtube.com/watch?v=olLNRWGZ4jA&feature=player_embedded
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.
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.
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.
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.
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.
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.
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!
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?
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.
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.
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
Subscribe to PhpStorm Blog updates
