KodeInfo | Learning resources for web and mobile development

Databases Configuration, Schema Builder and Migrations in Laravel

December 12th, 2014 07:52:22 by Imran Iqbal Comments(0) - Views(6435)

Till now in our tutorials we saw how to work with requests, responses, routes, controllers and views. But after all these combined what we get at end of the day is data and we don't know where to save data. For that purpose only we have to use database to save data for further processing based on the requests and we have to send the specific data as a response. Databases are simply grids of data, not pretty. Fortunately, these days we are spoiled by lovely ORMs that will allow us to access our database rows as object instances. Laravel framework has it's own ORM called Eloquent.

Laravel has lots of options to work with different databases like MySQL, SQLite, PostgreSQL and SQL Server.  As you can see now you have lot of choices when selecting a database platform. In this tutorial we are going to use MySQL to explain examples. But we can easily shift in between different databases. Laravel abstraction layer automatically provided SQL syntax based on the database we choose. Another advantage of laravel abstraction layer is security. We don't have to worry about escaping the values that we send to the database. Laravel will escape the values for us, in an effort to prevent various forms of injection attacks.

Configuration

All of laravel database configuration is contained in the file located at app/config/database.php. Let's take a look at database configuration file to know more.

'fetch' => PDO::FETCH_CLASS

By default laravel returns rows as an PHP stdClass object. That means we can access the data like below way.

{{ $book->name }}
{{ $book->author }}

But if we want to change into an array format we can change the configuration as PDO::FETCH_ASSOC. Then we can access the data like below.

{{ $book['name'] }}
{{ $book['author'] }}

For more options about how to fetch return data from queries please take a look at PHP PDO Constants Page. Next look at the connections array in default form.

'connections' => array(

		'sqlite' => array(
			'driver'   => 'sqlite',
			'database' => __DIR__.'/../database/production.sqlite',
			'prefix'   => '',
		),

		'mysql' => array(
			'driver'    => 'mysql',
			'host'      => 'localhost',
			'database'  => 'forge',
			'username'  => 'forge',
			'password'  => '',
			'charset'   => 'utf8',
			'collation' => 'utf8_unicode_ci',
			'prefix'    => '',
		),

		'pgsql' => array(
			'driver'   => 'pgsql',
			'host'     => 'localhost',
			'database' => 'forge',
			'username' => 'forge',
			'password' => '',
			'charset'  => 'utf8',
			'prefix'   => '',
			'schema'   => 'public',
		),

		'sqlsrv' => array(
			'driver'   => 'sqlsrv',
			'host'     => 'localhost',
			'database' => 'database',
			'username' => 'root',
			'password' => '',
			'prefix'   => '',
		),

	),

If you carefully observe there was a separate driver that can be used to specify the type of database. We can also nest different types of database connections like different mysql database connections also.

'connections' => array(

		'mysql' => array(
			'driver'    => 'mysql',
			'host'      => 'localhost',
			'database'  => 'database1',
			'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 different number of connections, we can switch between databases easily. All we need to do is provide the hostname, database name, user credentials to access and prefix to the tables if we want it.

Schema Builder

Now we know how to configure database in laravel framework. let's worry about to save data, for saving data in structured way. We need to build structure with different data types and relationships. We have to use Schema class to define structure of our tables. Let's build some tables. To create table we must make use of the create() method of the schema class. 

// app/routes.php

Route::get('/', function()
{

    Schema::create('users', function($table)
    {
     
           // let's add some code here
    });

});

The Schema::create() method accepts two parameters. First parameter is table name and second one is a variable which will be used to build the table structure.

Let's go ahead add some columns to the table.

// 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();
    });

});

The increments() method is available on our $table instance to create a new auto incremental column. An auto incremental column will automatically have a value and incremented for every row. This will also be the primary key of the table. Now if we go the route URL / ,  it will create a table in the database which we configured.

There are lot of column types to use while creating a table using Schema class, for reference please check the column types in laravel website.

