The practice of PHP… And more!

Create database tables for Laravel e-commerce application

Now that we have our requirements for our Laravel e-commerce application, we are ready to get started with actually developing the application. (Finally!)

We will use the powerful Laravel “migrations” feature to create our database tables and relationships. Migrations may seem like an odd name, but essentially it allows us to have some version control for the changes that we make to our database over the lifecycle of the project. Likewise, it is helpful for allowing others to take our source code and get their database up and running quickly, even if they are using a completely different database engine, since the migrations files are independent of the database platform that you are using.

In general, I like to use the migrations tool to create a template file and then manually customize the details to fit my preferences. In particular, I prefer to include the table name as part of the primary key column in the tables, whereas Laravel simply names the column ‘id’. Likewise, if you have some “boilerplate” columns that you like to include in all of your tables, using the template helps to ensure consistency of these column names.

To begin, we need to create a database for our application. Since we are using the LAMP stack, we’ll create the database in MySQL. You can create the database in the Terminal (see below) or, if you prefer, you can use other tools, such as phpMyAdmin.

For this example, we’ll name the database ‘laravel’ and simply use the MySQL ‘root’ user account. Likewise, I’m assuming that the MySQL ‘root’ account does not have a password. Obviously, this is VERY insecure, but it is fine for your local development environment. In a production scenario, you would want to create a specific user and grant that user limited permissions to the database.

To log into MySQL, run:

mysql -u root

If the MySQL ‘root’ user has a password, you can simply append -p to the command and you’ll be prompted to enter the password.

At the mysql> prompt, we will create our database and add a new user, with a password of ‘password’, with full privileges to the database.

After each command, MySQL should respond with Query OK and some other relevant information. In the last statement, laravel.* limits the command to the laravel database, but to all tables in that database.

We chose the user name ‘laravel_crud’ to indicate that this user will have full privileges on this database. ‘CRUD’ is an acronym for ‘Create-Read-Update-Delete’, which are all of the typical operations on database tables.

That’s all we need for the database for now! Simply log out of MySQL by entering exit;.

Now that we have our database, we need to configure Laravel to access the database. In Aptana (or another text editor), open the app/config/database.php file and locate the 'connections' array. In the 'connections' array, update the 'mysql' element array:

You can quickly confirm that the configuration is valid by displaying the site in your web browser (e.g., http://projects.local/laravel/). As long as the configuration is correct, the page should display correctly. If there is a configuration error, you’ll get a nice Laravel error message.

Laravel’s built-in migration functionality will simply create a “shell” of the class for us. We will supplement this with the excellent Laravel 4 Generators package by Jeffrey Way. To add this (or any third-party) package, in the Terminal, navigate to the root of the Laravel project (e.g., ~/projects/laravel) and run:

composer require "way/generators":"2.*" --prefer-dist

This will add the package to your composer.json file. Now we just need to run a Composer update to install the new package and its dependencies, if any.

composer update

To activate the new Generators package, open the app/config/app.php file and add this line to the end of the providers array:

'Way\Generators\GeneratorsServiceProvider',

If you like, you can also add an alias, such as Generator to the aliases array. Having an alias makes it simple to reference the classes by the short name, instead of the fully-qualified name.

Based on our requirements, we need the following tables (and associated Laravel models):

account

product

order

order_item

Let’s start by creating a migration for the account table. We’ll then use this migration class as a template for the other tables.

First, we need to create the migrations table in our database. This table simply keeps track of the various changes that we’ve made to the actual tables used by our application. To create the table, run:

php artisan migrate:install

To generate (using the Generators package which we added above) the migration for the account table, in the Terminal, from the project root directory, run:

This will create a migration file in the app/database/migrations with the name YYYY_MM_DD_HHMMSS_CreateAccountTable.php where YYYY is the 4-digit year, MM is the month, DD is the day and HHMMSS is the hour/minute/second that the migration was created. (Note that the HHMMSS is the UTC time, based on your system’s time zone setting.) For example, 2014_08_05_203018_CreateAccountTable.php. Open this file in your editor (Aptana, etc.). The generated file will look like:

As you can see, this class essentially consists of the up() method, which creates the table in the database, and the down() method, which drops the table. When you apply the migration (see below), the up() method will create the account table in the database.

We want to make a few customizations to the class for our specific needs. The only changes that are required are to the up(). Change this method as follows.

Now that we have a basic template for our migrations, we can use it for the other tables that we need for our database. Here are the details for each of the other classes. Again, we’ll only show the up() method, but you need to update the class name and the down() method for each, appropriately. Likewise, each class should be saved in it’s own file.

Once we have all of the migrations classes created for our tables, we are ready to apply the migrations to create the tables in our database. Actually, running the migrations is very simple:

php artisan migrate

Laravel will list the migrations that are executed in the terminal. Likewise, you can check the migrations table in the database to see them. The batch column in the migrations table gets incremented for each run.

If you need to revert any updates made via the migration, you run the migrate:rollback command which reverts only the last migration. If you want to start over completely, use the migrate:reset command. In addition, migrate:refresh will do a reset and then load the current migrations.

To get a list of all of the migrations commands run php artisan list command. Most of the migrations commands start with ‘migrate’.