Dbcli Export

Command Syntax

dbcli -c "CONNECTION_STRING" [-t DATABASE_TYPE] export TABLE_NAME > output.sql

Global Options

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

Basic Export

Single Table Export

# SQLite - Export Users table
dbcli -c "Data Source=app.db" export Users > Users_backup.sql

# Output file contains:
# INSERT INTO Users (Id, Name, Email) VALUES (1, 'Alice', '[email protected]');
# INSERT INTO Users (Id, Name, Email) VALUES (2, 'Bob', '[email protected]');

With Timestamp

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

echo "Backup created: Users_backup_${TIMESTAMP}.sql"

Different Databases

# SQL Server
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
dbcli -c "Server=localhost;Database=mydb;Trusted_Connection=True" -t sqlserver export Users > Users_backup_${TIMESTAMP}.sql

# MySQL
dbcli -c "Server=localhost;Database=mydb;Uid=root;Pwd=xxxxxxxxxx" -t mysql export Customers > Customers_backup_${TIMESTAMP}.sql

# PostgreSQL
dbcli -c "Host=localhost;Database=mydb;Username=postgres;Password=xxxxxxxxxx" -t postgresql export Orders > Orders_backup_${TIMESTAMP}.sql

Mandatory Backup Before Dangerous Operations

Before UPDATE - Export Backup Workflow

#!/bin/bash
# Safe UPDATE workflow with mandatory backup

TABLE="Users"
CONNECTION="Data Source=app.db"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
BACKUP_FILE="${TABLE}_backup_${TIMESTAMP}.sql"

# STEP 1: MANDATORY BACKUP
echo "Creating mandatory backup before UPDATE..."
dbcli -c "$CONNECTION" export $TABLE > $BACKUP_FILE

# Verify backup created
if [ ! -f "$BACKUP_FILE" ]; then
    echo "ERROR: Backup failed! Aborting UPDATE."
    exit 1
fi

BACKUP_SIZE=$(wc -l < "$BACKUP_FILE")
echo "Backup created: $BACKUP_FILE ($BACKUP_SIZE lines)"

# STEP 2: Confirm with user
read -p "Backup complete. Proceed with UPDATE? (yes/no): " confirm
if [ "$confirm" != "yes" ]; then
    echo "UPDATE cancelled by user"
    exit 0
fi

# STEP 3: Execute UPDATE
echo "Executing UPDATE..."
dbcli -c "$CONNECTION" exec "UPDATE Users SET status = 'verified' WHERE email_confirmed = 1"

echo "UPDATE complete. Backup saved: $BACKUP_FILE"

Before DELETE - Export Affected Records

#!/bin/bash
# Safe DELETE workflow with selective backup

TABLE="Users"
CONNECTION="Data Source=app.db"
WHERE_CLAUSE="last_login < date('now', '-365 days')"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)

# STEP 1: Preview what will be deleted
echo "Records to be deleted:"
dbcli -c "$CONNECTION" -f table query "SELECT * FROM $TABLE WHERE $WHERE_CLAUSE"

# Count affected records
COUNT=$(dbcli -c "$CONNECTION" query "SELECT COUNT(*) as count FROM $TABLE WHERE $WHERE_CLAUSE" | jq -r '.[0].count')
echo "Total records to delete: $COUNT"

# STEP 2: MANDATORY BACKUP of affected records
if [ "$COUNT" -gt 0 ]; then
    BACKUP_FILE="${TABLE}_deleted_${TIMESTAMP}.sql"
    echo "Creating backup of records to be deleted..."

    # Export full table (safest approach)
    dbcli -c "$CONNECTION" export $TABLE > $BACKUP_FILE

    echo "Backup created: $BACKUP_FILE"
fi

# STEP 3: Confirm deletion
read -p "Delete $COUNT records? (yes/no): " confirm
if [ "$confirm" != "yes" ]; then
    echo "DELETE cancelled"
    exit 0
fi

# STEP 4: Execute DELETE
dbcli -c "$CONNECTION" exec "DELETE FROM $TABLE WHERE $WHERE_CLAUSE"
echo "Deleted $COUNT records. Backup: $BACKUP_FILE"

Before DROP TABLE - Full Export

