Table of contents
- Databases & Tenant Isolation
- Understanding Multi-Tenancy
- Database Directory Structure
- Creating Databases
- Accessing Specific Databases
- Database Operations
- Reserved Databases
- SaaS Application Pattern
- Multi-Environment Setup
- Database Configuration
- Cross-Database Operations
- Monitoring
- Backup and Restore
- API Endpoints
- Best Practices
This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.
Databases & Tenant Isolation
VSKI provides built-in multi-tenancy support, allowing you to manage multiple isolated databases within a single VSKI instance. This is perfect for SaaS applications, multi-environment setups, and client data isolation.
Note
Schemas are append-only. You cannot rename columns through the interface. Dropping and renaming columns is a manual task.
Note
Tables and columns are always soft-deleted when using the VSKI API. Data migration and breaking changes is an admin task that has to be done carefully and with a cool head.
Understanding Multi-Tenancy
Each VSKI instance can host multiple databases, each stored as a separate file. This ensures complete data isolation between tenants while sharing the same application code and server infrastructure.
Database Directory Structure
data/
├── default.db # Default database
├── stats.db # Statistics database (reserved)
├── workflows.db # Workflows database (reserved)
├── client1.db # Client 1 database
├── client2.db # Client 2 database
└── client3.db # Client 3 database
Creating Databases
Create a New Database
Databases are ensure-exists resources in the IaC YAML (see IaC);
they are never deleted by vski apply:
# vski/databases.yaml
version: 1
databases:
client1:
description: Client 1 tenant
vski db create client1 --description "Client 1 tenant" # imperative alternative
database = await client.settings.databases.create({"name": "client1"})
print(database["name"]) # "client1"
This creates a new client1.db file in the data directory.
List All Databases
vski db list
databases = await client.settings.databases.list()
print(databases)
# [
# {"name": "default"},
# {"name": "stats"},
# {"name": "workflows"},
# {"name": "client1"},
# {"name": "client2"},
# ]
Storage Statistics
GET /api/stats/storage (authenticated; the x-dbname header selects the database) returns deployment-level storage numbers; all values are bytes:
{
"deployment_size": 1936069,
"db": "default",
"db_size": 348160,
"disk_available": 24025014272,
"disk_total": 24511787008
}
deployment_size: total size of the data directory (DATA_DIR).db_size: size of the selected database file including its-wal/-shmsidecars.disk_available/disk_total: free and total space on the disk holding the data directory.
Through the SDKs:
stats = await client.stats.storage() # current db
stats = await client.stats.storage(db="tenant1")
const stats = await client.stats.storage(); // current db
const stats = await client.stats.storage("tenant1");
The web dashboard shows these as cards on the Databases (SQL Console) page whenever no query results are displayed.
Accessing Specific Databases
The recommended approach is client.db(name):
# Access a specific database
client1_db = client.db("client1")
# All operations now use client1.db
posts = await client1_db.collection("posts").get_list(1, 20)
# Access another database
client2_db = client.db("client2")
posts2 = await client2_db.collection("posts").get_list(1, 20)
client.set_db(name) also exists but switches the whole client.
Using HTTP Headers
When making direct HTTP requests, use the x-dbname header:
import httpx
response = httpx.get(
"http://127.0.0.1:3000/api/collections/posts/records",
headers={
"Authorization": "Bearer your-token",
"x-dbname": "client1",
},
)
Database Operations
Database Isolation
Each database is completely isolated:
client1_db = client.db("client1")
await client1_db.collection("posts").create({"title": "Post in Client 1"})
client2_db = client.db("client2")
await client2_db.collection("posts").create({"title": "Post in Client 2"})
# switch back to client1
posts = await client1_db.collection("posts").get_list(1, 20)
print(len(posts["items"])) # 1: only the post created in client1
Deleting Databases
vski db rm client1
ok = await client.settings.databases.delete("client1") # True
Warning: Deleting a database permanently removes all its data.
Reserved Databases
VSKI reserves three database names:
Default Database (default.db)
The default database is used when no specific database is specified:
posts = await client.collection("posts").get_list(1, 20)
Stats Database (stats.db)
The stats database stores system statistics and logs:
stats_db = client.db("stats")
stats = await stats_db.collection("_stats").get_list(1, 20)
Do not store user data in the stats database.
Workflows Database (workflows.db)
The workflows database stores all workflow-related data:
- Workflow runs and their status
- Workflow events and signals
- Job queues for distributed workers
- Hooks for external integrations
- Wait states for sleep/signal operations
workflows_db = client.db("workflows")
runs = await workflows_db.collection("workflow_runs").get_list(1, 20)
Do not store user data in the workflows database. This database is
automatically omitted from replication.
SaaS Application Pattern
Tenant Registration
async def register_tenant(client, tenant_data):
# Create tenant record in the default database
tenant = await client.db("default").collection("tenants").create(
{"name": tenant_data["name"],
"subdomain": tenant_data["subdomain"],
"status": "active"}
)
# Create isolated database for the tenant
await client.settings.databases.create({"name": tenant["id"]})
# Initialize the tenant's collections (see below)
await initialize_tenant_database(client, tenant["id"])
return tenant
Tenant Database Initialization
Collections are declared once and apply across databases (schema is global;
records are stored separately in each database file). With IaC, a tenant
database reuses the same collection files with a different database: line:
# vski/tenants/client1/collections.yaml: same schema, scoped to the tenant db
version: 1
database: client1
collections:
users:
type: auth
fields:
- {name: email, type: email, required: true}
- {name: password, type: text, required: true}
- {name: name, type: text}
posts:
type: base
fields:
- {name: title, type: text, required: true}
- {name: content, type: text}
vski apply # creates the database entries and collections
Tenant Access
async def login_tenant_user(client, tenant_id, email, password):
tenant_db = client.db(tenant_id)
result = await tenant_db.auth.login(email, password)
return result
Multi-Environment Setup
Development, Staging, Production
import os
env = os.environ.get("NODE_ENV", "development")
databases = {
"development": "dev",
"staging": "staging",
"production": "prod",
}
env_db = client.db(databases[env])
Environment Isolation
# vski/databases.yaml
version: 1
databases:
dev: {description: Development}
staging: {description: Staging}
prod: {description: Production}
dev_db = client.db("dev")
# All operations here are isolated from staging and prod
Database Configuration
Custom Data Directory
Set a custom directory for database files:
export DATA_DIR="/path/to/custom/data"
Database File Location
# Default location: ./data/
# Custom location via DATA_DIR environment variable
#
# Database files are:
# - default.db
# - client1.db
# - etc.
Performance Tuning
All databases run SQLite in WAL mode with a bounded connection pool. The following environment variables tune the storage layer (defaults shown):
| Variable | Default | Purpose |
|---|---|---|
SYNCHRONOUS |
NORMAL |
SQLite synchronous pragma. The default NORMAL is the usual WAL-mode choice: safe against application crashes, losing at most the last commits on OS/power failure, and much faster; with the write queue batching commits it is the recommended setting. FULL fsyncs the WAL on every commit (caps writes at roughly one commit per fsync); also accepts OFF/EXTRA or 0–3. |
BUSY_TIMEOUT_MS |
5000 |
How long a query waits on the database write lock before failing with SQLITE_BUSY. Lower values fail faster under overload; higher values smooth write bursts. |
DATA_MAX_OPEN_CONNS |
120 |
Max pooled connections to tenant data databases (default + per-tenant files). |
DATA_MAX_IDLE_CONNS |
15 |
Currently inert: idle is pinned to the open cap (see note below). |
AUX_MAX_OPEN_CONNS |
20 |
Max pooled connections for the reserved stats and workflows databases. |
AUX_MAX_IDLE_CONNS |
3 |
Currently inert: idle is pinned to the open cap (see note below). |
WAL_CHECKPOINT_THRESHOLD_MB |
16 |
When a database's WAL file exceeds this size, the background checkpoint loop (1 s tick) runs a PASSIVE checkpoint followed by wal_checkpoint(TRUNCATE) to reset it. |
WRITE_QUEUE_ENABLED |
true |
Group-commits concurrent record writes into one SQLite transaction per flush window. false reverts to per-request transactions. |
WRITE_QUEUE_DEPTH |
4096 |
Max write requests waiting for a batch slot. Over it, requests wait up to WRITE_QUEUE_SUBMIT_WAIT_MS and then fail instead of piling up. |
WRITE_QUEUE_MAX_BATCH |
128 |
Max write requests committed together in one transaction. |
WRITE_QUEUE_WINDOW_MS |
2 |
How long the writer keeps collecting requests once at least two are waiting. A lone write skips the window and commits immediately. |
WRITE_QUEUE_SUBMIT_WAIT_MS |
5000 |
How long a write request waits for a queue slot under saturation before failing. |
Why idle is pinned to the open cap: closing a pooled SQLite connection mid-run (idle trim) leaked that connection's file descriptors and page cache under sustained load, slowly climbing to fd exhaustion (fixed at the SQLite layer in
vski-sqlite; the pin remains as defense-in-depth so the pool only closes connections at shutdown). Connection lifetime limits are likewise left unset for the same reason.
Cross-Database Operations
Scope per database with client.db(name) (records are isolated per
database; schemas are shared):
shared = await client.db("shared").collection("shared_data").get_list(1, 20)
Monitoring
Database Statistics
client1_db = client.db("client1")
stats = await client1_db.collection("_stats").get_list(1, 20)
print(stats["items"])
Storage Usage
Check database file sizes on the server host:
from pathlib import Path
data_dir = Path("./data")
for file in sorted(data_dir.glob("*.db")):
print(f"{file.name}: {file.stat().st_size / 1024 / 1024:.2f} MB")
Backup and Restore
cp data/client1.db backups/client1.db # backup
cp backups/client1.db data/client1.db # restore (stop the server first)
See Backup for the snapshot-based rotation script.
API Endpoints
| Method | Endpoint | Description |
|---|---|---|
GET |
/api/databases |
List all databases |
POST |
/api/databases |
Create new database |
DELETE |
/api/databases/:name |
Delete database |
Best Practices
- Use descriptive names: make database names clear and meaningful
- Isolate tenants completely: each tenant gets their own database
- Document database structure: keep track of which databases exist and
their purpose (the IaC
databases.yamlis that list) - Implement proper backup: regular backup of all databases
- Monitor storage: track database file sizes
- Use reserved names carefully: only use
default,stats, andworkflowsfor their intended purposes - Set proper permissions: ensure users can only access their own databases
- Test multi-tenancy: verify data isolation in your application
- Introduction
- Performance
- Getting Started
- Coding Agents
- Command Line
- Infra as Code
- CI/CD Guide
- Project Boilerplate
- SDKs
- Authentication
- Databases
- Collections
- Gated Queries
- Graphs
- Rules and Access Control
- Granular Access Control
- Triggers
- Plugins
- Containers Plugin
- vski mcp
- Realtime
- Full Text Search
- Vector Embeddings
- KWH
- Workflows
- Pipelines
- Dashboard Widgets
- Connectors
- Cron
- Webhooks
- Gates
- Migrations
- Replication
- File Sync
- Backup
- Distributed Realtime
- Tutorial: ReactJS Todo
- Tutorial: Graph-Memory Agent
- Tutorial: Image Gallery
- Tutorial: Embeddings
- Tutorial: Graphs
- Recipe: Calendar Timetree
- Tutorial: Plugins
- Tutorial: MCP Server
