Data accuracy and consistency issues can lead to everything from minor inconveniences to major corporate concerns. It’s critical to build code that safely stores, changes, and erases data in your database.
Enter Laravel database transactions.
Database transactions are an effective approach to ensuring data integrity. Laravel simplifies these transactions across a wide range of databases.
But what exactly are they? How can you work them out in Laravel?
By the end of this extensive guide, you’ll have learned all about database transactions in Laravel and how to use them effectively in your project.
What Are Laravel Database Transactions?
Before we jump into the technical side of things, let’s first understand what Laravel database transactions are and how you can benefit from them.
A database transaction is a set of operations that you can carry out securely within the database structure of your application, such as SQL queries to modify data (e.g. updates, deletions, and insertions).
At any point, you can decide to roll back all the transaction’s queries. In addition, any queries you make will be treated as a single action by the database.
Let’s look at an example of this.
Assume we have an app that enables users to create accounts. Naturally, there can be one or many users affiliated with each account. If this app simultaneously generates an account and the first user, you’ll have to deal with what happens if an account is generated properly, but the user isn’t.
Have a look at this sample code:
// Create Account
$newAcct = Account::create([
'accountname' => Input::get('accountname'),
]);
// Create User
$newUser = User::create([
'username' => Input::get('username'),
'account_id' => $newAcct->id,
]);There are two scenarios here that can cause unpleasant issues:
- Account isn’t generated
- Failure to create a user
Let’s consider the latter situation.
Having an account with no available users leads to data inconsistency in the database. To resolve this, you can either go through the daunting task of coding around it or save a lot of code or simply wrap it up in a transaction to get things done quickly.
While database transactions are present in most SQL databases, they vary mainly in their implementation and efficiency. Popular systems such as MySQL, SQLite, PostgreSQL, and Oracle support transactions, so you shouldn’t have trouble deploying your preferred SQL database.
Migrations
Migration is a crucial functionality in Laravel that allows you to build a table in your database, make modifications, and share the database schema of the application. You can use Laravel migration to edit tables by adding new columns or removing existing ones.
Say you’re discussing ideas with a team and need to make adjustments to the table. The SQL file must be shared and imported by someone on the team. It’s possible that they forget to import the SQL file, causing problems in the application’s operation.
This is where Laravel migration comes to the rescue. You can add a new column to your database or delete entries without affecting the ones that are already there.
Seeders
Seeding is a tool provided by Laravel for developers to facilitate testing different data types, fix bugs, and tune performance. You can add multiple rows of dummy data automatically to your database table via the database seeder in a single command.
As a result, you can start over with a new database and sample values instead of having to input them manually each time the database is restored.
Options for Laravel Database Transactions
Laravel offers different tools to manage your data like Adminer. For database transactions, there are three methods on the database end to start a transaction manually and have full control over transaction management.
Many users find these options more flexible to define exactly when a transaction should be committed or rolled back:
- Create a transaction: Use the
DB::beginTransaction();command to start a transaction. - Roll back a transaction: Use the
DB::rollBack();command if you want to make changes or undo actions. - Commit a transaction: If everything went as planned, use the
DB::commit();command.
Always remember to conclude every open transaction with either a commit or a rollback action, particularly loops. Otherwise, this manual method will go out of sync and your records won’t be updated.
How To Work With Your Laravel Database
Migrations and seeders, as previously mentioned, are sophisticated solutions designed for Laravel developers to quickly deploy, delete, and restore an application’s database by reducing disparities. It comes in handy, especially when more than one developer is working on the same app.
This section will show you how to use migrations and seeders easily with your Laravel database using artisan commands.
Prerequisites
Here’s what you need to get started:
- A non-root user with sudo permissions on an Ubuntu 18.04 local computer or development server. It’s a good idea to have an active firewall setup if you’re using a remote server.
- LEMP installed on your machine. You can choose to install Docker and Docker Compose to run your application if you feel more comfortable working with them.
Our DevKinsta tool is powered by Docker and used by 57,000++ developers and designers to easily create and develop single or multi WordPress sites.
There are other web development tools that you can use depending on your skills and coding needs.
Laravel Migrations
There are two methods in a migration class: up and down. The up method is used to create new tables, indexes, or columns in your database. The down method should undo the up method’s effects.
You can use the Laravel schema builder to build and edit tables freely in each of these methods. For instance, this migration generates a flights table:
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
class CreateFlightsTable extends Migration {
/**
* Run the migrations.
*
* @return void
*/
public function up() {
Schema::create('flights', function (Blueprint $table) {
$table->bigIncrements('id');
$table->string('name');
$table->string('airline');
$table->timestamps();
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down() {
Schema::drop('flights');
}Keep in mind that the make:migration commands need to clarify the table’s name. So make sure that the table_name matches what you want.
You can use the --table and --create options to specify the table’s name and whether the migration will create a new table as shown below:
php artisan make:migration create_users_table --create=usersphp artisan make:migration add_votes_to_users_table --table=usersYour database/migrations directory will now include the new migration. Each migration file name includes a timestamp, which Laravel uses to determine the migration order.
You also have the option to define a --path, which should be related to your installation’s root directory. Use the following command:
php artisan migrate:make foo --path=app/migrationsRunning Migrations
There are some helpful commands that you can take advantage of when running migrations. Let’s go over a few of them:
php artisan migrate: This command publishes all of your schema to the database. It also generates a table in the database.php artisan migrate --path=app/foo/migrations: This command runs all migrations under a directory. If you receive an error message “Nothing to migrate”, run thephp artisan migrate --path=database/migrations/foocommand without the app directory.php artisan migrate --package=vendor/package: Use this command if you want to run migrations for a package.
Sometimes you might get a “Class not found” error while executing migrations. If you do, run the composer dump-autoload command.
Some migrations can be hazardous and may result in the loss of your data. Therefore, Laravel will prompt you to confirm executing commands to safeguard your data.
If you don’t wish to be prompted, use --force flag to force commands as follows:
php artisan migrate --forceRolling Back Migrations
Utilize the rollback command when you need to reverse the last migration batch as follows:
php artisan migrate:rollbackHere are a few more roll back commands you can use:
php artisan migrate:reset: This command reverses all migrations, not just the last operation.php artisan migrate:fresh: Use this command when you want a new installation of your database. It removes all existing tables and executes themigrationcommand.php artisan migrate:refresh: This is a two-in-one command that executes both the:rollback and migratecommands.php artisan migrate:fresh --seed: This runs themigrate:freshcommand before seeding the database. When you install the app on a new host, you can utilize this command to seed (i.e. upload data into) the database.
Laravel Seeding
A seeder is a class that creates and places data samples (seeds) into a database. Laravel provides a simple technique for seeding your database with test data using seed classes in the database/seeds directory.
You’re free to choose the name of your seed classes. But we advise you to follow a clear naming pattern of your choice, like UsersTableSeeder. Afterwards, a DatabaseSeeder class is created for you by default.
Here’s an example of a database seed class in Laravel:
class DatabaseSeeder extends Seeder {
public function run() {
$this->call('UserTableSeeder');
$this->command->info('User table seeded!');
}
}
class UserTableSeeder extends Seeder {
public function run() {
DB::table('users')->delete();
User::create(array('email' => '[email protected]'));
}
}Creating a Seeder
Generating seeders is as easy as pie. You could do it with your eyes shut (but please don’t).
Execute the make:seeder artisan command to create a seeder. Now the database/seeds directory will include all the seeders produced by the framework:
php artisan make:seeder UsersTableSeederThe default method of a seeder class is run. The process takes place when you apply the db:seed artisan command. You can put data into your database in whatever way you prefer using the run function. Moreover, it’s entirely possible for you to use Eloquent model factories or Query Builder to insert data manually.
Regardless, you should keep in mind that during database seeding, mass assignment protection is deactivated automatically.
Here, we’ll make modifications to the basic DatabaseSeeder class and add a database insert statement to the run method:
<?php
use Illuminate\Database\Seeder;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Hash;
use Illuminate\Support\Str;
class DatabaseSeeder extends Seeder {
/**
* Run the database seeds.
*
* @return void
*/
public function run() {
DB::table('users')->insert([
'name' => Str::random(10),
'email' => Str::random(10).'@gmail.com',
'password' => Hash::make('password'),
]);
}
}If you want to type-hint any dependencies within the code of the run method, the Laravel service container will resolve them automatically.
Further, you can use the call function to execute different seed classes from this class, allowing you to customize the seeding order. You may split your database seeding over different files, ensuring that no single seeder class expands excessively.
Enter the name of the seeder class you want to use as shown below:
/**
* Run the database seeds.
*
* @return void
*/
public function run() {
$this->call([
UsersTableSeeder::class,
PostsTableSeeder::class,
CommentsTableSeeder::class,
]);
}Running Seeders
After generating your seeder, you might need to use the dump-autoload command to recreate Composer’s autoloader:
composer dump-autoloadNext, you need to execute the db:seed artisan command to seed your database:
php artisan db:seedThis command executes the DatabaseSeeder class by proxy, which can be used to run other seed classes. You can, however, use the --class parameter to execute a particular seeder class separately as follows:
php artisan db:seed --class=UserTableSeederWhat if you wish to recreate your database from scratch, including removing all tables and running all your migrations once again? In this case, use the migrate:fresh command to seed your database.
php artisan migrate:fresh --seedAs it’s the case with migrations, some seeding processes may lead to data loss or unwanted changes. For this reason, you’ll be prompted for approval before the seeders are executed to protect you from executing seeding commands on your primary database.
If you’re confident enough and don’t want to be interrupted by that security step, use the --force flag below:
php artisan db:seed --force5 More Ways To Use Raw Database Queries in Laravel
While Laravel provides handy tools such as Eloquent and Query Builder, you can still perform raw queries using SQL. We’ve rounded up five different ways to do so.
But before you start, you should know that raw queries aren’t secured automatically, making them a risky approach. Therefore, if you’re giving any parameters to the query, make sure they’re in the right format and have the right values, such as a number rather than a text.
Avg/Sum/Count Calculations
You can use a raw query if you want to create GROUP BY (), then utilize MySQL aggregate functions such as Count(), SUM(), AVG(), MIN(), or MAX() as shown in the following example:
$users = DB::table('users')
->selectRaw('count(*) as user_count, status')
->where('status', '<>', 1)
->groupBy('status')
->get();It’s even possible to do both count() and avg() in the same SQL query:
$salaries = DB::table('salaries')
->selectRaw('companies.name as company_name, avg(salary) as avg_salary, count(*) as people_count')
->join('companies', 'salaries.company_id', '=', 'companies.id')
->groupBy('companies.id')
->orderByDesc('avg_salary')
->get();Filtering Years
In case you need to perform SQL calculations within GROUP BY or ORDER BY, you can use groupByRaw() and orderByRaw() queries. After grouping, you can also utilize the where statement by using a having SQL query with havingRaw ().
For instance, this command below shows how to group a date/time field by year:
$results = User::selectRaw('YEAR(birth_date) as year, COUNT(id) as amount')
->groupByRaw('YEAR(birth_date)')
->havingRaw('YEAR(birth_date) > 2000')
->orderByRaw('YEAR(birth_date)')
->get();Calculating a Single Field (Sub-Query)
Suppose you want to calculate one column from another and return the result in an SQL query. How can you get it done?
Let’s take a look:
$products = Product::select('id', 'name')
->selectRaw('price - discount_price AS discount')
->get();Here’s another example of an SQL CASE statement:
$users = DB::table('users')
->select('name', 'surname')
->selectRaw("(CASE WHEN (gender = 1) THEN 'M' ELSE 'F' END) as gender_text")
->get();Convert Older SQL
It’s a common scenario to have an SQL statement that requires converting to Eloquent or Query Builder, especially from an old project you worked on.
Well, you don’t really need to do that. Instead, you can simply use the DB::select() statement as shown:
$results = DB::select('select * from users where id=?', [1]);Execute Query Without Results
DB::statement can run an SQL query, getting no results like INSERT or UPDATE with no variables.
This is frequently used in database migration when a table structure changes and old data must be changed with the new one:
DB::statement('UPDATE users SET role_id = 1 WHERE role_id IS NULL AND YEAR(created_at) > 2020');In addition, DB::statement() can run any SQL query with schema that’s not limited to values or columns. Here’s an example:
DB::statement('DROP TABLE users');
DB::statement('ALTER TABLE projects AUTO_INCREMENT=123');Summary
By now, you should have a deep understanding of database transactions in Laravel and how to implement them. Not only do they aid in data integrity, but they also help optimize Laravel performance and make your development process easier.
