Dbcli DB Ddl

Command Style (Use PATH)

All examples use the plain command name dbcli (no directory prefix). Ensure dbcli is on PATH instead of hardcoding paths like .\.claude\skills\dbcli\dbcli.exe.

DbCli Database DDL Skill

Execute Data Definition Language (DDL) operations - CREATE, ALTER, DROP tables, indexes, and views.

⚠️ CRITICAL SAFETY REQUIREMENT

MANDATORY BACKUPS BEFORE DROP/ALTER OPERATIONS

• DROP TABLE: Export table data + create table copy
• ALTER TABLE: Export table schema + data
• DROP INDEX/VIEW: Document structure before dropping

When to Use This Skill

• User wants to create tables, indexes, or views
• User needs to modify table structure (add/drop columns, change types)
• User wants to drop tables, indexes, or views
• User mentions CREATE, ALTER, DROP, schema, structure, or database design
• Never DROP/ALTER without backups

Command Syntax

dbcli -c "CONNECTION_STRING" [-t DATABASE_TYPE] ddl "DDL_STATEMENT"

Global Options

• -c, --connection: Database connection string (required)
• -t, --db-type: Database type (default: sqlite)

Subcommand Options

• -F, --file: Execute DDL from file

Notes:

• SQL Server supports GO batch separators in DDL scripts (use -F and keep GO on its own line).

CREATE Operations (Safe - No Backup Required)

Create Table

# SQLite - Basic table
dbcli -c "Data Source=app.db" ddl "CREATE TABLE Users (Id INTEGER PRIMARY KEY AUTOINCREMENT, Name TEXT NOT NULL, Email TEXT UNIQUE, CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP)"

# SQL Server
dbcli -c "Server=localhost;Database=mydb;Trusted_Connection=True" -t sqlserver ddl "CREATE TABLE Users (Id INT PRIMARY KEY IDENTITY(1,1), Name NVARCHAR(100) NOT NULL, Email NVARCHAR(255) UNIQUE, CreatedAt DATETIME DEFAULT GETDATE())"

# MySQL
dbcli -c "Server=localhost;Database=mydb;Uid=root;Pwd=xxxxxxxxxx" -t mysql ddl "CREATE TABLE Users (Id INT PRIMARY KEY AUTO_INCREMENT, Name VARCHAR(100) NOT NULL, Email VARCHAR(255) UNIQUE, CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP)"

# PostgreSQL
dbcli -c "Host=localhost;Database=mydb;Username=postgres;Password=xxxxxxxxxx" -t postgresql ddl "CREATE TABLE Users (Id SERIAL PRIMARY KEY, Name VARCHAR(100) NOT NULL, Email VARCHAR(255) UNIQUE, CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP)"

Create Table with Foreign Key

dbcli -c "Data Source=app.db" ddl "CREATE TABLE Orders (Id INTEGER PRIMARY KEY AUTOINCREMENT, UserId INTEGER NOT NULL, Total REAL NOT NULL, OrderDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (UserId) REFERENCES Users(Id))"

Create Table from Query (Backup/Copy)

# Create backup table
dbcli -c "Data Source=app.db" ddl "CREATE TABLE Users_backup AS SELECT * FROM Users"

# Create filtered copy
dbcli -c "Data Source=app.db" ddl "CREATE TABLE ActiveUsers AS SELECT * FROM Users WHERE active = 1"

Create Table if Not Exists

dbcli -c "Data Source=app.db" ddl "CREATE TABLE IF NOT EXISTS Settings (Key TEXT PRIMARY KEY, Value TEXT)"

Create Index

# Single column index
dbcli -c "Data Source=app.db" ddl "CREATE INDEX idx_users_email ON Users(Email)"

# Multi-column index
dbcli -c "Data Source=app.db" ddl "CREATE INDEX idx_orders_user_date ON Orders(UserId, OrderDate)"

# Unique index
dbcli -c "Data Source=app.db" ddl "CREATE UNIQUE INDEX idx_users_username ON Users(Username)"

Create View

# Simple view
dbcli -c "Data Source=app.db" ddl "CREATE VIEW ActiveUsers AS SELECT Id, Name, Email FROM Users WHERE active = 1"

# Complex view with JOIN
dbcli -c "Data Source=app.db" ddl "CREATE VIEW UserOrders AS SELECT u.Name, o.Id as OrderId, o.Total, o.OrderDate FROM Users u JOIN Orders o ON u.Id = o.UserId"

