Laravel Databases & Migrations for Beginners (2021)

Laravel provides a whole bunch of tools to interact with your application's database. The most important one is Eloquent, which is one of Laravel's most popular and influential features. In a world of powerful, but complex tools, Eloquent stands out for its simplicity. But before we dive into Eloquent, it's a good idea to first get everything set up and understanding the basics of Laravel's database functionality: connections & migrations.

In this tutorial, I'll first show you how to configure your database credentials and connections. Then, I'll explain how Laravel migrations work and give you a few examples. After that, I'll explain how to interact with your database and retrieve data.

In a next tutorial I'll show you how to get started with Eloquent and how to use seeders & factories to turn you into a power-user💪

1. Laravel database connections & configurations

2. Laravel database migrations

3. How to get data from the database

Laravel database connections & configuration

The Laravel configuration for database access lives in your config/database.php and .env files. Similar to other parts of Laravel, here you can define multiple connections and then decide which connection the code will use by default.

By default, there's one connection for each of the database drivers. A database driver is how Laravel retrieves the data. So, there's a database driver for mysql, sqlite, pgsql, redis and more.

If you open up the config/database.php file, you'll see the following default connections.

'default' => env('DB_CONNECTION', 'mysql'),
 
'connections' => [
 
    'sqlite' => [
        'driver' => 'sqlite',
        'url' => env('DATABASE_URL'),
        'database' => env('DB_DATABASE', database_path('database.sqlite')),
        'prefix' => '',
        'foreign_key_constraints' => env('DB_FOREIGN_KEYS', true),
    ],
 
    'mysql' => [
        'driver' => 'mysql',
        'url' => env('DATABASE_URL'),
        'host' => env('DB_HOST', '127.0.0.1'),
        'port' => env('DB_PORT', '3306'),
        'database' => env('DB_DATABASE', 'forge'),
        'username' => env('DB_USERNAME', 'forge'),
        'password' => env('DB_PASSWORD', ''),
        'unix_socket' => env('DB_SOCKET', ''),
        'charset' => 'utf8mb4',
        'collation' => 'utf8mb4_unicode_ci',
        'prefix' => '',
        'prefix_indexes' => true,
        'strict' => true,
        'engine' => null,
        'options' => extension_loaded('pdo_mysql') ? array_filter([
            PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
        ]) : [],
    ],
 
    'pgsql' => [
        'driver' => 'pgsql',
        'url' => env('DATABASE_URL'),
        'host' => env('DB_HOST', '127.0.0.1'),
        'port' => env('DB_PORT', '5432'),
        'database' => env('DB_DATABASE', 'forge'),
        'username' => env('DB_USERNAME', 'forge'),
        'password' => env('DB_PASSWORD', ''),
        'charset' => 'utf8',
        'prefix' => '',
        'prefix_indexes' => true,
        'schema' => 'public',
        'sslmode' => 'prefer',
    ],
 
    'sqlsrv' => [
        'driver' => 'sqlsrv',
        'url' => env('DATABASE_URL'),
        'host' => env('DB_HOST', 'localhost'),
        'port' => env('DB_PORT', '1433'),
        'database' => env('DB_DATABASE', 'forge'),
        'username' => env('DB_USERNAME', 'forge'),
        'password' => env('DB_PASSWORD', ''),
        'charset' => 'utf8',
        'prefix' => '',
        'prefix_indexes' => true,
    ],
 
],

Nothing stops you from deleting or modifying them. And neither can you only use one connection per driver: you could have 4 connections, all with the mysql driver if you wanted.

In many apps, you'll just pick the one connection you need, set it to 'default' => 'driver_I_need' and store the necessary connection details in .env.

I usually keep all the connections, even if I don't use them. It could be that I need them later and then it'll come in handy.

This 'connection-driven' design is very handy if you need to work with multiple databases. E.g. one database for reading and one database for writing. Support for multiple connections makes this possible.

Laravel changing database credentials

You database credentials are usually secret. That's why you don't want to commit them to version control (that means putting it in a file that is stored in Git). The .env file is the perfect place for such 'sensitive' information.

In the config/database.php, you'll see lines like this:

env('DB_HOST', '127.0.0.1')
 
// Get the value of 'DB_HOST' from .env and if it doesn't exist, use '127.0.0.1'.

So if you take a look at your .env (or .env.example, the template file for .env), you'll use it like this:

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=my_database_name
DB_USERNAME=root
DB_PASSWORD=

Using a different database connection

By default, any database query will use the default connection. Anytime you need a specific connection, you can use it as follows:

$users = DB::connection('second')->select('select * from users');

Laravel config/database.php additional settings

If you look further at the config/database.php file, you'll see that there are also connection options for the migrations table name and for Redis.

Usually you don't need to change the name for the migrations table, as migrations is totally fine.

