Laravel provides a suite of tools for interacting with your application’s database, but the most notable is Eloquent.

Configuration

To config the database access, we need to go to config/database.php.

Migrations

Laravel makes it easy to define your database structure with code-driven migrations. Every new table, column, index, and key can be defined in code.

Defining Migrations

A migration is a single file that defines two things: the modifications desired when running this migration up and the modifications desired when running this migration down

The Laravel’s default ‘create user table’ migration

<?php
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateUserTable extends Migration
{
  public function up(){
    Schema::create('users',function(Blueprint $table){
      $table->increments('id');
      $table->string('name');
      $table->string('email')->unique();
      $table->string('password',60);
      $table->rememberToken();
      $table->timestamps();
    });
  }
  
  public function down(){
    Schema::drop('users');
  }
}

up() method tells the migration to create a new table called users with a few fields, and down() method tells it to drop the users table

Create a migration

There’s an Artisan command for creating a migration file.

1	php artisan make:migration

The command has a parameter, which is the name of the migration. For example if you want to create a ‘create users table’ migration, you can run this:

1	php artisan make:migration create_users_table

There are two flags we can optionally pass to this command. --create=table_name prefills the migration with code designed to create a table named table_name, and --table=table_name just prefills the migration for modifications to an existing table.

1 2	php artisan make:migration create_users_table --create=users php artisan make:migration add_votes_to_users_table --talbe=users

Creating tables

In migration file, the migrations depend on the Schema facade and its methods.

To create a new table in a migration, use the Schema‘s create() method

1
2
3

Schema::create('table',function(Blueprint $table){
  
})

The first parameter is table’s name, and the second method closure that defines its columns.

Creating columns

To create new columns in a table, whether in a create table call or a modify table call, use the instance of Blueprint that’s passed into the second closure.

1
2
3

Schema::create('table',function(Blueprint $table){
  $table->string('name');
})

Blueprint has a lot of methods for us to create different type of columns

For more details, check Laravle’s available column types

Building extra properties fluently

Most of the properties of a field definition, for example, the length are set as teh second parameter of the field creation method. We can chain more method calls after the creation of the column.

1
2
3

Schema::table('users',function(Blueprint $table){
  $table->string('email')->nullable()->after('last_name');
});

In above code, we defined a nullable column called email and this column is placed right after ‘last_name’ column.

Droping tables

1	Schema::drop('users');

Modifying columns

To modify a column, just write the code we would write to create the column as if it were new, and then append a call to the change() method after it.

So if we have a string column named name that has a length of 255 and we want to change its length to 100, this is how we would write it:

1
2
3

Schema::table('users', function(Blueprint $table){
  $table->string('name',100)->change();
});

How to rename a column

1
2
3

Schema::table('users', function(Blueprint $table){
  $table->renameColumn('from','to');
});

How to drop a column

1
2
3

Schema::table('users',function(Blueprint $table){
  $table->dropColumn('name');
});

Adding indexes

$table->primary('id'); // primary key: unnecessary if used increments()
$table->primary(['first_name','last_name']); // composite keys
$table->unique('email'); // unique index
$table->unique('email','optional_custom_index_name');
$table->index('amount'); //basic index
$table->index('amount','optional_custom_index_name'); //basic index

Note: primary() is not necessary if you are using the increments() meth

od to create your index, this will automatically add a primary key index for you

Removing indexes

1
2
3

$table->dropPrimary('id');
$table->dropUnique('email');
$table->dropIndex('optional_custom_index_name');

Adding and removing foreign keys

To add a foreign key that a particular column references a column on another table

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

To remove foreign key

1	$table->dropForeign(['user_id']);

Running Migration

Once you have your migrations defined, there’s an Artisan command for running it

1	php artisan migrate

There are few other options in this namespace that we can work with.

1	php artisan migrate --seed

migrate:install

creates the database table the keeps track of which migrations you have and haven’t run; this is run automatically when you run your migrations

migrate:reset

rolls back every database migration you’ve run on this install

migrate:refresh

rolls back every database migration you’ve run on this install, and then runs every migration available. It’s the same as running migration:reset and then ‘migrate’, one after another

migrate:rollback

rolls back just the migration that ran the last time you ran migrate, or, with the added option --step=1, rolls back the number of migrations you specify.

migration:status

