Database Management

Overview

DeployStack uses SQLite with Drizzle ORM for database operations. This combination provides excellent performance, type safety, and a modern, developer-friendly experience without the need for external database dependencies.

Database Setup and Configuration

The backend server provides API endpoints for managing the initial database setup and checking its status.

Database Status

You can check the current status of the database (whether it's configured and initialized) using the following endpoint:

Endpoint: GET /api/db/status
Method: GET
Response: A JSON object indicating the database configured status (boolean), initialized status (boolean), and current dialect (e.g., "sqlite" or "postgres", or null if not configured).

Initial Database Setup

To perform the initial setup of the database, use the following endpoint:

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

For SQLite: The server will automatically manage the database file location. The request body should be:

{
  "type": "sqlite"
}

The SQLite database file will be created and stored at: services/backend/persistent_data/database/deploystack.db.

Important: All database files must be stored within the persistent_data directory to ensure proper data persistence and backup capabilities.

For PostgreSQL: The request body should be:

{
  "type": "postgres",
  "connectionString": "postgresql://username:password@host:port/mydatabase"
}

Replace the connectionString with your actual PostgreSQL connection URI.

Note: The database setup is now complete in a single API call. After successful setup, all database-dependent services (global settings, plugins, etc.) are automatically initialized and ready to use immediately. No server restart is required.

API Response

The setup endpoint returns a JSON response indicating the success status and whether a restart is required:

Successful Setup (No Restart Required):

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

Successful Setup (Restart Required - Fallback):

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

In most cases, the setup will complete successfully without requiring a restart. The restart_required: true response is a fallback for edge cases where the automatic re-initialization fails.

Database Configuration File

The choice of database (SQLite or PostgreSQL) and its specific configuration (like the connection string for PostgreSQL) is stored in a JSON file located at:

services/backend/persistent_data/db.selection.json

This file is automatically managed by the setup API. You typically do not need to edit it manually.

Key Components

SQLite: Embedded SQL database engine
Drizzle ORM: Type-safe ORM for TypeScript
Drizzle Kit: Schema migration tool for Drizzle ORM

Database Structure

The database schema is defined in src/db/schema.ts. It contains:

Base schema tables (core application)
Plugin tables (dynamically loaded)

Making Schema Changes

Follow these steps to add or modify database tables:

Modify Schema Definition

Edit src/db/schema.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(() => users.id),
  createdAt: integer('created_at', { mode: 'timestamp' }).notNull().default(sql`(strftime('%s', 'now'))`),
  updatedAt: integer('updated_at', { mode: 'timestamp' }).notNull().default(sql`(strftime('%s', 'now'))`),
});

// Don't forget to add it to baseSchema
export const baseSchema = {
  users,
  projects, // Add your new table here
};

Generate Migration

Run the migration generation command:
```
npm run db:generate
```
This will create SQL migration files in drizzle/migrations/ based on your schema changes.
Review Migrations

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

Either:
- Restart the application (migrations are applied on startup)
- Run migrations directly:
```
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;
});

Plugin Database Extensions

Plugins can add their own tables through the databaseExtension property:

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

Tables defined by plugins are automatically created when the plugin is loaded and initialized.

Migration Management

Migrations are tracked in a __drizzle_migrations table
Only new migrations are applied when the server starts
Migrations are applied in a transaction to ensure consistency

Development Workflow

Make schema changes in src/db/schema.ts
Generate migrations with npm run db:generate
Restart the server to apply migrations
Update application code to use the modified schema

Best Practices

Use meaningful column names and consistent naming conventions
Add appropriate indexes for columns that will be frequently queried
Include proper foreign key constraints for relational data
Add explicit types for all columns
Always use migrations for schema changes in development and production
Important: When adding foreign key relationships, update the dialect-specific schema files (e.g., src/db/schema.sqlite.ts) rather than the central schema.ts file, as Drizzle Kit uses these files for migration generation
Never manually create migration files - always use npm run db:generate to ensure proper migration structure

Inspecting the Database

You can inspect the SQLite database directly using various tools:

SQLite CLI:
```
sqlite3 services/backend/persistent_data/database/deploystack.db
```
(Assuming the command is run from the project root directory)
Visual Tools: DB Browser for SQLite or VSCode extensions like SQLite Viewer

Troubleshooting

Database Setup Issues

Setup fails with re-initialization error: If the setup endpoint returns restart_required: true, you can manually restart the server to complete the setup process
Database already configured: If you get a 409 error, the database has already been set up. Use the status endpoint to check the current configuration
Services not working after setup: Check the server logs for any initialization errors. In rare cases, a manual restart may be needed

Migration Issues

If you get a "table already exists" error, check if you've already applied the migration
For complex schema changes, you may need to create multiple migrations
To reset the database, delete the services/backend/persistent_data/database/deploystack.db file and restart the server

Plugin Issues

Plugins not working after setup: Plugins with database extensions should automatically receive database access after setup. Check server logs for plugin re-initialization messages
Plugin database tables missing: Ensure plugins are properly loaded before database setup, or restart the server if tables are missing

On this page