Laravel 5.4 API for Books management – part 2 – new project and connection to database

Share me please

Initializing REST API project in Laravel

In the previous post we designed the whole REST API endpoints with database structure. This post we will use to show how to start PHP Laravel project, prepare database schema and generate all needed resources to phisically build fully working Web API.

Create in your operation system new directory and name it bookingsystem.

Open your best editor like visual studio code and in the command line type:

composer create-project laravel/laravel bookingsystem "5.4.*" --prefer-dist

The command will generate standard structure for Laravel project. This is starting point for REST API.

During project installation you should see that lots of packages are downloaded for final Laravel project setup.

When project is generated you should have the standard folders structure of Laravel project. If you want to read more about it let’s go to my post about generating standard Laravel project in PHP where I explained in details the project structure.

You can run locally your new website using command:

php artisan serve 

When you run the command under the url http://localhost:8000 you will see default Laravel greeting site. If you made something wrong you probably have wrong version of PHP or uncorrectly installed laravel project or wrong project version. Send me feedback in comments if any problems occur.

Setting database connection

When project is correctly setup we need to type correct parameters for database connection. We need to find the file .env in the main directory. Modify part of the file like in the listing below:

DB_CONNECTION=mysql 
DB_HOST=127.0.0.1
DB_PORT=3306 
DB_DATABASE=bookingsystem_db 
DB_USERNAME=bookingsystem_usr 
DB_PASSWORD=bookingsystem_pass

You should also create mysql database with the name bookingsystem_db, user name bookingsystem_user and password bookingsystem_pass like I wrote in the post about mysql database configuration.

Creating first database tables with migrations

As in many other frameworks also in Laravel migrations are simple classes that describes the structure of database within all historical changes in the project. In other words migrations are a kind of increments that we want to perform on the database, e.g. adding/removing a new table or columns to an existing table.

When you build the project you should have in your database/migrations directory two default migrations named:

  • 2014_10_12_100000_create_password_resets_table.php
  • 2014_10_12_000000_create_users_table.php

Every migrations file consists of two main methods up and down.

Method up is responsible for creating new increment in database. Method down is responsible for removing the increment performed by the method up. We can say that it is the machanism with undo/redo in database. Using this functionality we are able technically to go to any historical moment in database structure and verify what was wrong in the design of database.

class CreateUsersTable extends Migration
{
 
    public function up()
    {
        Schema::create('users', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name');
            $table->string('email')->unique();
            $table->string('password');
            $table->rememberToken();
            $table->timestamps();
        });
    }
 
    public function down()
    {
        Schema::dropIfExists('users');
    }
}

We can now try to run migration process. This will help us to verify if we setup all database parameters correctly. Let’s test and type the command:

php artisan migrate

Error with uncorrect password to database

In my case I received the error that there is problem with connection to database. The problem is that I really didn’t change the database name with login and password. I made it especially so that you can see what errors can be expected when communication with the database is not correct:

In Connection.php line 647:
 
  SQLSTATE[HY000] [1045] Access denied for user 'homestead'@'localhost' (using password: YES) (SQL: select * from information_schema.ta
  bles where table_schema = homestead and table_name = migrations)
 
 
In Connector.php line 68:
 
  SQLSTATE[HY000] [1045] Access denied for user 'homestead'@'localhost' (using password: YES)

Syntax error or access violation: 1071 Specified key was too long

When everything is correctly setup you should see that migration tables are correctly created. In my case I received once more error. The error is:

In Connection.php line 647:
 
  SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes (SQL: alter table `us
  ers` add unique `users_email_unique`(`email`))
 
 
In Connection.php line 449:
 
  SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes

What does the error mean ? The problem is with index size and for older MySQL database we can fix it like it is described here by typing one additional line in AppServiceProvider.php in boot function. The file is localized under app\Providers directory. My file after improvements look like below:

 
namespace App\Providers;
 
use Illuminate\Support\ServiceProvider;
use Illuminate\Support\Facades\Schema;
 
class AppServiceProvider extends ServiceProvider
{
    /**
     * Bootstrap any application services.
     *
     * @return void
     */
    public function boot()
    {
        Schema::defaultStringLength(191);
    }
 
    /**
     * Register any application services.
     *
     * @return void
     */
    public function register()
    {
        //
    }
}

Now all migrations are passed correctly and you should see this in console:

PS C:\Web\bookingsystem> php artisan migrate
Migration table created successfully.
Migrating: 2014_10_12_000000_create_users_table
Migrated:  2014_10_12_000000_create_users_table
Migrating: 2014_10_12_100000_create_password_resets_table
Migrated:  2014_10_12_100000_create_password_resets_table

