Database Management

Overview

DeployStack supports multiple database types through an environment-based configuration system using Drizzle ORM. The system provides excellent performance, type safety, and a modern, developer-friendly experience with support for:

SQLite - Local file-based database (default for development)
Turso - Distributed SQLite database with global replication

All databases use the same SQLite syntax and schema, ensuring consistency across different deployment environments.

Database Setup and Configuration

The backend uses an environment-based configuration system where database credentials are provided via environment variables, and the database type is selected through the setup API.

Setup Instructions: For step-by-step setup instructions, see the Database Setup Guide.

Database-Specific Guides: For detailed technical information about specific databases, see:

SQLite Development Guide

Turso Development Guide

Environment Variables

Configure your chosen database type by setting the appropriate environment variables:

SQLite Configuration

# Optional - defaults to persistent_data/database/deploystack.db
# Path is relative to services/backend/ directory
SQLITE_DB_PATH=persistent_data/database/deploystack.db

Turso Configuration

TURSO_DATABASE_URL=libsql://your-database-url
TURSO_AUTH_TOKEN=your_auth_token

Database Status

Check the current status of the database configuration and initialization:

Endpoint: GET /api/db/status
Method: GET
Response: JSON object with database status information

{
  "configured": true,
  "initialized": true,
  "dialect": "sqlite"
}

Initial Database Setup

The initial database setup is performed through the frontend setup wizard at /setup, which provides a user-friendly interface. The wizard internally calls the backend API:

Frontend URL: https://<your-frontend-url>/setup
Backend Endpoint (called by frontend): POST /api/db/setup
Method: POST
Request Body: JSON object specifying the database type

Note for Developers: While you can call the API endpoint directly for testing, end-users should always use the frontend setup wizard for proper initialization.

Setup Examples

SQLite Setup:

{
  "type": "sqlite"
}

Turso Setup:

{
  "type": "turso"
}

API Response

The setup endpoint returns a JSON response indicating success and restart requirements: Successful Setup:

{
  "message": "Database setup successful. All services have been initialized and are ready to use.",
  "restart_required": false,
  "database_type": "sqlite"
}

Setup with Restart Required (Fallback):

{
  "message": "Database setup successful, but some services may require a server restart to function properly.",
  "restart_required": true,
  "database_type": "sqlite"
}

Database Selection File

The chosen database type is stored in:

services/backend/persistent_data/db.selection.json (relative to the backend service directory)

This file is automatically created and managed by the setup API when users complete the frontend setup wizard at https://<your-frontend-url>/setup. Manual editing is not recommended. Example content:

{
  "type": "sqlite",
  "selectedAt": "2025-01-02T18:22:15.000Z",
  "version": "1.0"
}

Important: This file is created during the initial setup process through the frontend wizard, which internally calls the /api/db/setup endpoint.

Architecture

Key Components

Drizzle ORM: Type-safe ORM with native driver support
Native Drivers:
- better-sqlite3 for SQLite
- @libsql/client for Turso
Unified Schema: Single schema definition works across all database types
Environment Configuration: Database credentials via environment variables

Database Drivers

The system uses native Drizzle drivers for optimal performance:

// SQLite
import { drizzle } from 'drizzle-orm/better-sqlite3';

// Turso
import { drizzle } from 'drizzle-orm/libsql';

Database Connection Patterns

When accessing the database in route handlers, always use getDb() to obtain the database connection dynamically:

import { getDb } from '../../../db';
import { YourService } from '../../../services/yourService';

export default async function yourRoute(server: FastifyInstance) {
  const db = getDb();
  const yourService = new YourService(db);
  // ... rest of route handler
}

Why this pattern is required:

server.db may be null during certain initialization states
getDb() always returns the active database connection
This ensures consistent behavior across all endpoints
Other working endpoints already follow this pattern

Avoid: Direct usage of server.db as it can cause “Cannot read properties of null” errors.

Database Driver Compatibility

⚠️ Critical for Multi-Database Applications: Understanding driver differences prevents hours of debugging!

The Problem: Different Result Property Names

When performing database operations (INSERT, UPDATE, DELETE), different database drivers return different property names in their result objects:

// SQLite (better-sqlite3) result object
{
  changes: 1,                    // ← Number of affected rows
  lastInsertRowid: "abc123"
}

// Turso (libSQL) result object  
{
  rowsAffected: 1,               // ← Number of affected rows
  lastInsertRowid: "abc123"
}

Key Difference:

SQLite: Uses result.changes to indicate affected rows
Turso: Uses result.rowsAffected to indicate affected rows

Real-World Impact

This difference caused a production bug where DELETE operations appeared to fail in Turso but worked in SQLite development:

// ❌ WRONG: Only works with SQLite
const deleted = result.changes > 0;

// ✅ CORRECT: Works with both SQLite and Turso
const deleted = (result.changes || result.rowsAffected || 0) > 0;

Symptoms of this bug:

✅ Works perfectly in development (SQLite)
❌ Fails mysteriously in production (Turso)
🔄 Data actually gets modified, but application thinks it failed
🐛 Users see error messages even though operation succeeded

