Multitenant Database Schemas

Boost on DEVBoost on HashnodeToot it!Tweet It!Share on LinkedIn

4 minute read

I recently got to hang with Jamie Barton (@notrab) from the Turso team. We discussed Multitenant database schemas and how to set them up. Let's dig in.

Understanding Multitenant Database Schemas with Turso

In today's cloud-based software landscape, efficiently managing data for multiple clients or organizations is crucial. In this post, we'll explore what multitenant database schemas are, how to set them up, and why they're beneficial for modern applications.

Introduction to Multitenant Databases

Multitenant databases allow multiple customers (tenants) to share a single database instance while keeping their data isolated. This approach offers significant advantages in terms of resource efficiency and scalability, making it popular for SaaS applications and enterprise software.

Understanding Schema Databases

Regular Database vs. Schema Database

The key difference between a regular database and a schema database lies in how changes are managed:

  • Regular Database: Operates independently, with schema changes applied directly to the database.
  • Schema Database: Acts as a template, where schema changes are observed and forwarded to attached child databases.

Turso implements a database-per-tenant architecture with shared schemas, allowing for efficient management of multi-tenant systems. This approach, as detailed in Turso's blog post about production-friendly improvements to database-per-tenant architectures, enables developers to maintain consistent schema across multiple databases while still providing isolation between tenants.

Creating a Multitenant Database Schema

Before getting started, ensure you have the Turso CLI installed.

The first time you run the Turso CLI, you'll be asked to log in.

Running command and receiving the error Error: user not logged in, please login with turso auth login

The process of setting up a multitenant database schema with Turso involves the following steps:

  1. Create a Group: This essentially creates a machine to host the database.

turso group create default

You'll receive a message like this one.


Created group default at yul in 8.989s.
  1. Create a Schema Database: This database defines the structure for child databases.

turso db create parent-db --type schema
  1. Set Up the Schema: Connect to the schema database shell and create your table structure.

turso db shell parent-db

For example, in the shell enter:


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

  1. Type .quit in the shell to exit the shell.

  2. Create a child database with the schema DB parent-db


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

  1. Run turso db shell child-db1 to load the shell for the newly created child-db1

  2. Run .schema from the shell. Notice the schema:


CREATE TABLE users ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT);

  1. Type .quit in the shell to exit the shell.

  2. Create another child Databases with the schema DB parent-db


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

  1. Run turso db shell child-db2 to load the shell for the newly created child-db2

  2. Run .schema from the shell. Notice the schema:


CREATE TABLE users ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT);

  1. Type .quit in the shell to exit the shell.

  2. Go back to the shell of the parent-db. Run turso db shell parent-db.

  3. Add another field, email

  4. Run .schema from the shell to view the updated schema.


CREATE TABLE users ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, email TEXT);

  1. Type .quit in the shell to exit the shell.

  2. Go back to the shell of each child database and view their schemas. Notice they have updated schemas that reflect the schema in the parent-db.

Potential Use Cases

Multitenant database schemas are particularly useful for:

  • SaaS applications serving multiple clients
  • Enterprise software supporting various departments or subsidiaries
  • Platforms requiring strict data isolation between users or organizations

Benefits of Multitenant Database Schemas

Implementing a multitenant database schema offers several advantages:

  1. Scalability: Easily accommodate growing numbers of tenants without major infrastructure changes.
  2. Consistency: Maintain uniform schema across all tenant databases.
  3. Efficient Management: Simplify updates and maintenance by managing schema in one place.
  4. Cost-Effective: Optimize resource utilization by sharing infrastructure.
  5. Data Isolation: Ensure data privacy and security between tenants.

Conclusion

Multitenant database schemas, as implemented by Turso, offer a powerful solution for modern, scalable applications. By understanding and leveraging this architecture, developers can build more efficient, manageable, and secure multi-client systems.

To dive deeper into this topic and see a practical demonstration, check out my full conversation with Jamie Barton.

Have you implemented multitenant databases in your projects? Share your experiences or questions in the comments below!

Thanks again Jamie for hanging on stream!

Until the next one!