#!/bin/bash
# Safe DROP TABLE workflow with complete backup

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

mkdir -p $BACKUP_DIR

# STEP 1: Export table schema
echo "Exporting table schema..."
dbcli -c "$CONNECTION" -f table columns $TABLE > "${BACKUP_DIR}/${TABLE}_schema_${TIMESTAMP}.txt"

# STEP 2: MANDATORY data export
echo "Exporting table data..."
dbcli -c "$CONNECTION" export $TABLE > "${BACKUP_DIR}/${TABLE}_data_${TIMESTAMP}.sql"

# STEP 3: Create table copy (fastest recovery option)
echo "Creating table copy..."
dbcli -c "$CONNECTION" query "CREATE TABLE ${TABLE}_copy_${TIMESTAMP} AS SELECT * FROM $TABLE"

# Verify backups
DATA_LINES=$(wc -l < "${BACKUP_DIR}/${TABLE}_data_${TIMESTAMP}.sql")
COPY_COUNT=$(dbcli -c "$CONNECTION" query "SELECT COUNT(*) as count FROM ${TABLE}_copy_${TIMESTAMP}" | jq -r '.[0].count')

echo "Backups created:"
echo "  - Schema: ${BACKUP_DIR}/${TABLE}_schema_${TIMESTAMP}.txt"
echo "  - Data: ${BACKUP_DIR}/${TABLE}_data_${TIMESTAMP}.sql ($DATA_LINES lines)"
echo "  - Table copy: ${TABLE}_copy_${TIMESTAMP} ($COPY_COUNT rows)"

# STEP 4: Confirm DROP
read -p "All backups created. DROP TABLE $TABLE? (type 'DROP' to confirm): " confirm
if [ "$confirm" != "DROP" ]; then
    echo "DROP TABLE cancelled"
    exit 0
fi

# STEP 5: Execute DROP
echo "Dropping table..."
dbcli -c "$CONNECTION" ddl "DROP TABLE $TABLE"
echo "Table dropped. Recovery files available in $BACKUP_DIR/"

Export All Tables (Database Backup)

#!/bin/bash
# Export all tables in database

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

mkdir -p $BACKUP_DIR

echo "Exporting all tables..."

# Get list of tables
dbcli -c "$CONNECTION" tables | jq -r '.[].TableName' | while read table; do
    echo "  Exporting $table..."
    dbcli -c "$CONNECTION" export $table > "${BACKUP_DIR}/${table}.sql"
done

# Create archive
tar -czf "backup_${TIMESTAMP}.tar.gz" $BACKUP_DIR

echo "Backup complete: backup_${TIMESTAMP}.tar.gz"

Restore from Export

Restore Single Table

# Drop and recreate table, then import backup
dbcli -c "Data Source=app.db" ddl "DROP TABLE IF EXISTS Users"
dbcli -c "Data Source=app.db" ddl -F Users_schema.sql  # Create table structure
dbcli -c "Data Source=app.db" exec -F Users_backup_20250127_143022.sql

echo "Table restored from backup"

Restore Specific Records

# Restore only specific records from backup
grep "WHERE Id IN (1, 2, 3)" Users_backup_20250127_143022.sql | \
    dbcli -c "Data Source=app.db" exec -F -

# Or manually edit SQL file to restore selective records

Cross-Database Migration

# Export from MySQL
dbcli -c "Server=source;Database=mydb;Uid=root;Pwd=xxxxxxxxxx" -t mysql export Users > Users_export.sql

# Import to PostgreSQL (may need SQL syntax adjustments)
dbcli -c "Host=target;Database=mydb;Username=postgres;Password=xxxxxxxxxx" -t postgresql exec -F Users_export.sql

Chinese Domestic Databases

DaMeng (达梦)

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

# Export table
dbcli -c "Server=localhost;User Id=SYSDBA;PWD=xxxxxxxxxx;DATABASE=mydb" -t dm export dm_test > dm_test_backup_${TIMESTAMP}.sql

