How to insert new record with Laravel Query Builder

Introduction

Laravel is a powerful PHP framework designed for web developers who need a simple and elegant toolkit to create full-featured web applications. One of its core features is the Fluent Query Builder which makes database interactions not just easy but also enjoyable. In this tutorial, we’re diving into how to use Laravel’s Query Builder to insert new records into a database.

This guide assumes you have a basic understanding of PHP and Laravel, as well as a Laravel application up and running with a configured database connection.

Getting Started with Query Builder

Before we start inserting records, it’s helpful to know that the Laravel Query Builder works with the DB facade. This facade offers you the full power of Laravel’s Query Builder without needing to use Eloquent ORM. Let’s see how this works.

Insert a Simple Record

DB::table('users')->insert([
    'name' => 'John Doe',
    'email' => '[email protected]',
    'password' => bcrypt('secret')
]);

In this example, we’re inserting a new user into the ‘users’ table with a name, email, and encrypted password. Remember to always encrypt sensitive data like passwords before storing them in the database.

Insert Multiple Records

DB::table('users')->insert([
    ['name' => 'Jane Doe', 'email' => '[email protected]', 'password' => bcrypt('secret')],
    ['name' => 'Bob Smith', 'email' => '[email protected]', 'password' => bcrypt('secret')] ]);

When you need to insert multiple records at once, the insert method takes an array of arrays, where each sub-array represents a record that needs to be inserted into the table.

More Advanced Insertions

Laravel also provides ways to handle more complex insertions.

Inserting and Getting the Inserted ID

$id = DB::table('users')->insertGetId([
    'name' => 'Sally Roe',
    'email' => '[email protected]',
    'password' => bcrypt('secret')
]);

If you’re inserting a record into a table with an auto-incrementing ID, you can use insertGetId to insert the record and retrieve the ID in one go.

Conditional Inserts Using upsert Method

DB::table('users')->upsert([
    ['email' => '[email protected]', 'votes' => 1],
    ['email' => '[email protected]', 'votes' => 1]
], ['email'], ['votes']);

Starting from Laravel 8, you can use the upsert method to perform an “insert” operation that ignores duplicate records. The first argument is the values to insert or update, the second argument is the columns that should be unique, and the third array is the columns that should be updated if a matching record already exists.

Dealing with Timestamps

Laravel’s Query Builder can automatically handle timestamps if you set up your table migrations accordingly. However, you can also do it manually:

DB::table('users')->insert([
    'name' => 'Tim Cook',
    'email' => '[email protected]',
    'password' => bcrypt('secret'),
    'created_at' => now(),
    'updated_at' => now()
]);

Here, we explicitly set the created_at and updated_at fields using the now() helper function provided by Laravel.

Conclusion

In this tutorial, we’ve covered how to use the Laravel Query Builder to insert new records into your database. Starting from essential insert operations to more advanced techniques, Laravel provides a clear and fluent interface to carry out database transactions efficiently and safely. The key to mastering inserts with Laravel’s Query Builder is understanding the underlying SQL and practicing these expressions as often as you can.