0

I'm building an enviroment that needs two databases on one PGSQL database server for Laravel, the production one is named laravel, and the testing/dev one would be named laravel_test, is there any way to create the new database using database migrations in Laravel? I've been trying things like

$dbName = config("database.connections.pgsql-test.database");
// Create the second database
DB::statement("CREATE DATABASE $dbName");

but end up getting an error that states this:

SQLSTATE[25001]: Active sql transaction: 7 ERROR:  CREATE DATABASE cannot run inside a transaction block (Connection: pgsql, SQL: CREATE DATABASE laravel_test)

I'm inexperienced when it comes to database specific operations like this, is there a way I could migrate a new Database using this primary (pgsql) DB connection: (config/database.php)

'pgsql' => [
     'driver' => 'pgsql',
     'url' => env('DATABASE_URL'),
     'host' => env('DB_HOST', '127.0.0.1'),
     'port' => env('DB_PORT', '5432'),
     'database' => env('DB_DATABASE', 'forge'),
     'username' => env('DB_USERNAME', 'forge'),
     'password' => env('DB_PASSWORD', ''),
     'charset' => 'utf8',
     'prefix' => '',
     'prefix_indexes' => true,
     'search_path' => 'public',
     'sslmode' => 'prefer',
],

so that I could use this connection (pgsql-test):

'pgsql-test' => [
     'driver' => 'pgsql',
     'url' => env('DATABASE_URL'),
     'host' => env('DB_HOST', '127.0.0.1'),
     'port' => env('DB_PORT', '5432'),
     'database' => env('DB_DATABASE', 'forge') . "_test",
     'username' => env('DB_USERNAME', 'forge'),
     'password' => env('DB_PASSWORD', ''),
     'charset' => 'utf8',
     'prefix' => '',
     'prefix_indexes' => true,
     'search_path' => 'public',
     'sslmode' => 'prefer',
],

without manually creating a database?

Erikas
  • 45
  • 6
  • check this out -> https://stackoverflow.com/questions/36914573/how-to-create-new-empty-database-with-laravel-over-model/59602936#59602936 – Doelmi Jun 15 '23 at 08:25
  • 1
    Why would a single environment need both production and testing database? I would recommend to use two environments. – Ron van der Heijden Jun 15 '23 at 11:33

1 Answers1

0

Simple answer. No. You can create a command like one of the comments sais or create it manually. Also when running migrations first time, you may be prompted if you want the schema to be created.

marius-ciclistu
  • 448
  • 4
  • 14
  • *"Also when running migrations first time, you may be prompted if you want the **schema** to be created."* - Really? I've never seen that... Are you thinking of the `migrations` table? If there isn't a **schema** (or database, terminology differs between RDBMS's), I thought you get an error, since it can't find the schema when it tries to check if the `migrations` table exists. – Tim Lewis Jun 15 '23 at 19:27
  • I said may because a devops coleague of mine said he was prompted like that when he deployed first time a laravel 9 or 10. – marius-ciclistu Jun 15 '23 at 19:30
  • It's fine, and I'm not suggesting you're wrong or anything, that's just not something I've seen before, but I have seen it create the `migrations` table if it doesn't exist. I wonder if it's a new thing in `9.x` or `10.x`. – Tim Lewis Jun 15 '23 at 19:31