Scratch Files

Scratch Files provide a persistent workspace for your SQL queries by saving them to disk instead of using temporary untitled files. This feature automatically organizes your SQL work by connection and manages file rotation and cleanup.

Overview

When enabled, DBCode creates persistent scratch files on disk instead of opening untitled files. Each connection gets its own scratch file, and files rotate based on your configured schedule (hourly, daily, weekly, or monthly). Old files are automatically cleaned up based on your retention settings.

Key Benefits:

  • Persistent Storage: Your SQL snippets are saved to disk and survive editor restarts
  • Automatic Organization: Files are organized by connection name and time period
  • Automatic Cleanup: Old scratch files are deleted based on your configured retention period
  • Flexible Rotation: Choose how often new scratch files are created

Configuration

To enable and configure Scratch Files, open VS Code settings and search for “DBCode Scratch Files”:

Enable Scratch Files

DBCode > Scratch Files: Enabled

Enable scratch file mode to save SQL snippets to disk instead of using untitled files.

  • Default: false (disabled)

Storage Path

DBCode > Scratch Files: Path

Directory path to store scratch files. Supports ~ for home directory expansion.

  • Default: ~/.dbcode/scratch

Rotation Period

DBCode > Scratch Files: Rotation Period

How often to create a new scratch file per connection.

  • Default: day
  • Options:
    • hour - Create a new scratch file each hour
    • day - Create a new scratch file each day
    • week - Create a new scratch file each week
    • month - Create a new scratch file each month

Automatic Deletion

DBCode > Scratch Files: Delete

Automatically delete old scratch files based on their last modified date.

  • Default: 6months
  • Options:
    • never - Never automatically delete scratch files
    • 1month - Delete scratch files older than 1 month
    • 6months - Delete scratch files older than 6 months
    • 1year - Delete scratch files older than 1 year

File Naming and Organization

Scratch files are automatically named based on the connection, database, and time period:

Format: scratch-{connectionName}-{database}-{timestamp}.sql

Examples by Rotation Period

  • Hourly: scratch-postgres-prod-myapp-2025-10-17-14.sql
  • Daily: scratch-postgres-prod-myapp-2025-10-17.sql
  • Weekly: scratch-mysql-dev-analytics-2025-W42.sql
  • Monthly: scratch-oracle-test-hrdb-2025-10.sql

Connection and database names are sanitized (special characters replaced with -) to ensure valid filenames.

Using Scratch Files

Once enabled, scratch files work automatically:

  1. Opening SQL Queries: When you open a SQL query from the schema explorer or other DBCode actions, it opens in the scratch file for that connection instead of an untitled file

  2. Appending Queries: Multiple queries opened for the same connection are appended to the same scratch file (separated by blank lines)

  3. File Rotation: When the rotation period changes (e.g., a new day starts with daily rotation), a new scratch file is automatically created

  4. Editing: You can edit scratch files like any normal SQL file in VS Code

Commands

DBCode provides commands to manage your scratch files:

Open Scratch Files Folder

Command: DBCode: Open Scratch Files Folder

Opens the scratch files directory in your system file explorer, allowing you to browse, organize, or manually manage your scratch files.

Delete Old Scratch Files

Command: DBCode: Delete Old Scratch Files

Manually triggers the deletion of old scratch files based on your configured retention period. This is useful if you want to clean up immediately rather than waiting for the automatic cleanup cycle.

DBCode automatically checks for old files to delete every 24 hours. This command allows you to manually trigger the cleanup process.

Automatic Cleanup

DBCode automatically manages scratch file cleanup:

  • Initial Cleanup: When DBCode starts with scratch files enabled, it checks for and deletes old files
  • Periodic Cleanup: Every 24 hours, DBCode checks for files older than your configured retention period
  • Safe Deletion: Files currently open in the editor are never deleted, even if they exceed the retention period

Behavior Details

File Reuse

When scratch files are enabled:

  • If a scratch file for a connection already exists for the current period, queries are appended to it
  • If the file is already open in VS Code, new content is added to the end
  • If the file is closed, it’s opened and new content is appended

Connection and Database Organization

  • Each connection and database combination gets its own scratch files
  • Files are named using the connection name and database at creation time
  • If you rename a connection or switch databases, existing scratch files keep their original names

Best Practices

  1. Choose Rotation Based on Usage:

    • Use daily rotation for most workflows
    • Use hourly for rapid development sessions
    • Use weekly/monthly for lighter database work

  2. Set Appropriate Retention:

    • For audit purposes: Set to 1year or never
    • For active development: 1month or 6months is usually sufficient

  3. Organize Your Workspace:

    • Use the “Open Scratch Files Folder” command to review and organize files
    • Consider version controlling important scratch files

  4. Custom Storage Path:

    • Store scratch files in a project-specific directory by setting a custom path
    • Use workspace settings to have different scratch paths for different projects

Troubleshooting

Scratch files not being created

  1. Verify the feature is enabled in settings (dbcode.scratchFiles.enabled)
  2. Check that the configured path is valid and writable
  3. Look for error messages in the DBCode output panel
  4. Try manually creating the directory at your configured path

Files not being cleaned up

  1. Check your dbcode.scratchFiles.delete setting
  2. Verify files are actually older than the retention period
  3. Ensure files aren’t currently open in VS Code (open files are preserved)
  4. Manually run the “Delete Old Scratch Files” command

Permission errors

  1. Ensure you have write permissions to the configured directory
  2. Try using a different path in user home directory
  3. Check for filesystem restrictions or antivirus interference
  • Query History: Automatically track and recall executed queries
  • SQL Editor: Edit and execute SQL queries with syntax highlighting
  • Favorites: Save frequently-used queries for quick access