# Before UPDATE
echo "Creating backup before UPDATE..."
dbcli -c "Server=localhost;User Id=SYSDBA;PWD=xxxxxxxxxx;DATABASE=mydb" -t dm export dm_test > dm_test_backup_${TIMESTAMP}.sql
read -p "Backup complete. Continue with UPDATE? (yes/no): " confirm
if [ "$confirm" = "yes" ]; then
    dbcli -c "Server=localhost;User Id=SYSDBA;PWD=xxxxxxxxxx;DATABASE=mydb" -t dm exec "UPDATE dm_test SET status = 1"
fi

GaussDB

TIMESTAMP=$(date +%Y%m%d_%H%M%S)
CONNECTION="Host=localhost;Port=8000;Database=mydb;Username=gaussdb;Password=xxxxxxxxxx"

# Export with timestamp
dbcli -c "$CONNECTION" -t gaussdb export gauss_test > gauss_test_backup_${TIMESTAMP}.sql

# Safe DELETE workflow
echo "Creating backup before DELETE..."
dbcli -c "$CONNECTION" -t gaussdb export gauss_test > gauss_test_backup_${TIMESTAMP}.sql
echo "Backup: gauss_test_backup_${TIMESTAMP}.sql"
read -p "Proceed with DELETE? (yes/no): " confirm
[ "$confirm" = "yes" ] && dbcli -c "$CONNECTION" -t gaussdb exec "DELETE FROM gauss_test WHERE inactive = 1"

Programmatic Export with Safety

Python - Safe Modification Function

import subprocess
import json
from datetime import datetime
import os

def safe_modify_table(connection, table, modify_sql, db_type='sqlite'):
    """Execute modification with automatic backup"""

    timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
    backup_dir = 'backups'
    os.makedirs(backup_dir, exist_ok=True)

    backup_file = f"{backup_dir}/{table}_backup_{timestamp}.sql"

    # STEP 1: MANDATORY BACKUP
    print(f"Creating backup: {backup_file}")
    export_cmd = ['dbcli', '-c', connection, '-t', db_type, 'export', table]

    with open(backup_file, 'w', encoding='utf-8') as f:
        result = subprocess.run(export_cmd, stdout=f, text=True)

    if result.returncode != 0:
        raise Exception("Backup failed! Aborting modification.")

    # Verify backup file created
    if not os.path.exists(backup_file):
        raise Exception("Backup file not created!")

    backup_size = os.path.getsize(backup_file)
    print(f"Backup created: {backup_size} bytes")

    # STEP 2: Prompt user
    confirm = input(f"Backup complete. Execute modification? (yes/no): ")
    if confirm.lower() != 'yes':
        print("Modification cancelled by user")
        return None

    # STEP 3: Execute modification
    print("Executing modification...")
    exec_cmd = ['dbcli', '-c', connection, '-t', db_type, 'exec', modify_sql]
    result = subprocess.run(exec_cmd, capture_output=True, text=True)

    if result.returncode != 0:
        print(f"Modification failed: {result.stderr}")
        print(f"Backup available: {backup_file}")
        return None

    data = json.loads(result.stdout)
    print(f"Modified {data['AffectedRows']} rows")
    print(f"Backup saved: {backup_file}")

    return backup_file

# Usage
backup = safe_modify_table(
    connection='Data Source=app.db',
    table='Users',
    modify_sql="UPDATE Users SET verified = 1 WHERE email_confirmed = 1"
)

PowerShell - Backup Before Delete

function Remove-TableDataSafely {
    param(
        [string]$Connection,
        [string]$Table,
        [string]$WhereClause,
        [string]$DbType = 'sqlite'
    )

    $timestamp = Get-Date -Format "yyyyMMdd_HHmmss"
    $backupFile = "${Table}_backup_${timestamp}.sql"

    # Preview deletion
    $previewSql = "SELECT * FROM $Table WHERE $WhereClause"
    Write-Host "Records to be deleted:"
    dbcli -c $Connection -t $DbType -f table query $previewSql

    $countSql = "SELECT COUNT(*) as count FROM $Table WHERE $WhereClause"
    $count = (dbcli -c $Connection -t $DbType query $countSql | ConvertFrom-Json)[0].count
    Write-Host "Total records to delete: $count"

    # MANDATORY BACKUP
    Write-Host "Creating backup..."
    dbcli -c $Connection -t $DbType export $Table > $backupFile

    if (-not (Test-Path $backupFile)) {
        Write-Error "Backup failed! Aborting deletion."
        return
    }

    Write-Host "Backup created: $backupFile"

    # Confirm deletion
    $confirm = Read-Host "Delete $count records? (yes/no)"
    if ($confirm -ne 'yes') {
        Write-Host "Deletion cancelled"
        return
    }

    # Execute DELETE
    $deleteSql = "DELETE FROM $Table WHERE $WhereClause"
    $result = dbcli -c $Connection -t $DbType exec $deleteSql | ConvertFrom-Json

    Write-Host "Deleted $($result.AffectedRows) rows"
    Write-Host "Backup: $backupFile"
}

