Laravel: [Database] Migrations, Models, Eloquent and Seeds

In the last lesson we dived into getting started with the Laravel framework and we covered a range of areas, from introducing the MVC principle (briefly mentioning the separation of concerns) to working with controllers, routing and the view (i.e. blade templates; we will cover this in more depth later). We also performed a new Laravel setup, either via a composer installation or by cloning the git repo.

I’d like for us to continue working with Laravel, however today we will cover working with databases which Laravel provides natively out of the box. The four elements of Laravel which regard to working with databases are:

• Migrations
• Models (The “Model” from the MVC principle)
• Eloquent
• Seeds

All of which we’ll cover in this chapter. To get started with these, we must first establish a database connection and we’ll use the MySQL server provided by XAMPP for this.

1. Database Tables

All the necessary knowledge and resources required to create a database table (including migrations, models, seeds and using eloquent) with Laravel will be provided in this section. Throughout this chapter a table called ‘fruits’ has been created purely to provide an example. However when you come to creating your own tables, please select any one of the following for practice purposes.

The following database tables are relating to a commerce system where customers may purchase products from a retail store.

You are free to create your own appropriate columns for these tables, so feel free to experiment.

Now that all the above is out the way, let’s get into Laravel.

2. Configuring a MySQL Database

Laravel is designed to work natively with MySQL as standard, however it is also capable of working with multiple database types by both first-party (i.e. built in) and also by third-party drivers. As we have done previously we will continue to use MySQL as our preferred database type, which is provided from our XAMPP installation.

To begin we must first ensure that our MySQL server is running by starting the service inside the XAMPP Control Panel. Then using a database client of your choice (i.e. HeidiSQL, phpMyAdmin or your own preference) we need to create a new database, let’s call it laravel (all lowercase) named after our current project. (We have covered working with databases previously, please see the "SQL Databases - RDBMS" section for an introduction.)

Now that we’ve created a new database for our project and our SQL server is up and running, we now need to configure our Laravel application to connect to the newly created database, which is a simple process.

Using your IDE/editor (e.g. Atom, Visual Studio Code or other) whilst the ‘laravel’ project is open, look for the .env file which is located in the root directory. As you will be familiar with working with .env files (as a result of following this course), you may have correctly suspected that our database credentials should be placed in here. Laravel has already pre-defined the variable keys/names in the .env file and we are required to populate the details appropriately. Look for the following lines inside the .env file:

As we’re only developing locally and not deploying the application to a server at the moment, we should be able to get away by keeping most of these values as they are (if you’ve retained the default MySQL settings within XAMPP). You will however be required to populate the DB_DATABASE, which in our case will be “laravel” (all lowercase) to use our newly created database.

Now we can save the .env file and initiate the database for our application’s use, which will also act as a confirmation to us if the connection was successful. We will now need to work in the command line, so open up Git Bash and ‘CD’ into our project’s directory (this should be ‘/c/xampp/htdocs/laravel’). Now run the following command:

The above command will insert all our defined database tables from within the application code and also insert any records/data we have pre-set, it may take a few seconds to complete. If running the command was a success, using your SQL client you will see some tables have been inserted into our database.

To better understand what the above command did, we need to first understand and work with migrations and seeds which we will now move onto.

3. Migrations

There is currently no way to version control databases and this is actually a problem for us in the industry, especially as we work in teams and we all have our own environment setups. However Laravel provides one of the best ways we can version control databases and this is by using ‘migrations.’

Migrations are a way of writing database tables by using our PHP application code, where our code is already shared through a central git repository available for all team members. As a result we are not required to write SQL queries to define our tables and we should opt for Laravel’s migration formatting and syntax instead. Having knowledge of SQL is beneficial however.

All our migrations are located in the following directory of our application:

And you’ll notice each file within the directory is dated, timestamped and followed with a name allowing us to identify the migration’s purpose, for example ’2021_03_23_223811_create_users_table.php’ denotes:

• ‘2021_03_23’ is the date following a YYYY_MM_DD format;
• ‘223811’ is the time in 24 hours, including seconds;
• ‘create_users_table’ is the name of the migration. We can give the migration any name, however it should follow some level of convention.

When running a migration from the CLI Laravel will execute the migrations in the order of the date and timestamp (from oldest to newest), i.e. the eldest dated migration will run first. You’ll also notice inside your database using a SQL client that Laravel creates a ‘migrations’ table by default purely to keep track of which migrations have been executed and rolled-back on your local environment.

Inside any migration file there will be two functions/methods, up() and down(). up() defines what should be executed when the migration is run, whereas down() defines what should occur when the migration is rolled back.

