Table Operations

Table Operations

Complete guide to managing tables in D1 databases with D1 Database Manager.

Overview

D1 Manager provides comprehensive table management:

• 📋 Browse tables with grid/list view toggle, search, and row search filter
• 👀 View table schemas, indexes, row counts, and status badges (STRICT, FTS5)
• ➕ Create tables (visual designer or SQL)
• ✏️ Rename tables
• 📋 Clone tables (structure + data + indexes)
• 📤 Export tables (SQL/CSV/JSON) and import data (CSV/JSON/SQL) with duplicate handling and auto-add missing columns
• 🛟 Table-level R2 Backup/Restore (when R2 is configured) plus undo snapshots for destructive operations
• 🗑️ Delete tables with dependency analysis and cascade impact simulation
• 📦 Bulk operations (clone, export, delete multiple tables)
• 🔧 Column management (add, modify, rename, delete) including STRICT mode conversions and generated columns

Table List View

Navigate to a database to see all its tables.

Table Cards and List View

┌─────────────────────────────────┐
│ [✓] 📊 users                    │
│                                 │
│ Columns: 5 • Rows: 1,234        │
│ Type: table • STRICT            │
│                                 │
│ [Browse] [Schema] [Export] [⋮]  │
│ [Backup] [Restore] [Color]      │
└─────────────────────────────────┘

Card Information:

• Checkbox - For bulk operations
• Name - Table name
• Columns/Rows - Number of columns and row count (where available)
• Type - table, view, or virtual; STRICT/FTS5 badges where applicable
• Color tag - Optional color for quick visual grouping

Quick Actions:

• Browse - View table data
• Schema - View column definitions
• Export - Download as SQL/CSV
• Backup/Restore - Table-level R2 backup and restore (when R2 is configured)
• More Menu (⋮) - Rename, Clone, Delete, Convert (STRICT/FTS5), Import

Search & Filter Tables

Use the search bar to quickly find tables by name:

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

Showing 3 of 12 tables matching "user"

Features:

• Instant filtering - Results update as you type
• Case-insensitive - "USERS", "users", and "Users" all match
• Partial matching - Search "user" matches "users", "user_logs", "admin_users", etc.
• Clear button - Click X to clear the search
• Row search - When browsing data, use the row search input to quickly filter visible rows client-side

Viewing Table Data

Browse Table

Click "Browse" to view table contents:

┌──────┬──────────┬──────────────────┬─────────────┐
│ id   │ name     │ email            │ created_at  │
├──────┼──────────┼──────────────────┼─────────────┤
│ 1    │ Alice    │ alice@email.com  │ 2024-11-01  │
│ 2    │ Bob      │ bob@email.com    │ 2024-11-02  │
│ 3    │ Charlie  │ charlie@email.com│ 2024-11-03  │
└──────┴──────────┴──────────────────┴─────────────┘

Showing 50 rows • Page 1 of 5 • [Previous] [Next]

Features:

• Pagination - 50 rows per page
• Sortable Columns - Click headers to sort
• Row search filter - Client-side text search across visible columns
• Navigation - Previous/Next page buttons

View Schema

Click "Schema" to view column definitions:

┌─────────────────────────────────────────────────────┐
│ Table: users                                        │
├─────────────────────────────────────────────────────┤
│ Column      Type        Constraints                 │
├─────────────────────────────────────────────────────┤
│ 🔑 id       INTEGER     PRIMARY KEY, NOT NULL       │
│ name        TEXT        NOT NULL                    │
│ email       TEXT        UNIQUE, NOT NULL            │
│ created_at  DATETIME    DEFAULT CURRENT_TIMESTAMP   │
└─────────────────────────────────────────────────────┘

Column Information:

• Name - Column identifier
• Type - Data type (INTEGER, TEXT, REAL, BLOB, etc.)
• Constraints - PRIMARY KEY, UNIQUE, NOT NULL, DEFAULT

Key Indicators:

