Implementing Sandbox Environments with PostgreSQL Schemas

A journey through implementing efficient sandbox environments using PostgreSQL schemas, exploring different approaches and their trade-offs.

Introduction

One common challenge in SaaS and enterprise applications is allowing users to experiment in isolated environments — without risking production data. We recently needed to implement exactly that: sandbox environments where users could simulate changes, test logic, and explore features without affecting the main data.

What started as a seemingly simple requirement led to some interesting architectural decisions — and ultimately an elegant solution using PostgreSQL schemas.

The Initial Challenge

The first question was: How do we isolate data for different sandbox environments?

My initial idea was to add a sandboxId column to each table and modify indexes and constraints to be sandbox-aware. But this solution had several drawbacks:

• Performance degradation as the dataset grew
• Increased complexity in queries and joins
• Higher risk of cross-sandbox data leakage
• Storage bloat due to data duplication

So I looked for alternatives.

Exploring Alternatives

After some prototyping, I narrowed the options down to two primary approaches:

1. Separate databases per sandbox
2. Shared database with PostgreSQL schemas

The first approach offered clean isolation, but it came at a cost:

• More expensive infrastructure
• Complicated deployments and migrations
• Harder to manage at scale

The PostgreSQL Schema Solution

Since we were already using PostgreSQL, schemas felt like a natural fit. PostgreSQL supports multiple schemas in a single database, offering lightweight isolation without the overhead of multiple database instances.

Here’s how we implemented it.

Backend Implementation (NestJS)

We created an HTTP interceptor that sets the current schema based on the incoming request's x-sandbox-id header:

import { ClsService } from 'nestjs-cls';
 
@Injectable()
export class SchemaInterceptor implements NestInterceptor {
  constructor(private readonly cls: ClsService) {}
 
  intercept(context: ExecutionContext, next: CallHandler): Observable<any> {
    const request = context.switchToHttp().getRequest();
    const sandboxId = request.headers['x-sandbox-id'];
 
    if (sandboxId) {
      this.cls.set('sandboxId', sandboxId);
    }
 
    return next.handle();
  }
}

Dynamic Schema Selection

We then built a service to dynamically set the schema per request:

@Injectable()
export class DatabaseService {
  constructor(
    private readonly cls: ClsService,
    @Inject(DATABASE) private readonly _db: Database
  ) {}
 
  private get db() {
    const sandboxId = this.cls.get('sandboxId');
    return this._db.withSchema(sandboxId || 'main');
  }
}

This pattern encapsulates schema switching using AsyncLocalStorage, so most of the application code remains schema-agnostic.

Frontend Implementation (Angular)

On the Angular frontend, we added an HTTP interceptor to attach the sandbox ID to every request:

@Injectable()
export class SandboxInterceptor implements HttpInterceptor {
  intercept(request: HttpRequest<any>, next: HttpHandler): Observable<HttpEvent<any>> {
    const sandboxId = this.getSandboxId(); // Retrieve from your app state
 
    if (sandboxId) {
      request = request.clone({
        setHeaders: {
          'x-sandbox-id': sandboxId
        }
      });
    }
 
    return next.handle(request);
  }
 
  private getSandboxId(): string | null {
    // Implement according to your app state/store
    return localStorage.getItem('sandboxId');
  }
}

MySQL Alternative with Connection Pool Manager

If you're using MySQL, schemas aren't supported the same way. But you can achieve similar isolation by using separate databases per sandbox — and manage them via a custom connection pool manager.

Here’s a high-level strategy:

1. One MySQL database per sandbox, e.g., sandbox_123, sandbox_456
2. Implement a simple key-value connection pool manager that:
   • Caches a Pool per database name
   • Reuses connections for each sandbox environment
3. Choose the correct pool dynamically based on the request's sandbox ID

Example (pseudo-code):

const pools: Record<string, Pool> = {};
 
function getPoolForSandbox(sandboxId: string): Pool {
  const dbName = `sandbox_${sandboxId}`;
  if (!pools[dbName]) {
    pools[dbName] = createPool({ database: dbName, /* ...config */ });
  }
  return pools[dbName];
}

This approach gives you strong isolation at the cost of higher operational complexity. It's well-suited for scenarios where security boundaries are critical.

Benefits of the PostgreSQL Schema Approach

The schema-based solution turned out to be:

• Performant: No extra columns or query filters
• Cost-effective: No need for separate database instances
• Simple to implement: Few changes to existing code
• Secure: Natural sandbox isolation
• Scalable: New environments are easy to provision

Lessons Learned

Working through this challenge highlighted several key takeaways:

1. Simple != efficient — The easiest solution isn’t always the best long-term
2. Database features matter — Understanding your DB engine can unlock elegant designs
3. Cost should guide architecture — Don't optimize for purity at the expense of operations
4. Interceptors are underrated — They're powerful for handling cross-cutting concerns like multitenancy

Conclusion

PostgreSQL schemas offered a clean, performant, and maintainable solution to our sandbox isolation challenge. By understanding the database's capabilities and designing around them, we avoided unnecessary complexity — while still meeting all requirements.

If you're facing similar isolation challenges, consider whether your database engine has native features like schemas or whether you need to roll out a more custom setup. Either way, a thoughtful approach to data isolation can pay dividends in scalability, maintainability, and performance.

Thanks for reading! If you’ve faced similar challenges — or solved sandboxing differently — I’d love to hear your thoughts.