How to use Stored Procedures using Laravel?

Using stored procedures has advantages like reducing network traffic, centralized business logic, and a more secure database.

How to use them within Laravel?

We are going to take a look at one of many options on how to do use Stored Procedures using MySQL and Laravel. We are using MySQL for simplicity, of course, you can use other Database engines, what would it change is the syntax of calling and creating your Stored Procedures.

1. Migrations. We want to keep track of our Stored Procedures as we usually do when creating tables or making changes to our database.
2. Execute Stored Procedures with Parameters for processing inserts and updates.
3. Execute Stored Procedures returning data.

1. Migrations

class CreateSuperInsertProcedure extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {

        DB::unprepared('DROP PROCEDURE IF EXISTS super_process;');
        -- run queries, inserts, updates, or any code that you need
        DB::unprepared("CREATE PROCEDURE super_process (first_value int, other_value int)
        BEGIN
        /*
                do super complex calculations and probably more super simple stuff
        */
        END");
    }

    public function down() 
    {
        DB::unprepared('DROP PROCEDURE IF EXISTS super_process;');
    }

Stored Procedures

For this, we are going to use two methods: statement and select. The first one is when we need to run a process, insert or update the information to our databases. The second one is for those cases when we want to retrieve some information from it.

2. Stored Procedures with Parameter

Statment

$result = DB::statement('call super_process',[1,2]);

3. Stored Procedures returning data

Select

$data = DB::select('call super_select',[1]);

Example Project

Migrations

Super Select Procedure

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

class CreateSuperSelectProcedure extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        DB::unprepared('DROP PROCEDURE IF EXISTS super_select_procedure;');

        DB::unprepared("CREATE PROCEDURE super_select_procedure(id int)
        BEGIN
            SELECT * FROM posts WHERE posts.id = id;
        END");
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        DB::unprepared('DROP PROCEDURE IF EXISTS super_select_procedure;');
    }
}

Super Insert Procedure

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

class CreateSuperInsertProcedure extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        DB::unprepared('DROP PROCEDURE IF EXISTS super_insert_procedure;');

        DB::unprepared('CREATE PROCEDURE super_insert_procedure(title varchar(50), content text)
        BEGIN
            INSERT INTO posts (title, content, created_at, updated_at) VALUES(title, content,NOW(),NOW());
        END');
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        DB::unprepared('DROP PROCEDURE IF EXISTS super_insert_procedure;');
    }
}

Calling or executing Stored Procedures

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;

class Post extends Model
{
    use HasFactory;

    public static function superInsert($title, $content)
    {
        return DB::statement('call super_insert_procedure(?,?)', [$title,$content]);
    }

    public static function superSelect($id)
    {
        return DB::select('call super_select_procedure(?)', [$id]) ;
    }
}

Usage

For this example, you can use the superInsert and superSelect like this.

Post::superInsert('super title','Nibh aliquet in varius per dis');
// true
Post::superSelect(1);
/*
[
     {
       +"id": 7,
       +"title": "super title",
       +"content": "Nibh aliquet in varius per dis",
       +"created_at": "2021-09-02 13:13:01",
       +"updated_at": "2021-09-02 13:13:01",
     },
   ]
*/

You can find the code in this Github repository Laravel using Stored Procedures