Run raw SQL in migration

Question

I'm trying to execute a raw SQL thru a Laravel (PHP) migration execution. 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).

I experienced the same multiple times. Seems like either MySQL or DB::statement doesn't like multiline alter statements. — xyz, Nov 09 '20 at 23:41

score 68 · Answer 1 · edited May 16 '18 at 17:57

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

potashin · Accepted Answer · 2018-05-12T21:35:53.160

49

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

edited May 12 '18 at 21:35

answered Feb 28 '15 at 21:47

potashin

44,205
11
83
107

7

Is there a reason for the query to be inside a Schema closure if not using the Blueprint object? – Lalo Sánchez Jan 13 '17 at 18:36
You don't need to Schema closure, it's just separate depending queries. Also you can run query in DB::statement('QUERY'). – KorbenDallas Dec 03 '17 at 14:34
@KorbenDallas: In the case of this question it was mostly about why his approach doesn't work in MySQL, not about how to make it better in Laravel (which I am not familiar working with at all) – potashin Dec 03 '17 at 14:47

score 0 · Answer 3 · answered Feb 19 '22 at 07:43

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/

I realize this is example code. Still, to prevent damage: As a rule of thumb *never* have a drop in the down() unless there is also a CREATE TABLE or Schema::create in the up() — Johan van den Broek, Feb 08 '23 at 09:15

score -1 · Answer 4 · answered Aug 03 '23 at 16:42

If your have your schema as public in your connection config and still getting this error, check that the owner in your database for the table migrations is the user which is configured in your connection :)

If not, execute:

ALTER TABLE public."migrations" OWNER TO [username]; 
ALTER SEQUENCE public."migrations_id_seq" OWNER TO [username];

in that order, that's important.

Suggestion: use just one owner for all your tables and sequences

Run raw SQL in migration

4 Answers4

Linked