Run raw SQL in migration

Question

I was trying with whatever syntax and can't think how can I write this correctly:

Schema::table('users', function(Blueprint $table){
    $sql = <<<SQL
        ALTER TABLE 'users' MODIFY 'age' DATETIME
    SQL;
    DB::connection()->getPdo()->exec($sql);
});

also tried with

DB::statement('ALTER TABLE \'users\' MODIFY COLUMN DATETIME);

and double quotation marks and so on. I always get the following when I run the migration:

Syntax error or access violation: 1064 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 ''users' MODIFY 'age' DATETIME' at line 1

Yes, I have checked, MariaDB uses MySQL's syntax (at least for this case).

Solution 1

Use back-ticks instead of single quotes to escape identifiers in MySQL:

alter table `users` modify `age` datetime

In this particular case you can omit escaping at all:

alter table users modify age datetime

Solution 2

The issue (as @postashin said) was the backticks.

As of Laravel 5 (not sure about Laravel 4), you could have done this:

DB::statement('ALTER TABLE `users` MODIFY `age` DATETIME');

In fact you didn't even need the back ticks as they don't need escaping. So you could have just written:

DB::statement('ALTER TABLE users MODIFY age DATETIME');

You do not need this in the closure either if you are just executing a database statement.

However a better approach to what you are doing is as follows:

Schema::table('users', function(Blueprint $table) {
    $table->dateTime('age')->change();
});

Note the last solution can sometimes raise an error due to a bug in Doctrine, which usually occurs if you have an enum in the table (not just the column you are changing).

For more information, see Laravel Database Migration - Modifying Column