shows a table listing every migration, with a Y or N next to each showing whether or not it has run yet in this environment

Seeding

Seeding with Laravel is simple. There’s a database/seeds folder that comes with a DatabaseSeeder class, which has a run() method that is called when you call the seeder.

There are two way to run the seeders: along with a migration, or separately.

1 2	php artisan migrate --seed php artisan migrate:refresh --seed

1 2	php artisan db:seed php artisan db:seed --class=UsersTableSeeder

Creating a Seeder

To create a seeder, user the make:seeder Artisan command:

1	php artisan make:seeder UsersTableSeeder

You’ll now see a UsersTableSeeder class show up in the database/seeds directory. Now we add it to the DatabaseSeeder class so it will run when we run our seeders:

//database/seeds/DatabaseSeeder.php
public function run(){
  $this->call(UsersTableSeeder::class);
}

Now we can edit the seeder itself. The simplest thing we can do there is manually insert a record using the DB facade:

<?php
use Illuminate\Database\Seeder;
use Illuminate\Database\Eloquent\Model;

class UsersTableSeeder extends Seeder
{
  public function run()
  {
    DB::table('users')->insert([
      'name' => 'hello',
      'email' => 'world@gmail.com'
    ]);
  }
}

Model Factories

Model factories define one or more patterns for creating fake entries for our database tables. By default they’re named after an Eloquent class, but you can also just name them after the table name if you’re not going to work with Elquent.

$factory->define(User::class, function(Faker\Generator $faker){
  return [
    'name' => $faker->name,
  ]
});

$factory->define('users', function(Faker\Generator $faker){
  return [
    'name' => $faker->name,
  ]
})

Creating a model factory

Model factories are defined in database/factories/ModelFactory.php. Each factory has a name and a definition of how to create a new instance of the define class. The $factory->define() method takes the factory name as the first parameter and a closure that’s run for each generation as the second parameter.

$factory->define(User::class, function(Faker\Generator $faker){
  return [
    'name' => $faker->name,
  ];
})

Now we can user the factory() global helper to create an instance of User in our seeding and testing

1 2	$user = factory(User::class)->create(); factory(User::class,20)->create();

Using a model factory

There are two primary contexts in which we’ll use model factories: testing and seeding

factory(User::class)->create([
  'name' => 'name'
]);