DeployStack’s Built-in Solution

DeployStack services automatically handle this compatibility issue. For example, in McpInstallationService.deleteInstallation():

// DeployStack handles both drivers automatically
const deleted = (result.changes || result.rowsAffected || 0) > 0;

Writing Compatible Database Code

When writing custom database operations, always use the cross-compatible pattern:

// ✅ CORRECT: Multi-driver compatible
export class MyService {
  async deleteRecord(id: string): Promise<boolean> {
    const result = await this.db
      .delete(myTable)
      .where(eq(myTable.id, id));

    // Handle both SQLite and Turso drivers
    return (result.changes || result.rowsAffected || 0) > 0;
  }

  async updateRecord(id: string, data: any): Promise<boolean> {
    const result = await this.db
      .update(myTable)
      .set(data)
      .where(eq(myTable.id, id));

    // Same pattern for updates
    return (result.changes || result.rowsAffected || 0) > 0;
  }
}

Testing Across Database Types

To catch these issues during development:

Test with both databases: Run your code against both SQLite and Turso
Use integration tests: Write tests that verify actual database operations
Check result objects: Log result objects during development to see the structure

// Debug logging to see result structure
const result = await db.delete(table).where(condition);
console.log('Delete result:', result); // Inspect the actual properties

Why This Happens

This difference exists because:

SQLite/better-sqlite3: Uses the native SQLite C API which returns changes
Turso/libSQL: Uses the HTTP/WebSocket protocol which standardizes on rowsAffected

Both represent the same concept (number of affected rows) but with different property names.

Prevention Checklist

When writing database operations:

Use (result.changes || result.rowsAffected || 0) pattern
Test with both SQLite and Turso if possible
Look for existing DeployStack service patterns to follow
Never assume specific property names exist
Add debug logging when troubleshooting database operations

💡 Pro Tip: This pattern also future-proofs your code for additional database types that DeployStack might support later.

Database Structure

The database schema is defined in src/db/schema.sqlite.ts. This is the single source of truth for all database schema definitions and works across all supported database types. The schema contains:

Core application tables (users, teams, MCP configurations, etc.)
Background job queue tables (queueJobs and queueJobBatches) - see Background Job Queue
Plugin table definitions (populated dynamically)
Proper foreign key relationships and constraints

Important: Only schema.sqlite.ts should be edited for schema changes. All databases use SQLite syntax.

Making Schema Changes

Follow these steps to add or modify database tables:

Modify Schema Definition Edit src/db/schema.sqlite.ts to add or modify tables:

// Example: Adding a new projects table
export const projects = sqliteTable('projects', {
  id: text('id').primaryKey(),
  name: text('name').notNull(),
  description: text('description'),
  userId: text('user_id').references(() => authUser.id),
  createdAt: integer('created_at', { mode: 'timestamp' }).notNull().$defaultFn(() => new Date()),
  updatedAt: integer('updated_at', { mode: 'timestamp' }).notNull().$defaultFn(() => new Date()),
});

Generate Migration Run the migration generation command:
```
npm run db:generate
```
This creates SQL migration files in drizzle/migrations_sqlite/ that work across all database types.
Review Migrations Examine the generated SQL files in drizzle/migrations_sqlite/ to ensure they match your intended changes.
Apply Migrations Migrations are automatically applied on server startup. You can also run them manually:
```
npm run db:up
```

Use the New Schema Update your application code to use the new tables:

// Example: Using the new table in a route
app.get('/api/projects', async (request, reply) => {
  const projects = await request.db.select().from(schema.projects).all();
  return projects;
});

Migration Management

Unified Migrations: Single migrations_sqlite folder works for all database types
Automatic Tracking: Migrations tracked in __drizzle_migrations table
Incremental Application: Only new migrations are applied
Transaction Safety: Migrations applied in transactions for consistency
Execution Timing: Migrations are applied automatically on server startup, but only after the database has been initialized through the setup process

Important: Migrations cannot run until the database exists. The initial setup (via frontend wizard at /setup) must be completed first to create the database, then migrations will apply on subsequent server startups.

Migration Compatibility

All databases use SQLite syntax, ensuring migration compatibility:

SQLite: Direct execution
Turso: libSQL protocol with SQLite syntax

Global Settings Integration

During database setup, DeployStack automatically initializes global settings that configure the application. This process is database-aware and handles database-specific limitations:

Automatic Initialization

The global settings system:

Loads setting definitions from all modules in src/global-settings/
Creates setting groups for organizing configuration options
Initializes default values for all settings with proper encryption
Handles database limitations through automatic batching

Database-Specific Handling

SQLite: Settings are created in large batches for optimal performance Turso: Uses efficient batch operations with libSQL protocol

Global Settings Documentation: For detailed information about global settings, see the Global Settings Guide.

Plugin Database Extensions

Plugins can extend the database with their own tables and settings. For detailed information about plugin database integration, including table definitions, dynamic table creation, and security boundaries, see the Plugin System Guide. Key plugin database features:

Dynamic table creation at runtime (separate from core migrations)
Automatic table prefixing with plugin IDs
Security isolation between core and plugin tables
Global settings integration for plugin configuration