DROP Operations (DANGEROUS - Requires Backup)

Safe DROP TABLE Workflow

TIMESTAMP=$(date +%Y%m%d_%H%M%S)

# STEP 1: Export table schema (for reference)
dbcli -c "Data Source=app.db" -f table columns OldTable > OldTable_schema_${TIMESTAMP}.txt

# STEP 2: Export table data (SQL format)
dbcli -c "Data Source=app.db" export OldTable > OldTable_backup_${TIMESTAMP}.sql

# STEP 3: Create table copy (fastest recovery)
dbcli -c "Data Source=app.db" query "CREATE TABLE OldTable_copy_${TIMESTAMP} AS SELECT * FROM OldTable"

# STEP 4: Verify backup
dbcli -c "Data Source=app.db" query "SELECT COUNT(*) FROM OldTable"
dbcli -c "Data Source=app.db" query "SELECT COUNT(*) FROM OldTable_copy_${TIMESTAMP}"

# STEP 5: Drop table
dbcli -c "Data Source=app.db" ddl "DROP TABLE IF EXISTS OldTable"

echo "Table dropped. Backups: OldTable_backup_${TIMESTAMP}.sql, OldTable_copy_${TIMESTAMP}"

Safe DROP INDEX

# Document index before dropping
dbcli -c "Data Source=app.db" query "SELECT sql FROM sqlite_master WHERE type='index' AND name='idx_old_index'" > idx_old_index_definition.sql

# Drop index
dbcli -c "Data Source=app.db" ddl "DROP INDEX IF EXISTS idx_old_index"

Safe DROP VIEW

# Export view definition
dbcli -c "Data Source=app.db" query "SELECT sql FROM sqlite_master WHERE type='view' AND name='OldView'" > OldView_definition.sql

# Drop view
dbcli -c "Data Source=app.db" ddl "DROP VIEW IF EXISTS OldView"

ALTER Operations (CRITICAL - Requires Backup)

Safe ALTER TABLE Workflow

TIMESTAMP=$(date +%Y%m%d_%H%M%S)

# STEP 1: Full table backup (data + schema)
dbcli -c "Data Source=app.db" export Users > Users_backup_${TIMESTAMP}.sql
dbcli -c "Data Source=app.db" query "CREATE TABLE Users_copy_${TIMESTAMP} AS SELECT * FROM Users"

# STEP 2: Verify backup
dbcli -c "Data Source=app.db" query "SELECT COUNT(*) FROM Users"
dbcli -c "Data Source=app.db" query "SELECT COUNT(*) FROM Users_copy_${TIMESTAMP}"

# STEP 3: Execute ALTER
dbcli -c "Data Source=app.db" ddl "ALTER TABLE Users ADD COLUMN PhoneNumber TEXT"

# STEP 4: Verify structure
dbcli -c "Data Source=app.db" -f table columns Users

ALTER TABLE - Add Column

# Backup first
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
dbcli -c "Data Source=app.db" export Users > Users_backup_${TIMESTAMP}.sql

# Add column
dbcli -c "Data Source=app.db" ddl "ALTER TABLE Users ADD COLUMN Age INTEGER"

# Add column with default
dbcli -c "Data Source=app.db" ddl "ALTER TABLE Users ADD COLUMN Status TEXT DEFAULT 'active'"

ALTER TABLE - Drop Column

# CRITICAL: Full backup required
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
dbcli -c "Data Source=app.db" export Users > Users_backup_${TIMESTAMP}.sql
dbcli -c "Data Source=app.db" query "CREATE TABLE Users_copy_${TIMESTAMP} AS SELECT * FROM Users"

# Drop column (SQL Server syntax)
dbcli -c "Server=localhost;Database=mydb;Trusted_Connection=True" -t sqlserver ddl "ALTER TABLE Users DROP COLUMN TempColumn"

ALTER TABLE - Rename Table

# Backup first
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
dbcli -c "Data Source=app.db" export OldName > OldName_backup_${TIMESTAMP}.sql

# Rename (SQLite syntax)
dbcli -c "Data Source=app.db" ddl "ALTER TABLE OldName RENAME TO NewName"

# Rename (SQL Server syntax)
dbcli -c "Server=localhost;Database=mydb;Trusted_Connection=True" -t sqlserver ddl "EXEC sp_rename 'OldName', 'NewName'"

