MySQL Reserved Words Checker for Laravel Migrations on PhpStorm

In this article we are going to setup a kind of "typo checker" on PhpStorm to look for MySQL reserved words being used as column names in your Laravel migration files. Although it's fine to name columns whatever you like, there is one small issue when writing raw SQL queries against the database when you do use reserved words as column names.

The problem when querying column names with reserved words

In our scenario we have the settings database described as follows:

MariaDB [dummy_db]> describe settings;
+---------------+------------------+------+-----+---------+----------------+
| Field         | Type             | Null | Key | Default | Extra          |
+---------------+------------------+------+-----+---------+----------------+
| id            | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| key           | varchar(191)     | NO   | UNI | NULL    |                |
| value         | longtext         | YES  |     | NULL    |                |
| description   | varchar(191)     | YES  |     | NULL    |                |
+---------------+------------------+------+-----+---------+----------------+
6 rows in set (0.04 sec)

Notice that we are using a column named "key" and that's fine, our Database Management System (DBMS) did not complain about it. However, we use that same word for commands like ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;.

Now let's suppose we want to retrieve the data in our settings table filtering by some specific key. To achieve that we can run the query bellow, which blows up, because it doesn't know what "key" is.

MariaDB [dummy_db]> select * from settings where key like '%primary_color%';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'key like '%primary_color%'' at line 1

Of course, to make it work all we have to do is escape key with backticks like the snippet bellow, but that's a little bit annoying. When using resources like Object Relational Mapper (ORM) it will take care of that for you automatically, so you don't have to worry about it, but every time you find yourself in situations when you have to query directly in the database, you must remember to escape the necessary fields.

MariaDB [dummy_db]> select * from settings where `key` like '%primary_color%';
+----+---------------+---------+-------------+
| id | key           | value   | description |
+----+---------------+---------+-------------+
| 46 | primary_color | #122560 | NULL        |
+----+---------------+---------+-------------+
1 row in set (0.01 sec)

Since this is not something prohibitive, we are going to setup a small checker on PhpStorm to remind us when using reserved words. Let's dive in!

Setting up the PhpStorm checker

1. Open PhpStorm and go to Settings → Editor → Inspections → General → Structural search inspection and click in the plus icon to add a new one and select Add search template…;

   

2. In the Search template text area type $table->$type$('$column$') and select the Search target as "column". Also, make sure to uncheck the Match_case option;

   

3. Click or select the $column$ portion inside the text area and then click on Add filter and choose Text;

4. Paste the list of reserved words bellow, make sure the Words option is checked and then hit OK;

   

   (ACCESSIBLE|ADD|ALL|ALTER|ANALYZE|AND|AS|ASC|ASENSITIVE|BEFORE|BETWEEN|BIGINT|BINARY|BLOB|BOTH|BY|CALL|CASCADE|CASE|CHANGE|CHAR|CHARACTER|CHECK|COLLATE|COLUMN|CONDITION|CONSTRAINT|CONTINUE|CONVERT|CREATE|CROSS|CURRENT_DATE|CURRENT_TIME|CURRENT_TIMESTAMP|CURRENT_USER|CURSOR|DATABASE|DATABASES|DAY_HOUR|DAY_MICROSECOND|DAY_MINUTE|DAY_SECOND|DEC|DECIMAL|DECLARE|DEFAULT|DELAYED|DELETE|DESC|DESCRIBE|DETERMINISTIC|DISTINCT|DISTINCTROW|DIV|DOUBLE|DROP|DUAL|EACH|ELSE|ELSEIF|ENCLOSED|ESCAPED|EXISTS|EXIT|EXPLAIN|FALSE|FETCH|FLOAT|FLOAT4|FLOAT8|FOR|FORCE|FOREIGN|FROM|FULLTEXT|GENERATED|GET|GRANT|GROUP|HAVING|HIGH_PRIORITY|HOUR_MICROSECOND|HOUR_MINUTE|HOUR_SECOND|IF|IGNORE|IN|INDEX|INFILE|INNER|INOUT|INSENSITIVE|INSERT|INT|INT1|INT2|INT3|INT4|INT8|INTEGER|INTERVAL|INTO|IO_AFTER_GTIDS|IO_BEFORE_GTIDS|IS|ITERATE|JOIN|KEY|KEYS|KILL|LEADING|LEAVE|LEFT|LIKE|LIMIT|LINEAR|LINES|LOAD|LOCALTIME|LOCALTIMESTAMP|LOCK|LONG|LONGBLOB|LONGTEXT|LOOP|LOW_PRIORITY|MASTER_BIND|MASTER_SSL_VERIFY_SERVER_CERT|MATCH|MAXVALUE|MEDIUMBLOB|MEDIUMINT|MEDIUMTEXT|MIDDLEINT|MINUTE_MICROSECOND|MINUTE_SECOND|MOD|MODIFIES|NATURAL|NOT|NO_WRITE_TO_BINLOG|NULL|NUMERIC|ON|OPTIMIZE|OPTIMIZER_COSTS|OPTION|OPTIONALLY|OR|ORDER|OUT|OUTER|OUTFILE|PARTITION|PRECISION|PRIMARY|PROCEDURE|PURGE|RANGE|READ|READS|READ_WRITE|REAL|REFERENCES|REGEXP|RELEASE|RENAME|REPEAT|REPLACE|REQUIRE|RESIGNAL|RESTRICT|RETURN|REVOKE|RIGHT|RLIKE|SCHEMA|SCHEMAS|SECOND_MICROSECOND|SELECT|SENSITIVE|SEPARATOR|SET|SHOW|SIGNAL|SMALLINT|SPATIAL|SPECIFIC|SQL|SQLEXCEPTION|SQLSTATE|SQLWARNING|SQL_BIG_RESULT|SQL_CALC_FOUND_ROWS|SQL_SMALL_RESULT|SSL|STARTING|STORED|STRAIGHT_JOIN|TABLE|TERMINATED|THEN|TINYBLOB|TINYINT|TINYTEXT|TO|TRAILING|TRIGGER|TRUE|UNDO|UNION|UNIQUE|UNLOCK|UNSIGNED|UPDATE|USAGE|USE|USING|UTC_DATE|UTC_TIME|UTC_TIMESTAMP|VALUES|VARBINARY|VARCHAR|VARCHARACTER|VARYING|VIRTUAL|WHEN|WHERE|WHILE|WITH|WRITE|XOR|YEAR_MONTH|ZEROFILL|GENERATED|OPTIMIZER_COSTS|STORED|VIRTUAL)

5. You will be prompted to give a name for the template. You can name it however you like, but personally, I like to give a meaningful name so it's easily searchable when needed. So I called "MySQL reserved words checker".

6. And that's it! One last thing I like to tweak is the severity level of the warning. I like to put it as a TYPO level, so I can see it highlighted in the code as the image bellow.

   

Hope you like it. It's something strictly cosmetic but it is very handy to me.