Database Management Overview
The Wheels CLI provides comprehensive database management commands that make it easy to create, manage, and maintain your application's database throughout the development lifecycle.
Overview
Database management in Wheels is divided into two main categories:
Database Commands (
wheels db
) - High-level database operationsMigration Commands (
wheels dbmigrate
) - Schema versioning and changes
This guide covers the database management commands. For migration-specific operations, see the migrations guide.
Database Lifecycle Commands
Creating a Database
The wheels db create
command creates a new database based on your datasource configuration:
# Create database using default datasource
wheels db create
# Create database for specific datasource
wheels db create --datasource=myapp_dev
# Create database for specific environment
wheels db create --environment=production
Note: The datasource must already be configured in your CFML server admin. The command will create the database itself but not the datasource configuration.
Dropping a Database
The wheels db drop
command removes an existing database:
# Drop database (with confirmation)
wheels db drop
# Drop database without confirmation
wheels db drop --force
# Drop specific datasource
wheels db drop --datasource=myapp_dev
Warning: This is a destructive operation. Always backup important data before dropping a database.
Database Setup
The wheels db setup
command performs a complete database initialization:
# Full setup: create + migrate + seed
wheels db setup
# Setup without seeding
wheels db setup --skip-seed
# Setup with custom seed count
wheels db setup --seed-count=20
This is ideal for setting up a new development environment or initializing a test database.
Database Reset
The wheels db reset
command completely rebuilds your database:
# Reset database (drop + create + migrate + seed)
wheels db reset
# Reset without confirmation
wheels db reset --force
# Reset without seeding
wheels db reset --skip-seed
# Reset specific environment
wheels db reset --environment=testing
Important: This command will destroy all existing data. Use with caution, especially in production environments.
Data Management
Seeding the Database
The wheels db seed
command populates your database with test or sample data:
# Seed with default settings (5 records per model)
wheels db seed
# Seed with custom record count
wheels db seed --count=10
# Seed specific models only
wheels db seed --models=user,post,comment
# Seed from a JSON file
wheels db seed --dataFile=seeds/test-data.json
Example seed data file format:
{
"users": [
{
"name": "John Doe",
"email": "[email protected]",
"role": "admin"
},
{
"name": "Jane Smith",
"email": "[email protected]",
"role": "user"
}
],
"posts": [
{
"title": "Welcome Post",
"content": "This is the first post",
"userId": 1
}
]
}
Status and Information Commands
Checking Migration Status
The wheels db status
command shows the current state of your migrations:
# Show migration status in table format
wheels db status
# Show status in JSON format
wheels db status --format=json
# Show only pending migrations
wheels db status --pending
Output example:
| Version | Description | Status | Applied At |
|---------------------|----------------------------------|----------|-------------------|
| 20231201120000 | CreateUsersTable | applied | 2023-12-01 12:30 |
| 20231202140000 | AddEmailToUsers | applied | 2023-12-02 14:15 |
| 20231203160000 | CreatePostsTable | pending | Not applied |
Checking Database Version
The wheels db version
command shows the current schema version:
# Show current version
wheels db version
# Show detailed version information
wheels db version --detailed
Migration Management
Rolling Back Migrations
The wheels db rollback
command reverses previously applied migrations:
# Rollback last migration
wheels db rollback
# Rollback multiple migrations
wheels db rollback --steps=3
# Rollback to specific version
wheels db rollback --target=20231201120000
# Force rollback without confirmation
wheels db rollback --steps=5 --force
Backup and Restore
Creating Database Backups
The wheels db dump
command exports your database:
# Basic dump (auto-named with timestamp)
wheels db dump
# Dump to specific file
wheels db dump --output=backup.sql
# Dump schema only (no data)
wheels db dump --schema-only
# Dump data only (no schema)
wheels db dump --data-only
# Dump specific tables
wheels db dump --tables=users,posts,comments
# Dump with compression
wheels db dump --output=backup.sql.gz --compress
Restoring from Backups
The wheels db restore
command imports a database dump:
# Restore from SQL file
wheels db restore backup.sql
# Restore compressed file
wheels db restore backup.sql.gz --compressed
# Clean restore (drop existing objects first)
wheels db restore backup.sql --clean
# Force restore without confirmation
wheels db restore backup.sql --force
Common Workflows
Setting Up a New Development Environment
# 1. Clone the repository
git clone https://github.com/myapp/repo.git
cd repo
# 2. Install dependencies
box install
# 3. Setup database
wheels db setup
# 4. Start the server
server start
Resetting Development Database
# Quick reset with fresh data
wheels db reset --force
# Or manually:
wheels db drop --force
wheels db create
wheels dbmigrate latest
wheels db seed --count=10
Production Database Backup
# Create timestamped backup
wheels db dump --compress
# Or with custom filename
wheels db dump --output=prod-backup-$(date +%Y%m%d).sql.gz --compress
Migrating Between Environments
# Export from development
wheels db dump --output=dev-data.sql --environment=development
# Import to staging
wheels db restore dev-data.sql --environment=staging
Interactive Database Shell
The wheels db shell
command provides direct access to your database's interactive shell:
# Launch CLI shell for current datasource
wheels db shell
# Launch web-based console (H2 only)
wheels db shell --web
# Use specific datasource
wheels db shell --datasource=myapp_dev
# Execute single command
wheels db shell --command="SELECT COUNT(*) FROM users"
Database-Specific Shells
H2 Database:
# CLI shell
wheels db shell
# Web console (opens in browser)
wheels db shell --web
MySQL:
# Opens mysql client
wheels db shell
# Connects to: mysql -h host -P port -u user -p database
PostgreSQL:
# Opens psql client
wheels db shell
# Connects to: psql -h host -p port -U user -d database
SQL Server:
# Opens sqlcmd client
wheels db shell
# Connects to: sqlcmd -S server -d database -U user
Shell Requirements
The database shell commands require the appropriate database client tools to be installed:
H2: No additional installation needed (included with Lucee)
MySQL: Install
mysql
clientPostgreSQL: Install
psql
clientSQL Server: Install
sqlcmd
client
Database Support
The database commands support multiple database engines:
MySQL/MariaDB - Full support for all operations
PostgreSQL - Full support for all operations
SQL Server - Full support for most operations
H2 - Full support (auto-created databases)
Oracle - Limited support (basic operations)
Configuration
Database commands use the datasource configuration from your Wheels application. You can override settings using command parameters:
# Use specific datasource
wheels db create --datasource=myapp_test
# Use specific environment
wheels db setup --environment=testing
Safety Features
Confirmation Prompts - Destructive operations require confirmation
Force Flag - Use
--force
to skip confirmations in scriptsEnvironment Detection - Extra warnings for production environments
Transaction Support - Operations are wrapped in transactions where possible
Troubleshooting
Common Issues
Datasource Not Found
Error: Datasource 'myapp' not found in server configuration
Solution: Create the datasource in your CFML server admin first.
Database Already Exists
Error: Database already exists: myapp_dev
Solution: Use wheels db drop
first or use wheels db reset
instead.
Permission Denied
Error: Access denied for user 'myuser'@'localhost'
Solution: Ensure the database user has CREATE/DROP privileges.
Missing Database Tools
Error: mysqldump not found in PATH
Solution: Install database client tools for dump/restore operations.
Best Practices
Always backup before destructive operations
wheels db dump --output=backup-before-reset.sql wheels db reset
Use environment-specific datasources
myapp_dev
for developmentmyapp_test
for testingmyapp_prod
for production
Automate with scripts
#!/bin/bash # reset-db.sh wheels db dump --output=backups/pre-reset-$(date +%s).sql wheels db reset --force echo "Database reset complete"
Version control your seeds
Keep seed files in
db/seeds/
Use environment-specific seed files
Document seed data structure
Related Commands
wheels dbmigrate
- Database migration commandswheels test
- Test database operationswheels generate model
- Generate models with migrations
Last updated
Was this helpful?