Run raw SQL in migration
PhpMysqlSqlLaravelLaravel 4Php Problem Overview
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).
Php Solutions
Solution 1 - Php
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
Solution 2 - Php
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 3 - Php
You can use as below
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
class CreateTestTable extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
DB::statement('ALTER TABLE `users` MODIFY `age` DATETIME');
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::dropIfExists('users'); \or whatever you want on rollback
}
}
Also i have create a package to manage raw sql query migration (Queries created using phpMyAdmin or tool but manage the migration across the developer)
Check here : https://readerstacks.com/how-to-run-raw-sql-query-in-migration-laravel/