For Redis, you'll only need those settings if you decide to use Queues and Jobs: a more advanced feature of Laravel. For now, you can totally skip this and come back to it when you ever want to implement queues.

Laravel database migrations

Now that we've correctly set up and connected our database, the next thing we'll look at is Laravel database migrations. So first, what are migrations?

Default Laravel users table migration

So, let's dive in. How does an actual migration look like? As an example, take the default migration Laravel offers for the users table.

Go to the folder database/migrations. Here you'll see list of all the migrations. Every file is a single migration. The file name contains a timestamp, so that Laravel can determine the order of the migrations.

<?php
 
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
 
class CreateUsersTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
            // Create a table called 'users'
        Schema::create('users', function (Blueprint $table) {
 
            $table->id();                             // Each user has an id
            $table->string('name');                   // Each user has a name
            $table->string('email')->unique();        // Each user has a unique email
            $table->timestamp('email_verified_at')->nullable();   // Only applicable if you need email verification
            $table->string('password');               // Each user has a password
            $table->rememberToken();                  // Each user has a remember token (to allow him or her to stay signed in
            $table->timestamps();                     // Each user has two timestamps: created_at and last_edited_time
 
        });
    }
 
    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('users');               // To undo this migration, we need to drop the table 'users'
    }
}

Each migration is built up in the same way. It consists of two methods: up() and down().

• Migration up(): this function is run when you migrate your database.

