Skip to content

Database Management

Jump to bottom
Temp edited this page Jan 7, 2026 · 5 revisions

Database Management

Complete guide to managing Cloudflare D1 databases with D1 Database Manager.

Overview

D1 Manager provides comprehensive database management capabilities:

  • 📋 Grid/List database views with search, badges (FTS5), and color tags
  • ➕ Create new databases
  • ✏️ Rename databases (migration-based with automatic safety backups)
  • 🗑️ Delete databases (with optional R2 safety backup)
  • ☁️ R2 Backup/Restore + Backup Hub - Cloud backups with multi-select restore/download and orphaned-backup visibility
  • ⏰ Scheduled R2 Backups - Daily/weekly/monthly schedules with next-run/last-run tracking
  • 📦 Bulk operations (download, delete, optimize)
  • 📤 Upload/import SQL files (upload or paste)
  • ⚡ Optimize database performance

Database List View

Search & Filter Databases

Use the search box to quickly find databases by name:

┌─────────────────────────────────────────────────┐
│ 🔍 Search databases...                           │
└─────────────────────────────────────────────────┘

Showing 3 of 12 databases matching "prod"

Features:

  • Instant filtering - Results update as you type
  • Case-insensitive - "PROD", "prod", and "Prod" all match
  • Partial matching - Search "app" matches "my-app-db", "app-data", etc.
  • Clear button - Click X to clear the search and show all databases

This is particularly useful when managing many databases, helping you quickly locate specific databases without scrolling through a long list.

Database Cards and List View

Toggle between grid and list layouts. Each database entry shows:

┌─────────────────────────────────┐
│ [✓] 📊 my-database    production│
│                                 │
│ UUID: a1b2c3d4-...              │
│ Created: Nov 2, 2024            │
│ Size: 2.4 MB                    │
│ Tables: 12                      │
│                                 │
│ [Browse] [Query] [Rename]       │
└─────────────────────────────────┘

Card/List Information:

  • Checkbox - For bulk operations
  • Name - Database name
  • Badges - FTS5 indicator, replication, STRICT/FTS5 counts, version
  • Color - Custom color tag (where set)
  • UUID - Unique identifier
  • Created - Creation date
  • Size - Database file size
  • Tables - Number of tables

Quick Actions (grid and list):

  • Browse / Query / Rename / Clone - Primary actions
  • Import / Download / Optimize / Delete - Secondary actions
  • Backup/Restore (R2) - Create or restore backups (when R2 is configured)
  • Backup & Restore Hub - Open unified hub to see undo + R2 backups (includes backup counts and orphaned backups)

Multi-Select

Use checkboxes to select multiple databases for bulk operations:

  1. Click checkbox on each database card
  2. Or click "Select All" to select all databases
  3. Perform bulk operations (see below)

Creating a Database

Create Single Database

  1. Click "Create Database" button
  2. Enter a database name
  3. Click "Create"

Name Requirements:

  • 3-63 characters
  • Lowercase letters (a-z)
  • Numbers (0-9)
  • Hyphens (-) allowed (not at start/end)
  • Must be unique

Valid Names:

✅ my-database
✅ app-db-2024
✅ production-data
✅ test-db-001

Invalid Names:

❌ MyDatabase (uppercase)
❌ db (too short)
❌ -database (starts with hyphen)
❌ database- (ends with hyphen)
❌ my_database (underscores not allowed)

API Endpoint

POST /api/databases
Content-Type: application/json

{
  "name": "my-database"
}

Renaming a Database

D1 doesn't natively support database renaming, so D1 Manager uses a migration-based approach with built-in safety backups (undo snapshot and optional R2 backup).

Rename Process

1. Validate new name
2. Create new database with desired name
3. Export all data from original database
4. Import data into new database
5. Verify data integrity
6. Delete original database

How to Rename

  1. Click "Rename" on a database card or row (grid/list)
  2. Review the important warnings
  3. Choose backup option (R2 backup, download, or skip) — automatic R2 backup is recommended
  4. Enter the new database name
  5. Check the confirmation boxes
  6. Click "Rename Database"

Progress Tracking

The rename dialog shows real-time progress:

Step 1/6: Validating and preparing...        16%
Step 2/6: Creating new database...           33%
Step 3/6: Exporting data...                  50%
Step 4/6: Importing data...                  66%
Step 5/6: Verifying data integrity...        83%
Step 6/6: Cleaning up...                    100%

Verification Process

After importing data, the system automatically verifies integrity:

✅ Table Count Verification

  • Ensures all tables were migrated
  • Checks source and target table counts match

