A database per tenant for Laravel on serverless

A multi-tenant Laravel app with a database per customer makes a new database at the moment a customer signs up. On a server with PostgreSQL that is one CREATE DATABASE, and packages such as stancl/tenancy do it for you. On Scaleway's Serverless SQL Database, that statement is refused.

This post explains why, and how a Laravel app on Brewless gets a Serverless SQL Database per tenant anyway: it asks Brewless with a signed request, and Brewless makes the database in your own Scaleway project.

Can a Laravel app create a database per tenant on Serverless SQL?#

Yes, but not with CREATE DATABASE. On a Serverless SQL Database each database is a resource of its own, with its own address, made through the Scaleway API, and your application has no key for that API. With databases and a prefix in brewless.yml, the app asks Brewless for the database with a signed request, and Brewless makes it.

That also suits the pricing. Each tenant gets a database that sleeps on its own when that tenant is quiet, instead of one large database that is awake whenever any tenant is busy.

Turn it on in brewless.yml#

environments:
  production:
    region: fr-par
    database: shop-main
    queue: sqs
    databases:
      prefix: shop-

Three rules apply:

• The environment's own database has to be a Serverless SQL Database. A Managed Database is refused. Serverless SQL is only in Paris so far.
• The prefix starts with a lowercase letter, has lowercase letters, digits and dashes, ends in a dash and is at most 32 characters. A dash, not an underscore: Scaleway accepts only letters, digits and dashes in the name of a Serverless SQL Database, and the database inside has the same name.
• Every database the app asks for has a name that starts with the prefix.

The next deploy gives the app what it needs to sign requests. Those variables are added to the environment's own, so the variables have to be kept through Brewless: push them once with brewless env:push.

What the application gets#

Every container of the environment gets three variables, the web container too, because an app may make a customer's database during a sign-up request:

The key is kept in your Secret Manager as <application>-<environment>-databases-key. Brewless keeps it too, encrypted, to check signatures. It is never shown or logged.

A new tenant database signs in exactly like the environment's own: with DB_USERNAME and DB_PASSWORD, the identity Brewless made for the environment, which may use every Serverless SQL Database of the project. Only the host differs.

Asking for a database from Laravel#

brewless/laravel 0.7.1 or later reads the three variables and signs the requests for you. Use 0.7.1: 0.7.0 refuses a name with a dash, and every name here has one.

use Brewless\Laravel\Databases\DatabaseClient;

$client = resolve(DatabaseClient::class);

$database = $client->create('shop-'.$tenant->id);
$database = $client->waitUntilReady($database->name);

// $database->host and $database->port, with DB_USERNAME and DB_PASSWORD

Making a database takes a while at Scaleway, so do this in a queued job rather than while the customer waits. Asking for the same name again makes nothing twice, so a job that is retried is safe.

Fitting it into stancl/tenancy#

stancl/tenancy makes and removes tenant databases in jobs it runs on its tenant events, through a database manager that speaks SQL. On Serverless SQL, that is the step to replace:

1. In your tenancy service provider, take the job that creates the database out of the pipeline for a new tenant, and put a job of your own in its place that calls DatabaseClient::create() and waitUntilReady().
2. Store the host and port Brewless returns on the tenant, and use them, with the database name, for the tenant's connection. The username and password stay those of the environment.
3. Keep the migration job after it. Making the tables is your app's work: Brewless never connects to a tenant database.
4. For a deleted tenant, replace the job that drops the database with one that sends the DELETE below, or leave it out if you remove databases by hand.

In raw SQL, quote the name: PostgreSQL reads a bare dash as a minus, so it is "shop-1", not shop-1.

The requests underneath#

Without the package, any HTTP client will do. Each request carries three headers:

The signature covers the method, the path, the timestamp and the SHA-256 of the body, with the key as it stands in the variable, not decoded from base64:

hash_hmac('sha256', $method."\n".$path."\n".$timestamp."\n".hash('sha256', $body), $key);

PUT and GET answer with name, status, host and port. The status is creating, ready, deleting or error, and host and port stay null until Scaleway names them. A request more than five minutes off, with a wrong signature, or for an environment without databases gets a 401 with the same sentence, whatever was wrong. The full description is in Databases per tenant.

Limits worth knowing#

• A name is lowercase letters, digits and dashes, at most 63 characters, and starts with the prefix. An underscore gets a 422 before Scaleway is asked.
• The environment's own database is never a tenant database: its name is refused, and so is a database of that name that Brewless did not make for this environment.
• GET and DELETE only reach databases Brewless made through this route for this environment. Anything else is a 404.
• A new database scales between the same vCPUs as the environment's own database.
• Each environment may make 120 requests a minute. When Scaleway cannot be reached or refuses, the answer is 503: ask again.
• Every database made and removed is in the audit trail. Each is a resource of the environment, so the console lists it and an export names it.

Tip

Each tenant database sleeps on its own, five minutes after its last query. A scheduled task that loops over every tenant every minute keeps all of them awake. Run such tasks less often, or per tenant when there is work.

Removing tenant databases#

Brewless takes no hidden destructive steps on your resources, and that holds here too:

• DELETE removes a database at Scaleway now, with all its data. It is the only way Brewless removes one, and only your app sends it.
• databases: false takes the prefix away. The app can ask for nothing more, but the databases it made stay at Scaleway and stay recorded.
• Another prefix is refused while databases of the current one are recorded, because they would no longer be reachable by name.
• The environment's own database can never be removed through this route.

Warning

DELETE does not ask twice. Put the call behind the same checks as the rest of your tenant removal, and take a backup first if the data may be needed again.

Summary#

1. Use a Serverless SQL Database as the environment's own database, in Paris.
2. Add databases.prefix to brewless.yml, push your variables once, and deploy.
3. Require brewless/laravel 0.7.1 or later and call DatabaseClient from a queued job.
4. Replace the create and delete jobs of stancl/tenancy, and keep its migrations.
5. Remove a tenant database only with an explicit DELETE.

To decide whether serverless databases suit your app at all, read Serverless SQL or Managed PostgreSQL for Laravel.