Skip to main content

​
SQLite Database Development Guide

​
Overview

SQLite is the default database for DeployStack development and small to medium deployments. It provides excellent performance, zero configuration, and a simple file-based architecture that makes it ideal for development, testing, and single-server deployments.
Setup Instructions: For initial SQLite configuration, see the Database Setup Guide.

​
Technical Architecture

​
File-Based Database

SQLite stores the entire database in a single file, making it extremely portable and easy to manage:
  • Database File: persistent_data/database/deploystack.db
  • Zero Configuration: No server setup or network configuration required
  • ACID Compliance: Full transaction support with rollback capabilities
  • Cross-Platform: Works identically across all operating systems

​
Direct Driver Integration

DeployStack uses the better-sqlite3 driver for optimal SQLite performance: 
import { drizzle } from 'drizzle-orm/better-sqlite3';
import Database from 'better-sqlite3';

// Direct file-based connection
const sqlite = new Database(dbPath);
const db = drizzle(sqlite, { schema });

​
Performance Characteristics

​
Advantages

Fast Local Operations:
  • No network latency for database operations
  • Direct file system access for maximum speed
  • Excellent read performance for concurrent operations
Simple Deployment:
  • Single file contains entire database
  • No separate database server required
  • Easy backup and restore operations
Development Friendly:
  • Instant startup with no configuration
  • Easy to reset and recreate for testing
  • Perfect for local development workflows

​
Limitations

Single Server Only:
  • Cannot be shared across multiple application instances
  • No built-in replication or clustering
  • Limited to single-server deployments
Concurrent Write Limitations:
  • Single writer at a time (multiple readers supported)
  • Write operations are serialized
  • May become a bottleneck under heavy write loads

​
Development Workflow

​
Local Development Setup

SQLite is the recommended database for local development: 
# SQLite requires no additional setup
DB_TYPE=sqlite

# Optional: Custom database path
SQLITE_DB_PATH=persistent_data/database/my-custom.db

​
Database File Management

Default Location: services/backend/persistent_data/database/deploystack.db Directory Structure: 
services/backend/
├── persistent_data/
│   ├── database/
│   │   └── deploystack.db          # Main database file
│   └── db.selection.json           # Database type selection

​
Testing with SQLite

SQLite is excellent for testing due to its simplicity: 
// Test setup - create temporary database
const testDb = new Database(':memory:'); // In-memory for speed
// or
const testDb = new Database('test.db');  // File-based for persistence

// Run migrations
await migrate(drizzle(testDb), { migrationsFolder: './migrations' });

// Run tests
// ...

// Cleanup
testDb.close();

​
Global Settings Integration

​
Batch Operations

SQLite excels at batch operations and can handle large global settings initialization efficiently:
  • Large Batches: Can insert all 17+ global settings in a single transaction
  • No Parameter Limits: Unlike D1, SQLite has no practical parameter limits
  • Transaction Safety: All settings created atomically

​
Performance Benefits

// SQLite can handle large batch operations efficiently
await db.transaction(async (tx) => {
  // Insert all settings in a single transaction
  await tx.insert(globalSettings).values(allSettingsData);
  await tx.insert(globalSettingGroups).values(allGroupsData);
});

​
Database Inspection and Debugging

​
SQLite CLI

The SQLite command-line interface is the primary tool for database inspection: 
# Open database
sqlite3 services/backend/persistent_data/database/deploystack.db

# Common commands
.tables                    # List all tables
.schema tablename         # Show table schema
.headers on               # Show column headers
.mode column              # Format output in columns

# Query examples
SELECT * FROM globalSettings LIMIT 10;
SELECT COUNT(*) FROM users;
.quit                     # Exit

​
GUI Tools

DB Browser for SQLite (Recommended):
  • Download: https://sqlitebrowser.org/
  • Visual table browsing and editing
  • Query execution with syntax highlighting
  • Schema visualization
