8 Databases
Anton Nesterov edited this page 2026-11-08 20:25:29 +01:00
This file contains ambiguous Unicode characters

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

img

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/-shm sidecars.
  • 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

  1. Use descriptive names: make database names clear and meaningful
  2. Isolate tenants completely: each tenant gets their own database
  3. Document database structure: keep track of which databases exist and their purpose (the IaC databases.yaml is that list)
  4. Implement proper backup: regular backup of all databases
  5. Monitor storage: track database file sizes
  6. Use reserved names carefully: only use default, stats, and workflows for their intended purposes
  7. Set proper permissions: ensure users can only access their own databases
  8. Test multi-tenancy: verify data isolation in your application