Skip to content

index.mdx

Latest commit

 

History

History
424 lines (319 loc) · 12.5 KB

index.mdx

File metadata and controls

424 lines (319 loc) · 12.5 KB
title Database Management
description PostgreSQL database management with Drizzle ORM for DeployStack Backend development.
sidebarTitle Overview

Overview

DeployStack uses PostgreSQL as its database backend, providing enterprise-grade reliability, ACID compliance, and excellent performance. The system leverages Drizzle ORM for type-safe database operations with a modern, developer-friendly experience.

PostgreSQL provides:

  • ACID Compliance - Full transactional support with rollback capabilities
  • Connection Pooling - Efficient connection management via node-postgres
  • Native Type System - Boolean, timestamp with timezone, JSONB, arrays, and more
  • Horizontal Scaling - Read replicas and partitioning for production deployments

Database Setup and Configuration

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

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

PostgreSQL Technical Guide: For detailed technical information, see the PostgreSQL Development Guide.

Environment Variables

Configure PostgreSQL by setting these environment variables:

POSTGRES_HOST=localhost
POSTGRES_PORT=5432
POSTGRES_DATABASE=deploystack
POSTGRES_USER=your_user
POSTGRES_PASSWORD=your_password
POSTGRES_SSL=false  # Set to 'true' for SSL connections

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": "postgresql"
}

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 Request

{
  "type": "postgresql"
}

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": "postgresql"
}

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": "postgresql"
}

Database Selection File

The database configuration 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": "postgresql",
  "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
  • node-postgres (pg): Native PostgreSQL driver with connection pooling
  • Environment Configuration: Database credentials via environment variables
  • Automatic Migrations: Migrations applied on server startup

Database Driver

The system uses the native PostgreSQL driver for optimal performance:

import { drizzle } from 'drizzle-orm/node-postgres';
import { Pool } from 'pg';

// PostgreSQL connection pool
const pool = new Pool({
  host: config.host,
  port: config.port,
  database: config.database,
  user: config.user,
  password: config.password,
  ssl: config.ssl ? { rejectUnauthorized: false } : false
});

const db = drizzle(pool, { schema });

Database Connection Patterns

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

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

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

Database Operations

Working with Query Results

PostgreSQL operations (INSERT, UPDATE, DELETE) return a result object with rowCount indicating the number of affected rows:

// PostgreSQL result object
{
  rowCount: 1,           // Number of affected rows
  rows: [],              // Returned rows from SELECT queries
  command: 'DELETE',     // SQL command type
  oid: 0,
  fields: []
}

Standard Patterns

Delete Operations:

export class McpInstallationService {
  async deleteInstallation(id: string): Promise<boolean> {
    const result = await this.db
      .delete(mcpServerInstallations)
      .where(eq(mcpServerInstallations.id, id));

    return (result.rowCount || 0) > 0;
  }
}

Update Operations:

export class TeamService {
  async updateTeamName(id: string, name: string): Promise<boolean> {
    const result = await this.db
      .update(teams)
      .set({ name, updated_at: new Date() })
      .where(eq(teams.id, id));

    return (result.rowCount || 0) > 0;
  }
}

Counting Affected Rows:

export class TokenService {
  async revokeExpiredTokens(): Promise<number> {
    const result = await this.db
      .delete(oauthAccessTokens)
      .where(lt(oauthAccessTokens.expires_at, Date.now()));

    return result.rowCount || 0;
  }
}

Database Structure

DeployStack uses PostgreSQL-native types and features:

Schema Files

src/db/schema.ts - PostgreSQL schema definition

  • Native PostgreSQL types (boolean, timestamp with timezone, jsonb)
  • Proper foreign key relationships and constraints
  • Migration directory: drizzle/migrations/

The schema contains:

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

Making Schema Changes

Follow these steps to add or modify database tables:

  1. Modify Schema Definitions

    Edit src/db/schema-tables/[table-group].ts:

    // Example: src/db/schema-tables/teams.ts
import { pgTable, text, timestamp } from 'drizzle-orm/pg-core';

export const projects = pgTable('projects', {
  id: text('id').primaryKey(),
  name: text('name').notNull(),
  createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
});

  2. Generate Migration

    Run the migration generation command:

    npm run db:generate

    This generates SQL migration files in drizzle/migrations/.

  3. Review Migrations

    Examine the generated SQL files in drizzle/migrations/ to ensure they match your intended changes.

  4. Apply Migrations

    Migrations are automatically applied on server startup. You can also run them manually:

    npm run db:up

  5. 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);
  return projects;
});

Migration Management

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

Global Settings Integration

During database setup, DeployStack automatically initializes global settings that configure the application:

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
  • Uses efficient batch operations with PostgreSQL connection pooling

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

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

PostgreSQL-Specific Features

Connection Pooling

  • Efficient connection management via node-postgres
  • Configurable pool size and timeout settings
  • Automatic connection recycling

Native Types

  • Boolean columns with native boolean type
  • Timestamps with timezone support
  • JSONB for efficient JSON storage
  • Arrays and custom types

Advanced Features

  • Multi-version concurrency control (MVCC)
  • Point-in-time recovery and continuous archiving
  • Full-text search capabilities
  • Horizontal scaling with read replicas

Inspecting the Database

# Using psql CLI
psql -h localhost -U your_user -d deploystack

# Common psql commands
\dt              # List all tables
\d tablename     # Describe table structure
\q               # Quit

# Using pgAdmin (GUI)
# Download from: https://www.pgadmin.org/

Environment Configuration Examples

Development

POSTGRES_HOST=localhost
POSTGRES_PORT=5432
POSTGRES_DATABASE=deploystack
POSTGRES_USER=postgres
POSTGRES_PASSWORD=development_password
POSTGRES_SSL=false

Production with SSL

POSTGRES_HOST=production-host.example.com
POSTGRES_PORT=5432
POSTGRES_DATABASE=deploystack
POSTGRES_USER=deploystack_user
POSTGRES_PASSWORD=secure_production_password
POSTGRES_SSL=true

Docker Compose

services:
  postgres:
    image: postgres:18-alpine
    environment:
      POSTGRES_DB: deploystack
      POSTGRES_USER: deploystack
      POSTGRES_PASSWORD: your_secure_password
    ports:
      - "5432:5432"
    volumes:
      - postgres_data:/var/lib/postgresql/data

  backend:
    build: ./services/backend
    environment:
      POSTGRES_HOST: postgres
      POSTGRES_PORT: 5432
      POSTGRES_DATABASE: deploystack
      POSTGRES_USER: deploystack
      POSTGRES_PASSWORD: your_secure_password
      POSTGRES_SSL: false
    depends_on:
      - postgres

volumes:
  postgres_data: