Skip to content

PRAGMA Operations

Jump to bottom
Temp edited this page Sep 23, 2025 · 1 revision

PRAGMA Operations

Last Updated: September 23, 2025 1:48 PM EST

The SQLite MCP Server provides comprehensive PRAGMA management tools for database configuration, optimization, and introspection.

🔧 Available PRAGMA Tools

Tool Description
pragma_settings Get/set SQLite PRAGMA configuration settings
pragma_optimize Run PRAGMA optimize for performance improvements
pragma_table_info Get detailed table schema using PRAGMA table_info
pragma_database_list List all attached databases with file paths
pragma_compile_options Show SQLite compile-time options and capabilities

⚙️ Configuration Management

Get/Set PRAGMA Settings

// Get current setting
pragma_settings({
  "pragma_name": "journal_mode"
})
// Returns: PRAGMA journal_mode = delete

// Set new value
pragma_settings({
  "pragma_name": "synchronous", 
  "value": "NORMAL"
})
// Sets and confirms: PRAGMA synchronous = NORMAL

Common Configuration Settings

Journal Mode Configuration

// Set WAL mode for better concurrency
pragma_settings({
  "pragma_name": "journal_mode",
  "value": "WAL"
})

// Check current journal mode
pragma_settings({
  "pragma_name": "journal_mode"
})

Synchronous Mode Settings

// Set synchronous mode for performance vs safety balance
pragma_settings({
  "pragma_name": "synchronous",
  "value": "NORMAL"  // OFF, NORMAL, FULL, EXTRA
})

// Production safety setting
pragma_settings({
  "pragma_name": "synchronous",
  "value": "FULL"
})

Cache Size Optimization

// Set cache size (in KB)
pragma_settings({
  "pragma_name": "cache_size",
  "value": "10000"  // 10MB cache
})

// Set cache size in pages (negative values)
pragma_settings({
  "pragma_name": "cache_size",
  "value": "-2000"  // 2000 pages
})

🚀 Performance Optimization

Database Optimization

pragma_optimize({
  "analysis_limit": 1000  // Optional: limit analysis scope
})
// Runs PRAGMA optimize for query performance improvements

Advanced Optimization Settings

// Comprehensive optimization
pragma_optimize({
  "analysis_limit": 5000,
  "include_stat4": true,      // Include histogram data
  "force_reanalyze": false,   // Only analyze if needed
  "vacuum_if_needed": true    // Run VACUUM if fragmented
})

// Quick optimization for production
pragma_optimize({
  "analysis_limit": 100,
  "quick_mode": true,
  "background_priority": true
})

Memory Management

// Set memory-mapped I/O size
pragma_settings({
  "pragma_name": "mmap_size",
  "value": "268435456"  // 256MB
})

// Configure temporary storage
pragma_settings({
  "pragma_name": "temp_store",
  "value": "memory"  // memory, file
})

// Set maximum page count
pragma_settings({
  "pragma_name": "max_page_count",
  "value": "1073741823"  // ~1TB limit
})

🔍 Database Introspection

Detailed Table Information

pragma_table_info({
  "table_name": "users",
  "include_foreign_keys": true  // Include FK and index info
})
// Returns: columns, foreign keys, indexes, constraints

Comprehensive Schema Analysis

// Get complete table information
pragma_table_info({
  "table_name": "products",
  "include_foreign_keys": true,
  "include_indexes": true,
  "include_triggers": true,
  "include_constraints": true
})

// Analyze all tables
pragma_table_info({
  "all_tables": true,
  "summary_only": true
})

Database List and Attachments

pragma_database_list()
// Returns: all attached databases with file paths and schemas

SQLite Capabilities

pragma_compile_options()
// Returns: SQLite version, compile options, feature availability

Returns information about:

  • SQLite version and build date
  • Compile-time options enabled
  • Available extensions
  • Threading model
  • Platform-specific features

🔧 Security Settings

Foreign Key Enforcement

// Enable foreign key constraints
pragma_settings({
  "pragma_name": "foreign_keys",
  "value": "ON"
})

// Check foreign key status
pragma_settings({
  "pragma_name": "foreign_keys"
})

