wheels db dump

Export database schema and data to a file.

Synopsis

wheels db dump [--output=<file>] [--datasource=<name>] [--environment=<env>] 
              [--schema-only] [--data-only] [--tables=<list>] [--compress]

Description

The wheels db dump command exports your database to a SQL file that can be used for backups, migrations, or setting up new environments. It supports various options for customizing what gets exported.

Options

--output=

Output file path. Defaults to dump_[datasource]_[timestamp].sql.

wheels db dump --output=backup.sql

--datasource=

Specify which datasource to dump. If not provided, uses the default datasource.

wheels db dump --datasource=myapp_prod

--environment=

Specify the environment to use. Defaults to the current environment.

wheels db dump --environment=production

--schema-only

Export only the database structure (no data).

wheels db dump --schema-only

--data-only

Export only the data (no structure).

wheels db dump --data-only

--tables=

Comma-separated list of specific tables to dump.

wheels db dump --tables=users,posts,comments

--compress

Compress the output file using gzip.

wheels db dump --compress

Examples

Basic Backup

Create a timestamped backup:

wheels db dump
# Creates: dump_myapp_dev_20231204153045.sql

Production Backup

wheels db dump --environment=production --output=prod-backup.sql --compress

Schema Only Export

For version control:

wheels db dump --schema-only --output=schema.sql

Specific Tables

Export user-related tables:

wheels db dump --tables=users,user_roles,user_sessions --output=user-data.sql

Compressed Backup

Save space with compression:

wheels db dump --output=backup.sql.gz --compress

Database-Specific Behavior

MySQL/MariaDB

• Uses mysqldump utility

• Includes stored procedures and triggers

• Preserves character sets and collations

PostgreSQL

• Uses pg_dump utility

• Includes schemas, functions, and extensions

• Handles permissions and ownership

SQL Server

• Basic export functionality

• For full backups, use SQL Server Management Studio

H2

• Uses built-in SCRIPT command

• Exports to SQL script format

• Supports compression natively

Output Format

The dump file contains:

1. Database structure (CREATE TABLE statements)

2. Indexes and constraints

3. Data (INSERT statements)

4. Views, procedures (if supported)

Example output structure:

-- Database dump generated by Wheels
-- Date: 2023-12-04 15:30:45

-- Table structure for users
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    email VARCHAR(255) UNIQUE
);

-- Data for users
INSERT INTO users (id, name, email) VALUES
(1, 'John Doe', '[email protected]'),
(2, 'Jane Smith', '[email protected]');

Common Use Cases

Daily Backups

Automated backup script:

#!/bin/bash
DATE=$(date +%Y%m%d)
wheels db dump --output=backups/daily-$DATE.sql.gz --compress

Before Major Changes

# Before deployment
wheels db dump --output=pre-deployment-backup.sql

# Deploy changes
# ...

# If something goes wrong
wheels db restore pre-deployment-backup.sql

Environment Sync

# Export from production
wheels db dump --environment=production --output=prod-data.sql

# Import to staging
wheels db restore prod-data.sql --environment=staging

Data Migration

# Export specific tables
wheels db dump --tables=legacy_users,legacy_orders --output=migration-data.sql

# Process with migration scripts
# Import to new structure

Performance Considerations

• Large databases may take time to dump

• Use --compress to reduce file size

• Consider --tables for partial backups

• Off-peak hours for production dumps

Security Notes

1. Protect dump files - They contain sensitive data

2. Encrypt backups - Use additional encryption for sensitive data

3. Secure transfer - Use secure methods to transfer dumps

4. Clean up - Don't leave dump files in public directories

Troubleshooting

"mysqldump not found"

Install MySQL client tools:

# macOS
brew install mysql-client

# Linux
sudo apt-get install mysql-client

"Permission denied"

• Check database user has SELECT permissions

• Ensure write permissions for output directory

Large Database Issues

• Use --compress to reduce size

• Consider table-by-table exports

• Increase timeout settings if needed

Related Commands

• wheels db restore - Restore from dump

• wheels db status - Check before dump

• wheels db shell - Manual export options