|
1 2 3 |
php artisan make:migration create_mytablename_table |
I prefer creating migration file without auto generation of table (using flags):
|
1 2 3 4 5 6 |
//available flags --create=mytablename //or --table=mytablename |
This approach allows me to define all table details in migration file
|
1 2 3 |
$table->string('name', 100) |
|
1 2 3 |
php artisan migrate |
Laravel now can add my table to database and fill migrations table with proper batch number
|
1 2 3 |
SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes |
Fix:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
//app\Providers\AppServiceProvider.php use Illuminate\Support\Facades\Schema; public function boot() { /** * fix for: * "SQLSTATE[42000]: Syntax error or access violation: * 1071 Specified key was too long; max key length is 767 bytes" * see: https://laravel-news.com/laravel-5-4-key-too-long-error */ Schema::defaultStringLength(191); } |
Read more here.
In order to start your Laravel based app you have to have your database in order – setup and ready.
Laravel gives you a chance to do two quite useful things:
- it will store all your table updates/upgrades in files – each file with its timestamp
(see: /database/migrations/ folder)
so anything fgoes wrong, you have a chance to track down since when you have to apply fixes - it allows you to create your database/table on-the-fly without bothering with anything like PHPMyAdmin – this sometimes can be very useful (not that it cannot be done without Laravel, of course, by using PHP/MySQL capacity)
- above approach allows you to grab migration files from version control repo (if you work in team) and apply them with easy CLI command (more to come on this subject)
Basic migration commands
|
1 2 3 4 5 6 7 |
//create migration for selected table php artisan make:migration create_users_table --create=users //run migration php artisan migrate |
More about migrations can be found: here
More about Laravel console (CLI) can be found: here
There is a problem with #3 from listing above.
Picture that 2 team members (or more) are trying to update table Tasks.
They create migrate files using CLI
|
1 2 3 |
php artisan make:migration create_tasks_table --create=tasks |
which contain PHP classes – something like shown below:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
<?php use Illuminate\Database\Schema\Blueprint; use Illuminate\Database\Migrations\Migration; class CreateTasksTable extends Migration { //migration content goes inside //this ehere table will be added, edited and dropped } |
As I mentioned already, more will be written about migrating itself later on on this – right after I explain this practical conundrum, you most likely come across more than once.
We synch your local files with version control repo and now, inside of /database/migrations folder we have 2 migration files:
- 2016_03_27_182158_create_tasks_table
- 2016_03_27_192624_create_tasks_table
Each of them contains a PHP class: CreateTasksTable – as shown in code above – with all necessary changes to database.
To apply changes to your database using new migration files received from repo you have to run Laravel CLI code:
|
1 2 3 |
php artisan migrate |
As you know, PHP does not allow to declare more than one class with a given name.
That is why you have Namespaces to limit possibility of such run-ins.
Also, if you are familiar with Zend, you probably remember these ridiculously long class names. They were that long to avoid fatal run-ins. You may think of them as class names with builtin namespace 😉
Anyway, if you try to run migrate CLI command, you will get an error like this (with your local folder path of course):
|
1 2 3 4 5 6 7 |
PHP Fatal error: Cannot declare class CreateTasksTable, because the name is already in use in U:\www\b10\database\migrations\2016_03_27_194730_create_tasks_table.php on line 31 [Symfony\Component\Debug\Exception\FatalErrorException] Cannot declare class CreateTasksTable, because the name is already in use |
How to practically handle above problem.
Now lets see at one way how to handle that.
Not necessarily the best, but one that works for me.
You can assign to each team member a unique number (e.g. some integer), or a unique nickname.
Then you can rework a bit CLI artisan command creating migration to
something like this:
|
1 2 3 |
php artisan make:migration create_tasks_table_tmid_timestamp --create=tasks |
tmid: team member id (or nickname)
timestamp: approximate timestamp (e.g. taken from Epoch site) for the time of creation of this migration (could be also some sort of random string)
Sample of code above would look like this:
|
1 2 3 4 |
php artisan make:migration create_tasks_table_1_1459110067 --create=tasks php artisan make:migration create_tasks_table_2_1459110125 --create=tasks |
Above code would create migration files looking like this:
- 2016_03_27_202308_create_tasks_table_1_1459110067.php
- 2016_03_27_202339_create_tasks_table_2_1459110125.php
When you look inside of them, you can see, that class names are different and practically there is no chance for any team member to duplicate them.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
//class #1: class CreateTasksTable21459110125 extends Migration { //content } //class #2 class CreateTasksTable11459110067 extends Migration { //content } |
If you fix this problem, you can still run into another one closely related. See next “Practical Problem”.
“Base table or view already exists:” error
I some rare cases, migrate script can try to change table by creating table that is already there:
|
1 2 3 4 5 |
Schema::create('tasks', function (Blueprint $table) { //code here }); |
In case table is already in, it would generate an error:
|
1 2 3 4 5 6 7 8 9 10 11 |
[Illuminate\Database\QueryException] SQLSTATE[42S01]: Base table or view already exists: 1050 Table 'tasks' alre ady exists (SQL: create table `tasks` (`id` int unsigned not null auto_incr ement primary key, `created_at` timestamp null, `updated_at` timestamp null ) default character set utf8 collate utf8_unicode_ci) [PDOException] SQLSTATE[42S01]: Base table or view already exists: 1050 Table 'tasks' alre ady exists |
Solution to this problem: test for table existence – here is how.
Your team should try this conditional, when getting any updates to DB ready and it should solve this problem for you:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
if (Schema::hasTable('tasks')) //check if table exists { Schema::table('tasks', function ($table) { $table->string('name1'); }); } else { Schema::create('tasks', function (Blueprint $table) { $table->increments('id'); $table->string('name1'); $table->timestamps(); }); } |
“Column already exists” error
This error appears, when you are trying to migrate column, which is already there.
Solution is same as for a problem described above this one.
Please use this code for each column you adjusting/adding:
|
1 2 3 4 5 |
if (Schema::hasColumn('users', 'email')) { //code goes here } |