One Postgres cluster, many apps

Yo, I heard you liked databases, so we put logical databases in your database cluster so you can database while you database

The goal

Say we want to run multiple applications with distinct schemas. Let's start with two classic side project app ideas: a blog and a to-do list.

PlanetScale Postgres clusters start at $5/month, but this doesn't mean you have to create a new one for every new app idea. A single database cluster can contain many logical databases.

However, orchestrating logical databases and the roles that connect to them gets complicated fast. We can automate away the complexity, but let's first understand how they work.

Definitions

• Database cluster: The shared Postgres instance (compute, storage, connection endpoint) of a single branch. Apps connect to the cluster via a connection string.
• Branch: An isolated Postgres environment inside that cluster (e.g. main). Connection credentials are scoped to a branch. Role usernames include the branch's ID (e.g. <role-id>.<branch-id>).
• Logical database: A named Postgres database within a cluster (e.g. blog, todo) with its own schema and data. Logical databases share the branch's compute, storage, and connection limits.
• Role: A unit of access control: a username, password, and permissions.

About logical databases

A new managed Postgres instance likely already contains a few logical databases, primarily for administration. In a fresh PlanetScale Postgres database, you may see multiple logical databases owned by pscale_admin and pscale_superuser.

If you're using pgcli, you can list all logical databases with \l, the output should look something like this:

pg> \l

+------------------+------------------+----------+-------------+-------------+---------------------------------------+
| Name             | Owner            | Encoding | Collate     | Ctype       | Access privileges                     |
|------------------+------------------+----------+-------------+-------------+---------------------------------------|
| postgres         | postgres         | UTF8     | en_US.UTF-8 | en_US.UTF-8 | postgres=CTc/postgres                 |
|                  |                  |          |             |             | pscale_api_ecr2xwwa8rhj=c/postgres    |
| pscale_admin     | pscale_admin     | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/pscale_admin                      |
|                  |                  |          |             |             | pscale_admin=CTc/pscale_admin         |
| pscale_exporter  | pscale_admin     | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/pscale_admin                      |
|                  |                  |          |             |             | pscale_admin=CTc/pscale_admin         |
|                  |                  |          |             |             | pscale_exporter=c/pscale_admin        |
| pscale_pgbouncer | pscale_admin     | UTF8     | en_US.UTF-8 | en_US.UTF-8 | <null>                                |
| template0        | pscale_admin     | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/pscale_admin                       |
|                  |                  |          |             |             | pscale_admin=CTc/pscale_admin         |
| template1        | pscale_superuser | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/pscale_superuser                   |
|                  |                  |          |             |             | pscale_superuser=CTc/pscale_superuser |
+------------------+------------------+----------+-------------+-------------+---------------------------------------+

These other logical databases can be safely ignored. Typically, you'll only interact with the postgres database, which has the postgres owner.

Creating more logical databases in a cluster is straightforward, but the permission structure of Postgres makes isolating access to them a little difficult.

Creating logical databases

You will need to connect to and work "inside" the database cluster to create logical databases. It is not done at the platform level.

After creating a new database cluster in the PlanetScale dashboard, you'll need to create a role to connect to it. The "default role" has full privileges and is ideal for the following steps.

While connected with a role with the postgres permissions, create the logical databases.

CREATE DATABASE blog;
CREATE DATABASE todo;

Revoke logical database permissions

By default, any current and future role can CONNECT to new logical databases. If we want the blog and todo logical databases to be isolated from one another, we'll need to revoke these permissions.

In Postgres, PUBLIC is a special keyword that represents every existing and future role in the cluster. (Not to be confused with public, the default schema in a Postgres database, naming things is hard.)

Running the snippet below will remove CONNECT access from PUBLIC for the default postgres logical database and then grant it to the administrator's role.

REVOKE CONNECT ON DATABASE blog     FROM PUBLIC;
REVOKE CONNECT ON DATABASE todo     FROM PUBLIC;
REVOKE CONNECT ON DATABASE postgres FROM PUBLIC;

-- Put the default role back on postgres only
GRANT CONNECT ON DATABASE postgres TO postgres;

About roles

The default role you created earlier is useful for administrative actions but should not be used by individual applications.