• 🔑 - Primary key column
• ⚠️ - NOT NULL constraint

View Indexes

Expand the indexes section to see:

┌─────────────────────────────────────────────────────┐
│ Indexes on users                                    │
├─────────────────────────────────────────────────────┤
│ idx_users_email (email) - UNIQUE                    │
│ idx_users_created (created_at)                      │
└─────────────────────────────────────────────────────┘

Creating Tables

Method 1: Visual Schema Designer

The recommended way to create tables with a graphical interface.

Steps:

1. Navigate to a database
2. Click "Create Table"
3. Enter table name
4. Add columns using the visual builder
5. Review SQL preview
6. Click "Create"

Add Column Interface:

┌─────────────────────────────────────────┐
│ Column 1                                │
├─────────────────────────────────────────┤
│ Name:        [username          ]       │
│ Type:        [▼ TEXT            ]       │
│ □ NOT NULL                              │
│ □ PRIMARY KEY                           │
│ Default:     [                  ]       │
│                                         │
│ [+ Add Column] [Remove]                 │
└─────────────────────────────────────────┘

Column Types:

• TEXT - String data
• INTEGER - Whole numbers
• REAL - Decimal numbers
• BLOB - Binary data
• NUMERIC - Numeric data (flexible)

Constraints:

• NOT NULL - Value required
• PRIMARY KEY - Unique identifier
• DEFAULT - Default value if not specified

SQL Preview:

CREATE TABLE users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  username TEXT NOT NULL,
  email TEXT UNIQUE NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

Validation:

• Table name required
• At least one column required
• Column names must be unique
• PRIMARY KEY conflicts prevented

Method 2: SQL Query

Use the Query Console to create tables with SQL:

CREATE TABLE products (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  description TEXT,
  price REAL NOT NULL DEFAULT 0.0,
  stock INTEGER DEFAULT 0,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

Benefits of SQL method:

• More control
• Complex constraints (FOREIGN KEY, CHECK)
• Trigger creation
• View creation

API Endpoint

POST /api/tables/:dbId/create
Content-Type: application/json

{
  "tableName": "users",
  "columns": [
    {
      "name": "id",
      "type": "INTEGER",
      "primaryKey": true,
      "notNull": true
    },
    {
      "name": "name",
      "type": "TEXT",
      "notNull": true
    }
  ]
}

Renaming Tables

Rename tables with validation and dependency checking.

Steps:

1. Click "Rename" button on table card
2. Enter new name
3. Review warnings
4. Click "Rename Table"

Validation:

• New name must be unique
• Cannot rename system tables
• Name follows SQLite naming rules

SQL Operation:

ALTER TABLE old_name RENAME TO new_name;

Fast Operation:

• Instant rename (no data copy)
• Preserves all indexes
• Preserves foreign keys
• Preserves triggers

API Endpoint

PATCH /api/tables/:dbId/:tableName/rename
Content-Type: application/json

{
  "newName": "customers"
}

Cloning Tables

Create exact copies of tables including structure, data, and indexes.

Steps:

1. Click "Clone" button on table card
2. Enter new table name
3. Review what will be copied
4. Click "Clone Table"

What Gets Cloned:

• ✅ Table structure (columns, types, constraints)
• ✅ All data rows
• ✅ All indexes
• ❌ Foreign key references (new table is independent)
• ❌ Triggers (SQLite limitation)

Clone Process:

-- 1. Copy structure
CREATE TABLE users_copy AS SELECT * FROM users;

-- 2. Recreate indexes
CREATE INDEX idx_users_copy_email ON users_copy(email);

-- 3. Verify row count
SELECT COUNT(*) FROM users_copy;

Use Cases:

• Testing - Test changes on copy
• Backup - Quick table backup
• Versioning - Keep historical snapshots
• Development - Work on copy, not production

Bulk Clone

Clone multiple tables at once:

1. Select tables using checkboxes
2. Click "Clone Selected"
3. Review suggested names
4. Modify names if needed
5. Click "Clone Tables"

Naming Suggestions:

users → users_copy
posts → posts_copy
comments → comments_copy

API Endpoint

POST /api/tables/:dbId/:tableName/clone
Content-Type: application/json

{
  "newTableName": "users_copy"
}

Exporting Tables

Export tables as SQL dumps or CSV files.

Export Single Table

Steps:

1. Click "Export" button on table card
2. Choose format:
   • SQL - Complete table dump with CREATE and INSERT
   • CSV - Data only in CSV format
3. File downloads automatically

SQL Export Format:

-- users.sql
CREATE TABLE users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  email TEXT UNIQUE
);

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