Development Workflow

Environment Setup: Configure environment variables for your chosen database
Initial Setup: Complete the frontend setup wizard at /setup (for first-time setup)
- This creates persistent_data/db.selection.json
- Initializes the database based on your selection
- For development, you can also directly call POST /api/db/setup
Schema Changes: Modify src/db/schema.sqlite.ts
Generate Migrations: Run npm run db:generate
Apply Changes: Restart server or run npm run db:up
Update Code: Use the modified schema in your application

Backup Strategy: Always backup the entire services/backend/persistent_data/ directory as it contains:

The SQLite database file (if using SQLite)
The database selection configuration
Any other persistent application data

Database-Specific Considerations

SQLite

File Location: services/backend/persistent_data/database/deploystack.db (full path from project root)
Performance: Excellent for development and small to medium deployments
Backup: Simple file-based backup - backup the entire persistent_data/ directory
Selection File: Database type stored in persistent_data/db.selection.json

Turso

Global Replication: Multi-region database replication
Edge Performance: Low-latency access worldwide
libSQL Protocol: Enhanced SQLite with additional features
Scaling: Automatic scaling based on usage

Best Practices

Schema Design

Use meaningful column names and consistent naming conventions
Add appropriate indexes for frequently queried columns
Include proper foreign key constraints for relational data
Always use migrations for schema changes

Environment Management

Keep database credentials in environment variables
Use different databases for different environments (dev/staging/prod)
Never commit database credentials to version control

Migration Safety

Always review generated migrations before applying
Test migrations in development before production
Keep migrations small and focused
Never manually edit migration files

Inspecting Databases

SQLite

# Using SQLite CLI (from project root)
sqlite3 services/backend/persistent_data/database/deploystack.db

# Or from backend directory
cd services/backend
sqlite3 persistent_data/database/deploystack.db

# Using DB Browser for SQLite (GUI)
# Download from: https://sqlitebrowser.org/

Turso

# Using Turso CLI
turso db shell your-database

# Using libSQL shell
# Available at: https://github.com/libsql/libsql

Troubleshooting

Setup Issues

Configuration Error: Verify environment variables are set correctly
Network Issues: Check connectivity for Turso
Permissions: Ensure API tokens have proper permissions

Migration Issues

Migration Conflicts: Check for duplicate or conflicting migrations
Schema Drift: Ensure all environments use the same migrations
Rollback: Manually revert problematic migrations if needed

Performance Issues

SQLite: Check file system performance and disk space
Turso: Monitor regional performance and connection latency

Plugin Issues

Missing Tables: Ensure plugins are loaded before database initialization
Schema Conflicts: Check for table name conflicts between plugins
Initialization Errors: Review plugin database extension implementations

Future Database Support

The environment-based architecture makes it easy to add support for additional databases:

PostgreSQL: Planned for future release
MySQL: Possible future addition
Other SQLite-compatible databases: Can be added with minimal changes

The unified schema approach ensures that adding new database types requires minimal changes to existing application code.

Basics

Advanced

API

Database

Auth

Satellite

​Database Management

​Overview

​Database Setup and Configuration

​Environment Variables

​SQLite Configuration

​Turso Configuration

​Database Status

​Initial Database Setup

​Setup Examples

​API Response

​Database Selection File

​Architecture

​Key Components

​Database Drivers

​Database Connection Patterns

​Database Driver Compatibility

​The Problem: Different Result Property Names

​Real-World Impact

​DeployStack’s Built-in Solution

​Writing Compatible Database Code

​Testing Across Database Types

​Why This Happens

​Prevention Checklist

​Database Structure

​Making Schema Changes

​Migration Management

​Migration Compatibility

​Global Settings Integration

​Automatic Initialization

​Database-Specific Handling

​Plugin Database Extensions

​Development Workflow

​Database-Specific Considerations

​SQLite

​Turso

​Best Practices

​Schema Design

​Environment Management

​Migration Safety

​Inspecting Databases

​SQLite

​Turso

​Troubleshooting

​Setup Issues

​Migration Issues

​Performance Issues

​Plugin Issues

​Future Database Support

Database Management

Overview

Database Setup and Configuration

Environment Variables

SQLite Configuration

Turso Configuration

Database Status

Initial Database Setup

Setup Examples

API Response

Database Selection File

Architecture

Key Components

Database Drivers

Database Connection Patterns

Database Driver Compatibility

The Problem: Different Result Property Names

Real-World Impact

DeployStack’s Built-in Solution

Writing Compatible Database Code

Testing Across Database Types

Why This Happens

Prevention Checklist

Database Structure

Making Schema Changes

Migration Management

Migration Compatibility

Global Settings Integration

Automatic Initialization

Database-Specific Handling

Plugin Database Extensions

Development Workflow

Database-Specific Considerations

SQLite

Turso

Best Practices

Schema Design

Environment Management

Migration Safety

Inspecting Databases

SQLite

Turso

Troubleshooting

Setup Issues

Migration Issues

Performance Issues

Plugin Issues

Future Database Support