Laravel (6.0.3) doesn't flush DB::statement commands?

Question

Laravel (6.0.3): in my migration task I want to manually (with a custom sql) create tables and execute restore console command, so I've created the file database/migrations/today_date_create_my_custom_tables.php with up() function:

use Illuminate\Support\Facades\DB;
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\QueryException;

class CreateMyTables extends Migration
{
    public function up()
    {
        // ...
        try {
            \Log::info('create tables...');
            $result = DB::statement($a_lot_of_sql_commands_creating_tables_read_from_file);
            \Log::info('DB::statement executed... but...');
            \Log::info('also if I wait seconds...');
            sleep(3);
            \Log::info('try to call my working custom console command "pg_restore"...');
            $exitCode = Artisan::call('db:restore'); // it call pg_restore server command
            \Log::info('...give error: tables aren\'t created yet.');
            // here I need to do a lot of other stuff (create foreign keys, ecc..),
            // but data must be restored.
        }
        catch (QueryException $e) {
            //...
        }
    }
}

I use postgresql. My custom artisan console command db:restore works. My data are in binary format, so only pg_restore can put them back.

If I check the db (in example with pgAdmin) while in sleep row (after the tables should be created) I've seen that the tables doesn't exist yet. Seems like all DB commands are flushed after the end of the function (or the db connection?), so I see the tables only when the migration finish.

I want to concatenate other stuff in migration command, but I can't if the data didn't restored. Do you have any idea how to immediately flush the DB commands, or something other way to solve the issue? Thanks a lot!

Answer 1

Ok, at the end I've changed strategy and solve the issue implementing a new custom DB connection and using it before calling any terminal commands.

I've created an Helper with code like this:

namespace App\Helpers;

class MyDB {
  /**
   * Custom DB connection
   */
  public static $dbConnection;

  /**
   * Create the connection resource to the DB
   */
  public static function connectDB () {
    $connectionString = 'user='.env('DB_DATABASE');
    $connectionString .= ' password='.env('DB_PASSWORD');
    $connectionString .= ' host='.env('DB_HOST');
    $connectionString .= ' port='.env('DB_PORT');
    $connectionString .= ' dbname='.env('DB_DATABASE');
    MyDB::$dbConnection = pg_pconnect($connectionString);
  }

  /**
   * Execute a Statement query with the custom DB connection.
   */
  public static function executeStatement ($query) {
    if (is_null(MyDB::$dbConnection)) {
      MyDB::connectDB();
    }
    $resource = pg_query(MyDB::$dbConnection, $query);
    if ($resource === false) {
      return false;
    }
    return true;
  }
}