Column Commands¶

Manage columns within existing tables.

list¶

List all columns in a table.

cinch column list TABLE_NAME

Arguments¶

TABLE_NAME - Table to list columns for

Example Output¶

Columns in table 'users':
┏━━━━━━━━━━━━━┳━━━━━━━━━┳━━━━━━━━━━┓
┃ Name        ┃ Type    ┃ Nullable ┃
┡━━━━━━━━━━━━━╇━━━━━━━━━╇━━━━━━━━━━┩
│ id          │ TEXT    │ No       │
│ username    │ TEXT    │ Yes      │
│ email       │ TEXT    │ Yes      │
│ active      │ BOOLEAN │ Yes      │
│ created_at  │ TEXT    │ No       │
│ updated_at  │ TEXT    │ No       │
└─────────────┴─────────┴──────────┘

add¶

Add a new column to an existing table.

cinch column add TABLE_NAME COLUMN:TYPE

Arguments¶

TABLE_NAME - Table to add column to
COLUMN:TYPE - Column definition (name:type)

Column Types¶

TEXT - String data
INTEGER - Whole numbers
REAL - Decimal numbers
BOOLEAN - True/false values
BLOB - Binary data

Options¶

Add ? suffix for nullable columns

Examples¶

# Add required column
cinch column add users phone:TEXT

# Add nullable column
cinch column add users avatar_url:TEXT?

# Add various types
cinch column add products weight:REAL
cinch column add products in_stock:BOOLEAN
cinch column add users age:INTEGER

Notes¶

New columns are added with NULL values for existing rows
Make columns nullable if table has existing data
Column names must be unique within the table

delete¶

Remove a column from a table.

cinch column delete TABLE_NAME COLUMN_NAME

Arguments¶

TABLE_NAME - Table containing the column
COLUMN_NAME - Column to delete

Options¶

--force - Skip confirmation

Examples¶

# With confirmation
cinch column delete users old_field

# Without confirmation
cinch column delete users old_field --force

Notes¶

Cannot delete protected columns (id, created_at, updated_at)
Permanently deletes all data in the column
Cannot be undone

rename¶

Rename a column.

cinch column rename TABLE_NAME OLD_NAME NEW_NAME

Arguments¶

TABLE_NAME - Table containing the column
OLD_NAME - Current column name
NEW_NAME - New column name

Example¶

cinch column rename users username user_name

Notes¶

Preserves all data
Updates any views that reference the column
Cannot rename protected columns

alter-nullable¶

Change whether a column allows NULL values.

cinch column alter-nullable TABLE_NAME COLUMN_NAME [--nullable | --not-nullable]

Arguments¶

TABLE_NAME - Table containing the column
COLUMN_NAME - Column to modify

Options¶

--nullable - Make column accept NULL values
--not-nullable - Make column reject NULL values
--fill-value VALUE - Value to use for NULL values when making NOT NULL

Examples¶

# Make column nullable
cinch column alter-nullable users phone --nullable

# Make column NOT NULL (no existing NULLs)
cinch column alter-nullable users email --not-nullable

# Make column NOT NULL with fill value for NULLs
cinch column alter-nullable users phone --not-nullable --fill-value "000-0000"

# Interactive mode for NULL replacement
cinch column alter-nullable users age --not-nullable
> Column 'age' has 5 NULL values. Provide a fill value: 0

Notes¶

Cannot modify protected columns (id, created_at, updated_at)
When making NOT NULL, must provide fill_value if NULLs exist
Preserves all existing non-NULL data
Recreates table internally (SQLite limitation)

info¶

Show detailed information about a column.

cinch column info TABLE_NAME COLUMN_NAME

Arguments¶

TABLE_NAME - Table containing the column
COLUMN_NAME - Column to inspect

Example Output¶

Column: email
Table: users
Type: TEXT
Nullable: Yes
Default: NULL
Values: 1,234 non-null, 56 null

Common Workflows¶

Adding User Features¶

# Add profile fields
cinch column add users bio:TEXT?
cinch column add users website:TEXT?
cinch column add users location:TEXT?

# Add authentication fields
cinch column add users password_reset_token:TEXT?
cinch column add users last_login:TEXT?
cinch column add users failed_attempts:INTEGER

E-commerce Enhancements¶

# Add product attributes
cinch column add products sku:TEXT
cinch column add products weight:REAL?
cinch column add products dimensions:TEXT?

# Add order tracking
cinch column add orders tracking_number:TEXT?
cinch column add orders shipped_at:TEXT?
cinch column add orders delivered_at:TEXT?

Migration Examples¶

# Rename for clarity
cinch column rename users name full_name
cinch column rename products desc description

# Remove deprecated columns
cinch column delete users old_password_field
cinch column delete orders legacy_status

Best Practices¶

Nullable for Existing Tables
Always use nullable (?) when adding to tables with data
Required columns need default values or migration
Naming Conventions
Use lowercase with underscores
Be descriptive: email_verified not verified
Include units: weight_kg, price_usd
Type Selection
Use TEXT for emails, URLs, names
Use INTEGER for counts, IDs
Use REAL for money, measurements
Use BOOLEAN for flags/states
Safe Migrations
Test column changes on feature branches
Backup before bulk column operations
Consider data migration needs

Constraints & Limitations¶

SQLite Constraints¶

# Primary key - automatically created
cinch table create users email:TEXT  # 'id' column added automatically

# Unique constraint
cinch column add users username:TEXT  # Add column first
cinch query "CREATE UNIQUE INDEX idx_username ON users(username)"  # Then add constraint

# Foreign key relationship
cinch column add orders user_id:INTEGER
cinch query "CREATE INDEX idx_orders_user_id ON orders(user_id)"

# Check constraints (data validation)
cinch column add users age:INTEGER
cinch query "ALTER TABLE users ADD CONSTRAINT check_age CHECK (age >= 0 AND age <= 150)"

CinchDB Limitations¶

Cannot modify column types after creation
Cannot add NOT NULL columns to tables with data (use nullable then alter-nullable)
Cannot use reserved names (id, created_at, updated_at)
Cannot modify nullable constraint on primary key columns

Constraint Examples¶

# Email validation pattern
cinch column add users email:TEXT
cinch query "CREATE INDEX idx_users_email ON users(email)"

# Price must be positive
cinch column add products price:REAL
cinch query "ALTER TABLE products ADD CONSTRAINT check_price CHECK (price > 0)"

# Status must be valid
cinch column add orders status:TEXT
cinch query "ALTER TABLE orders ADD CONSTRAINT check_status CHECK (status IN ('pending', 'shipped', 'delivered'))"

Working with Constraints¶

# Check existing constraints
cinch query "SELECT sql FROM sqlite_master WHERE type='index' AND tbl_name='users'"

# Drop constraint (drop index)
cinch query "DROP INDEX idx_username"

# Modify constraint (recreate)
cinch query "DROP INDEX idx_username"
cinch query "CREATE UNIQUE INDEX idx_username ON users(LOWER(username))"

Remote Operations¶

Next Steps¶

Table Commands - Create and manage tables
Query Command - Work with column data
Migration Patterns - Safe schema changes