Laravel 5: Database and Database Tables Setup (migrate – practical problems)

Important: Laravel 5.4 Database migration error

Fix:

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:

  1. 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
  2. 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)
  3. 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

 

 


 

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

which contain PHP classes – something like shown below:

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:

  1. 2016_03_27_182158_create_tasks_table
  2. 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:

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):

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:

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:

Above code would create migration files looking like this:

  1. 2016_03_27_202308_create_tasks_table_1_1459110067.php
  2. 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.

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:

In case table is already in, it would generate an error:

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:

 


 

“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: