Column Management

Complete guide to adding, modifying, renaming, and deleting columns in D1 database tables.

Overview

D1 Manager provides comprehensive column management directly from the table view:

➕ Add Column - Add new columns to existing tables
✏️ Rename Column - Rename columns (fast operation)
🔧 Modify Column - Change type, constraints, defaults
🗑️ Delete Column - Remove columns (with validation)

All column operations preserve data when possible and include automatic validation.

Accessing Column Management

Navigate to a database
Click "Browse" on a table
View the column management section above the data grid
Each column row shows action buttons

Add Column

Add a new column to an existing table.

How to Add

Click "Add Column" button
Fill in column details:
- Name - Column identifier (required)
- Type - Data type (required)
- NOT NULL - Require value
- Default - Default value if not specified
Click "Add Column"

Column Types

Type	Description	Example Values
`TEXT`	String data	'Alice', 'hello@example.com'
`INTEGER`	Whole numbers	1, 42, -100
`REAL`	Decimal numbers	3.14, -0.001, 99.99
`BLOB`	Binary data	Images, files
`NUMERIC`	Flexible numeric	Can store as INTEGER or REAL

Constraints

NOT NULL:

ALTER TABLE users ADD COLUMN phone TEXT NOT NULL DEFAULT '';

Requires a value for all rows
Must provide default if table has existing rows

Default Values:

ALTER TABLE users ADD COLUMN status TEXT DEFAULT 'active';
ALTER TABLE users ADD COLUMN created_at DATETIME DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE users ADD COLUMN count INTEGER DEFAULT 0;

SQL Operation

-- Basic add
ALTER TABLE users ADD COLUMN phone TEXT;

-- With constraints
ALTER TABLE users ADD COLUMN email TEXT NOT NULL DEFAULT '';

-- With default value
ALTER TABLE users ADD COLUMN status TEXT DEFAULT 'active';

-- With timestamp
ALTER TABLE users ADD COLUMN created_at DATETIME DEFAULT CURRENT_TIMESTAMP;

Important Notes

⚠️ Existing Rows: If table has data, NOT NULL columns must have a default
✅ Fast Operation: Instant, no data copy required
✅ Preserves Data: Existing data unaffected
✅ Preserves Indexes: All indexes remain intact

API Endpoint

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

{
  "columnName": "phone",
  "columnType": "TEXT",
  "notNull": false,
  "defaultValue": null
}

Rename Column

Rename an existing column (fast operation).

How to Rename

Click "Rename" button on column row
Enter new column name
Click "Rename Column"

SQL Operation

ALTER TABLE users RENAME COLUMN name TO full_name;
ALTER TABLE users RENAME COLUMN email TO email_address;

Important Notes

✅ Fast Operation: Instant rename, no data copy
✅ Preserves Data: All data intact
✅ Updates Indexes: Indexes automatically updated
✅ Updates Foreign Keys: FK references updated
⚠️ App Code: Update application code that references old name

Validation

New name must be unique in table
Cannot rename system columns (e.g., rowid)
Name must be valid SQLite identifier

API Endpoint

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

{
  "newName": "full_name"
}

Modify Column

Change column type, constraints, or default value.

How to Modify

Click "Modify" button on column row
Change desired properties:
- Type - New data type
- NOT NULL - Add/remove constraint
- Default - Change default value
Review warnings
Click "Modify Column"

SQL Operation

SQLite doesn't support direct column modification, so D1 Manager uses table recreation:

-- Process:
1. CREATE TABLE users_new (columns with changes);
2. INSERT INTO users_new SELECT * FROM users;
3. DROP TABLE users;
4. ALTER TABLE users_new RENAME TO users;
5. Recreate indexes;

Type Conversions

Safe Conversions (no data loss):

INTEGER → REAL     -- 42 becomes 42.0
INTEGER → TEXT     -- 42 becomes '42'
REAL → TEXT        -- 3.14 becomes '3.14'
TEXT → TEXT        -- Always safe

Unsafe Conversions (potential data loss):

REAL → INTEGER     -- 3.14 becomes 3 (truncated)
TEXT → INTEGER     -- 'abc' becomes 0 or NULL
TEXT → REAL        -- 'abc' becomes 0.0 or NULL
BLOB → TEXT        -- May lose binary data

Adding NOT NULL

-- If table has existing rows:
-- 1. Must provide default, OR
-- 2. Must ensure no NULL values exist first

