Setting Up a Database for a RESTful API in Laravel

Photo by Carlos Muza on Unsplash

Laravel is one of the biggest frameworks for PHP, it is known for its excellence in the development of back-end applications, has an enormous amount tools for common uses in back-end development such as form security checks and database relational access.

Knowing these features, developing an application program interface (API) with Laravel has a comparatively low difficulty than with other frameworks or programming languages. The structure of APIs and the general web development interfaces are relatively simple, having a front-end (the presentation of the applications, the one the user interacts with) and the back-end(which is the part of the application which controls “logistics” and functioning, including the database).

Considering the above mentioned importance of the backend and thus of databases, its configuration must be done at the beginning of every project. In this article, you’ll watch the way in which it’s done with a project developed in Laravel in mind.

In this article, the project example that’s being worked on will be an online art gallery. Setting up the database for this project will require:

1. Defining the database components for this specific project.
2. Start defining the code necessary for the database.
3. Create the connections necessary for the relations between tables.
4. Create the database in phpMyAdmin.
5. Connect the database to Laravel.
6. Disable the form’s security middleware.
7. Create the models for the tables.

Photo by Zany Jadraque on Unsplash

Defining the database components for this specific project:

Make organization charts with the information that will be in the MySQL database. Every table will have attributes within, which will show the fields of the table (the elements which comprise it).

1. Users: ID, role, name, surname, email, password, image, created_at, uptadet_at, remember_token (for the Laravel-integrated authentication system to remember the logged user).
2. Pieces: ID, user_id, title, description, price, status, created_at, updated_at.

Start the code necessary for the database:

Define the database through a .sql document, in which the previously defined fields will be entered and thus, the database will have the code for its creation with such fields in addition to configuring the primary key, or the primary element, which defines the content of the table. The .sql document should have the definition written in a code-like syntax shown in the following example:

CREATE DATABASE IF NOT EXISTS apilaravel;
USE apilaravel;
CREATE TABLE users (
id int(255) auto_increment not null,
role      varchar(20),
name   varchar(255),
surname   varchar(255),
email    varchar(255),
created_at    datetime DEFAULT NULL,
updated_at   datetime DEFAULT NULL,
remember_token  varchar(255),
CONSTRAINT pk_users PRIMARY KEY(id)
) ENGINE=InnoDb

Create the connections necessary for the relations between tables:

Connect both tables as previously stated in the organization chart specifying it at the end of the table which needs the data from another table with syntax similar to the one shown in the following example:

CONSTRAINT fk_pieces_users FOREIGN KEY(user id) REFERENCES users (id)

Create the database in phpMyAdmin:

Create the database in phpMyAdmin, within the database, using the utf8_general_ci configuration. You should be able to see this database in the list below. After this, you’ll need to click on the database and in SQL button on the upper side of the web page, which will take you to an editor where the written-before code will need to be pasted.

Photo by rawpixel on Unsplash

Connect the database to Laravel:

To connect this database to Laravel, the .env file should be opened. Within this file, several lines of code will be refered to a DB_CONNECTION which will be changed to a configuration similar to the one here:

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=apilaravel
DB_USER=root
DB_PASSWORD=null

Disable the form’s security middleware:

In order for this database to work correctly with this software, the middleware which checks the form’s security. For this to be done, the last middleware in the Kernel.php file found within the App file, then the Http file, then the Middleware file. The one that will be commented out should look like this:

//App\Http\Middleware\VerifyCsrfToken::class

Create the models for the tables:

Finally, the Pieces model (a file within Laravel which interacts with the table) will need to be created for its correct functioning and interaction with Laravel (the User model is already built within Laravel). For the creation of this model, some Artisan commands within Wamp server will be needed, these are:

cd api-laravel (in order to go inside the project file)
php artisan make::model Pieces

Inside the model itself, some specifications needs to be made in order to connect this model with its specific database. These specifications will look like this:

protected $table = ‘pieces’
public function users(){
return $this -> belongsTo (‘App\User’, ‘user_id’)
}