Launch Week Day 1: Database Per Tenant Architectures Get Production Friendly Improvements

Turso offers its users a legendary amount of databases: any free Starter plan user can create up to 500 databases, while users on our $29/month Scaler plan can create up to 10,000. And they can be created programmatically from an API.

This makes database per tenant (user, cluster, policy, etc.) architectures not only feasible, but efficient. You can leverage hundreds of thousands or even millions of databases in an efficient way similar to how you use a traditional relational database backend architecture today, but with full native data isolation / without the permissioning and partitioning complexity that comes with other approaches like RLS.

For some users, such as Application Development Platforms, those databases are completely independent, and have their own schema. Examples of that are Astro and Val.town, who run platforms where users have their own databases with direct access to it, including the freedom to define their own schema.

But for others, we don’t expect databases to be that independent. Highly regulated industries like Healthcare and Fintech, or even B2B SaaS applications with many users will still benefit from the database per tenant model, since sensitive user’s data can be kept completely separate, but all users will have the same schema, and at times have to interact with each other.

To better serve these use cases, today we are launching two exciting new features to bridge that gap and make running database per tenant architectures powered by Turso in production all the more feasible.

#Multi-database queries

Users who are new to SQLite may not know it, but SQLite already has a way to query multiple databases, through the ATTACH statement. Since each database is just a file, you can attach multiple files to the current session, and then just refer to them by their names.

In line with our mission of being SQLite for Production, Turso now allows you to attach multiple databases into the same connection, and then just refer to them in queries in that same connection.

For example, consider an e-commerce website with a collection of users, each of them with their own databases, and a product catalogue, shared across all users. We may want to query both databases at the same time, to find out if there are any special promotions we should show the user:

ATTACH "<database-id>" AS userdb;

SELECT category, discount
FROM promotions
JOIN userdb.user_preferences AS up ON category = up.interested_category
WHERE up.user_id = "...";

Querying attached databases don’t require extra round trips, and can be done in the same server round trip. For security reasons, databases cannot be attached unless a global attach permission is enabled, and the user has a JWT that lists attach permissions.

There’s a new CLI command to allow databases to be attached to others:

turso db config attach allow <database-name>

If you’re creating and managing databases using the Platform API, you can update your database configuration with the new allow_attach field.

Since enabling ATTACH for a database require new permissions, you can create a specific token using the CLI or Platform API for use with a Client SDK:

turso db tokens create <database-name> --attach <another-database-name>

Alternatively, you can create a token on the fly when connecting to a database by passing the name of the databases you want to ATTACH:

turso db shell <database-name> --attach <...database-name(s)>

Each query can ATTACH at most 10 databases in the same query. This feature is now in beta, and as of today is available to all Turso users, in all plans — see documentation.

#Multi-database schema changes

Turso now also allows for many databases' schema to be managed and evolved together. Consider the use case of a Multitenant B2B SaaS that needs to serve many users, each with a user profile:

CREATE TABLE users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  username TEXT NOT NULL UNIQUE
);

We then want to evolve that schema, and add a column in every database:

ALTER TABLE users ADD COLUMN phone_number;

First, begin by creating a database with the new --type flag using the Turso CLI:

turso db create parent-db --type schema

Now you can create as many databases as you want. Make sure you pass the database name to the --schema flag:

turso db create child-db --schema parent-db

To apply changes to ALL databases, you must apply the migration to the parent database. Changes will automatically be applied to all databases sharing that parent database schema, or none in the event of an error.

However, keep in mind when querying that some databases may not yet have been migrated. Make sure your application can handle the absence of any columns, or tables created or modified.

Databases created with a schema can no longer have their individual schemas evolved separately. All DDL operations are blocked in those databases:

turso db shell new-child-db "CREATE TABLE new_table (name);"

Error: failed to execute SQL:CREATE TABLE new_table (name);
Operation was blocked

This feature is now in beta, and as of today is available to all Turso users, in all plans — see documentation.

#What's next?

The ability to perform multi-database queries and multi-database schema changes allow users to use the long-standing SQLite pattern of creating millions of databases usable in production. That allows handling of sensitive data without having to resort to row-level security, with full isolation, even encrypting each user’s data with a different key (coming soon, link to blog).

But we know there’s more to build, and we’re committed to building it! What would you like to see next, to make working with multiple databases a winning pattern? Let us know on Discord.