CREATE INDEX idx_users_email ON users(email);

CSV Export Format:

id,name,email
1,Alice,alice@email.com
2,Bob,bob@email.com

Bulk Export

Export multiple tables in a ZIP archive:

1. Select tables using checkboxes
2. Click "Export Selected"
3. Choose format (SQL or CSV)
4. Click "Export"
5. Download ZIP file

ZIP Structure (SQL):

tables-export.zip
├── users.sql
├── posts.sql
└── comments.sql

ZIP Structure (CSV):

tables-export.zip
├── users.csv
├── posts.csv
└── comments.csv

API Endpoints

Single Table:

GET /api/tables/:dbId/:tableName/export?format=sql
GET /api/tables/:dbId/:tableName/export?format=csv

Bulk Export:

POST /api/tables/:dbId/bulk-export
Content-Type: application/json

{
  "tableNames": ["users", "posts", "comments"],
  "format": "sql"
}

Deleting Tables

Delete tables with foreign key dependency analysis, cascade impact simulation, and automatic undo snapshots/R2 backups.

Delete Single Table

Steps:

1. Click "Delete" from more menu (⋮)
2. Review dependency analysis
3. Optional: Click "Simulate Cascade Impact" for detailed visualization
4. Read warnings about cascade impacts
5. Check confirmation checkbox (if dependencies exist)
6. Click "Delete Table"

Undo/Rollback Support:

• ✅ Automatic undo snapshot - Table schema, indexes, and data are captured before deletion
• ✅ R2 backup option (when R2 is configured) - Create a cloud backup before delete; table backups appear in the Backup & Restore hub
• ✅ Restore capability - Click Backup & Restore in the header to restore undo snapshots or R2 backups
• ✅ History - Undo history per database and R2 backup history (including orphaned backups)

See Undo Rollback for complete documentation.

Cascade Impact Simulator

For comprehensive deletion analysis, use the Cascade Impact Simulator button:

• Interactive Graph - Visual representation of all cascade paths
• Row Count Analysis - Exact counts of affected rows at each level
• Warning System - Severity-based alerts for high-impact operations
• Export Options - Generate CSV, JSON, Text, or PDF reports

See Cascade Impact Simulator for complete documentation.

When to Use:

• Deleting tables with complex foreign key relationships
• Need detailed impact analysis before proceeding
• Documenting deletion operations for compliance
• Understanding cascade depth and circular dependencies

Dependency Analysis

Before deletion, D1 Manager shows:

Outbound Dependencies:

⚠️ Table users references roles (role_id → id)
   ON DELETE: CASCADE

Inbound Dependencies:

⚠️ Table posts references users (user_id → id)
   ON DELETE: CASCADE
   Affected: 152 rows will be deleted

Dependency Details:

• Referenced Table - Table being referenced
• Column - Foreign key column
• ON DELETE Behavior - CASCADE, RESTRICT, SET NULL, NO ACTION
• Row Count - Estimated rows affected

See Foreign Key Dependencies for more details.

Confirmation Required

If dependencies exist:

┌─────────────────────────────────────────────┐
│ ⚠️ Warning: This table has dependencies     │
├─────────────────────────────────────────────┤
│ Deleting will affect:                       │
│ • posts (152 rows will be CASCADE deleted)  │
│ • comments (47 rows will be CASCADE deleted)│
│                                             │
│ □ I understand that deleting this table     │
│   will affect dependent tables              │
│                                             │
│ [Cancel] [Delete Table] (disabled)          │
└─────────────────────────────────────────────┘