Instead, you should create distinct roles, for distinct actions, in distinct applications. Granting only what each connection requires, following the principle of least privilege.

Creating roles in PlanetScale Postgres is best done "outside" the database on the platform level in the dashboard, the pscale CLI, or the PlanetScale API.

Creating roles

Let's create a role for each logical database named blog_app and todo_app.

The permissions you choose when creating a role, like pg_read_all_data and pg_write_all_data, apply to the database cluster. There's no option upon creation to scope permissions to a logical database.

These permissions also only cover data access, not schema changes. To allow a role to create or alter tables, you grant CREATE on the database and schema in SQL and you can limit those grants to a single logical database.

Grant permissions

Finally, grant each role access to only its own logical database.

• CONNECT controls which database the role can open.
• CREATE on the database and schema controls where it can run migrations.

-- blog_app: connect and migrate within blog only
GRANT CONNECT ON DATABASE blog TO <blog_app-role-id>;
GRANT CREATE ON DATABASE blog TO <blog_app-role-id>;
GRANT CREATE, USAGE ON SCHEMA public TO <blog_app-role-id>;

-- todo_app: same pattern for todo
GRANT CONNECT ON DATABASE todo TO <todo_app-role-id>;
GRANT CREATE ON DATABASE todo TO <todo_app-role-id>;
GRANT CREATE, USAGE ON SCHEMA public TO <todo_app-role-id>;

Speeding up the process

The steps above were ordered in sequence for clarity. If you created the roles first, you could have run all the SQL statements in a single operation.

But now you can see how Postgres' permission model, public CONNECT on new databases by default and cluster-wide permissions make maintaining logical databases and roles difficult.

So now you know what needs to be done. Let's look at a simpler way to do it.

Automating logical database and role pairs

Instead of "click ops" (infrastructure configuration performed by manual actions) let's convert our clown-car database to "infrastructure as code" (IaC, configuration files that can spin up the desired state on-demand).

Here are a few options for automating the process:

• Terraform is the most standard choice for long-lived infrastructure. PlanetScale's provider can manage Postgres branches and roles. It is an excellent choice when this pattern becomes organization-owned production infrastructure.
• Pulumi gives us a similar provider-driven model while letting us write the workflow in TypeScript. The PlanetScale provider can be installed as a local package from our Terraform provider.
• SST is a good fit when PlanetScale credentials need to be linked into SST-managed applications, functions, or preview environments. For this tutorial's database-level CONNECT isolation, you would still need separate SQL automation.
• Alchemy is another choice using TypeScript and Effect. It offers a direct integration with PlanetScale. The logical database and grant-management layer we're going for would require additional custom code.

No matter which tool you choose, the split stays the same: roles are created through PlanetScale's API, while logical databases, CONNECT isolation, and DDL grants are managed inside Postgres.

The added benefit of using one of these infrastructure-as-code platforms is the ability to extend them. For example, configuring Cloudflare Workers and Hyperdrive connections as part of the same setup.

For this blog, we'll use Pulumi with the PlanetScale provider.

Prepare your database cluster

Once you've created a new database cluster on PlanetScale, make a note of your organization, database, and branch names. These are mine for example:

- Organization: "ready-set-go"
- Database: "clown-car"
- Branch: "main"

It's also handy to add these to the AGENTS.md file in your project when using the PlanetScale MCP server.

Create a service token

Since Pulumi is going to be creating roles (an action that occurs on PlanetScale outside of the database cluster) it will need to be authenticated with a service token. Creating logical databases and applying GRANT/REVOKE statements requires a Postgres connection. The Pulumi stack creates its own bootstrap role for that, using credentials returned from the PlanetScale API.

You can create a new token by clicking on "Settings" in the PlanetScale dashboard, clicking the settings of your organization, then clicking "Service tokens."

Once created, you will need to edit the token's permissions to give it access to the database you've noted above, and grant the following:

• connect_production_branch (to create roles)
• delete_production_branch_password (to delete roles)

Alternatively, you can create a new token using the PlanetScale CLI. The snippet below creates a token named "pulumi".

pscale service-token create --name pulumi