// Defer foreign key checks
pragma_settings({
  "pragma_name": "defer_foreign_keys",
  "value": "ON"
})

Security-Related Settings

// Enable secure delete (overwrite deleted data)
pragma_settings({
  "pragma_name": "secure_delete",
  "value": "ON"
})

// Enable recursive triggers
pragma_settings({
  "pragma_name": "recursive_triggers",
  "value": "ON"
})

// Set trusted schema only
pragma_settings({
  "pragma_name": "trusted_schema",
  "value": "ON"
})

📊 Monitoring and Diagnostics

Database Statistics

// Get page count and size information
pragma_settings({
  "pragma_name": "page_count"
})

pragma_settings({
  "pragma_name": "page_size"
})

pragma_settings({
  "pragma_name": "freelist_count"
})

Performance Monitoring

// Check cache hit ratio
pragma_settings({
  "pragma_name": "cache_size"
})

// Monitor WAL checkpoint status
pragma_settings({
  "pragma_name": "wal_checkpoint"
})

// Check auto-vacuum status
pragma_settings({
  "pragma_name": "auto_vacuum"
})

Index Analysis

// List all indexes
pragma_settings({
  "pragma_name": "index_list",
  "table_name": "users"
})

// Get index information
pragma_settings({
  "pragma_name": "index_info",
  "index_name": "idx_users_email"
})

// Check index usage
pragma_settings({
  "pragma_name": "stats",
  "reset": false
})

💡 Real-World Configurations

High-Performance Setup

// Configure for maximum performance
const performanceConfig = [
  { pragma_name: "journal_mode", value: "WAL" },
  { pragma_name: "synchronous", value: "NORMAL" },
  { pragma_name: "cache_size", value: "-64000" }, // 64MB cache
  { pragma_name: "temp_store", value: "memory" },
  { pragma_name: "mmap_size", value: "268435456" }, // 256MB mmap
  { pragma_name: "optimize", value: null }
];

// Apply configuration
performanceConfig.forEach(config => {
  if (config.value !== null) {
    pragma_settings({
      pragma_name: config.pragma_name,
      value: config.value
    });
  } else {
    pragma_optimize();
  }
});

High-Safety Setup

// Configure for maximum data safety
const safetyConfig = [
  { pragma_name: "journal_mode", value: "DELETE" },
  { pragma_name: "synchronous", value: "FULL" },
  { pragma_name: "foreign_keys", value: "ON" },
  { pragma_name: "secure_delete", value: "ON" },
  { pragma_name: "auto_vacuum", value: "FULL" }
];

// Apply safety configuration
safetyConfig.forEach(config => {
  pragma_settings({
    pragma_name: config.pragma_name,
    value: config.value
  });
});

Memory-Constrained Setup

// Configure for low memory usage
const memoryConfig = [
  { pragma_name: "cache_size", value: "2000" },    // Small cache
  { pragma_name: "temp_store", value: "file" },    // Use disk for temp
  { pragma_name: "mmap_size", value: "0" },        // Disable mmap
  { pragma_name: "journal_mode", value: "TRUNCATE" }
];

// Apply memory-efficient configuration
memoryConfig.forEach(config => {
  pragma_settings({
    pragma_name: config.pragma_name,
    value: config.value
  });
});

🔧 Advanced PRAGMA Operations

Custom Optimization Routines

// Database health check routine
async function databaseHealthCheck() {
  // Check integrity
  const integrity = await pragma_settings({
    pragma_name: "integrity_check"
  });
  
  // Check fragmentation
  const pageCount = await pragma_settings({
    pragma_name: "page_count"
  });
  
  const freelistCount = await pragma_settings({
    pragma_name: "freelist_count"
  });
  
  // Calculate fragmentation percentage
  const fragmentation = (freelistCount / pageCount) * 100;
  
  // Optimize if needed
  if (fragmentation > 10) {
    await pragma_optimize({
      analysis_limit: 1000,
      vacuum_if_needed: true
    });
  }
  
  return {
    integrity: integrity.result,
    fragmentation: fragmentation,
    optimized: fragmentation > 10
  };
}

