SQL support and Database tools

As you work with databases in your project, there are quite a lot of routine tasks that have to be performed on a regular basis. However, now there is a great way to do it right from your IDE. You can view database structure and easily modify it by refactoring affected code; manage data stored in the table simply editing or adding values; write SQL queries with smart code completion and syntax highlighting; and run SQL queries just from the code or the built-in SQL editor.

If you have more complicated tasks to perform, there are UML diagrams showing relations between tables and tools to compare databases and migrate from one to another.

We will give you a short overview of Database tools and SQL support in our products.

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

All the features are supported out of the box, without any external plugins. You can work with databases such as MySQL, PostgreSQL, Oracle, DB2 and others.

In order to take advantage of all the features, just add a data source to your project.

To open the Database Tool Window on the right, click the Database button or go to the main menu and select View | Tool Windows | Database, then add DB Data Source.

Enter the data source name, choose JDBC Driver (for example MySQL), then click to download the connector and specify the folder to save it to.

JDBC driver is a software component enabling application to interact with a database. Most JDBC drivers cannot be bundled with the IDE because of license terms. However, even if no restrictions existed, it would be too much to force our users to download an IDE with all the popular (but not necessarily needed) libraries bundled – because software package size is important too. So, to download the required driver you just need to click a few buttons.

Now that we have a JDBC driver installed, we need to configure it. If you are not fluent with JDBC connectors, please consult MySQL documentation for details about the correct MySQL JDBC URL syntax. Enter the Database URL, Username and Password. You can easily test connection in order to check if everything went well.

Next, specify the Schemas & Tables we need to scan – they’ll be used for autocompletion. Just select the ones you need in your project.

Don’t forget to specify the Default SQL Dialect on the Console tab. In our case it’s MySQL. The Default SQL Dialect should be selected in order to take advantage of smart SQL code completion in the SQL editor or inside your code.

Just press OK. We’ve got our MySQL Database structure on the right: tables, columns (fields), indexes, etc. You can easily manage your database from the database tool window.

For example, let’s rename email field to email_new.

And with only one button, we apply a refactoring to a whole project (just check what occurrences should be affected).

By the way, invoking ‘edit table dialog’ with F4 hotkey or double-click on the table will show you all the table data so that you can easily add/edit/remove values, order columns, export data to file, etc.

Open Database Console using hotkey Ctrl+Shift+F10/Cmd+Shift+F10 and enter a query in SQL editor supported by syntax highlighting, smart code completion, on-the-fly code analysis, multiple intentions and navigation.

Run it and get results immediately!

You can also use UML Diagrams in order to view tables and relations between them with Ctrl+Shift+Alt+U/Meta+Alt+Shift+U hotkey.

One more powerful feature is SQL code injection. You can use it easily while writing your code in order to get all the features of SQL editor inside PHP, JavaScript, HTML or other languages. In the editor, place the cursor within the string literal that represents the language injection of interest. Click on the yellow bulb or press Alt+Enter, and select the language to use (MySQL in our case).

Also, a piece of your code will be considered as injected code with automatic patterns from the IntelliLang (for SQL predefined patterns are select/delete/insert/update/create and <<< SQL Heredoc). These patterns can be adjusted in Settings | Language Injections. Please note that it will not work on concatenated literals (such as “…WHERE VAR=”.$var.”…”).

That’s it! Now you have syntax highlighting, smart code completion, and on-the-fly code analysis for SQL inside your code.

Feel free to run SQL query in the console just from your code! Press Alt+Enter and select proper option. Results will be shown in the database console. If you lack some parameters (WHERE condition which depend on variable, for example), the IDE will ask you to provide it in a special window.

You can also export and import DDL data sources just from the database tool window.

If we have a few data sources, we can select them and compare. Found some difference? Migrate between data sources with a single button.

And one more important thing for today. Do you need to find where you use a certain column or table? Just select it in the database tool window and invoke Find Usages with Alt+F7:

Now you see why you really don’t need any external database management software. All you need is already inside your IDE! Just add a data source and begin working with your database. No need to switch: you can handle your data quickly, conveniently and reliably using just one window.

Develop with pleasure!
-JetBrains Web IDE Team

This blog is permanently closed.

For up-to-date information please follow to corresponding WebStorm blog or PhpStorm blog.

 
This entry was posted in Cool Feature, PhpStorm, Tutorial and tagged , , , . Bookmark the permalink.

17 Responses to SQL support and Database tools

  1. Marco says:

    hmm, I’m wondering why this is not in Webstorm?

  2. m00nk says:

    The “Find usages…” feature is not working for me in PhpStorm 5.0.3 :(

    And I can not use russian language when I’m editing the data in the table editor.

  3. UFTimmy says:

    I had no idea this feature existed, and I love it.

    But, I too cannot get the Find usages feature to work. I had version 4.x and it did not work there, I just upgraded to 5.04 and it is still not working.

    I made sure the table I was looking for was in a string that phpStorm identifies as SQL, and ran it. But it does nothing. It gives no indication that it is working, or searching, or found anything. It just sits there.

    Additionally, when I do a refactor (rename) the preview does not find any usages.

    This would be a killer feature, if I could get it to work.

  4. Pavel says:

    I will be great to add possibility to configure tunnel options directly from PhpStorm.

  5. Pingback: PHP Digest: Faker PHP Repository, Practical Code Refactoring, the Law of Demeter and Much More | Zfort Group Blog

  6. Kate says:

    Thanks for sharing the great news! It was included into a digest of the hottest and the most interesting PHP news: http://www.zfort.com/blog/php-digest-november-5-2012-zfort-group/

  7. Gerry says:

    I am trying to connect to database url like jdbc:mysql://domain.com:3306/db_test with the appropriate user and password, but I get an “not allowed to connect to this MySQL server” error.
    The database is on a cloud hosted virtual private server (VPS).
    Any suggestions what I could do?

    • Mikhail Vink says:

      I think you can’t connect because remote access to MySQL is not enabled. If you have VPS from the hoster usually remote access is disabled by default due to security reasons. If you use control panel for your hosting (ex. CPANEL) there is a configuration page for that named similar to “Remote MySQL access”.

      Otherwise you can just configure MySQL server to accept connections from your host, check GRANT syntax at http://dev.mysql.com/doc/refman/5.1/en/grant.html or Google “mysql configure remote access”

  8. Impressive job, guys! DB integration works perfectly.

  9. Raymond says:

    Very nice! I really like these DB features.

    I think another great feature to have the is the ability to generate DDL diff scripts after comparing two data sources.

  10. yifanes says:

    hello,I am a Chinese php developer.
    after choose some ide,I use phpstorm to develop,I think it is very powerful.
    but when I use database(mysql) in phpstorm,I meet a difficulty which phpstorm can not show the data normal. it show like this:
    http://www.liaolz.com/wp-content/uploads/2013/03/fasdf.png
    sorry to use the link of my blog.
    mysql charset :utf-8.
    jdbc: jdbc:mysql://localhost:3306?useUnicode=true&characterEncoding=utf-8

    Hope your help.
    btw: I am sorry to my pool English.

    • Mikhail Vink says:

      Hello,

      Please check http://youtrack.jetbrains.com/issue/IDEA-47783#

      There are 2 options to cope with it:
      1. Change Project Encoding to the corresponding encoding (Big5, UTF16_e or something similar).
      2. Add proper characterEncoding to his connection to MySQL.
      Now you use UTF8 and it’s absolutely not able to handle Chinese encoding.

      The second option is more appropriate.

  11. Jeroen Versteeg (@drjayvee) says:

    I love it, but there’s one big problem: concatenated queries.

    The following examples work fine, but raise errors:

    $db->query(sprintf(
    "INSERT INTO leetspeek (id, name) VALUES (%d, %s)",
    1337, 'elite'
    ));

    $where = $userid
    ? "WHERE id = " . (int)$userid
    : "";
    $dbh->query("SELECT * FROM user $where");

Comments are closed.