The response will show the token's ID and value. Make a note of the value for the steps below, and use the token's ID (and not the token's name) when adding permissions to the service token, like so:

pscale service-token add-access <token-id> connect_production_branch delete_production_branch_password --database clown-car

Initialize Pulumi

Install the Pulumi CLI using the instructions in their documentation.

brew install pulumi/tap/pulumi

All the code for this blog is available in the tutorial-pulumi repository. You can either clone it locally or follow the instructions below.

Run the following to create a new, blank TypeScript-based Pulumi project.

mkdir tutorial-clown-car && cd tutorial-clown-car
pulumi new typescript --name tutorial-clown-car --stack clown-car

The default installation may contain boilerplate code in the index.ts file, which you can safely ignore or remove.

If you did not install dependencies in the step above, ensure they're done now:

npm install

You'll also need to add the PlanetScale provider (which generates a local SDK from our Terraform provider) and install the PostgreSQL provider.

pulumi package add terraform-provider planetscale/planetscale
npm install @pulumi/postgresql

Configure the stack

Now, using the PlanetScale details and credentials noted earlier, add configuration values to the Pulumi stack.

pulumi config set organization "ready-set-go"
pulumi config set clusterName "clown-car"
pulumi config set branch "main"
pulumi config set importBranch true
pulumi config set --secret planetScaleServiceToken "pscale_tkn_..."
pulumi config set --plaintext planetScaleServiceTokenId "p..."

importBranch true ensures that Pulumi will not try to create a branch but instead use the existing one. Leveraging branches is out of scope for this tutorial, just be aware if you want Pulumi to additionally create and manage branches you will also need to set a clusterSize configuration value.

Build the program

The example repository contains four specific files that create our desired outcome. You'll need to copy these to your project.

• lib/services.ts defines the logical databases and roles.
• lib/connection.ts outputs connection strings in the correct format.
• lib/planetscale.ts contains a helper function for working with PlanetScale role names.
• index.ts the root file that creates the infrastructure.

Test and deploy

You can now confirm your project is set up correctly by running the following:

pulumi preview

This will step through the setup process, and you will see the same steps we detailed earlier happening automatically. Creating databases, granting and revoking access, and applying DDL grants.

The preview command is just a dry run. To take action on the database, we need to run:

pulumi up

With a successful deploy, you can now generate connection strings for each logical database and its distinct roles' username and password.

Per-database connections

Run the following to output connection strings for each logical database, along with nice formatting.

pulumi stack output apps --show-secrets | jq -C .

You should receive a response like this:

{
  "blog": {
    "direct": "postgresql://pscale_api_...",
    "pooled": "postgresql://pscale_api_..."
  },
  "todo": {
    "direct": "postgresql://pscale_api_...",
    "pooled": "postgresql://pscale_api_..."
  }
}

The connection string URLs default to their logical database name in the pathname (/blog and /todo). Use the "direct" URL for schema migrations and writes and the "pooled" URL for runtime application traffic.

Use the direct URL, connect to the blog logical database with the blog_app role and run the following to attempt to connect to the other logical databases. It should fail.

\c blog      # OK
\c todo      # fail
\c postgres  # fail

Packing the car

Say you've just had a great idea to create an online store. This should now be as simple as adding the new database name to services.ts

// lib/services.ts

export const appDatabases: string[] = [
    "blog",
    "todo",
    // 👇 new database
    "shop"
];

Deploying the stack again:

pulumi up

Where you'll see confirmation that new resources need to be created:

Resources:
    + 10 to create
    16 unchanged

Upon completion, you can reveal the connection strings again and see the set for the new "shop" logical database and its role.

The inverse is also true: removing a logical database and its role is as simple as removing it from the appDatabases array and deploying the stack again.

Schema and migrations

While Pulumi manages logical databases, roles, and grants, your applications own schema definitions and migration files.

Each app's role already has the DDL grants it needs, so your migration tool, for example Drizzle, connects with the same credential from within your application and runs against its own logical database.

Conclusion

Automating laborious maintenance tasks, such as creating logical databases and roles, is a great way to free up time to focus on problem-solving.

The IaC foundation you've now built could be extended to include other application infrastructure, like application hosting, queues, and notifications.

Now you can add new logical databases and role pairs on the same cluster on demand — each app walled off from its neighbors, migrations included. What will you build next?

Thank you to Jacob Hands for the original inspiration for this blog post.