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.
Right, so you have decided that you want to store things in the database. The database isn't exactly a simple key-value store though. Within the database our data can have structure, consist of different types, and have relationships. Sexy, wonderful relationships.
In order to store our structured data, we first need to define the structure. This isn't a book about SQL, so I hope by now that you will understand the concept of a database table and it's columns. In this chapter we are going to take a look at the Schema class, that we can use to define the structure of our tables. We aren't going to be storing any data in this chapter, so make sure that you are in the mindset of structure, and not content.
In the next chapter you will learn about an ideal location to start building your database structure, but I like to describe each feature in isolation. For now, we will be writing our schema-building code within routed closures. Well let's not waste any more time, and take a quick look at the query builder.
Creating Tables
To create a table we must make use of the create() method of the Schema class. Here's an example.
<?php
// app/routes.php
Route::get('/', function()
{
Schema::create('users', function($table)
{
// Let's not get carried away.
});
});
The Schema::create() method accepts two parameters. The first is the name of the table that we wish to create. In this case we are creating a table named 'users'. If the table we are creating will be used to store data representing a type of object, we should name the table in lowercase as the plural of the object. Database columns and tables are commonly named using snake-casing, where spaces are replaced with underscores (_) and all characters are lowercase.
The second parameter to the method is a Closure, with a single parameter. In the above example I have called the parameter $table, but you can call it whatever you want! The $table parameter can be used to build the table structure.
Let's add auto incrementing primary key to our table, this way our table rows can be identified by a unique index.
<?php
// app/routes.php
Route::get('/', function()
{
Schema::create('users', function($table)
{
$table->increments('id');
});
});
The increments() method is available on our $table instance to create a new auto incremental column. An auto incremental column will automatically be populated with an integer value that increments as each row is added. It will start at one. This column will also be the primary key for the table. The first parameter to the increments method is the name of the column that will be created. That was simple right?
Let's go ahead and add some more columns to this table.
<?php
// app/routes.php
Route::get('/', function()
{
Schema::create('users', function($table)
{
$table->increments('id');
$table->string('username', 32);
$table->string('email', 320);
$table->string('password', 60);
$table->timestamps();
});
});
Great! Now we have a blueprint to create the structure of our users table. Don't worry about each of the individual columns right now, we will cover them in detail in the next section. First let's build this table by visiting the / URI to fire our routed Closure.
Now let's take a look at the database structure that has been created for us. Now I don't know which database you chose to use, but I'm going to be using mySQL for this book, so I will take a look at the database using the mySQL command line interface. Feel free to use whatever software you feel most comfortable with.
mysql> use myapp;
Database changed
mysql> describe users;
+------------+------------------+-----+----------------+
| Field | Type | Key | Extra |
+------------+------------------+-----+----------------+
| id | int(10) unsigned | PRI | auto_increment |
| username | varchar(32) | | |
| email | varchar(320) | | |
| password | varchar(60) | | |
| created_at | timestamp | | |
| updated_at | timestamp | | |
+------------+------------------+-----+----------------+
6 rows in set (0.00 sec)
Well I've simplified the describe table a little to fit in the books formatting restrictions, but I hope you get the picture. Our user table structure has been built using the blueprint that we created with our $table object.
You must be wondering what methods and columns we have available on the $table object? Well let's take a look!
Column Types
We are going to examine the methods that are available on the $table blueprint object. I'm going to leave the routed closure out of these examples to simplify things, so you will have to use your imagination! Let's get started.
increments
The increments method will add an auto incremental integer primary key to the table. This is a very useful method for building the structure for Eloquent ORM models, which we will learn about in a later chapter.
<?php
Schema::create('example', function($table)
{
$table->increments('id');
});
The first and only parameter for the increments() method is the name of the column to create. Here's the resulting table structure:
+-------+------------------+-----+----------------+
| Field | Type | Key | Extra |
+-------+------------------+-----+----------------+
| id | int(10) unsigned | PRI | auto_increment |
+-------+------------------+-----+----------------+
bigIncrements
Oh, so the increments method wasn't big enough for you? Well the bigIncrements() method will create a big integer, rather than a regular one.
<?php
Schema::create('example', function($table)
{
$table->bigIncrements('id');
});
Just like the increments() method, the bigIncrements() method will accept a single string parameter as the column name.
+-------+---------------------+-----+----------------+
| Field | Type | Key | Extra |
+-------+---------------------+-----+----------------+
| id | bigint(20) unsigned | PRI | auto_increment |
+-------+---------------------+-----+----------------+
string
The string() method can be used to create varchar columns, which are useful for storing short string values.
<?php
Schema::create('example', function($table)
{
$table->string('nickname', 128);
});
The first parameter to string() method is the name of the column to create, however, there is an optional second parameter to define the length of the string in characters. The default value is 255.
+----------+--------------+
| Field | Type |
+----------+--------------+
| nickname | varchar(255) |
+----------+--------------+
text
The text() method can be used to store large amounts of text that will not fit into a varchar column type. For example, this column type could be used to contain the body text of a blog post.
<?php
Schema::create('example', function($table)
{
$table->text('body');
});
The text() method accepts a single parameter. The name of the column that will be created.
+-------+------+
| Field | Type |
+-------+------+
| body | text |
+-------+------+
integer
The integer column type can be used to store integer values, are you surprised? Well I can't think of a way to make it any more interesting! Well, I suppose I could mention how integer values are useful when referencing the auto incremented id of another table. We can use this method to create relationships between tables.
<?php
Schema::create('example', function($table)
{
$table->integer('shoe_size');
});
The first parameter to the integer() method is the name of the column. The second parameter is a boolean value that can be used to define whether or not the column should be auto incremental. The third parameter is used to define whether or not the integer is unsigned. A signed integer can be positive or negative, however, if you define an integer as unsigned, then it can only be postive. Signed integers can contain a range of integers from –2,147,483,648 to 2,147,483,647, where an unsigned integer can hold value from 0 to 4,294,967,295.
+-----------+---------+
| Field | Type |
+-----------+---------+
| shoe_size | int(11) |
+-----------+---------+
bigInteger
Big integer values work exactly like normal integers, only they have a much larger range. A signed inter has a range of –9,223,372,036,854,775,808 to 9,223,372,036,854,775,807, and unsigned integers have a range of 0 to 18,446,744,073,709,551,615. An integer of this size is normally used to store my waist size in inches.
<?php
Schema::create('example', function($table)
{
$table->bigInteger('waist_size');
});
The method signature for all of the integer variants is exactly the same as the integer() method, so I won't waste any time by repeating it! If you have forgotten already, maybe you should take a look at the 'integer' section again?
+------------+------------+
| Field | Type |
+------------+------------+
| waist_size | bigint(20) |
+------------+------------+
mediumInteger
This column is another type of integer, let's see if we can get through these column types a little faster shall we? I'm just going to specify the column value ranges from now on. The signed range is –8388608 to 8388607. The unsigned range is 0 to 16777215.
<?php
Schema::create('example', function($table)
{
$table->mediumInteger('size');
});
The method signature is identical to that of the integer() method.
+-------+--------------+
| Field | Type |
+-------+--------------+
| size | mediumint(9) |
+-------+--------------+
tinyInteger
This is another integer type column. The signed range is –128 to 127. The unsigned range is 0 to 255.
<?php
Schema::create('example', function($table)
{
$table->tinyInteger('size');
});
The method signature is identical to that of the integer() method.
+-------+------------+
| Field | Type |
+-------+------------+
| size | tinyint(1) |
+-------+------------+
smallInteger
This is another integer type column. The signed range is –32768 to 32767. The unsigned range is 0 to 65535.
<?php
Schema::create('example', function($table)
{
$table->smallInteger('size');
});
The method signature is identical to that of the integer() method.
+-------+-------------+
| Field | Type |
+-------+-------------+
| size | smallint(6) |
+-------+-------------+
float
Float column types are used to store floating point numbers. Here's how they can be defined.
<?php
Schema::create('example', function($table)
{
$table->float('size');
});
The first value parameter is the name used to identify the column. The optional second and third integer parameters can be used to specify the length of the value, and the number of decimal places to use to represent the value. The defaults for these parameters are 8 and 2 respectively.
+-------+------------+
| Field | Type |
+-------+------------+
| size | float(8,2) |
+-------+------------+
decimal
The decimal() method is used to store... wait for it... decimal values! It looks very similar to the float() method.
<?php
Schema::create('example', function($table)
{
$table->decimal('size');
});
The method accepts a column name as the first parameter, and two optional parameters to represent the length and number of decimal places that should be used to define the column. The defaults for the optional parameters are again, 8 and 2.
+-------+--------------+
| Field | Type |
+-------+--------------+
| size | decimal(8,2) |
+-------+--------------+
boolean
Not all values consist of large ranges of digits and characters. Some only have two states, true or false, 1 or 0. Boolean column types can be used to represent these values.
<?php
Schema::create('example', function($table)
{
$table->boolean('hot');
});
The only parameter for the boolean method is the name given to the column it creates.
+-------+------------+
| Field | Type |
+-------+------------+
| hot | tinyint(1) |
+-------+------------+
The tinyint in the above example is not a typo. Tiny integers are used to represent boolean values as 1 or 0. I'd also like to mention at this point that I almost burnt the kitchen down while being distracted writing this section. I thought it might be interesting to know about the perilous life of a technical writer! No? Fine, let's carry on with the column descriptions.
enum
The enumerated type will store strings that are contained within a list of allowed values. Here's an example.
<?php
Schema::create('example', function($table)
{
$allow = array('Walt', 'Jesse', 'Saul');
$table->enum('who', $allow);
});
The first parameter is the name of the column that will be created. The second parameter is an array of values that are permitted for this enumerated type.
+-------+-----------------------------+------+
| Field | Type | Null |
+-------+-----------------------------+------+
| who | enum('Walt','Jesse','Saul') | NO |
+-------+-----------------------------+------+
date
As the name suggests, the date() method can be used to create columns that store dates.
<?php
Schema::create('example', function($table)
{
$table->date('when');
});
The first and only parameter is used to specify the name of the column that will be created.
+-------+------+
| Field | Type |
+-------+------+
| when | date |
+-------+------+
dateTime
The dateTime() method will not only store a date, but also the time. No kidding, it really will. I know, I know, a lot of these methods are similar, but trust me, this will make a great reference chapter!
<?php
Schema::create('example', function($table)
{
$table->dateTime('when');
});
Once again, the name of the column to be created is the only parameter.
+-------+----------+
| Field | Type |
+-------+----------+
| when | datetime |
+-------+----------+
time
Don't want the date included with your times? Fine! Just use the time() method instead.
<?php
Schema::create('example', function($table)
{
$table->time('when');
});
Once again, the first and only parameter to the time() method is the name of the column being created.
+-------+------+
| Field | Type |
+-------+------+
| when | time |
+-------+------+
timestamp
The timestamp() method can be used to store a date and time in the TIMESTAMP format. Surprised? No? Oh... well, let's take a look at how it works.
<?php
Schema::create('example', function($table)
{
$table->timestamp('when');
});
The first and only value is the name of the database column that will be created.
+-------+-----------+---------------------+
| Field | Type | Default |
+-------+-----------+---------------------+
| when | timestamp | 0000-00-00 00:00:00 |
+-------+-----------+---------------------+
binary
The binary() method can be used to create columns that will store binary data. These types of columns can be useful for storing binary files such as images.
<?php
Schema::create('example', function($table)
{
$table->binary('image');
});
The only parameter to the binary method is the name of the column that is being created.
+-------+------+
| Field | Type |
+-------+------+
| image | blob |
+-------+------+
Special Column Types
Laravel includes several special column types that have varied uses. Let's take a look at them. First up, we have the timestamps() method.
The timestamps() method can be used to add two 'TIMESTAMP' columns to the table. The created_at and updated_at columns can be used to indicate when a row was created, and updated. In a later chapter we will learn how Laravel's own Eloquent ORM can be told to automatically update these columns when an ORM instance is created or updated. Let's have a look at how the timestamps() method is used.
<?php
Schema::create('example', function($table)
{
$table->timestamps();
});
The timestamps() method doesn't accept any parameters. Here's the table structure that is created.
+------------+-----------+---------------------+
| Field | Type | Default |
+------------+-----------+---------------------+
| created_at | timestamp | 0000-00-00 00:00:00 |
| updated_at | timestamp | 0000-00-00 00:00:00 |
+------------+-----------+---------------------+
Next we have the softDeletes() method. Occasionally you will want to mark table row as deleted, without actually deleting the data contained within. This is useful if you may wish to restore the data in the future. With the softDeletes() method you can place an indicator column on the row to show that the row has been deleted. The column that is created will be called deleted_at and will be of type 'TIMESTAMP'. Once again, Laravel's Eloquent ORM will be able to update this column, without deleting the row when you use the delete method on an ORM instance. Here's how we can add the deleted_at column to our table.
<?php
Schema::create('example', function($table)
{
$table->softDeletes();
});
The softDeletes() method does not accept any parameters. Here's the resulting table.
+------------+-----------+------+
| Field | Type | Null |
+------------+-----------+------+
| deleted_at | timestamp | YES |
+------------+-----------+------+
Column Modifiers
Column modifiers can be used to add extra constraints or properties to the columns that we create with the create() method. For example, earlier we used the increments() method to create a table index column that was both auto incremental, and a primary key. That's a handy shortcut, but let's take a look at how we can turn another column into a primary key using column modifiers.
First we will make a new column, and declare that it must contain unique values.
<?php
Schema::create('example', function($table)
{
$table->string('username')->unique();
});
By chaining the unique() method on to our column creation method, we have told the database that duplicate values will not be allowed for this column. Our primary key should be used to identify individual rows, so we don't want to have duplicate values, do we!
Let's make the 'username' column the table's primary key.
<?php
Schema::create('example', function($table)
{
$table->string('username')->unique();
$table->primary('username');
});
We can mark any column as a primary key using the primary() method. The only parameter to this method a string representing the name of the column to mark as the key. Let's describe the table we have just created.
+----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| username | varchar(255) | NO | PRI | NULL | |
+----------+--------------+------+-----+---------+-------+
Great! We have a new primary key.
Here's a neat trick, both the primary() key, and the unique() methods are able to act on their own, or fluently chained to an existing value. This means that the above example could also be written like this:
<?php
Schema::create('example', function($table)
{
$table->string('username')->unique()->primary();
});
The above example shows how the column modifiers can be chained to an existing column definition. Alternatively, the column modifiers can be used in isolation by providing a column name as a parameter.
<?php
Schema::create('example', function($table)
{
$table->string('username');
$table->unique('username');
$table->primary('username');
});
If you aren't satisfied with a single primary key for your table, you can use multiple composite keys by providing an array of column names to the primary() method that we used in the previous example. Let's take a look.
<?php
Schema::create('example', function($table)
{
$table->integer('id');
$table->string('username');
$table->string('email');
$keys = array('id', 'username', 'email');
$table->primary($keys);
});
Now our three new columns will act as a composite key, whereby any combination of the values contained in the columns will be a unique reference to an individual role. Let's have a look at the output from 'describe'.
+----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| username | varchar(255) | NO | PRI | NULL | |
| email | varchar(255) | NO | PRI | NULL | |
+----------+--------------+------+-----+---------+-------+
We can speed up our queries by marking columns that are used to lookup information as indexes. We can use the index() method to mark a column as an index. It can be used fluently, like this:
<?php
Schema::create('example', function($table)
{
$table->integer('age')->index();
});
Or in isolation, like this:
<?php
Schema::create('example', function($table)
{
$table->integer('age');
$table->index('age');
});
Either way, the result will be the same. The column will be marked as an index.
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| age | int(11) | NO | MUL | NULL | |
+-------+---------+------+-----+---------+-------+
We can also pass an array of column names to the index() method to mark multiple columns as indexes. Here's an example.
<?php
Schema::create('example', function($table)
{
$table->integer('age');
$table->integer('weight');
$table->index(array('age', 'weight'));
});
Here's the resulting table structure.
+--------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| age | int(11) | NO | MUL | NULL | |
| weight | int(11) | NO | | NULL | |
+--------+---------+------+-----+---------+-------+
Sometimes we want to set a constraint on a column to state whether or not it can contain a null value. We can set a column to nullable using the nullable() method. It can be used as part of a method chain, like this:
<?php
Schema::create('example', function($table)
{
$table->string('name')->nullable();
});
Here's the resulting table structure.
+-------+--------------+------+
| Field | Type | Null |
+-------+--------------+------+
| name | varchar(255) | YES |
+-------+--------------+------+
As you can see, the column can now contain a null value. If we don't want the column to allow a null value, we can pass boolean false as the first parameter to the nullable() chained method, like this:
<?php
Schema::create('example', function($table)
{
$table->string('name')->nullable(false);
});
Now let's take another look at the resulting table structure.
+-------+--------------+------+
| Field | Type | Null |
+-------+--------------+------+
| name | varchar(255) | NO |
+-------+--------------+------+
As you can see, the 'name' column can no longer contain a null value.
If we wish for our columns to contain a default value when a new row is created, we can provide the default value by chaining the default() method onto the new column definition. Here's an example.
<?php
Schema::create('example', function($table)
{
$table->string('name')->default('John Doe');
});
The first and only parameter to the default() method is the intended default value for the column. Let's take a look at the resulting table structure.
+-------+--------------+------+-----+----------+
| Field | Type | Null | Key | Default |
+-------+--------------+------+-----+----------+
| name | varchar(255) | NO | | John Doe |
+-------+--------------+------+-----+----------+
If we don't provide a value for the 'name' column when creating a new row, then it will default to 'John Doe'.
We have one final column modifier to look at. This one isn't really needed, but it's a nice little shortcut. Do you remember creating integer columns in the previous section? We used a boolean parameter to specify whether or not an integer was signed, and could contain a negative value. Well we can use the unsigned() chained method on an integer column to specify that it may not contain negative numbers. Here's an example.
<?php
Schema::create('example', function($table)
{
$table->integer('age')->unsigned();
});
Here's the resulting table structure after using the unsigned() chained method.
+-------+------------------+
| Field | Type |
+-------+------------------+
| age | int(10) unsigned |
+-------+------------------+
Whether you choose to use the boolean switch, or the unsigned() method, the choice is entirely yours.
Updating Tables
Once a table has been created, there's no way to change it.
Are you sure, because the heading say...
I'm sure, there's absolutely no way.
Hmm, but the heading says updating tables?
You just won't let it go will you? Fine, I was going to go take a nap, but you have convinced me. You need to know about updating tables, so let's get started.
First of all, we can change the name of a table that we have already created quite easily using the Schema::rename() method. Let's take a look at an example.
<?php
// Create the users table.
Schema::create('users', function($table)
{
$table->increments('id');
});
// Rename the users table to idiots.
Schema::rename('users', 'idiots');
The first parameter of the rename() method is the name of the table that we wish to change. The second parameter to the method, is the new name for the table.
If we want to alter the columns of an existing table, we need to use the Schema::table() method. Let's take a closer look.
<?php
Schema::table('example', function($table)
{
// Modify the $table...
});
The table() method is almost identical to the create() method we used earlier to create a table. The only difference is that it acts upon an existing table that we specify within the first parameter to the method. Once again, the second parameter contains a Closure with a parameter of a table builder instance.
We can use any of the column creation methods that we discovered in the previous section to add new columns to the existing table. Here's an example.
<?php
Schema::create('example', function($table)
{
$table->increments('id');
});
Schema::table('example', function($table)
{
$table->string('name');
});
In the above example we use the Schema::create() method to build the 'example' table with a primary key. Then we use the Schema::table() method to add a string column to the existing table.
Here's the result from describe example;:
+-------+------------------+-----+----------------+
| Field | Type | Key | Extra |
+-------+------------------+-----+----------------+
| id | int(10) unsigned | PRI | auto_increment |
| name | varchar(255) | | |
+-------+------------------+-----+----------------+
Now you can use any of the column creation methods that we learned about in the previous section to add additional columns to a table. I won't cover every creation method again, their signatures haven't changed. If you need a quick refresher course, then have another look at the 'Column Types' section.
If we decide that we no longer wish to have a column on our table, we can use the dropColumn() method to remove it. Let's take a look at this in action.
<?php
Schema::create('example', function($table)
{
$table->increments('id');
$table->string('name');
});
Schema::table('example', function($table)
{
$table->dropColumn('name');
});
In the above example, we create the 'example' table with two columns. Then we use the dropColumn() method to remove the 'name' column from the table. The dropColumn() method will accept a string parameter, which is the name of the column that we wish to remove.
Here is what our 'example' table will look like after the above code has been executed.
+-------+------------------+------+-----+----------------+
| Field | Type | Null | Key | Extra |
+-------+------------------+------+-----+----------------+
| id | int(10) unsigned | NO | PRI | auto_increment |
+-------+------------------+------+-----+----------------+
As you can see, the 'name' column was removed succesfully.
If we wish to remove more than one column at once, we can either provide an array of column names as the first parameter to the dropColumn() method...
<?php
Schema::table('example', function($table)
{
$table->dropColumn(array('name', 'age'));
});
...or we can simply provide multiple string parameters for column names.
<?php
Schema::table('example', function($table)
{
$table->dropColumn('name', 'age');
});
Feel free to use whichever method suits your style of coding.
We don't have to drop our columns, though. If we want to, we can simply rename them. Let's have a look at an example.
<?php
Schema::create('example', function($table)
{
$table->string('name');
});
Schema::table('example', function($table)
{
$table->renameColumn('name', 'nickname');
});
The renameColumn() method is used to change the name of a column. The first parameter to the method is the name of the column that we wish to rename, and the second parameter is the new name for the column. Here's the resulting table structure for the above example.
+----------+--------------+
| Field | Type |
+----------+--------------+
| nickname | varchar(255) |
+----------+--------------+
Now, do you remember the primary keys that we constructed in the previous section? What happens if we no longer wish for those columns to be primary keys? Not a problem, we just remove the key. Here's an example.
<?php
Schema::create('example', function($table)
{
$table->string('name')->primary();
});
Schema::table('example', function($table)
{
$table->dropPrimary('name');
});
Using the dropPrimary() method, we supply the name of a column as a parameter. This column will have its primary key attribute removed. Here's how the table looks after the code has executed.
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name | varchar(255) | NO | | NULL | |
+-------+--------------+------+-----+---------+-------+
As you can see, the name column is no longer a primary key. To remove a number of composite keys from a table, we can instead supply an array of column names as the first parameter of the dropPrimary() method. Here's an example.
<?php
Schema::create('example', function($table)
{
$table->string('name');
$table->string('email');
$table->primary(array('name', 'email'));
});
Schema::table('example', function($table)
{
$table->dropPrimary(array('name', 'email'));
});
We can remove the unique attribute for a column by using the dropUnique() method. This method accepts a single parameter, which consists of the table name, column name, and 'unique' separated by underscores. Here's an example of removing the unique attribute from a column.
<?php
Schema::create('example', function($table)
{
$table->string('name')->unique();
});
Schema::table('example', function($table)
{
$table->dropUnique('example_name_unique');
});
Once again, we can pass an array of column names in same format to the dropUnique() method if we wish. Here's an example.
<?php
Schema::create('example', function($table)
{
$table->string('name')->unique();
$table->string('email')->unique();
});
Schema::table('example', function($table)
{
$columns = array('example_name_unique', 'example_email_unique');
$table->dropUnique($columns);
});
Finally, we can drop an index attribute from a table column by using... wait for it... okay you guessed it. We can use the dropIndex() method. Simply provide the column name in the same format as we used with the dropUnique() method, that's table name, column name, and 'index'. For example:
<?php
Schema::create('example', function($table)
{
$table->string('name')->index();
});
Schema::table('example', function($table)
{
$table->dropIndex('example_name_index');
});
For some reason, I was unable to provide an array of columns to the dropIndex() method. I will ask Taylor about this and update the chapter with any changes. For now, let's move on.
Dropping Tables
To drop a table, simply cut off its legs.
Just kidding, we can drop a table using the Schema::drop() method, let's take a look at this method in action.
<?php
Schema::create('example', function($table)
{
$table->string('name');
});
Schema::drop('example');
To drop a table we simply pass the name of the table as the first parameter of the Schema::drop() method. Let's try to describe the table to see if it exists.
mysql> describe example;
ERROR 1146 (42S02): Table 'myapp.example' doesn't exist
Well I guess it worked! It looks like the table is gone.
If we try to drop a table that doesn't exist, then we will get an error. We can avoid this by instead using the dropIfExists() method. As the name suggests, it will only drop a table that exists. Here's an example.
<?php
Schema::create('example', function($table)
{
$table->string('name');
});
Schema::dropIfExists('example');
Just like the drop() method, the dropIfExists() method accepts a single parameter, the name of the table to drop.
Schema Tricks
Tricks? Maybe not. However, this section is used for methods that simply don't fit into the previous sections. Let's waste no time by taking a look at the first method.
We can use the Schema::connection() method to perform our schema changes on an alternative database or connection. Let's take a look at an example.
<?php
Schema::connection('mysql')->create('example', function($table)
{
$table->increments('id');
});
Schema::connection('mysql')->table('example', function($table)
{
$table->string('name');
});
The connection() method can be placed before any of the Schema class methods to form a chain. The first parameter for the method, is the name of the database connection which subsequent methods will act upon.
The connection() method can be very useful if you need to write an application that uses multiple databases.
Next up, we have a couple of methods that can be used to check the existance of columns and tables. Let's go ahead and jump right in with an example.
<?php
if (Schema::hasTable('author')) {
Schema::create('books', function($table)
{
$table->increments('id');
});
}
We can use the hasTable() method to check for the existance of a table. The first parameter to the method is the name of the table that we wish to check. In the above example we create the 'books' table only if the 'authors' table exists.
As you might have already guessed, we have a similar method to check for the existance of a column. Let's take a look at another example.
<?php
if (Schema::hasColumn('example', 'id')) {
Schema::table('example', function($table)
{
$table->string('name');
});
}
We can use the Schema::hasColumn() method to check if a table has a column. The first parameter to the method is the table, and the second parameter is the name of the column that we want to look for. In the above example, a 'name' column will be added to the 'example' table.
If you happen to be a database genius, you might want to change the storage engine used by the table. Here's an example.
<?php
Schema::create('example', function($table)
{
$table->engine = 'InnoDB';
$table->increments('id');
});
Simply change the value of the engine attribute on the table blueprint to the name of the storage engine that you wish to use. Here are some of the available storange engines for the mySQL database:
- MyISAM
- InnoDB
- IBMDM2I
- MERGE
- MEMORY
- EXAMPLE
- FEDERATED
- ARCHIVE
- CSV
- BLACKHOLE
For more information about these storage engines, please consult the mySQL documentation for the topic.
On mySQL databases, you are able to reorder the columns of a table by using the after() method. Here's an example.
<?php
Schema::create('example', function($table)
{
$table->string('name')->after('id');
$table->increments('id');
});
Simply chain the after() method onto the column that you wish to reposition. The only parameter to the method is the name of the column that you wish for the new column to be placed after. Feel free to use this method, although I would recommend simply building your tables in the intended order, this will look much clearer.
Well that's all that I have about building database schemas. Why don't we learn about a more suitable place to build our schemas. Let's move on to the migrations chapter.
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!