# Usage
Remove-TableDataSafely -Connection "Data Source=app.db" `
                        -Table "Users" `
                        -WhereClause "active = 0"

Backup Verification

# Verify backup completeness
TABLE="Users"
BACKUP="Users_backup_20250127_143022.sql"

# Count records in original table
ORIGINAL_COUNT=$(dbcli -c "Data Source=app.db" query "SELECT COUNT(*) as count FROM $TABLE" | jq -r '.[0].count')

# Count INSERT statements in backup
BACKUP_COUNT=$(grep -c "^INSERT INTO" $BACKUP)

echo "Original table: $ORIGINAL_COUNT records"
echo "Backup file: $BACKUP_COUNT INSERT statements"

if [ "$ORIGINAL_COUNT" -eq "$BACKUP_COUNT" ]; then
    echo "Backup verified - counts match"
else
    echo "WARNING: Backup incomplete! Counts don't match!"
fi

Automated Backup Schedule

#!/bin/bash
# daily_backup.sh - Schedule with cron

CONNECTION="Data Source=production.db"
BACKUP_DIR="/backups/database"
RETENTION_DAYS=30
TIMESTAMP=$(date +%Y%m%d_%H%M%S)

mkdir -p $BACKUP_DIR

# Export all tables
dbcli -c "$CONNECTION" tables | jq -r '.[].TableName' | while read table; do
    dbcli -c "$CONNECTION" export $table > "${BACKUP_DIR}/${table}_${TIMESTAMP}.sql"
done

# Compress backups
tar -czf "${BACKUP_DIR}/full_backup_${TIMESTAMP}.tar.gz" ${BACKUP_DIR}/*_${TIMESTAMP}.sql
rm ${BACKUP_DIR}/*_${TIMESTAMP}.sql

# Delete old backups
find $BACKUP_DIR -name "full_backup_*.tar.gz" -mtime +$RETENTION_DAYS -delete

echo "Backup complete: full_backup_${TIMESTAMP}.tar.gz"

Best Practices

1. ALWAYS export before dangerous operations - UPDATE, DELETE, DROP
2. Use timestamps in backup filenames for version control
3. Verify backups immediately after creation
4. Compress large backups to save disk space
5. Store backups off-server for disaster recovery
6. Test restore procedures regularly
7. Document backup locations for team members
8. Automate regular backups with cron/scheduled tasks

Common Patterns

Pre-Modification Checklist

#!/bin/bash
# pre_modify_checklist.sh

TABLE="$1"
CONNECTION="Data Source=app.db"

echo "=== Pre-Modification Safety Checklist ==="
echo

# 1. Export current data
echo "[1/4] Creating backup..."
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
dbcli -c "$CONNECTION" export $TABLE > "${TABLE}_backup_${TIMESTAMP}.sql"
echo "    Backup: ${TABLE}_backup_${TIMESTAMP}.sql"

# 2. Count records
COUNT=$(dbcli -c "$CONNECTION" query "SELECT COUNT(*) FROM $TABLE" | jq -r '.[0].count')
echo "[2/4] Record count: $COUNT"

# 3. Check table structure
echo "[3/4] Table structure:"
dbcli -c "$CONNECTION" -f table columns $TABLE

# 4. Create table copy
COPY_TABLE="${TABLE}_copy_${TIMESTAMP}"
dbcli -c "$CONNECTION" query "CREATE TABLE $COPY_TABLE AS SELECT * FROM $TABLE"
echo "[4/4] Table copy created: $COPY_TABLE"

echo
echo "=== Checklist Complete ==="
echo "Safe to proceed with modifications"