Add a new column to a table with data in Laravel

Question

Add a new column to a table with data in Laravel

Navigation

#1 by (1 votes)

0

In Laravel we have a users migration that comes by default with the following structure

public function up()
    {
        Schema::create('users', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name');
            $table->string('email')->unique();
            $table->timestamp('email_verified_at')->nullable();
            $table->string('password');
            $table->rememberToken();
            $table->timestamps();
        });
    }

Which we create from console with the following command

php artisan migrate

This will generate that all the migrations are executed and therefore all the tables are created, one for each migration file

However, the detail is as follows; I insert a row of data from the Maria DB console

MariaDB [blog]> insert into users(name, email, email_verified_at, password, remember_token, created_at, updated_at)
    -> values
    -> ("alfa", "[email protected]", NULL, "dsfdfsfsf", "dfsdf765675", NOW(), NOW());
Query OK, 1 row affected (0.077 sec)

MariaDB [blog]> SELECT * FROM users;
+----+------+---------------+-------------------+-----------+----------------+---------------------+---------------------+
| id | name | email         | email_verified_at | password  | remember_token | created_at          | updated_at          |
+----+------+---------------+-------------------+-----------+----------------+---------------------+---------------------+
|  1 | alfa | [email protected] |              NULL | dsfdfsfsf | dfsdf765675    | 2018-10-06 14:11:10 | 2018-10-06 14:11:10 |
+----+------+---------------+-------------------+-----------+----------------+---------------------+---------------------+

The problem now is

I need to add a column to that table called features of type JSON but I need to keep the existing data; therefore I can not execute the migration again by altering the original structure

How do I achieve it?

sql laravel laravel-5

asked by element 06.10.2018 в 21:37

source

1 answer

How to send data using 2 buttons without refreshing HTML page java.lang.ClassCastException: java.lang.String can not be cast to entities.User

score 1 · Accepted Answer

In order to add a new column to the table users we require the new migration, which we will call in this way

Using the syntax of add_ we can differentiate the migrations that are to create those that are just to modify the structure of a table, in this case adding a new column

php artisan make:migration add_features_to_users

The previous migration will generate a new table that as it reads in its name will serve to add the column features to the table users

public function up()
    {
        Schema::table('users', function(Blueprint $table){
            $table->json('features')
                  ->after('email')
                  ->nullable;
        });
    }

As you realize in the previous code we have the following characteristics

Instead of using Schema::create we use Schema::table with that we clarify to the migration that we do not want to make a new table, if not modify an existing one that in this case is called 'users

to the property $table we indicate that we will add a new column of type json called features

Optionally, we can indicate after which column the newly created column should go, in this case we indicate that it goes after the email column

Once the above is done, we execute the following command

C:\Users\P\app1>php artisan migrate
Migrating: 2018_10_06_191852_add_features_to_users
Migrated:  2018_10_06_191852_add_features_to_users

Where, as you can see, the only migration the system finds as pending is that of add_features_to_users and it is the one that processes

At the end if we go back to the Maria DB console and execute a describe of the users table we will see the new column just entered

MariaDB [blog]> describe users;
+-------------------+------------------+------+-----+---------+----------------+
| Field             | Type             | Null | Key | Default | Extra          |
+-------------------+------------------+------+-----+---------+----------------+
| id                | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name              | varchar(255)     | NO   |     | NULL    |                |
| email             | varchar(255)     | NO   | UNI | NULL    |                |
| features          | longtext         | NO   |     | NULL    |                |
| email_verified_at | timestamp        | YES  |     | NULL    |                |
| password          | varchar(255)     | NO   |     | NULL    |                |
| remember_token    | varchar(100)     | YES  |     | NULL    |                |
| created_at        | timestamp        | YES  |     | NULL    |                |
| updated_at        | timestamp        | YES  |     | NULL    |                |
+-------------------+------------------+------+-----+---------+----------------+
9 rows in set (0.008 sec)

If we now make a select to the users table, we will see that the newly entered column already appears without an assigned value but the data that was already there remains

MariaDB [blog]> SELECT * FROM users;
+----+------+---------------+----------+-------------------+-----------+----------------+---------------------+---------------------+
| id | name | email         | features | email_verified_at | password  | remember_token | created_at          | updated_at          |
+----+------+---------------+----------+-------------------+-----------+----------------+---------------------+---------------------+
|  1 | alfa | [email protected] |          |              NULL | dsfdfsfsf | dfsdf765675    | 2018-10-06 14:11:10 | 2018-10-06 14:11:10 |
+----+------+---------------+----------+-------------------+-----------+----------------+---------------------+---------------------+