🐥 Be careful with building delete statements using Laravel Query Builder

Laravel is very powerful in the way it abstracts interaction with your database. It makes a lot of things really easy, but once in a while, you hit a suprise. A while, we had this happening and it was a rather nasty one.

What we wanted to do was deleting records from a table, but skipping the first row. When writing the query, we ended up with this:

DB::table('cache')->skip(1)->delete();

It looks legitimate and it ran without any error. The result was a surprise though, all records in the table were gone. I was surprised that Laravel even allowed us to do something like this without any error message.

If you check the MySQL reference manual on the delete statement, you'll see that it is defined like this:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name [[AS] tbl_alias]
    [PARTITION (partition_name [, partition_name] ...)]
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

It turns out that Laravel simply ignored the skip(1) call, known that it wouldn't do anything and it would never end up in the SQL query. The SQL query which got execute was simply:

DELETE FROM cache;

Is there a way to prevent this happening in the future? Yes, write a test for the code before you deploy to production. That's unfortunately the only way to verify that it will delete only what you intend to delete.