Full Stack Web Developer (Q1-Q2 2021)
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:
- Models (The “Model” from the MVC principle)
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.
|Customers||The table should all the customer specific details, such as name and contact details (address, email, phone) and etc.|
|Addresses||This database table should be capable of holding records of all UK postal addresses, therefore you’ll most likely need a column for each line of address as minimum.|
|Stores||As this database relates to a retail commerce system, this table should hold records of each store. Column examples could include the store name, id, region and address.|
|Colleagues||This is similar to the customers’ table however should be specific only to store colleagues. We’ll still need to capture their names, email address and also a colleague id.|
|Cities||This table should hold records on each city or town in the UK, therefore at minimum we should have a ‘name’ column as well as area dialling code.|
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.
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() 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:
|id||Integer (bigInt)||AUTO_INCREMENT for each newly added record.|
|name||String (varchar)||UNIQUE, another record cannot have the same value.|
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.
$table->timestamps() will automatically create the two
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.
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
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.
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:
||This will return all the records in the ‘fruits’ table.|
||This will record the ‘fruit’ record which has the ‘id’ of 1. An error will be thrown if the record does not exist.|
||This is an example of running a ‘where’ clause on the Eloquent method, whereby in the given example will return all fruits which have the ‘green’ colour attribute.|
This is another example of using where clauses however here we have chained two together. We can chain as many as we need and they don’t have to only be ‘where’ clauses, we can add other capabilities Eloquent provides.
In this example we are returning records from the ‘users’ table where the name matches ‘Alex’ and the sex is ‘M’ for male.
||Here we’re creating a new record using Eloquent into the fruits table. The two attributes we define are ‘grape’ with the colour of ‘red’.|
The above are some basic examples, however Eloquent provides a vast amount of capabilities which we can utilise.
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.
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.