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 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
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.

On this page