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

Perform the initial database setup by selecting your database type:

Endpoint: POST /api/db/setup
Method: POST
Request Body: JSON object specifying the database type

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

This file is automatically created and managed by the setup API. Manual editing is not recommended.

Example content:

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

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

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 add their own tables through the databaseExtension property:

Define tables in the plugin's schema file
Include tables in the plugin's databaseExtension.tableDefinitions
Implement onDatabaseInit() for seeding or initialization

Plugin tables are automatically created and work across all database types.

Plugin Global Settings

Plugins can also contribute global settings that are automatically integrated during database setup:

// Example plugin with global settings
class MyPlugin implements Plugin {
  globalSettingsExtension: GlobalSettingsExtension = {
    groups: [{ id: 'my_plugin', name: 'My Plugin Settings' }],
    settings: [
      {
        key: 'myPlugin.feature.enabled',
        defaultValue: true,
        type: 'boolean',
        groupId: 'my_plugin'
      }
    ]
  };
}

Development Workflow

Environment Setup: Configure environment variables for your chosen database
Database Selection: Use /api/db/setup to select and initialize database
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

Database-Specific Considerations

SQLite

File Location: persistent_data/database/deploystack.db
Performance: Excellent for development and small to medium deployments
Backup: Simple file-based backup

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
sqlite3 services/backend/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