> ## Documentation Index
> Fetch the complete documentation index at: https://docs.deploystack.io/llms.txt
> Use this file to discover all available pages before exploring further.

# Database Management

> PostgreSQL database management with Drizzle ORM for DeployStack Backend development.

## 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](/self-hosted/database-setup).

> **PostgreSQL Technical Guide**: For detailed technical information, see the [PostgreSQL Development Guide](/development/backend/database/postgresql).

### Environment Variables

Configure PostgreSQL by setting these environment variables:

```bash theme={null}
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

```json theme={null}
{
  "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

```json theme={null}
{
  "type": "postgresql"
}
```

#### API Response

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

**Successful Setup:**

```json theme={null}
{
  "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):**

```json theme={null}
{
  "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:

```json theme={null}
{
  "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:

```typescript theme={null}
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:

```typescript theme={null}
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:

```typescript theme={null}
// 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**:

```typescript theme={null}
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**:

```typescript theme={null}
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**:

```typescript theme={null}
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](/development/backend/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`:

   ```typescript theme={null}
   // 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:

   ```bash theme={null}
   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:

   ```bash theme={null}
   npm run db:up
   ```

5. **Use the New Schema**

   Update your application code to use the new tables:

   ```typescript theme={null}
   // 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](/development/backend/global-settings).

## 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](/development/backend/plugins#plugin-integration-points).

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

```bash theme={null}
# 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

```bash theme={null}
POSTGRES_HOST=localhost
POSTGRES_PORT=5432
POSTGRES_DATABASE=deploystack
POSTGRES_USER=postgres
POSTGRES_PASSWORD=development_password
POSTGRES_SSL=false
```

### Production with SSL

```bash theme={null}
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

```yaml theme={null}
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:
```