Database Language Injection Configuration

Many applications written in PHP are connecting to a database. After connecting to the database from within the IDE, we can use language injections in PhpStorm to write our database-oriented code. The editor provides us full language support for SQL queries embedded in a string literal in PHP. We get syntax highlighting, code completion, navigation and inspections. And not only for our SQL statement, but also for any PHP variables we use in there.

SQL completion inside strings with PhpStorm

PhpStorm does its best to recognize and parse SQL that contains other elements, but there are some cases where we have to help it a little bit. Some developers prefer to use sprintf() to format their queries. The IDE will not recognize the placeholders for column names, as we can see from the error:

PhpStorm sprintf placeholder in SQL query syntax

Frameworks often have their own placeholders in queries. For example the Yii framework uses {{TableName}} to refer to table names, replacing these placeholders with the correct database table name upon using it. Same thing here, though: the IDE is unable to recognize this placeholder by default:

YII framework placeholder

How can we solve this? PhpStorm lets us customize SQL parameters! From the settings under Tools | Databases, we can tell PhpStorm which tokens the SQL analyzer should recognize as being valid. We’ll have to check Use in other language string literals, and then add a regular expression pattern.

Custom SQL parameters can recognize sprintf tokens

The sprintf() tokens will be recognized by adding \%\w+. For Yii’s placeholders, we could add \{\{\w+\}\}. Not only will this remove the errors we saw earlier: adding custom SQL parameters will also make sure PhpStorm asks us for the placeholder value when running the query using Alt+Enter.

Provide missing values when running query

We would love to hear your feedback through the issue tracker, by posting in the comments below, or in our forums!

Develop with pleasure!
– JetBrains PhpStorm Team

This entry was posted in Cool Feature. Bookmark the permalink.