Bulk Delete

Delete multiple tables with dependency analysis:

Steps:

1. Select tables using checkboxes
2. Click "Delete Selected"
3. Review dependencies for each table
4. Check confirmation boxes
5. Click "Delete Tables"

Progress Tracking:

Analyzing dependencies...
Deleting table 1 of 3: users...
Deleting table 2 of 3: posts...
Deleting table 3 of 3: comments...
Complete: 3 tables deleted

Dependency Accordion:

┌─────────────────────────────────────────┐
│ ▼ users (2 dependencies)                │
├─────────────────────────────────────────┤
│   Inbound: posts (152 rows CASCADE)     │
│   Inbound: comments (47 rows CASCADE)   │
└─────────────────────────────────────────┘
│ ▼ posts (1 dependency)                  │
├─────────────────────────────────────────┤
│   Outbound: users (references user_id)  │
└─────────────────────────────────────────┘

API Endpoints

Single Delete:

DELETE /api/tables/:dbId/:tableName

Get Dependencies:

GET /api/tables/:dbId/dependencies?tables=users,posts

Response:

{
  "users": {
    "outbound": [],
    "inbound": [
      {
        "table": "posts",
        "column": "user_id",
        "onDelete": "CASCADE",
        "rowCount": 152
      }
    ]
  }
}

Column Management

Add, modify, rename, and delete columns from existing tables.

See Column Management for detailed documentation.

Quick Reference:

Add Column

ALTER TABLE users ADD COLUMN phone TEXT;

Rename Column

ALTER TABLE users RENAME COLUMN name TO full_name;

Modify Column

Uses table recreation:

-- Change column type or constraints
-- Involves copying data to new table

Drop Column

ALTER TABLE users DROP COLUMN phone;

Table Statistics

Row Count

Shown at bottom of table view:

Showing 50 of 1,234 rows

Storage Information

View in database info:

• Total database size
• Table count
• Largest tables

Index Information

View all indexes on a table:

┌─────────────────────────────────────────┐
│ Indexes                                 │
├─────────────────────────────────────────┤
│ sqlite_autoindex_users_1 (email) UNIQUE│
│ idx_users_created (created_at)          │
│ idx_users_name (name)                   │
└─────────────────────────────────────────┘

Best Practices

Naming Conventions

Use descriptive, consistent names:

✅ users
✅ blog_posts
✅ user_sessions
✅ product_categories

Avoid:

❌ tbl_users (redundant prefix)
❌ Users (inconsistent casing)
❌ user-data (hyphens in table names)

Table Structure

Good Structure:

CREATE TABLE orders (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INTEGER NOT NULL,
  total REAL NOT NULL DEFAULT 0.0,
  status TEXT NOT NULL DEFAULT 'pending',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

Key Principles:

• Always have a PRIMARY KEY
• Use AUTOINCREMENT for auto-generated IDs
• Use NOT NULL for required fields
• Provide sensible DEFAULT values
• Add foreign keys for referential integrity

Index Strategy

Create indexes for:

• Foreign keys - Always index FK columns
• Search columns - Columns in WHERE clauses
• Sort columns - Columns in ORDER BY
• Join columns - Columns used in JOINs

CREATE INDEX idx_posts_user ON posts(user_id);
CREATE INDEX idx_posts_created ON posts(created_at);
CREATE INDEX idx_posts_status ON posts(status);

Backup Before Changes

Always backup before:

• Dropping columns
• Modifying column types
• Deleting tables with dependencies
• Bulk operations

Troubleshooting

"Table already exists"

Cause: Table name conflicts with existing table

Solution:

-- Check existing tables
SELECT name FROM sqlite_master WHERE type='table';

-- Use different name or drop existing table first
DROP TABLE IF EXISTS old_table;

"Cannot drop column: Table would be empty"

Cause: Trying to drop the only column

Solution:

• Tables must have at least one column
• Add a new column first, then drop the old one

"Foreign key constraint failed"

Cause: Trying to delete/modify row that's referenced

Solution:

-- Check foreign key constraints
PRAGMA foreign_key_list(table_name);

-- Options:
-- 1. Delete dependent rows first
-- 2. Set ON DELETE CASCADE
-- 3. Temporarily disable FK checks (not recommended)

"Export failed: Out of memory"

Cause: Table too large for export

Solution:

• Use Wrangler CLI for large tables
• Export in batches using SQL queries with LIMIT/OFFSET

wrangler d1 execute my-db --remote --command="SELECT * FROM large_table LIMIT 10000 OFFSET 0"

API Reference

List Tables

GET /api/tables/:dbId/list

Get Schema

GET /api/tables/:dbId/schema/:tableName

Get Data

GET /api/tables/:dbId/data/:tableName?limit=50&offset=0

Get Indexes

GET /api/tables/:dbId/indexes/:tableName

Create Table

POST /api/tables/:dbId/create

Rename Table

PATCH /api/tables/:dbId/:tableName/rename

Clone Table

POST /api/tables/:dbId/:tableName/clone

Export Table

GET /api/tables/:dbId/:tableName/export?format=sql|csv

Delete Table

DELETE /api/tables/:dbId/:tableName

Get Dependencies

GET /api/tables/:dbId/dependencies?tables=table1,table2

Foreign Key Navigation

When viewing table data, columns that are foreign keys are automatically detected and displayed as clickable links. Clicking a foreign key value navigates to the referenced table with an automatic filter applied to show only related records.

Features

🔗 Clickable FK Values with Visual Indicators

Foreign key columns display with:

• Link icon (🔗) indicating the column is a foreign key
• Blue border and primary color styling
• Hover tooltips showing the referenced table (e.g., "References customers.id")
• Click to navigate to the referenced table

🍞 Breadcrumb Trail Showing Navigation Path

The breadcrumb navigation appears when you navigate via foreign keys:

Database > orders > customers > addresses

• Click any table name to jump back to that point
• Shows last 5 tables (older tables indicated with "...")
• Current table is highlighted and non-clickable

🔍 Auto-Filtering on Referenced Column

When navigating via FK:

1. Target table opens automatically
2. Filter applied to the referenced column matching your clicked value
3. Only relevant related records are shown
4. Filter bar displays with the auto-applied filter

⌨️ Keyboard Shortcut: Alt+Left to Go Back

Press Alt+Left Arrow to navigate back through your table history.

Navigation Example

Scenario: View an order and explore its related customer

1. Browse orders table
2. Click customer_id = 42 (FK value appears as blue link with icon)
3. Automatically navigates to customers table
4. Auto-filters to WHERE id = 42
5. Breadcrumb shows: Database > orders > customers
6. Click "orders" in breadcrumb or press Alt+Left to return

Edge Cases

• NULL Values: NULL foreign keys are not clickable and display as gray text
• Self-Referential FKs: Works correctly (e.g., employees.manager_id → employees.id)
• Composite FKs: Each column clickable individually
• No FK Constraints: If table has no foreign keys defined, all values display normally

Tips

1. Use breadcrumbs to quickly jump to any previous table instead of clicking back multiple times
2. Combine with filters: FK navigation adds an initial filter, but you can add more filters to narrow results
3. Clear FK filter: After navigating, click "Clear All" in the filter bar to see the entire target table
4. Watch the indicator: Table subtitle shows "• Navigated from FK" when in FK navigation mode

For comprehensive details, see the Foreign Key Navigation guide.

Next Steps

• Foreign Key Navigation - Complete guide to FK navigation
• Column Management - Manage table columns
• Foreign Key Dependencies - Understand relationships
• Schema Designer - Visual table creation
• Query Console - Execute SQL queries

---

Need Help? See Troubleshooting or open an issue.