✅ Row Count Verification

  • Validates row counts for each table
  • Confirms no data loss during migration

✅ Schema Verification

  • Checks column counts match
  • Ensures table structures are identical

If Verification Fails:

  • New database is automatically deleted
  • Original database remains untouched
  • Detailed error message shows what failed

FTS5 Limitations

🚫 Databases with FTS5 tables cannot be renamed

Cloudflare D1's export API does not support virtual tables (FTS5). If your database contains FTS5 (Full-Text Search) tables, the rename operation will be immediately blocked with a clear error message listing the FTS5 tables.

Workaround:

  1. Create a new database manually
  2. Migrate non-FTS5 tables using the migration wizard
  3. Recreate FTS5 tables manually in the new database
  4. Delete the original database

See FTS5-Full-Text-Search for more information about FTS5 tables.

Important Warnings

⚠️ Temporary Duplication:

  • Both databases exist during migration
  • Counts toward your D1 quota temporarily
  • Original deleted after successful migration

⚠️ Data Migration Limitations:

  • DEFAULT values (like datetime('now')) are not preserved
  • Column structure, types, and constraints are migrated correctly
  • All data is migrated
  • Indexes are recreated

⚠️ Risk of Data Loss:

  • Always download a backup first
  • Migration can fail if quota exceeded
  • Network issues can interrupt process

Backup Before Rename

You have two backup options before renaming:

  • Automatic R2 backup (recommended) — shown inline in the dialog; stored in R2 with Before Rename source tag and visible in the Backup & Restore hub (including orphaned backups)
  • Download backup — export as SQL for an offline copy

See R2-Backup-Restore for complete backup documentation.

Rollback on Failure

If the rename fails:

  • The new database is automatically deleted
  • The original database remains unchanged
  • Error message displayed with details

API Endpoint

POST /api/databases/:dbId/rename
Content-Type: application/json

{
  "newName": "my-new-database"
}

Deleting Databases

Delete Single Database

  1. Select database using checkbox
  2. Click "Delete Selected"
  3. Review the confirmation dialog
  4. Click "Delete Database"
  5. (Optional, recommended) Create an R2 backup from the dialog before confirming

Delete Multiple Databases

  1. Select multiple databases using checkboxes
  2. Click "Delete Selected"
  3. Review the list of databases to delete
  4. Click "Delete X Databases"

Progress Tracking

For bulk deletes:

Deleting database 1 of 3...
Deleting database 2 of 3...
Deleting database 3 of 3...

Progress bar shows completion percentage.

Safety Backups:

  • Delete dialogs offer an R2 backup option (when configured) and automatically create an undo snapshot
  • Backups appear in the Backup & Restore hub with Delete Database source tags, including orphaned backups for deleted databases

Protected System Database

The d1-manager-metadata database is protected:

  • Hidden - Doesn't appear in database list
  • Delete Protection - Returns 403 Forbidden if deletion attempted
  • Rename Protection - Returns 403 Forbidden if rename attempted
  • Export Protection - Silently skipped in bulk exports

API Endpoint

DELETE /api/databases/:dbId

Bulk Operations

Select All / Clear Selection

Select All:

  • Click "Select All" button
  • All databases selected instantly

Clear Selection:

  • Click "Clear Selection" button
  • All checkboxes unchecked

Bulk Download

Export multiple databases in your chosen format (SQL, JSON, or CSV) as a ZIP archive.

Steps:

  1. Select databases using checkboxes
  2. Choose export format from dropdown (SQL/JSON/CSV)
  3. Click "Download Selected"
  4. Wait for export (progress shows current database name)
  5. ZIP file downloads automatically

Format Selection:

┌─────────┐ ┌──────────────────────┐
│  SQL ▼  │ │ Download Selected    │
└─────────┘ └──────────────────────┘

Progress Indicator:

┌─────────────────────────────────────────────────────┐
│ ↻ Exporting my-database...           2 / 5 databases │
│ ████████████████░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░  40% │
└─────────────────────────────────────────────────────┘

Export Formats

SQL Format (Default)

  • Complete database dump with CREATE TABLE + INSERT statements
  • Fully compatible with D1 import
  • Best for exact database recreation
databases-sql-1704614400000.zip
├── my-database.sql
├── test-database.sql
└── production-db.sql

JSON Format

  • Portable JSON with metadata and data as arrays of objects
  • Preserves column types in metadata
  • Good for data transformation pipelines
databases-json-1704614400000.zip
├── my-database.json
├── test-database.json
└── production-db.json

