CODEWITHSUNDEEP
phpdatabaselaraveleloquentmigration
Mohsin
Mohsin

Reputation: 203

How to insert data in different schema other than public schema in database. [LARAVEL]

I want to insert data in the schema i created in laravel migration but i couldn't find a way.

Can anyone please guide ?

public function up()
{
    DB::statement('CREATE SCHEMA IF NOT EXISTS reports');

    Schema::create('reports.campaign_reports', function (Blueprint $table) 
     {
        $table->bigIncrements('id');
        $table->string('campaign')->nullable();
        $table->string('currency')->nullable();
    });
}

This is my model:

class CampaignReport extends Model
{

//    protected $connection = 'schema.reports';

protected $table = 'campaign_reports';

protected $fillable = [
    'campaign',
    'currency'
    ];
}

And this is how i am saving:

CampaignReport::create((array) $dataObject);

I am getting this error:

SQLSTATE[42P01]: Undefined table: 7 ERROR: relation "campaign_reports" does not exist LINE 1: insert into "campaign_reports" ("campaign", "currency",...

enter image description here

Upvotes: 1

Views: 4199

Answers (1)

Kenny Horna
Kenny Horna

Reputation: 14251

Try defining a second database connection in your database config:

/** config/database.php */

// ...

  'connections' => [

        'public_schema' => [
            'driver' => 'pgsql',
            'database' => env('DB_DATABASE'),
            // ...
            'schema' => 'public',
        ],

        'reports_shema' => [
            'driver' => 'pgsql',
            'database' => env('DB_DATABASE'),
            // ...
            'schema' => 'reports',
        ],
    ],

// ...

Then, set the connection in your model (this is useful to do Eloquent/Query Builder operations):

class CampaignReport extends Model
{

    protected $connection = 'reports_schema'; // <----

    protected $table = 'campaign_reports';

    protected $fillable = [
            'campaign',
            'currency'
        ];

    // ...
}

Of course, when you make your migration that needs to be run in a different connection than the default, you have to specify it:

public function up()
{
    DB::statement('CREATE SCHEMA IF NOT EXISTS reports');

    Schema::connection('reports_schema')->create('campaign_reports', function (Blueprint $t)
#           ^^^^^^^^^^^^^^^^^^^^^^^^^^^
     {
        $t->bigIncrements('id');
        $t->string('campaign')->nullable();
        $t->string('currency')->nullable();
    });
}

Btw, update your .env default database key to this:

DB_CONNECTION=public_schema

Upvotes: 5

Related Questions