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

This entry was posted in Cool Feature and tagged , , , , , . Bookmark the permalink.
  • Antoine Hedgecock

    Any chance we can get DQL support ?

  • Pencroff

    What about WebStorm? Could I expect this functionality in WebStorm?

    • Ekaterina Prigara

      There are no Database tools or SQL support in WebStorm. We suggest using PhpStorm if you need these features.

  • http://www.paragon-es.de Jürgen

    This is looking really good. I always liked to work within one single application instead of switch around all the time. Those improvements are much apreciated.

    Just wondering if the variable recognition “\$\w+” shouldn’t be in the list of patterns by default? At least inside PhpStorm?

    And a question: how can I access the list of variables should I ever want to change the values?

    • Maarten Balliauw

      It depends a bit. Many frameworks use the ? or :var syntax for doing variable substitution. The pattern I suggested only makes sense if you are embedding PHP variables in a statement directly.

      • Áxel

        @JetBrainers, remember that this simply won’t work while WI-14299 is not fixed, which I suspect is caused by WI-13319.

      • Áxel

        For @PhpStormUsers, you can enjoy the feature with this workaround: add next pattern: (?<![a-z])_\w+, which will find parameters like _anyvar, which is the result of submitting $anyvar.

    • Áxel Costas Pena

      @Jürgen, you can see/modify variables values by clicking the orange button with a “P” letter on the Database Panel

  • Valery

    good ;)

  • SAMI

    Hello , iam new with phpstorm ,

    i need to ask how to have autocomplete for MYSQL functions , tables and database with phpstorm .. i tried “<<<<SQL" Language injection . but how achieve this goal without it?

    i mean when i type "SELECT" i need autocomplete for it as mysql statement ?

    • Mikhail Vink

      Hi. You can invoke quick fix on some “String” with Alt+Enter inside quotes and select Inject Language … MySQL.

  • Steffan

    Hi, is there any way to duplicate a database structure and data in order to create a backup or dev version on the DB server?

    • Maarten Balliauw

      There is no official way to do this currently, but the following may work.

      * Exporting the schema: use the context menu on the schema and select “Copy DDL”. This will exort tables, idnexes, … to the clipboard. Paste them in a document somewhere.
      * Exporting tables: for every table, use the context menu Save to File | SQL Insert Statements and all data will be exported as inserts.

      That said, I think this approach will be much easier for now: http://stackoverflow.com/questions/1887964/duplicate-entire-mysql-database

  • Pingback: PhpStormからVagrant上のMySQLに接続する | mawatari.jp

  • Mauricio Tellez

    Hi, what about SQL code inside PHP heredoc? For complex queries I put the SQL code inside heredoc instead of quoted strings, but I can’t inject inside heredoc nor code completion. Any help?

    • Maxim Kolmakov

      You may press ALT-ENTER and choose Inject Language/Reference and then select necessary SQL dialect.

  • Pingback: Blog | Bill Dong's Web World | Just a personal website

  • Ariom

    Hi!
    I can’t find a data source dialog in the tool windows of the IntelliJ IDEA of community edition.
    what must i do? please, help.

    • Mikhail Vink

      Database development tools are not included in the IntelliJ IDEA community edition, please try the professional edition instead.