factory(User::class,20)->create()->each(function ($u) use ($post) {
  $post->comments()->save(factory(Comment::class)->make([
    'user_id' => $u->id
  ]));
}

When we’re using factory(), it will returns the factory, and then we can run one of two methods on it: make() or create()

Both method generate an instane of this class. The difference is that make() creates the instance but doesn’t save it to the database, whereas create() saves it to the database instantly.

Overwriting properties when calling a model factory

1
2
3

factory(User::class)->create([
  'name' => 'name'
]);

Generatin more than one instance with a model factory

1	factory(User::class, 20)->create();

We will create 20 fake users in database

Defining and accessing multiple model factory types

Let say we have a customer factory

$factory->define(Customer::class, function(Faker\Generator $faker){
  return [
    'name' => $faker->name
  ]
});

But somethimes you need more than one factory for a class of object, for example, we need to add some customers who are VIP, we can define a second factory like this

$factory->defineAs(Customer::class,'vip', function(Faker\Generator $faker){
  return [
    'name' => $faker->name,
    'vip' => true
  ]
});

Above code looks fine, right. But imagine that the customer has more than 100 columns, will you write the colunmns one by one again and agian?

Actually, we can make any given model factory extend another, and then it can just override one or a few properties. So above code could be changed to this:

$factory->defineAs(Customer::class,'vip',function(Faker\Generator $faker) use ($factory) {
  $customer = $factory->raw(Customer::class);
  return array_merge(['vip' => true,$customer]);
});

Now, let;s make a specific customer type:

1	factory(Customer::class,'vip',3)->create();

Query Builder

Laravel provides a fluent interface, which means by using query builder, we don’t need to pass all the relevant data into either a constructor or a method call, fluent uses method chaining to provide a simpler API to the end user.

// non-fluent
$user = DB::select(['table' => 'users', 'where' => ['type' => 'donor'] ]);

//fluent
$user = DB::table('users')->where('type','donor')->get();

Basic Usage of the DB Facade

DB facade is used both for query builder chaining and for simpler raw queries.

// basic statement
DB::statement('drop table users');

// raw select, and parameter binding
DB::select('select * from users where validated = ?', [true]);

//select using the fluent builder
DB::table('users')->get();

//join and other complex calls
DB::table('users')->join('contacts', function($join){
  $join->on('users.id','=','contacts.user_id')->where('contacts.type','donor');
});

Raw SQL

It’s able to make any raw call to the database using the DB facede and the

statement() method

1	DB::statement('SQL statement here')

Bur there are also specific methods for varipus common actions: select(), insert(), update(), delete(). These are still raw calls, but there are differences. First, using update() and delete() will reutrn the number of rows affected, whereas statement() won’t; second, with these methods it’s clearer.

Raw selects

The simplest of the specific DB method is select()

1	DB::select('select * from users');

This will return a collection of stdClass objects.

Parameter bindings and named bindings

Laravel’s database architrcture allows for the use of PDO parameter binding, which protects your queries from potential SQL attacks.

Passing a parameter to a statement is as simple as replacing the value in your statement with a ?, then adding the value to the second parameter (array) of your call

1	DB::select('select * from users where type = ?', ['type' => $userType]);

Raw insert

Raw insert looks like this

1 2	DB::insert('insert into contacts (name, email) values (?, ?)', ['sally','sally@gmail.com']);

Raw updates

Update looks like this:

1	DB::update('update contacts set status = ? where id = ?', ['donor',$id]);

Raw deletes

Delete looks like this:

1	DB::delete('delete from contacts where archived = ?', [true]);

Chaining with the query builder

The query builder makes it possible to chain methods together to build a query. At the end of the chain you’ll use some method to trigger the actual execution of the query you’ve just built.

1	DB::table('user')->where('type',$type)->get();

Constraining methods

These methods take the query as it is and constrain it to return a smaller subset of possible data

select()

Allows you to choose which columns you’re selecting:

DB::table('contacts')->select('email','email2 as second_email')->get();

//or

DB::table('contacts')->select('email')->addSelect('email2 as second_eamil')->get();

where()

Allows you to limit the scope of what’s being returned using WHERE. By default, where() method is that it takes three parameters: the column, the comparsion operator, and the value:

1	DB::table('contacts')->where('created_at','>',Carbon::now()->subDay())->get();

However, if your comparison is =, which is the most common comparison, you can drop the second operator

1	DB::table('contacts')->where('vip',true)->get();

If you want to combine where() statements, you can either chain them after each other, or pass an array of arrays:

1
2
3

DB::table('contacts')->where('vip',true)->where('created_at','>',Carbon::now()->subDay())->get();

DB::table('contacts')->where(['vip',true],['created_at','>',Carbon::now()->subDay()]);

orWhere()

Creates simple OR WHERE statements:

1
2
3

DB::table('contacts')->where('vip',true)->orWhere(function($query){
  $query->where('created_at','>',Carbon::now()->subDay())->where('trial',false);
})->get();

Potential confusion with multiple where and orWhere calls

DB::table('users')->where('admin',true)->orWhere('plan','premium')->where('is_plan_owner',true)->get();

SELECT * FROM users 
	WHERE admin = 1
	OR plan = 'premium'
	AND is_plan_owner = 1;

DB::table('users')->where('admin',true)->orWhere(function($query){
  $query->where('plan','premium')->where('is_plan_owner',true);
})->get();

SELECT * FROM users
	WHERE admin = 1
	OR (plan = 'premium' AND is_plan_owner = 1);

whereBetween(colName, [low, high])

Allows you to scope a query to return only rows where a column is between two values (inclusive of the two values):

1	DB::table('drinks')->whereBetween('size',[6,12])->get();

The same works for whereNotBetween(), but it will select the inverse.

whereIn(colName, [1,2,3])

Allows you to scope a query to return only rows where a column is in an explicitly provided list of options:

1	DB::table('contacts')->whereIn('state',['FL','GA','AL'])->get();

whereNull(colName) and whereNotNull(colName)

Allow you to select only rows where a given column is NULL or is NOT NULL respectively.

whereRaw()

Allows you to pass in a raw, unescaped string to be added after WHERE statement

1	DB::table('contacts')->whereRaw('id = 12345')->get();

whereExists()

Allows you to select only rows that, when passed into a provided subquery, return at least one row. Imagine you only want to get those users who have left at least on comment:

1
2
3

DB::table('users')->whereExists(function($query){
	$query->select('id')->from('comments')->whereRaw('comments.user_id = users.id');  
})->get();

distinct()

Select the distinct rows.

Modifying methods

These methods change the way the query’s results will be output, rather than just limiting its results

orderBy()

Orders the result. The second parameter may be either asc (the defaullt) or desc:

1	DB::table('contacts')->orderBy('last_name','desc')->get();

groupBy()

Groups your result by a column. Optionally, having() and havingRaw() allow you to filter your result based on properties of the groups.

1
2
3

DB::table('contacts')->groupBy('city')
->havingRaw('count(contact_id) > 30')
->get();

skip() and take()

Most often used for pagination, there allow you to define how many rows to return and how many to skip before starting the return, for example, a page number and a page size in a pagination system:

1	DB::table('contacts')->skip(30)->take(10)->get();

latest(colName) andoldest(colName)

Sotr by the passed column in descending (latest()) or ascending (oldest()) order

inRandomOrder()

Sorts teh result randomly

Eending/returning methods

These methods stop the query chain and trigger the execution of the SQL query

get()

Get all results for the built query

1	DB::table('cotacts')->get();

first() and firstOrFail()

Get only the first result, but with a LIMIT 1 added

// fails silently
DB::table('contacts')->orderBy('created_at','desc')->first();
// throw an exception
DB::table('contacts')->orderBy('created_at','desc')->firstOrFail();

find(id) and findOrFail(id)

Like first(), but you pass in an ID value that corresponds to the primary key to look up.

1	DB::table('contacts')->find(1);

value()

Plucks just the value from a single field from the first row. Like first(), but if you only want a single column:

1	DB::table('contacts')->value('email')

count()

Returns an integer count of all the matching results

1	DB::table('contacts')->where('vip',true)->count();

min()and max()

Return the minimun or maximum value of particular column

1	DB::table('orders')->max('amount');

sum() and avg()

Return the sum or average of all the values in a particular column

1	DB::table('orders')->where('status','completed')->avg('amount');

Writing raw queries inside query builder methods with `DB::raw`

We’ve already seen a few custome methods for raw statements, select() has a selectRaw() that allows you to pass in a string for the query builder to place after the WHERE statement.

You can also pass in the result of a DB::raw() call to almost any method in the query builder to achieve the same result.

1	DB::table('contacts')->select(DB::raw(',(score 100) as integer_score'))->get()

Joins

DB::table('users')
->join('contacts','users.id','=','contacts.user_id')
->select('users.*','contacts.name','contacts.status')
->get();

The join() method creates an inner join. You can also chain together multiple joins one after another, or use leftJoin() to get a left join.

DB::table('users')
->join('contacts', function($join){
  $join->on('users.id','=','contacts.id')
  ->orOn('users.id','=','contacts.proxy_user_id');
})->get();

Unions

You can union two queries together by creating them frist and the using teh union() or unionAll() method to union them

1
2
3

$first = DB::table('contacts')->whereNull('first_name');

$second = DB::table('contacts')->whereNull('last_name')->union(first)->get();

Inserts

The insert() method is pretty simple. Pass it an array to insert a single row or an array of arrays to insert multiple rows, and use insertGetId()instead of insert() to get the autoincrementing primary key ID back as a return.

DB::table('users')->insert([
  'name' => 'hello',
  'email' => 'world@gmail.com'
]);

DB::table('users')->insertGetId([
  ['name' => 'hello',
  'email' => 'world@gmail.com'],
  ['name' => 'hello1',
  'email' => 'world1@gmail.com']
]);

Note: when you are using insertGetId() the parameter should not be an array of arrays.

Updates

1	DB::table('users')->where('id',1)->update(['name' => 'hello']);

You can also quickly increment or decrement a column’s value by using increment() or decrement()

1	DB::table('users')->increment('votes',5);

Deletes

1	DB::table('users')->where('last_login','<',Carbon::now()->subYear()))->delete();

You can also truncate the table

1	DB::table('users')->truncate();

JSON operations

If you have JSON columns, you can update or select rows based on aspects of the JSON structure by using the arrow syntax to traverse children

1 2	DB::table('users')->where('options->isAdmin',true)->get(); DB::table('users')->update(['option->isVerified',true]);