CODEWITHSUNDEEP
phplaravelpostgresqllaravel-5
alfredjmg
alfredjmg

Reputation: 388

Laravel 5.6: Create schema in database

I am using postgresql. I am creating a database using this command:

<?php

namespace efsystem\Console\Commands;

use Illuminate\Console\Command;

class CreatePostgressDatabase extends Command
{
/**
 * The name and signature of the console command.
 *
 * @var string
 */
protected $signature = 'pgsql:createdb {name?}';

/**
 * The console command description.
 *
 * @var string
 */
protected $description = 'Create a new pgsql database schema based on the database config file';

/**
 * Create a new command instance.
 *
 * @return void
 */
public function __construct()
{
    parent::__construct();
}

/**
 * Execute the console command.
 *
 * @return mixed
 */
public function handle()
{
    $dbname = config('database.connections.pgsql.database');
    $dbuser = config('database.connections.pgsql.username');
    $dbpass = config('database.connections.pgsql.password');
    $dbhost = config('database.connections.pgsql.host');

    try {
                $db = new \PDO("pgsql:host=$dbhost", $dbuser, $dbpass);

                $test = $db->exec("CREATE DATABASE \"$dbname\" WITH TEMPLATE = template0 encoding = 'UTF8' lc_collate='Spanish_Spain.1252' lc_ctype='Spanish_Spain.1252';");
                if($test === false)
                    throw new \Exception($db->errorInfo()[2]);
                $this->info(sprintf('Successfully created %s database', $dbname));
    }
    catch (\Exception $exception) {
                $this->error(sprintf('Failed to create %s database: %s', $dbname, $exception->getMessage()));
    }
}
}

It works fine, but I want to create several schemas in that database too. I tried using db::unprepared in a migration file but it does not worked because to do the migration it needs to have the schemas created previously.

EDIT1: I tried to create the schemas using this migration:

<?php

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

class CreateSchemaAdministracion extends Migration
{
/**
 * Run the migrations.
 *
 * @return void
 */
public function up()
{
    DB::unprepared('
        CREATE SCHEMA administracion
    ');
}

/**
 * Reverse the migrations.
 *
 * @return void
 */
public function down()
{
    DB::unprepared('DROP SCHEMA `administracion`');
}
}

But I got: Invalid schema name: 7 ERROR: No schema has been selected.

Upvotes: 3

Views: 3146

Answers (1)

Kirill Adamuk
Kirill Adamuk

Reputation: 33

This will help:

        public function up()
    {
        DB::connection($this->getConnection())->unprepared("
        SET search_path to public;
        CREATE SCHEMA administracion;
        SET search_path to administracion;
    ");
    }

    public function down()
    {
        DB::connection($this->getConnection())->unprepared("
        DROP SCHEMA IF EXISTS administracion;
");
    }

Upvotes: 1

Related Questions