JSON structure:

{
  "metadata": {
    "databaseName": "my-database",
    "exportedAt": "2024-01-07T10:00:00.000Z",
    "tables": [{
      "name": "users",
      "columns": [
        {"name": "id", "type": "INTEGER"},
        {"name": "email", "type": "TEXT"}
      ],
      "rowCount": 100
    }]
  },
  "tables": {
    "users": [
      {"id": 1, "email": "alice@example.com"},
      {"id": 2, "email": "bob@example.com"}
    ]
  }
}

CSV Format

  • ZIP containing _metadata.json + CSV per table
  • Spreadsheet-compatible for data analysis
  • Empty tables included in metadata (no CSV file)
databases-csv-1704614400000.zip
└── my-database/
    ├── _metadata.json
    ├── users.csv
    └── orders.csv

Metadata includes column types for schema reconstruction:

{
  "databaseName": "my-database",
  "tables": [{
    "name": "users",
    "columns": [
      {"name": "id", "type": "INTEGER"},
      {"name": "email", "type": "TEXT"}
    ],
    "rowCount": 100
  }]
}

Rate Limiting:

  • 300ms delay between each database export
  • Exponential backoff (2s→4s→8s) on 429 errors
  • Prevents API throttling for large batch exports

Progress Tracking:

Preparing...                      0%
Exporting my-database (1/3)...   33%
Exporting test-db (2/3)...       66%
Exporting production (3/3)...   100%
Download Complete

Bulk Delete

Delete multiple databases with progress tracking.

Steps:

  1. Select databases using checkboxes
  2. Click "Delete Selected"
  3. Review confirmation dialog
  4. Click "Delete X Databases"
  5. Watch progress

Error Handling:

  • Continues even if one database fails
  • Shows which databases failed with error messages
  • Successful deletions are completed

API Call:

POST /api/databases/bulk-delete
Content-Type: application/json

{
  "databaseIds": ["uuid1", "uuid2", "uuid3"]
}

Bulk Optimize

Run ANALYZE on multiple databases to improve query performance.

What it Does:

  • Runs PRAGMA optimize (equivalent to ANALYZE)
  • Updates query statistics
  • Helps SQLite query planner choose better execution plans
  • No data modification

Steps:

  1. Select databases using checkboxes
  2. Click "Optimize Selected"
  3. Review optimization dialog
  4. Click "Optimize X Databases"
  5. Wait for completion

Progress:

Running ANALYZE (Database 1 of 3)...
Running ANALYZE (Database 2 of 3)...
Running ANALYZE (Database 3 of 3)...

Note about VACUUM:

  • VACUUM is not available via D1 REST API
  • D1 automatically manages space reclamation
  • For manual VACUUM, use Wrangler CLI:
wrangler d1 execute my-database --remote --command="VACUUM"

When to Optimize:

  • After bulk data imports
  • After creating new indexes
  • After significant schema changes
  • Periodically for large databases

Upload/Import Database

Upload SQL files to create new databases or import into existing ones.

Upload Dialog

  1. Click "Upload Database" button
  2. Select SQL file (up to 5GB)
  3. Choose import mode:
    • Create new database - Creates a new database
    • Import into existing - Adds to existing database

Create New Database from SQL

Steps:

  1. Select "Create new database"
  2. Enter database name
  3. Choose SQL file
  4. Click "Upload"

Process:

1. Create new database
2. Parse SQL file
3. Execute CREATE TABLE statements
4. Execute INSERT statements
5. Create indexes
6. Verify import

Import into Existing Database

Steps:

  1. Select "Import into existing database"
  2. Choose target database from dropdown
  3. Select SQL file
  4. Click "Upload"

Behavior:

  • Executes SQL statements in order
  • Adds to existing tables (doesn't replace)
  • Creates new tables if they don't exist
  • May fail if conflicts with existing schema

SQL File Format

D1 Manager accepts standard SQL dumps:

-- Comments are supported
CREATE TABLE IF NOT EXISTS users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  email TEXT UNIQUE
);

INSERT INTO users (name, email) VALUES
  ('Alice', 'alice@example.com'),
  ('Bob', 'bob@example.com');

CREATE INDEX idx_users_email ON users(email);

Size Limits

  • Maximum file size: 5GB
  • API limitation: D1 REST API limits
  • For larger databases, use Wrangler CLI:
wrangler d1 execute my-database --remote --file=large-dump.sql

Error Handling