-- Check for NULLs
SELECT COUNT(*) FROM users WHERE email IS NULL;

-- Update NULLs if any
UPDATE users SET email = 'unknown@example.com' WHERE email IS NULL;

-- Then add constraint
ALTER TABLE users MODIFY COLUMN email TEXT NOT NULL;

Removing NOT NULL

Always safe - makes column optional:

ALTER TABLE users MODIFY COLUMN phone TEXT;  -- Remove NOT NULL

Important Notes

⚠️ Data Loss Risk: Type conversions may lose data
⚠️ Slow Operation: Copies entire table
⚠️ Temporary Duplication: Table exists twice during operation
✅ Backup Recommended: Always backup before modifying
✅ Indexes Preserved: Recreated after modification
❌ Triggers Not Preserved: Manually recreate triggers
❌ Foreign Keys: May need manual adjustment

API Endpoint

PATCH /api/tables/:dbId/:tableName/columns/:columnName/modify
Content-Type: application/json

{
  "newType": "REAL",
  "notNull": true,
  "defaultValue": "0.0"
}

Delete Column

Remove a column from a table.

How to Delete

Click "Delete" button on column row
Review warnings
Confirm deletion
Click "Delete Column"

SQL Operation

ALTER TABLE users DROP COLUMN phone;

Available in SQLite 3.35.0+ (D1 supports this).

Validation

Cannot Delete If:

Table would have zero columns (minimum 1 required)
Column is part of PRIMARY KEY (compound key)
Column is referenced by foreign key (in another table)
Column is part of unique index

Will Delete:

Column data (permanent)
Indexes that use only this column
Default values and constraints

Will Preserve:

Other columns and data
Indexes that use other columns
Foreign keys from other columns

Important Notes

⚠️ Permanent: Cannot undo, all data lost
⚠️ Backup First: Always backup before dropping columns
✅ Fast Operation: Usually quick (uses SQLite DROP COLUMN)
✅ Preserves Other Data: Only affects target column
⚠️ Check Dependencies: Verify no app code depends on column

Confirmation Dialog

┌─────────────────────────────────────────────┐
│ Delete Column: phone                        │
├─────────────────────────────────────────────┤
│ ⚠️ Warning: This action cannot be undone    │
│                                             │
│ The following will be deleted:              │
│ • All data in the 'phone' column           │
│ • Any indexes using only this column        │
│                                             │
│ The following will be preserved:            │
│ • All other columns and data                │
│ • Indexes using other columns               │
│                                             │
│ □ I understand this is permanent            │
│                                             │
│ [Cancel] [Delete Column]                    │
└─────────────────────────────────────────────┘

API Endpoint

DELETE /api/tables/:dbId/:tableName/columns/:columnName

Best Practices

Before Making Changes

Backup the database:

# Use bulk download or Wrangler
wrangler d1 execute database-name --remote --command=".dump" > backup.sql

Test on copy first:

-- Clone table
CREATE TABLE users_test AS SELECT * FROM users;

-- Test change
ALTER TABLE users_test ADD COLUMN phone TEXT;

-- Verify
SELECT * FROM users_test LIMIT 5;

-- If good, apply to original
ALTER TABLE users ADD COLUMN phone TEXT;

Check dependencies:

-- Check what references this column
PRAGMA foreign_key_list(table_name);

-- Check indexes
PRAGMA index_list(table_name);

Naming Conventions

Use consistent column names:

✅ Good:
- created_at, updated_at (timestamps)
- user_id, post_id (foreign keys)
- is_active, is_deleted (booleans)
- first_name, last_name (multi-word)

❌ Avoid:
- createdAt (camelCase)
- user-id (hyphens)
- 1st_name (starts with number)
- id, name (too generic)

Type Selection

Choose appropriate types:

-- Integers for IDs, counts, ages
user_id INTEGER
age INTEGER
post_count INTEGER

-- TEXT for strings
name TEXT
email TEXT
description TEXT

-- REAL for decimals
price REAL
rating REAL
temperature REAL

-- DATETIME for timestamps
created_at DATETIME
last_login DATETIME

-- BLOB for binary
avatar BLOB
document BLOB

Default Values

Provide sensible defaults:

-- Status/state fields
status TEXT DEFAULT 'pending'
is_active INTEGER DEFAULT 1

-- Timestamps
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP

-- Numeric fields
balance REAL DEFAULT 0.0
views INTEGER DEFAULT 0