ALTER TABLE - Rename Column

# Backup first
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
dbcli -c "Data Source=app.db" export Users > Users_backup_${TIMESTAMP}.sql

# Rename column (SQLite 3.25+)
dbcli -c "Data Source=app.db" ddl "ALTER TABLE Users RENAME COLUMN OldColumnName TO NewColumnName"

# SQL Server
dbcli -c "Server=localhost;Database=mydb;Trusted_Connection=True" -t sqlserver ddl "EXEC sp_rename 'Users.OldColumnName', 'NewColumnName', 'COLUMN'"

DDL from File

Execute Schema File

# Create schema.sql
cat > schema.sql <<EOF
CREATE TABLE Users (
    Id INTEGER PRIMARY KEY AUTOINCREMENT,
    Name TEXT NOT NULL,
    Email TEXT UNIQUE,
    CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE Orders (
    Id INTEGER PRIMARY KEY AUTOINCREMENT,
    UserId INTEGER NOT NULL,
    Total REAL NOT NULL,
    OrderDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (UserId) REFERENCES Users(Id)
);

CREATE INDEX idx_users_email ON Users(Email);
CREATE INDEX idx_orders_user ON Orders(UserId);
EOF

# Execute schema
dbcli -c "Data Source=app.db" ddl -F schema.sql

Migration Scripts

# migrations/001_create_users.sql
dbcli -c "Data Source=app.db" ddl -F migrations/001_create_users.sql

# migrations/002_add_orders.sql
dbcli -c "Data Source=app.db" ddl -F migrations/002_add_orders.sql

# migrations/003_add_indexes.sql
dbcli -c "Data Source=app.db" ddl -F migrations/003_add_indexes.sql

Chinese Domestic Databases

DaMeng (达梦)

# Create table
dbcli -c "Server=localhost;User Id=SYSDBA;PWD=xxxxxxxxxx;DATABASE=mydb" -t dm ddl "CREATE TABLE dm_test (id INT PRIMARY KEY, name VARCHAR(100), create_time TIMESTAMP)"

# Create index
dbcli -c "Server=localhost;User Id=SYSDBA;PWD=xxxxxxxxxx;DATABASE=mydb" -t dm ddl "CREATE INDEX idx_dm_test_name ON dm_test(name)"

# Create view
dbcli -c "Server=localhost;User Id=SYSDBA;PWD=xxxxxxxxxx;DATABASE=mydb" -t dm ddl "CREATE VIEW v_dm_test AS SELECT id, name FROM dm_test WHERE id > 0"

GaussDB

# Create table with sequence
dbcli -c "Host=localhost;Port=8000;Database=mydb;Username=gaussdb;Password=xxxxxxxxxx" -t gaussdb ddl "CREATE TABLE gauss_test (id SERIAL PRIMARY KEY, name VARCHAR(100), amount DECIMAL(10,2))"

# Create index
dbcli -c "Host=localhost;Port=8000;Database=mydb;Username=gaussdb;Password=xxxxxxxxxx" -t gaussdb ddl "CREATE INDEX idx_gauss_test_name ON gauss_test(name)"

# Create function (PL/pgSQL)
dbcli -c "Host=localhost;Port=8000;Database=mydb;Username=gaussdb;Password=xxxxxxxxxx" -t gaussdb ddl "CREATE OR REPLACE FUNCTION get_total() RETURNS DECIMAL AS \$\$ BEGIN RETURN (SELECT SUM(amount) FROM gauss_test); END; \$\$ LANGUAGE plpgsql"

KingbaseES (人大金仓)

# Create table
dbcli -c "Server=localhost;Port=54321;UID=system;PWD=xxxxxxxxxx;database=mydb" -t kdbndp ddl "CREATE TABLE kingbase_test (id SERIAL PRIMARY KEY, name VARCHAR(100), price NUMERIC(10,2))"

# Create view
dbcli -c "Server=localhost;Port=54321;UID=system;PWD=xxxxxxxxxx;database=mydb" -t kdbndp ddl "CREATE VIEW v_kingbase_active AS SELECT * FROM kingbase_test WHERE status = 1"

Advanced DDL

Composite Primary Key

dbcli -c "Data Source=app.db" ddl "CREATE TABLE UserRoles (UserId INTEGER NOT NULL, RoleId INTEGER NOT NULL, AssignedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (UserId, RoleId))"

Check Constraints

dbcli -c "Data Source=app.db" ddl "CREATE TABLE Products (Id INTEGER PRIMARY KEY, Name TEXT NOT NULL, Price REAL CHECK(Price > 0), Stock INTEGER CHECK(Stock >= 0))"

Triggers (SQLite)

dbcli -c "Data Source=app.db" ddl "CREATE TRIGGER update_timestamp AFTER UPDATE ON Users BEGIN UPDATE Users SET UpdatedAt = datetime('now') WHERE Id = NEW.Id; END"

Partial Index

dbcli -c "Data Source=app.db" ddl "CREATE INDEX idx_active_users ON Users(Email) WHERE active = 1"

Backup Recovery for DDL

Recover Dropped Table

# From table copy
dbcli -c "Data Source=app.db" ddl "CREATE TABLE Users AS SELECT * FROM Users_copy_20250127_143022"

# From SQL export
dbcli -c "Data Source=app.db" exec -F Users_backup_20250127_143022.sql

Recover Dropped Index

# Re-create from saved definition
dbcli -c "Data Source=app.db" ddl -F idx_users_email_definition.sql

Rollback ALTER TABLE

# Drop altered table
dbcli -c "Data Source=app.db" ddl "DROP TABLE Users"

# Restore from backup
dbcli -c "Data Source=app.db" ddl "CREATE TABLE Users AS SELECT * FROM Users_copy_20250127_143022"

Schema Verification

Check Table Exists

# SQLite
dbcli -c "Data Source=app.db" query "SELECT name FROM sqlite_master WHERE type='table' AND name='Users'"

# SQL Server
dbcli -c "Server=localhost;Database=mydb;Trusted_Connection=True" -t sqlserver query "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Users'"

Check Column Exists

# SQLite
dbcli -c "Data Source=app.db" query "PRAGMA table_info(Users)"

# SQL Server
dbcli -c "Server=localhost;Database=mydb;Trusted_Connection=True" -t sqlserver query "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Users' AND COLUMN_NAME = 'Email'"

Check Index Exists

# SQLite
dbcli -c "Data Source=app.db" query "SELECT name FROM sqlite_master WHERE type='index' AND tbl_name='Users' AND name='idx_users_email'"

Common Patterns

Idempotent DDL (Safe Re-run)

# Drop if exists, then create
dbcli -c "Data Source=app.db" ddl "DROP TABLE IF EXISTS TempTable"
dbcli -c "Data Source=app.db" ddl "CREATE TABLE TempTable (Id INTEGER PRIMARY KEY, Data TEXT)"

# Create if not exists
dbcli -c "Data Source=app.db" ddl "CREATE TABLE IF NOT EXISTS Settings (Key TEXT PRIMARY KEY, Value TEXT)"

Database Initialization

#!/bin/bash
# init_db.sh

CONNECTION="Data Source=app.db"

# Create tables
dbcli -c "$CONNECTION" ddl -F schema/001_users.sql
dbcli -c "$CONNECTION" ddl -F schema/002_orders.sql
dbcli -c "$CONNECTION" ddl -F schema/003_products.sql

# Create indexes
dbcli -c "$CONNECTION" ddl -F schema/004_indexes.sql

# Create views
dbcli -c "$CONNECTION" ddl -F schema/005_views.sql

echo "Database initialized"

Schema Migration

#!/bin/bash
# migrate.sh

CONNECTION="Data Source=app.db"
BACKUP_DIR="backups"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)

# Backup entire database
cp app.db "${BACKUP_DIR}/app_${TIMESTAMP}.db"

# Run migrations
for migration in migrations/*.sql; do
    echo "Running $migration..."
    dbcli -c "$CONNECTION" ddl -F "$migration"
    if [ $? -ne 0 ]; then
        echo "Migration failed: $migration"
        echo "Restoring from backup..."
        cp "${BACKUP_DIR}/app_${TIMESTAMP}.db" app.db
        exit 1
    fi
done

echo "All migrations completed successfully"

Security Best Practices

1. Always backup before DROP/ALTER - Cannot emphasize enough
2. Test DDL on backup database first
3. Use transactions for multiple DDL operations when supported
4. Document schema changes with timestamps and reasons
5. Version control all DDL scripts
6. Limit DDL permissions to admin users only
7. Review constraints before adding (can block future inserts)