Laravel is a framework that expects you to follow conventions to work (rather than requiring additional configuration), therefore any database tables we create should be plural. Let’s now explore creating a new database table using a migration, we’ll call our table ‘fruits.’ We can create a new migration by running the following command in the CLI:

(You don’t need to do any of the following, you don’t need to create a ‘fruits’ table; this is purely for demonstration and understanding. Please create your own chosen table from section 1 of this lesson. You will be however required to read this for the knowledge.)

The above will create a new migration in the database/migrations directory for us, giving it today’s date and timestamp. If we open the newly created migration, we will see the Schema::table() method is empty for both up() and down(). Immediately for the down() method we can replace the code with:

Which will drop/delete the table if the migration is reversed/rolled-back. Next for the fruits table, I’ll define the following columns in the migration:

The migration methods will now look like the following:

You’ll notice inside the up() function the following Schema::create() method defines columns using the $table variable. You can find all the supported column types on the ’Available Column Types’ section of the ‘Migrations’ page of the Laravel Docs, which also includes useful examples.

The $table->timestamps() will automatically create the two created_at and updated_at columns, which Laravel will automatically populate if the records are inserted using application code.

At this stage our migration is complete. If we save the file and now run the following in the command line:

Laravel will create the newly added table with the defined columns. As previously mentioned however, you don’t need to create the ‘fruits’ table for this task but you will need to do the above for your own custom table.

You can read more about Migrations on the Laravel Docs.

4. Models

Once we’ve added the database migration, we now need to add a corresponding model for this in Laravel. This will allow us to work with the database table from our Laravel’s application code.

As Laravel follows conventions, the name of the model should be singular form of the migration table. As the table name is ‘fruits’ then the model name should be ‘Fruit’ (notice capitalised first letter too), so the model only relates to a single record.

We can use Laravel’s Artisan CLI utility again to create this model by simply running:

This will place our newly created ‘Fruit’ model in the app/Models directory of our project. If you open the file you’ll see it’s practically an empty class. We firstly need to define which columns our application should have permission to write to, we can do this by creating a $fillable variable which holds an array of our columns, like so:

This should be placed inside our model’s class, below the use HasFactory statement. We don’t need to include the id, created_at and updated_at columns as Laravel will handle this for us by default. We can now save the file and that’s all for the model.

We can implement additional functionality into the model, including defining database relationships and our own custom methods. However for now we will keep this task simple and not explore these capabilities.

5. Eloquent

Now to interact with the database from our application code we use something called Eloquent which Laravel provides. We do not write SQL queries directly into our application code and by following this practice we deploy clean code; whereby our PHP code is not mixed with SQL. Additionally you should find that reading Eloquent statements are more clean than trying to read SQL queries.

Laravel automatically attaches the Eloquent query builder to a model, so we can get started with using Eloquent quite simply.

In any PHP class (e.g. a controller, seeder, FormRequest or other), we first need to declare the use of the Model. We do this by adding use App\Models\Fruit; at the top of the PHP file, after the namespace declaration.

Once we’ve declared the use statement, we can then run any query builder method to perform database actions. Some basic examples include:

The above are some basic examples, however Eloquent provides a vast amount of capabilities which we can utilise.

You can read more about using Eloquent on the Laravel Docs and also the Query Builder on Laravel Docs.

From the Query Builder examples from the Laravel docs you’ll see they use a DB class, for example:

As Eloquent makes use of the query builder, we can substitute that directly for the Model, e.g.:

As previously mentioned, as Laravel is widely supported you’ll find plenty of resources and Q/A’s online on Stackoverflow and Laracasts with using Eloquent.

6. Seeds

Finally for this lesson, in addition to providing migrations for creating database tables, Laravel also provides seeds for inserting rows of data into our tables. Using this we can pre-populate data into the tables we create, with either real or fake data.

We can create our own seeder by using the Artisan CLI utility. Let’s continue using the fruits example and create a seed file for the table, which we can do so using the following command:

The above command will create the FruitSeeder file in the /database/seeders directory. If we open the file we’ll see it has a run() method which is empty. We are required to define the code which should be executed on seeding. We can populate the data in any way we desire, however we will use the recently covered Eloquent model. Inside the run() method we can add:

We now need to execute the Seeder file we created by declaring a call inside the DatabaseSeeder file which is also located inside the same `/database/seeders` directory. Within the run() method of the DatabaseSeeder there should be a $this->call() method which defines all the seeder files which should be executed. If the call() method does not exist, we can copy and paste the following:

The above method takes an array of Seeder files which should be executed. In the above example we have only passed in a single FruitSeeder, however we can define more like so:

You can read more about Seeding on the Laravel Docs.

And that will be all for this lesson. Once you’ve completed the above, you should become familiar with working with databases in Laravel as we have covered a significant amount.