I have a little bit of a chicken and egg problem here. I am building a multitenant app in Laravel where each tenant has their own database on a Postgrsql server. So I have a job to create a new tenant that calls:
Schema::connection($this->tenant->getDatabaseConnection())->createDatabase($this->tenant->getDatabaseName())
So $this->tenant->getDatabaseConnection() chooses a tenant DB server to add the tenant to and returns the that's servers connection name. Then $this->tenant->getDatabaseName() will return the string of the DB name for that tenant.
I got one small problem. This throws an exception:
SQLSTATE[08006] [7] FATAL: database "port=5432" does not exist (SQL: create database "local_tenant_(id)" encoding "utf8")
This is because the connection by default had the DB name as null. And when a user logs into their tenant we dynamically inject getDatabaseName() from the Tenant model into that.
But I can't do that here because the tenant DB does not yet exist. So here are the options I see:
choose a random database of an existing tenant on the server to "act as" when creating the new tenant's DB. I don't really like having to hijack another tenant's DB even temporarily like that even though this should have 0 side affects to that tenant. And if it's the first tenant being provisioned on the server, there's no existing DB to use, which makes this a no-go in my book on it's own.
I could just use the DB providers API to create the tenant DB. This would be by far the simplest but would break in local and testing environments and therefore I want to avoid.
I could just bypass Laravel and establish a direct PDO connection to the database for this, but I would say that's my last resort if I can't find something better.
Technically you don't have to have a DB selected to use the create DB command. That's an idiosyncrasy of Laravel's Schema Builder that it tries to select a DB before calling the method (in this case CreatedDatabase). Is there a way around this, so I can create the DB without Laravel first trying to select a null DB?
The correct solution is to connect to the postgres database in order to run CREATE DATABASE. The postgres database is always there (unless somebody dropped it), and that is what it is designed for.