There are certain special column types like timestamps() and softDeletes() methods. let's take a look at timestamps() method. The timestamps() method can be used to add two TIMESTAMP columns to the table.The create_at and updated_at  columns can be used to indicate when a row was created and updated.  

Now next method is softDeletes(), occasionally we want to mark a table row as deleted, without deleting the data contained within. With the softDeletes() method you can place an indicator column on the row to show that rows has been deleted. The column that is created will be called deleted_at and will be of type TIMESTAMP again. 

We can also change the columns which we added previously and make them primary keys. Let's make username column as a primary key.

// app/routes.php

$table->string('username', 32)->unique()->primary();

We can also update tables using Schema::rename() method like below.

Schema::rename('users', 'members');

When there were lot of database connections we can choose which connection we want while creating a table using connection() method like below.

Schema::connection('mysql_1')->create('users', function($table)
{
   
   $table->increments('id');

});

To drop tables we can use Schema::drop() method.

Schema::drop('users');

Schema::dropIfExists('users');

To rename and drop columns we have to use renameColumn and dropColumn methods. But before using these make sure that you add doctrine/dbal to your composer.json file.

Schema::table('users', function($table)
{

   $table->renameColumn('username', 'name');

   $table->dropColumn('age');

});

To check for the existence of table and column we have to use hasTable and hasColumn methods respectively.

if(Schema::hasTable('users'))
{
    //
}

if(Schema::hasColumn('users', 'email'))
{
    //
}

Foreign Keys

Laravel provides support to add foreign key constraints between two tables like below.

$table->integer('user_id')->unsigned();

$table->foreign('user_id')->references('id')->on('users');

Storage Engines

We can also mention which type of storage engine we want to sue using schema builder. 

$table->engine('InnoDB');

There are so many other Schema  class methods which will be useful lot like adding indexes, dropping indexes etc. Please take a look at laravel documentation to know more tricks.

Migrations

When building our database by team of members. How can we track what changes were added to each table by different team member and how to synchronize it. That's where migrations comes handy. Migrations  are nothing but PHP scripts with timestamp that are used to change the structure or content of your database. Laravel keeps a record of which migrations have already been executed within another table on our database. 

Let's create a migration using Artisan command line interface. Let's recreate  the schema build that we used to create the users table. We will start by using Artisan to build create_users  migration.

php artisan migrate::make create_users

After we ran this command in Artisan command line interface, it will create a new migration template in app/database/migrations directory. The template will be named with current timestamp and the name which we used in the migration creation command like below.

app/database/migrations/2014_12_12_123456_create_users.php

Within migration class we have two public methods, up() and down(). Now in the up() method we have to keep the Schema::create() code which we wrote previously tow build users table. And at the same time down() method should be exactly opposite action, i mean you have to write the code to drop the table like using Schema::drop() method. Because both up() and down()  methods are bidirectional.

To run all the migrations we have to use below command.

php artisan migrate

To refresh and rollback migrations.

php artisan migrate:refresh

php artisan migrate:rollback

To know more about migrations please check laravel docs. Thanks for reading the tutorial will come up with more tutorials on Eloquent in future.

Thanks 

KodeInfo

Author

  • Imran Iqbal
    Imran Iqbal

    Imran is a web developer and consultant from India. He is the founder of KodeInfo, the PHP and Laravel Community . In the meantime he follows other projects, works as a freelance backend consultant for PHP applications and studies IT Engineering . He loves to learn new things, not only about PHP or development but everything.

Related

WHY USE A FRAMEWORK OVER PLAIN PHP

WHY USE A FRAMEWORK OVER PLAIN PHP
read more

GETTING STARTED WITH LARAVEL

GETTING STARTED WITH LARAVEL
read more

UNDERSTANDING LARAVEL STRUCTURE

UNDERSTANDING LARAVEL STRUCTURE
read more

UNDERSTANDING LARAVEL ROUTES

UNDERSTANDING LARAVEL ROUTES
read more

comments powered by Disqus