laravel 9 how to create new database by DB:raw

Hello

Laravel 9.19, Mysql 5.7.33, Env: windows 10.

I tried to create database for multitenat environment. I have already looking fr hour solution for this.

this is best what I have found:

   $created_databse = DB::statement(DB::raw('CREATE DATABASE IF NOT EXISTS '. $schemaName));

Process is following:

purge exist connection:
```
 DB::purge('landlord');
```
reconnect serverl level as there is no databse connection availabel as I am creating it.
```
$conn = DB::reconnect('mysqlserver');
```

create new database

$schemaName = $tenant->databse;
$created_databse = DB::statement(DB::raw('CREATE DATABASE IF NOT EXISTS '. $schemaName));

Looks like I can talk with mysql server as error is:

"SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax.

--> what is the correct way to do it with laravel 9?

Thanks Mika.

SilenceBringer

3 years ago

Level 55

@mikromike maybe typo here? (should be database instead of databse?)

$schemaName = $tenant->databse;

Also some notes: you do not DB::raw inside statement and it doesn't return any value https://laravel.com/docs/9.x/database#running-a-general-statement

mikromike

3 years ago

Level 27

@SilenceBringer thanks, noted typo.

SilenceBringer

3 years ago

Level 55

@mikromike you're welcome. Mark response as the best answer if it solved your problem

mikromike

3 years ago

Level 27

changed line to :

   DB::raw('CREATE DATABASE IF NOT EXISTS '.$schemaName);

checked in mysqlworkbench sql : CREATE DATABASE IF NOT EXISTS Foo; and CLI.

both works.

still not in DB::raw();

Tray2

3 years ago

Level 73

You really should use a single database for this, it will save you tons of headaches in the long run.

mikromike

3 years ago

Level 27

@Tray2, thansk, I have used three diffrent databses al the time.

Problem was syntax.

now works.

mikromike

3 years ago

Best Answer

Level 27

     public function createDatabase()
    {

      $tenants = $this->get();

    foreach ($tenants as $tenant) {
        $schemaName = $tenant->database;

    try{
         DB::purge('landlord');
        $conn = DB::reconnect('mysqlserver');

       if(!$conn ) {
           return false;
         }

      $create_database = DB::raw('CREATE DATABASE IF NOT EXISTS ' .$schemaName);
       DB::statement($create_database);

    //   $tables = DB::select("SELECT table_name FROM information_schema.tables WHERE 
    //  table_schema = '{$new_db_name}'");
  } // end of try block 
   catch(\Exception $e){       
       return false;
    }
    } // end of foreach 
   }

Finally, works.

Please or to participate in this conversation.