Let’s verify also your database. Three new tables should be created. Look into the table migrations. The table keeps track of all historically created migrations on database. This behaviour will help you in future to verify if any changes are made properly.

Create migration for all the rest tables

When were designing the database for REST API we found out that we need three tables: users, reservations and books. First table with users is created in our first migration. The next two tables we need to create. So let’s start from the command:

php artisan make:migration add_reservations_and_books_tables

The command will execute the creation of new migration file named add_reservations_and_books_tables.php file localized in database/migrations directory. According to our definition of tables just fill the content of newly created file like below:

class AddReservationsAndBooksTables extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('reservations', function (Blueprint $table) {
            $table->increments('id');
            $table->foreign('book_id')->references('id')->on('books');
            $table->foreign('user_id')->references('id')->on('users');
            $table->dateTime('created_date');
        });
 
        Schema::create('books', function (Blueprint $table) {
            $table->increments('id');
            $table->string('title');
            $table->string('isbn');
            $table->string('year');  
            $table->dateTime('return_date');
            $table->dateTime('borrow_date');  
            $table->boolean('is_available');         
            $table->dateTime('created_date');
        });
    }
 
    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('reservations');
        Schema::dropIfExists('books');
    }
}

General error: 1005 Can’t create table – the order of schema tables

Technically our code looks very well, but when we try to run the migration we will see the error that reservations table cannot be created correctly.  The full error message is:

In Connection.php line 647:

SQLSTATE[HY000]: General error: 1005 Can’t create table `bookingsystem_db`.`#sql-3ee0_184` (errno: 150 “Foreign key constraint is inc
orrectly formed”) (SQL: alter table `reservations` add constraint `reservations_books_id_foreign` foreign key (`books_id`) references
`books` (`id`))

We see that php artisan generator cannot create reservation_books_id_foreign key. The explanation of the fact is very easy. This foreign key cannot be create because it tryes to relate to non existing id field from book table. The books table will be created in the next lines by generator.

Everything what we need to do is to change the order of Schema::create functions so that we will have firstly created books table and next the reservations table.

    public function up()
    {
        Schema::create('books', function (Blueprint $table) {
            $table->increments('id');
            $table->string('title');
            $table->string('isbn');
            $table->string('year');  
            $table->string('return_date');
            $table->string('borrow_date');  
            $table->boolean('is_available');         
            $table->dateTime('created_date');
        });
 
        Schema::create('reservations', function (Blueprint $table) {
            $table->increments('id');
            $table->integer('books_id');
            $table->integer('users_id');
            $table->foreign('books_id')->references('id')->on('books');
            $table->foreign('users_id')->references('id')->on('users');
            $table->dateTime('created_date');
        });       
 
    }

Let’s run the migration command to see if the table structure is correct now.

General error: 1005 Can’t create table – the foreign key type is incorrect

It is still the same 1005 error with foreign key, but why now ? We need to look deeper how php artisan generate sql code for all the tables. Let’s run the command in your phpmyadmin or other similar tool that you use to manage databases:

SET GLOBAL log_output = 'TABLE';
SET GLOBAL general_log = 'ON';

and go to the table general_log in database mysql. Find all queries responsible for creation of new tablese call sql command:

SELECT * FROM `general_log` WHERE `argument` LIKE '%create%' ORDER BY `event_time` DESC

Here is what we have found looking for create books or create reservations:

It looks like php artisan generates wrong sql types for book_id foreign key in reservations table that is referenced with id column in books table.

The solution for this is to explicitly tell the generator that we want to int unsigned column type for books_id column in reservations tables. Below is the full correctly prepared code:

    public function up()
    {
        Schema::create('books', function (Blueprint $table) {
            $table->increments('id');
            $table->string('title');
            $table->string('isbn');
            $table->string('year');  
            $table->string('return_date');
            $table->string('borrow_date');  
            $table->boolean('is_available');         
            $table->dateTime('created_date');
        });
 
        Schema::create('reservations', function (Blueprint $table) {
            $table->increments('id');
            $table->integer('books_id')->unsigned();
            $table->integer('users_id')->unsigned();
            $table->foreign('books_id')->references('id')->on('books');
            $table->foreign('users_id')->references('id')->on('users');
            $table->dateTime('created_date');
        });        
 
    }

Run migration command and you should have the positive results. The migration tables should be created. In migration table you also will see a new row that our migration is done correctly.

We finished creating all database tables that we need to create full php rest api for book management system. In the next post I will explain you how implement api endpoints, how to generate models and connect them with api endpoints. We also will see how to verify endpoints and see results in web browser.

Leave a Reply