Common errors:

  • Syntax errors - Invalid SQL
  • Duplicate keys - Data conflicts with existing rows
  • Type mismatches - Data doesn't match column types
  • Quota exceeded - Account D1 storage limit reached

API Endpoint

POST /api/databases/import
Content-Type: multipart/form-data

{
  "file": [SQL file],
  "createNew": true,
  "databaseName": "my-database"
}

Or for import:

POST /api/databases/import
Content-Type: multipart/form-data

{
  "file": [SQL file],
  "createNew": false,
  "targetDatabaseId": "database-uuid"
}

Database Information

View Database Details

Click on a database card to view:

  • All tables in the database
  • Table count
  • Quick access to tables

Database Metadata

Metadata shown on each card:

UUID:

a1b2c3d4-e5f6-7890-abcd-ef1234567890

Unique identifier for API calls and Wrangler commands.

Created Date:

Nov 2, 2024

When the database was created.

File Size:

2.4 MB

Total storage used (data + indexes + overhead).

Table Count:

12 tables

Number of tables in the database.

Version Badge:

  • production - Production databases (green)
  • alpha - Alpha databases (yellow)
  • beta - Beta databases (yellow)

Get Database Info API

GET /api/databases/:dbId/info

Response:

{
  "result": {
    "uuid": "a1b2c3d4-...",
    "name": "my-database",
    "version": "production",
    "created_at": "2024-11-02T12:00:00.000Z",
    "file_size": 2457600,
    "num_tables": 12
  }
}

Best Practices

Naming Conventions

Use descriptive, consistent names:

✅ production-users-db
✅ staging-app-data
✅ test-integration-2024
✅ analytics-warehouse

Organization

Prefix databases by environment:

prod-app-main
prod-app-analytics
stage-app-main
stage-app-analytics
dev-app-main
test-app-main

Backup Strategy

  1. Before Major Operations:

    • Always backup before rename
    • Backup before bulk imports
    • Backup before schema changes

  2. Regular Backups:

    • Daily backups for production
    • Weekly backups for staging
    • On-demand for development

  3. Backup Methods:

    • R2 Cloud Backup - Recommended for production (see R2-Backup-Restore)
    • Bulk download as ZIP
    • Individual SQL exports
    • Wrangler CLI exports

Performance

  1. Optimize Regularly:

    • After bulk data changes
    • After index creation
    • Monthly for active databases

  2. Monitor Size:

    • Watch database file size
    • Plan for growth
    • Archive old data

  3. Clean Up:

    • Delete unused databases
    • Remove test databases
    • Keep development databases small

Troubleshooting

"Failed to create database"

Causes:

  • Name already exists
  • Invalid name format
  • Quota exceeded
  • API permissions issue

Solutions:

# Check quota
wrangler d1 list

# Verify API token has D1 Edit permission
# Check name follows naming rules

"Rename failed: Quota exceeded"

Cause: Not enough space for temporary duplicate

Solution:

# Free up space by deleting unused databases
# Or upgrade your Cloudflare plan

"Import failed: Syntax error"

Cause: Invalid SQL in uploaded file

Solution:

  • Validate SQL syntax
  • Check for unsupported SQLite features
  • Remove comments if causing issues

"Database list not loading"

Causes:

  • API credentials incorrect
  • Network issue
  • Worker not deployed

Solutions:

# Verify secrets
npx wrangler secret list

# Check Worker status
npx wrangler tail

# Test API endpoint
curl https://your-worker.workers.dev/api/databases

API Reference

List Databases

GET /api/databases

Create Database

POST /api/databases
Content-Type: application/json

{"name": "my-database"}

Get Database Info

GET /api/databases/:dbId/info

Rename Database

POST /api/databases/:dbId/rename
Content-Type: application/json

{"newName": "new-name"}

Delete Database

DELETE /api/databases/:dbId

Bulk Export

POST /api/databases/export
Content-Type: application/json

{
  "databases": [
    {"uuid": "id1", "name": "db1"},
    {"uuid": "id2", "name": "db2"}
  ]
}

Import Database

POST /api/databases/import
Content-Type: multipart/form-data

{
  "file": [SQL file],
  "createNew": true|false,
  "databaseName": "name" (if createNew),
  "targetDatabaseId": "uuid" (if !createNew)
}

Next Steps

Need Help? See Troubleshooting or open an issue.

D1 Manager Wiki

🚀 Getting Started

📚 Core Features

🔗 Relationships

🔍 Search & Analysis

📊 Observability

🔧 Advanced Features

⚙️ Development

🌐 Deployment

📖 Reference

Clone this wiki locally