Performance Tuning Workflow

// Automatic performance tuning based on usage patterns
async function autoTuneDatabase() {
  // Analyze current settings
  const journalMode = await pragma_settings({
    pragma_name: "journal_mode"
  });
  
  const cacheSize = await pragma_settings({
    pragma_name: "cache_size"
  });
  
  // Check database size
  const pageCount = await pragma_settings({
    pragma_name: "page_count"
  });
  
  const pageSize = await pragma_settings({
    pragma_name: "page_size"
  });
  
  const dbSize = pageCount * pageSize;
  
  // Adjust settings based on database size
  if (dbSize > 100 * 1024 * 1024) { // >100MB
    // Large database optimizations
    await pragma_settings({
      pragma_name: "journal_mode",
      value: "WAL"
    });
    
    await pragma_settings({
      pragma_name: "cache_size",
      value: "-32000" // 32MB cache
    });
  }
  
  // Run optimization
  await pragma_optimize({
    analysis_limit: Math.min(5000, pageCount / 10)
  });
}

📋 Supported PRAGMA Commands

Configuration PRAGMAs

  • journal_mode: DELETE, TRUNCATE, PERSIST, MEMORY, WAL, OFF
  • synchronous: OFF, NORMAL, FULL, EXTRA
  • cache_size: Page cache size in KB or pages
  • temp_store: FILE, MEMORY
  • locking_mode: NORMAL, EXCLUSIVE

Performance PRAGMAs

  • optimize: Analyze and optimize query performance
  • analysis_limit: Limit for ANALYZE operations
  • mmap_size: Memory-mapped I/O size
  • threads: Number of helper threads

Security PRAGMAs

  • foreign_keys: Enable/disable foreign key constraints
  • recursive_triggers: Enable/disable recursive triggers
  • secure_delete: Overwrite deleted data
  • trusted_schema: Restrict schema modifications

Debugging PRAGMAs

  • compile_options: Show compile-time options
  • database_list: List attached databases
  • table_info: Table schema information
  • index_list: List table indexes
  • stats: Query planner statistics

🎯 Best Practices

1. Environment-Specific Configuration

// Development environment
const devConfig = {
  journal_mode: "DELETE",    // Simpler debugging
  synchronous: "OFF",        // Faster development
  foreign_keys: "ON"         // Catch constraint issues early
};

// Production environment
const prodConfig = {
  journal_mode: "WAL",       // Better concurrency
  synchronous: "NORMAL",     // Balance safety/performance
  foreign_keys: "ON",        // Data integrity
  secure_delete: "ON"        // Security
};

2. Regular Optimization Schedule

// Daily optimization
pragma_optimize({
  analysis_limit: 1000,
  quick_mode: true
});

// Weekly deep optimization
pragma_optimize({
  analysis_limit: 10000,
  force_reanalyze: true,
  vacuum_if_needed: true
});

3. Monitor Key Metrics

// Regular health monitoring
const healthMetrics = [
  "page_count",
  "freelist_count", 
  "cache_size",
  "journal_mode",
  "synchronous"
];

healthMetrics.forEach(async (metric) => {
  const value = await pragma_settings({
    pragma_name: metric
  });
  console.log(`${metric}: ${value}`);
});

4. Test Configuration Changes

// Always test PRAGMA changes in development first
async function testPragmaChange(pragmaName, newValue) {
  // Get current value
  const oldValue = await pragma_settings({
    pragma_name: pragmaName
  });
  
  try {
    // Apply new value
    await pragma_settings({
      pragma_name: pragmaName,
      value: newValue
    });
    
    // Test database operations
    await testDatabaseOperations();
    
    console.log(`Successfully changed ${pragmaName} to ${newValue}`);
  } catch (error) {
    // Revert on error
    await pragma_settings({
      pragma_name: pragmaName,
      value: oldValue
    });
    
    throw new Error(`Failed to change ${pragmaName}: ${error.message}`);
  }
}

📚 Related Pages

⚙️ Configuration Tip: PRAGMA settings are connection-specific and don't persist across database connections. For permanent changes, consider setting them in your application initialization code.

Clone this wiki locally