Other Options:
  • SQLiteStudio: Cross-platform SQLite manager
  • DBeaver: Universal database tool with SQLite support
  • VS Code Extensions: SQLite Viewer, SQLite3 Editor

​
Programmatic Inspection

// Get database info
const info = db.prepare("PRAGMA database_list").all();
const tables = db.prepare("SELECT name FROM sqlite_master WHERE type='table'").all();

// Check table structure
const schema = db.prepare("PRAGMA table_info(globalSettings)").all();

// Performance analysis
const stats = db.prepare("PRAGMA compile_options").all();

​
Backup and Recovery

​
File-Based Backup

SQLite’s file-based nature makes backup extremely simple: 
# Simple file copy (when database is not in use)
cp persistent_data/database/deploystack.db backup/deploystack-$(date +%Y%m%d).db

# Using SQLite backup command (safe during operation)
sqlite3 persistent_data/database/deploystack.db ".backup backup/deploystack-$(date +%Y%m%d).db"

​
Automated Backup Script

#!/bin/bash
# backup-sqlite.sh

DB_PATH="persistent_data/database/deploystack.db"
BACKUP_DIR="backup"
DATE=$(date +%Y%m%d_%H%M%S)

mkdir -p $BACKUP_DIR

# Create backup
sqlite3 $DB_PATH ".backup $BACKUP_DIR/deploystack-$DATE.db"

# Keep only last 7 days of backups
find $BACKUP_DIR -name "deploystack-*.db" -mtime +7 -delete

echo "Backup created: $BACKUP_DIR/deploystack-$DATE.db"

​
Recovery

# Restore from backup
cp backup/deploystack-20250103.db persistent_data/database/deploystack.db

# Or using SQLite restore
sqlite3 persistent_data/database/deploystack.db ".restore backup/deploystack-20250103.db"

​
Performance Optimization

​
Indexing Strategy

SQLite benefits greatly from proper indexing: 
-- Example indexes for common queries
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_global_settings_key ON globalSettings(key);
CREATE INDEX idx_sessions_user_id ON sessions(user_id);
CREATE INDEX idx_teams_created_at ON teams(created_at);

​
PRAGMA Settings

Optimize SQLite performance with PRAGMA settings: 
// Performance optimizations
db.pragma('journal_mode = WAL');    // Write-Ahead Logging
db.pragma('synchronous = NORMAL');  // Balanced safety/performance
db.pragma('cache_size = 1000000');  // 1GB cache
db.pragma('temp_store = MEMORY');   // Use memory for temp tables

​
Connection Pooling

While SQLite doesn’t need traditional connection pooling, you can optimize connection usage: 
// Reuse single connection
const sqlite = new Database(dbPath, {
  readonly: false,
  fileMustExist: false,
  timeout: 5000,
  verbose: process.env.NODE_ENV === 'development' ? (message) => {
    server.log.debug({ operation: 'sqlite_query' }, message);
  } : undefined
});

// Enable WAL mode for better concurrency
sqlite.pragma('journal_mode = WAL');

​
Migration Considerations

​
SQLite-Specific Features

SQLite has some unique characteristics for migrations: 
-- SQLite doesn't support all ALTER TABLE operations
-- Instead of ALTER COLUMN, you need to recreate the table

-- Example: Adding a column (supported)
ALTER TABLE users ADD COLUMN phone TEXT;

-- Example: Changing column type (not supported directly)
-- Requires table recreation:
CREATE TABLE users_new (
  id TEXT PRIMARY KEY,
  email TEXT NOT NULL,
  name TEXT NOT NULL,
  age INTEGER  -- Changed from TEXT to INTEGER
);

INSERT INTO users_new SELECT id, email, name, CAST(age AS INTEGER) FROM users;
DROP TABLE users;
ALTER TABLE users_new RENAME TO users;

​
Migration Best Practices

  1. Test Migrations: Always test on a copy of production data
  2. Backup Before Migration: Create backup before applying migrations
  3. Use Transactions: Wrap migrations in transactions for rollback capability
  4. Check Constraints: Verify foreign key constraints after table recreation