-- Text fields (if NOT NULL)
description TEXT DEFAULT ''
notes TEXT DEFAULT ''

Troubleshooting

"Cannot add NOT NULL column"

Error: Table has existing rows, cannot add NOT NULL without default

Solution:

-- Provide a default value
ALTER TABLE users ADD COLUMN email TEXT NOT NULL DEFAULT 'unknown@example.com';

-- Or add as nullable first, populate, then make NOT NULL
ALTER TABLE users ADD COLUMN email TEXT;
UPDATE users SET email = 'default@example.com' WHERE email IS NULL;
-- Then use modify to add NOT NULL

"Column already exists"

Error: Column name conflicts with existing column

Solution:

-- Check existing columns
PRAGMA table_info(table_name);

-- Use different name or drop existing column first
ALTER TABLE users DROP COLUMN old_column;
ALTER TABLE users ADD COLUMN new_column TEXT;

"Cannot drop column: Referenced by foreign key"

Error: Column is referenced by another table

Solution:

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

-- Option 1: Drop foreign key in child table first
ALTER TABLE child_table DROP CONSTRAINT fk_name;

-- Option 2: Drop child table if not needed
DROP TABLE child_table;

-- Then drop column
ALTER TABLE parent_table DROP COLUMN column_name;

"Type conversion failed"

Error: Cannot convert data to new type

Solution:

-- Check problematic data
SELECT column_name FROM table_name WHERE typeof(column_name) = 'text';

-- Clean data first
UPDATE table_name SET column_name = 0 WHERE column_name = 'invalid';

-- Then try type change again

"Operation timed out"

Error: Modify operation takes too long on large table

Solution:

# Use Wrangler for large tables
wrangler d1 execute database-name --remote --command="
  BEGIN TRANSACTION;
  CREATE TABLE users_new (...modified schema...);
  INSERT INTO users_new SELECT ...columns... FROM users;
  DROP TABLE users;
  ALTER TABLE users_new RENAME TO users;
  COMMIT;
"

Examples

Add Timestamp Columns

-- Add created_at
ALTER TABLE products ADD COLUMN created_at DATETIME DEFAULT CURRENT_TIMESTAMP;

-- Add updated_at
ALTER TABLE products ADD COLUMN updated_at DATETIME DEFAULT CURRENT_TIMESTAMP;

-- Add created_by (with foreign key)
ALTER TABLE products ADD COLUMN created_by INTEGER;
-- Then add foreign key constraint (requires table recreation)

Add Status Column

-- Add with default
ALTER TABLE orders ADD COLUMN status TEXT DEFAULT 'pending';

-- Populate existing rows if needed
UPDATE orders SET status = 'completed' WHERE shipped_date IS NOT NULL;

Rename for Consistency

-- Standardize naming
ALTER TABLE users RENAME COLUMN Name TO name;
ALTER TABLE users RENAME COLUMN Email TO email;
ALTER TABLE users RENAME COLUMN createdAt TO created_at;

Change Text to Integer

-- Step 1: Check data format
SELECT DISTINCT age FROM users;

-- Step 2: Add new column
ALTER TABLE users ADD COLUMN age_int INTEGER;

-- Step 3: Copy and convert
UPDATE users SET age_int = CAST(age AS INTEGER);

-- Step 4: Verify
SELECT age, age_int FROM users LIMIT 10;

-- Step 5: Drop old column
ALTER TABLE users DROP COLUMN age;

-- Step 6: Rename new column
ALTER TABLE users RENAME COLUMN age_int TO age;

Column Management

Column Management

Overview

Accessing Column Management

Add Column

How to Add

Column Types

Constraints

SQL Operation

Important Notes

API Endpoint

Rename Column

How to Rename

SQL Operation

Important Notes

Validation

API Endpoint

Modify Column

How to Modify

SQL Operation

Type Conversions

Adding NOT NULL

Removing NOT NULL

Important Notes

API Endpoint

Delete Column

How to Delete

SQL Operation

Validation

Important Notes

Confirmation Dialog

API Endpoint

Best Practices

Before Making Changes

Naming Conventions

Type Selection

Default Values

Troubleshooting

"Cannot add NOT NULL column"

"Column already exists"

"Cannot drop column: Referenced by foreign key"

"Type conversion failed"

"Operation timed out"

Examples

Add Timestamp Columns

Add Status Column

Rename for Consistency

Change Text to Integer

Next Steps

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!