5

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:

  1. 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.

  2. 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.

  3. 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?

James4645
  • 105
  • 6
  • 1
    You could do this, I'd like to know why though, and what is your migration strategy down the road, say you have 1000 tenants, how're you going to add a column to a specific table in each database? (I'm asking because I wonder if you've thought this through. and if so, I am interested in your strategy) – Tropus Jul 13 '21 at 13:57
  • 1
    @Tropus A database per tenant strategy is the most scalable multitenancy option. See [this Laracon talk](https://tomschlick.com/laracon-2017-multi-tenancy-talk/) by Tom Schlick on multitenancy strategies. I'm using the [spatie/laravel-multitenancy](https://github.com/spatie/laravel-multitenancy) package. It handles the core logic to make a tenant "active" for a given request. It dynamically adjusts the 'DB_NAME' in a tenant connection. Any tenant specific models use that connection. But I still have to manually handle provisioning/deprovisioning tenants with this package. – James4645 Jul 13 '21 at 14:59
  • 1
    @Tropus As for migrations, the package includes a way to run any artisan command such as "migrate" as a specific tenant, or you can pass in a list of tenant_id's and it will cycle though all of them. I would run multiple instances of the migrate command each with a distinct list of tenant_id's to migrate. This plus planning ahead on migrations should work fine. – James4645 Jul 13 '21 at 15:13

1 Answers1

1

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.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Coming from MySQL I didn't even realize that was just an empty db. I thought that was equivalent to the "mysql" database and didn't want to touch it. Thanks for the tip. Works like a charm. Part of me doesn't like having to select a DB I'm not really using but since its just an empty default it's not a horrible option. – James4645 Jul 13 '21 at 13:24