​
Troubleshooting

​
Common Issues

“Database is locked”
  • Cause: Another process has the database open
  • Solution: Ensure only one application instance accesses the database
  • Prevention: Use WAL mode for better concurrency
“No such table” errors
  • Cause: Migrations haven’t been applied
  • Solution: Run npm run db:up or restart the server
  • Check: Verify migration files exist in drizzle/migrations_sqlite/
Poor performance
  • Cause: Missing indexes or suboptimal queries
  • Solution: Add appropriate indexes and optimize queries
  • Analysis: Use EXPLAIN QUERY PLAN to analyze query performance
File corruption
  • Cause: Unexpected shutdown or disk issues
  • Solution: Restore from backup
  • Prevention: Use WAL mode and regular backups

​
Debugging Queries

// Enable query logging
const db = drizzle(sqlite, { 
  schema,
  logger: {
    logQuery: (query, params) => {
      server.log.debug({ operation: 'sqlite_query', query, params }, 'Executing query');
    }
  }
});

// Analyze query performance
const explain = db.prepare('EXPLAIN QUERY PLAN SELECT * FROM users WHERE email = ?').all('test@example.com');
server.log.debug({ operation: 'sqlite_explain', explain }, 'Query execution plan');

​
Production Considerations

​
When to Use SQLite in Production

Good For:
  • Single-server applications
  • Read-heavy workloads
  • Small to medium datasets (< 1TB)
  • Applications with predictable load patterns
  • Embedded applications
Consider Alternatives When:
  • Multiple application servers needed
  • High concurrent write requirements
  • Need for real-time replication
  • Distributed deployment requirements

​
Production Optimizations

// Production SQLite configuration
const sqlite = new Database(dbPath, {
  readonly: false,
  fileMustExist: true,
  timeout: 10000
});

// Production PRAGMA settings
sqlite.pragma('journal_mode = WAL');
sqlite.pragma('synchronous = NORMAL');
sqlite.pragma('cache_size = 2000000');  // 2GB cache
sqlite.pragma('mmap_size = 268435456'); // 256MB memory-mapped I/O
sqlite.pragma('optimize');              // Optimize database

​
Monitoring

// Monitor database size and performance
const stats = {
  fileSize: fs.statSync(dbPath).size,
  pageCount: db.prepare('PRAGMA page_count').get(),
  pageSize: db.prepare('PRAGMA page_size').get(),
  walSize: fs.existsSync(dbPath + '-wal') ? fs.statSync(dbPath + '-wal').size : 0
};

server.log.info({ operation: 'sqlite_monitoring', stats }, 'Database statistics');

​
Integration with DeployStack Features

​
Global Settings

SQLite provides optimal performance for global settings:
  • Fast initialization: All settings created in single transaction
  • No batching needed: No parameter limits to worry about
  • Immediate consistency: All changes immediately visible

​
Plugin System

Plugins work seamlessly with SQLite:
  • Table creation: Plugin tables created through standard migrations
  • Data operations: Full SQL feature support
  • Performance: Excellent performance for plugin data operations

​
Migration System

SQLite migration advantages:
  • Fast execution: Local file operations are very fast
  • Transaction safety: Full rollback support for failed migrations
  • Simple debugging: Easy to inspect database state during development

​
Future Considerations

​
Scaling Beyond SQLite

When you outgrow SQLite, DeployStack makes migration easy:
  1. Export Data: Use SQLite’s .dump command
  2. Transform Schema: Convert to target database format
  3. Update Configuration: Change database type in setup
  4. Import Data: Load data into new database

​
Hybrid Approaches

Consider hybrid approaches for scaling:
  • Read Replicas: Use D1 or Turso for global read access
  • Caching Layer: Add Redis for frequently accessed data
  • Microservices: Split into multiple services with separate databases
For general database concepts and cross-database functionality, see the Database Development Guide. For initial setup and configuration, see the Database Setup Guide.
⌘I