• Migration down(): this function is the opposite function of the up() method. It is run when you rollback your database (e.g. you have 5 migrations, you're not satisfied with the last one, so you rollback the latest migration and bring your database back to that state).

Usually you initially only bother yourself with the up() call. You only look at the down() method after you've defined what the up() does. In short, the down() method undoes the changes made by the up() method.

Creating database migrations

So, how do we create database migrations? As always, there's an artisan command for that.

php artisan make:migration create_posts_table

This command creates a file in the database/migrations folder with {timestamp}_create_posts_table. If Laravel can automatically determine the table name from the migration name, it'll pre-fill that name. In this case the table name would be posts.

But you can also specify the table name yourself. If you want to create a new table, run:

php artisan make:migration create_tablename_table --create=tablename

And if you want to update a table, run:

php artisan make:migration update_tablename_table --table=tablename

Migration calls on the `Schema::` facade

The whole point of migrations is to say: 'I want this and this column created/modified/deleted in this and this way'. However, you can't just plainly type some code and expect it to work.

In this phase, you need to distinct between two things:

1. Creating, modifying and deleting tables

2. Creating, modifying and deleting columns

An interesting thing is that each of the methods contain a call to the Schema:: facade. For each change to a database table, you use a new Schema:: call. For modifying columns inside that table, you add code in the Schema:: call itself. If this sounds very confusing, don't worry, it's actually very simple.

Creating, modifying or deleting tables

The Schema:: facade itself is not so interesting. In fact, you can see it as a wrapper for the actual code that contains some configuration, like whether you're creating a new table or only updating it.

The Schema::method() is used for adding, updating or deleting tables. Each Schema::method() contains the code to add, update or delete columns.

Here are a few important use cases of the Schema::method() facade:

• If you want to create a table, use Schema::create('tablename', function(Blueprint $table) { // Create columns here } );

• If you want to update a table, use Schema::table('tablename', function(Blueprint $table) { `// Create columns here` } );

• Check whether a table exists: Schema::hasTable('users')

• Check whether a column exists: Schema::hasColumn('users')

• Migrate a different connection than your default: Schema::connection('sqlite')->create('users', ..

And then there are also a few methods for dropping things:

• Rename a table: Schema::rename('users', 'my_users');

• Drop a table: Schema::drop('users');

• Drop a table if it exists: Schema::dropIfExists('users');

Creating columns

Laravel offers many methods to create, modify or drop columns. The documentation specifies them all, but here's a list of the most important ones:

IDs and timestamps

Let's start with two simple functions. These functions are included by default in every migration. They create a column called id and two columns with timestamps: **created_at** and **updated_at**. It is advisable to use this on every table you create.

$table->id();
$table->timestamps();

Creating a string column

$table->string('column_name');

Creating a date column, a datetime column and a timestamp column

Storing dates and times is somewhat tricky, but understanding the differences between the date(), datetime(), timestamp() and time() helpers can be very useful.

The $table->date() helper creates a column that only contains the date.

The $table->datetime() helper creates a column that contains both the date and the time part.

The $table->timestamp() helper creates a column that contains both the date and the time, but supports timezones. Effectively, when you store a timestamp that is in a different timezone than UTC, your database will convert the timezone to UTC. When you get the timestamp again, the database will convert the time back to the correct timezone.

The $table->time() helper creates a column that contains only the time part.

See the examples for storing dates and times in Laravel below:

$table->date('column_name');              // YYYY-MM-DD
$table->datetime('column_name');          // YYYY-MM-DD HH:MM:SS
$table->timestamp('column_name');         // YYYY-MM-DD HH:MM:SS in UTC
$table->time('column_name');              // HH:MM:SS

Creating an integer column

$table->integer('column_name');

Creating a decimal number column

$table->decimal('column_name', 8, 2);
 
// This creates a decimal column with a precision of 8 and 2 digits.
// This means that is can contain numbers up to 999999.99 (6 numbers before and 2 numbers after).
 
// Increase precision for larger numbers.

Creating a true/false column (boolean)

$table->boolean('column_name');

Creating a json column

$table->json('column_name');
 
// Useful for storing arrays. Convert an array to json() when you store it and convert it back to array when you read it.
 
// Eloquent can do this automatically for you and I'll write an article about this soon.

Creating a foreignId column

$table->foreignId('user_id');
 
/*
This creates a column that can contains an id. This shortcut will create a column that references the column 'id' on the table 'users'.
 
Very useful with Eloquent Relationships. Checkout the following example, where a user has many posts:
 
 
Create a table 'users' and a table 'posts'. The table 'posts' contains:
$table->foreignId('user_id');
 
 
In your Eloquent User model, add the following:
*/
 
public function posts(): HasMany {
   return $this->hasMany(Post::class);
}
 
// In your Eloquent Post model, add the following:
public function user(): BelongsTo {
   return $this->belongsTo(User::class);
}
 
// Now use it like this:
$user = auth()->user();
 
$post = $user->posts()->create([
    'title' => 'Title', // Or any other column you create
    //
]);
 
// And get the user when you have a post:
$user_retrieved_from_post = $post->user;

Creating a column with a long text

$table->longText('column_name');
 
// This creates a column that can contain very lots of text. Take our example of a User that has many Posts again. You wouldn't use longText() for the title, but it would be ideal for the body text.

Creating the columns for softdeletes

This creates a column deleted_at with a timestamp. This is used for softdeletes. When you delete a row that uses soft deletes, the record will not actually be deleted, but it will set the deleted_at column to now().

Laravel will now know that this row has been deleted earlier and should not be included in your query results (except when you specifically ask for them). This is useful for restoring data, but be aware that it is prone to human errors and will also clutter your database.

<?php
 
namespace App\Models;
 
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\SoftDeletes; // <= Add this
 
class Post extends Model
{
    use SoftDeletes; <= Add this
}

In your migration:
 
Schema::table('posts', function (Blueprint $table) {
    $table->softDeletes();
});
 
Schema::table('posts', function (Blueprint $table) {
    $table->dropSoftDeletes();
});

If you want to see more column types, please refer to the documentation.

Dropping a column

If you want to delete a column, that is done inside a Schema::table() call:

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

How to get data from the database

So, how do we now get data into the database and how do we retrieve it?

Laravel offers two tools for that: the DB:: facade aka the Query Builder, and Eloquent. First, we'll dive into the DB:: facade and in a next tutorial I'll show you how to get started with Eloquent. The Query Builder is similar to Eloquent, so it'll help you if you already have a basic idea of how it works.

Getting all the rows with the Laravel Query Builder

To get the results, you start by making a call to the DB:: facade with a method called table('table_name'). After that initial part, you can start 'chaining' database calls. Chaining means that calls look like this: method()->otherMethod()->andAnother($coolParameter)->get();.

Keep in mind that ->get() or ->first() will always be the last call you make, it tells Laravel: 'go, now do what I specified before'. You see?

use Illuminate\Support\Facades\DB;
 
$users = DB::table('users')->get();

Keep this in mind: this database call will not return a simple array, but a so-called collection. You can view collections like a sort of 'super-arrays'. To get info from a single instance, you can get it as if the value is a property of a class, e.g. $user->email or $post->title. Here, email and title are both columns.

foreach ($users as $user) {
    echo $user->name;
}

Getting a single row with the Query Builder

To get a single row, you can use ->first() instead of ->get():

use Illuminate\Support\Facades\DB;
 
$users = DB::table('users')->first();

And you can add conditions:

use Illuminate\Support\Facades\DB;
 
  // Gets the first item where 'email' = 'email@example.com'
$user = DB::table('users')->where('email', 'email@example.com')->first();
 
  // Gets the first item where 'email' = 'email@example.com' AND 'job_title' = 'manager'
$manager = DB::table('users')->where('email', 'email@example.com')->where('job_title', 'manager')->first();
 
  // Gets the first item where 'email' = 'email@example.com' OR 'email' = 'secondary@example.com'
$user = DB::table('users')->where('email', 'email@example.com')->orWhere('email', 'secondary@example.com')->first();

Working with thousands of results: chunking

Another interesting thing is chunking database queries. This comes in handy when you have large numbers of records, in the thousands or millions. If you use ->get() to get them all, you're likely to hit your memory limits – and the app will crash.

In order to solve that, use ``Model::chunk()`:

use Illuminate\Support\Facades\DB;
 
DB::table('users')->orderBy('id')->chunk(100, function ($users) {
    foreach ($users as $user) {
        //
    }
});

This function returns a maximum of 100 records at a time and iterates over them with a foreach loop.

The chunk() function takes a closure (a PHP function) as a parameter. That means that you don't have access to other variables defined before. Solve that by adding the use ($read_only_variable) or use (&$read_and_write_variable) after the closure definition, like so:

use Illuminate\Support\Facades\DB;
 
    // Only read the variable in the closure (recommended)
DB::table('users')->orderBy('id')->chunk(100, function ($users) use ($read_only_variable) {    foreach ($users as $user) {
        //
    }
});
 
    // Modify the variable in the closure
DB::table('users')->orderBy('id')->chunk(100, function ($users) use (&$read_and_write_variable) {    foreach ($users as $user) {
        //
    }
});

How to determine if a Laravel database record exists

This example is straight from the documentation, but it is very self-explanatory. It checks whether a record exists:

if (DB::table('orders')->where('finalized', 1)->exists()) {
    // ...
}
 
if (DB::table('orders')->where('finalized', 1)->doesntExist()) {
    // ...
}

Special database functions

In addition to the above function, Laravel also offers some ways to count certain values or calculate the average. It also supports getting the lowest or highest value in the database with the min and max method.

You can also chain ->where() calls for more exact results, see the third example.

use Illuminate\Support\Facades\DB;
 
// Get the number of users
$users = DB::table('users')->count();
 
// Get the user with the lowest id and the order with the highest price
$user = DB::table('users')->min('id');
$price = DB::table('orders')->max('price');
 
// Determine the average order price of orders that are finalized
$price = DB::table('orders')
                ->where('finalized', 1)
                ->avg('price');

Updating records in the database

Now that we've seen how to connect to the database and get records from it, let's also take a look at how to insert, update and delete records.

Add records to the database

The easiest way to insert a record is with the insert method:

use Illuminate\Support\Facades\DB;
 
// Insert one record
DB::table('users')->insert([
    'email' => 'kayla@example.com',
    'firstname' => 'Kayla',
    'lastname' => 'Murray',
]);

Insert multiple records by passing an array with arrays:

// Insert multiple records
DB::table('users')->insert([
    ['email' => 'terrence@example.com', 'firstname' => 'Terrence', 'lastname' => 'Robertson'],
    ['email' => 'Jake@example.com', 'firstname' => 'Jake', 'lastname' => 'McDonald'],
]);

Update or insert a record in the database

Take a look at the below code. This will update the price column if there is a record with matching departure and destination columns already. If there is no record that has those departure and destination values, it will create it.

DB::table('flights')->upsert([
    ['departure' => 'Oakland', 'destination' => 'San Diego', 'price' => 99],
    ['departure' => 'Chicago', 'destination' => 'New York', 'price' => 150]
], ['departure', 'destination'], ['price']);
 
 
// Or use the following syntax, which is usually used for updating or creating single records.
 
// The updateOrInsert() method takes two arrays as parameters: the first array contains
// the conditions to find a record, and the second array contains the columns to update.
 
DB::table('users')->updateOrInsert(
   ['departure' => 'Oakland', 'destination' => 'San Diego'],
   ['price' => '99', ]
);

Updating a record in the database

To update one or more records, use the below code. This will update the column votes on the user with an id of 1. In this example, if you leave out the line with ->where(), it will update all the users.

$updated_user = DB::table('users')
              ->where('id', 1)
              ->update(['votes' => 1]);

Deleting one or more records

To delete one or multiple records, use the ->delete() method. This can be used for both deleting multiple records or a single record.

// Delete users with more than 100 votes
DB::table('users')->where('votes', '>', 100)->delete();

There are a few other methods to explore, but those discussed above are the most important methods.

The other methods include things like ->insertGetId() (for auto-incrementing ids), ->increment() and->decrement() for integers and using update on JSON columns. Checkout the docs for those.

Conclusion

As you've seen, getting and retrieving data from the database is not hard. It inly requires a little configuration to get started, and after you've got used to it, it's very easy. The Query Builder is a powerful feature of Laravel, but don't forget that there's an even better way: Laravel Eloquent. In the next article, I'll discuss how Eloquent works and show you how to harness the power of Laravel Eloquent.

As always, if you have any questions, comments, updates or additions, please leave a comment below and I'll change the article and credit you. ⚡️