# A database per tenant for Laravel on serverless

How a multi-tenant Laravel app gets a Serverless SQL Database per tenant at Scaleway, by asking Brewless with a signed request instead of CREATE DATABASE.

## In short

- On a Scaleway Serverless SQL Database, CREATE DATABASE over SQL is never allowed, because each database is a resource of its own made through the Scaleway API.
- With databases and a prefix in brewless.yml, a running Laravel app asks Brewless for a new Serverless SQL Database per tenant with a signed request.
- Brewless makes the database in your own Scaleway project and never connects to it; making the tables is the work of your application.
- Every tenant database signs in with the same identity as the environment's own database, and only the host differs.
- Removing a tenant database at Scaleway is always an explicit DELETE from your app; switching the feature off or changing the file never removes one.

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

```yaml 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:

| Variable | What it is |
| --- | --- |
| `BREWLESS_API_URL` | The address of your organisation at Brewless, such as `https://acme.brewless.eu` |
| `BREWLESS_ENVIRONMENT` | The id of the environment |
| `BREWLESS_DATABASES_KEY` | A random key of 32 bytes, base64, made once per environment |

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.

```php app/Actions/CreateTenantDatabase.php
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:

| Header | Value |
| --- | --- |
| `X-Brewless-Environment` | `BREWLESS_ENVIRONMENT` |
| `X-Brewless-Timestamp` | The time in whole seconds since 1970 |
| `X-Brewless-Signature` | An HMAC-SHA256 in hexadecimal |

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:

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

| Request | Answer |
| --- | --- |
| `PUT /api/runtime/databases/{name}` | Makes the database when it does not exist. `202` while it is being made, `200` once it is there |
| `GET /api/runtime/databases/{name}` | Where it stands, `200` |
| `DELETE /api/runtime/databases/{name}` | Removes it at Scaleway now, with everything in it. `202` |

`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](https://docs.brewless.eu/running/tenant-databases).

## 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](/blog/serverless-sql-or-managed-postgresql).

## Frequently asked questions

### Why can stancl/tenancy not create a database on Scaleway Serverless SQL?

Its database managers run CREATE DATABASE over SQL, and Serverless SQL Database never allows that. Each database is a separate Scaleway resource with its own address, made through the Scaleway API. With Brewless, your app asks Brewless to make it instead.

### Which version of brewless/laravel do I need for tenant databases?

brewless/laravel 0.7.1 or later. Version 0.7.0 refuses a database name with a dash, and the prefix has to end in a dash because Scaleway only accepts letters, digits and dashes in the name.

### Does each tenant database need its own username and password?

No. A tenant database signs in exactly like the environment's own, with DB_USERNAME and DB_PASSWORD. That identity may use every Serverless SQL Database of the project, so only the host and port differ per tenant.

### What happens to tenant databases when I set databases to false?

The app can no longer ask for new ones. The databases it made stay at Scaleway and stay recorded at Brewless. Removing one is only done by a DELETE request from your app or by hand at Scaleway.

### Can a Managed Database be used for databases per tenant?

Not through this feature. The environment's own database has to be a Serverless SQL Database, and a Managed Database is refused. Every tenant database Brewless makes is a Serverless SQL Database.

Source: https://test.brewless.eu/blog/laravel-database-per-tenant-serverless