This title was written for Laravel version 4. If you're looking for material on the latest version of Laravel, then please check out Code Smart.
Now I have to make a confession here. I'm not a big fan of databases. I got bitten by one as a child you see, and once bitten, twice shy. Okay, okay, I'm kidding again. It's because a database killed my brother.
Once more, just kidding, I don't have any siblings. I don't really know why I don't enjoy them, I suppose I like visual things, pretty things, fun things. Databases are simply big grids of data, not pretty, the anti-fun. Fortunately, these days we are spoiled by lovely ORMs that will allow us to access our database rows as object instances. In a later chapter you will discover more about Laravel's own ORM named Eloquent. Eloquent is lovely, and it makes working with databases a pleasurable experience, even for a grim database-hater like myself.
Well let's put my own hates aside for a moment and talk about the concept of a database. Why do we need one? Well, maybe we don't?
Does your application need to store data that will be available within all future requests?
C> I just want to show static pages.
Well then you don't need a database, but what happens when you do need to store data across multiple requests, and display or use it on other routes of your application? Well then you need a data storage method, and you will be glad that you read these next few chapters.
Abstraction
So what databases can we use with Laravel four? Well let's see if any of the following take your fancy.
As you can see, you have a great deal of choice when selecting a database platform. For this book, I will be using the MySQL Community Server Edition. It's a great free platform, and one of the most popular ones used for development.
You don't have to worry about using another database server though. You see, Laravel provides an abstraction layer, it decouples the framework's database components from the RAW SQL, providing different queries for different types of databases. Simply put, you don't have to worry about the SQL syntax, let Laravel take care of it.
Another advantage of using Laravel's database abstraction layer is security. In most sithations, unless I indicate otherwise, you won't have to worry about escaping the values that you send to the database from Laravel. Laravel will escape these values for you, in an effort to prevent various forms of injection attacks.
Let's weigh up the flexibility of Laravel's database abstraction layer for a moment. So you can switch database servers whenever you like, without having to change any of the database code you have written, and you won't have to worry about simple matters of security? That to me sounds like a great chunk of work has been removed from your projects. Escaping values is boilerplate, we don't need to do that. Let's let Laravel take care of it.
Now that we know that we wish to use a database, let's learn how we can setup Laravel to use one. Don't worry, it's quite a simple process! First let's take a look at the configuration options.
Configuration
All of Laravel's database configuration is contained in the file located at app/config/database.php. That's easy to remember, right? Let's take a trip through the file, and look at some of the configuration options available.
/*
|--------------------------------------------------------------------------
| PDO Fetch Style
|--------------------------------------------------------------------------
|
| By default, database results will be returned as instances of the PHP
| stdClass object; however, you may desire to retrieve records in an
| array format for simplicity. Here you can tweak the fetch style.
|
*/
'fetch' => PDO::FETCH_CLASS,
When rows are returned from a query that one of Laravel's database components executes, they will be default be in the form of a PHP stdClass object. This means that you are able to access the data in their columns in a format similar to this.
<?php
echo $book->name;
echo $book->author;
However, if you wish to alter the format in which rows are returned you may simply change the fetch option of the database configuration to something more suitable. Let's alter the option to PDO::FETCH_ASSOC which will instead use an associative PHP array to store our rows. Now we can access our database rows in the following manner.
<?php
echo $book['name'];
echo $book['author'];
For a full list of PDO fetch modes, take a look at the PHP PDO constants documentation page, look at the constants that start with FETCH_.
Next let's take a look at the connections array. Here's how it looks in its default form.
<?php
'connections' => array(
'sqlite' => array(
'driver' => 'sqlite',
'database' => __DIR__.'/../database/production.sqlite',
'prefix' => '',
),
'mysql' => array(
'driver' => 'mysql',
'host' => 'localhost',
'database' => 'database',
'username' => 'root',
'password' => '',
'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',
'prefix' => '',
),
'pgsql' => array(
'driver' => 'pgsql',
'host' => 'localhost',
'database' => 'database',
'username' => 'root',
'password' => '',
'charset' => 'utf8',
'prefix' => '',
'schema' => 'public',
),
'sqlsrv' => array(
'driver' => 'sqlsrv',
'host' => 'localhost',
'database' => 'database',
'username' => 'root',
'password' => '',
'prefix' => '',
),
),
Woah, that's a huge list of default connections! That makes it a lot easier to get started. Now, looking at the above array, you might think that we have a different index for each type of database. However, if you look more closely, you will notice that each nested array has a driver that can be used to specify the type of database. This means that we could easily have an array of different MySQL database connections, like this:
<?php
'connections' => array(
'mysql' => array(
'driver' => 'mysql',
'host' => 'localhost',
'database' => 'database',
'username' => 'root',
'password' => '',
'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',
'prefix' => '',
),
'mysql_2' => array(
'driver' => 'mysql',
'host' => 'localhost',
'database' => 'database2',
'username' => 'root',
'password' => '',
'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',
'prefix' => '',
),
'mysql_3' => array(
'driver' => 'mysql',
'host' => 'localhost',
'database' => 'database3',
'username' => 'root',
'password' => '',
'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',
'prefix' => '',
),
),
By having a number of different database connections, we can switch databases at will. This way our application doesn't only have to have a single database. Very flexible, I think you will agree.
The first index of the connections array is simply a nickname given to the connection, that we can supply when we need to perform an action on a specific database from within our code. You can call your databases anything you like! Now let's get to the meat and potatoes. We will take a closer look at an individual connection array. Here's an example once more.
'my_connection' => array(
'driver' => 'mysql',
'host' => 'localhost',
'database' => 'database',
'username' => 'root',
'password' => '',
'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',
'prefix' => '',
),
The driver option can be used to specify the type of database that we intend to connect to.
'driver' => 'mysql',
Here are the possible values.
mysql- MySQLsqlite- SQLitepgsql- PostgreSQLsqlsrv- SQL Server
Next up we have the host index, which can be used to specify the network location of the machine that hosts the database server.
'host' => 'localhost',
You can provide either an IP address (168.122.122.5) or a host name (database.example.com). In the local development environment you will be primarily using 127.0.0.1 or localhost to refer to the current machine.
SQLite Databases
SQLite databases are stored at a location on disk, and thus do not have a host entry. For this reason you can simply ommit this index from a SQLite database connection block.
The next index of the connection array is the database option.
{:lang="php"} 'database' => 'database_name',
It is a string value used to identify the name of the database which the connection is due to act upon. In the case of an SQLite database, it is used to specify the file that is used to store the database, for example:
'database' => __DIR__.'/path/to/database.sqlite',
The username and password indexes can be used to provide access credentials for your database connection.
'username' => 'dayle',
'password' => 'emma_w4tson_is_hot',
SQLite Databases
Once again, SQLite databases are a bit different here. They don't have credentials. You can ommit these indexes from an SQLite connection block.
The next configuration index is is charset, it can be used to specify the default character set for a database connection.
'charset' => 'utf8',
SQLite Databases
You guessed it! The SQLite database doesn't support this option. Just leave this index out of the connection array.
You can set the default database collation using the collation index.
'collation' => 'utf8_unicode_ci',
SQLite Databases
Once again, SQLite chooses to be a unique snowflake. You don't need to provide the character set or collation index for it.
Finally, we have the prefix option, which can be used to add a common prefix to your database tables.
'prefix' => '',
Preparing
If you want to work through the examples in the next few chapters, you are going to want to setup a working database connection. Go ahead and download a database platform, and install it.
Next create a connection array, and fill in all the required parameters. You are almost there. We simply need to tell Laravel which database connection to use by default. Take another look at the app/config/database.php file.
/*
|--------------------------------------------------------------------------
| Default Database Connection Name
|--------------------------------------------------------------------------
|
| Here you may specify which of the database connections below you wish
| to use as your default connection for all database work. Of course
| you may use many connections at once using the Database library.
|
*/
'default' => 'mysql',
Within the default array index, we should place the identifier for the new connection that we have created. This way, we won't have to specify a connection every time we intend to use the database.
Well I know you are excited by databases. You strange, strange person you! Let's not waste any more time. Flip the page, let's examine the schema builder.
My books are available online for free to encourage learning. However, if you'd like for me to keep writing, then please consider buying a digital copy over at Leanpub.com.
It's available in PDF, ePub, and Kindle format, and contains a bunch of extras that you won't find on the site. I have a full-time job, and I write my books in my spare time. Please consider buying a copy so that I can continue to write new books from the comfort of my sofa!