Database Commands

Azu CLI provides a comprehensive set of database commands for managing your application's database. These commands work with CQL ORM and support PostgreSQL, MySQL, and SQLite.

Overview

All database commands follow the pattern:

azu db:<command> [options]

Available Commands

Command

Description

azu db:create

Create the database

azu db:migrate

Run pending migrations

azu db:rollback

Rollback migrations

azu db:reset

Drop, create, and migrate database

azu db:seed

Run seed data

azu db:status

Show migration status

azu db:setup

Setup database (create and migrate)

azu db:drop

Drop the database

Database Configuration

Environment Variables

# Database URL (recommended)
export DATABASE_URL="postgresql://username:password@localhost/my_app_development"

# Or individual components
export AZU_DB_HOST="localhost"
export AZU_DB_PORT="5432"
export AZU_DB_NAME="my_app_development"
export AZU_DB_USER="username"
export AZU_DB_PASSWORD="password"

Configuration File

# config/database.yml
development:
  adapter: postgres
  database: my_app_development
  host: localhost
  port: 5432
  username: username
  password: password

test:
  adapter: postgres
  database: my_app_test
  host: localhost
  port: 5432
  username: username
  password: password

production:
  adapter: postgres
  url: <%= ENV["DATABASE_URL"] %>

azu db:create

Creates the database for the current environment.

Basic Usage

# Create database for current environment
azu db:create

# Create with custom database name
azu db:create --database my_custom_db

Options

Option

Description

Default

--env <environment>

Target environment

development

--database <name>

Database name

auto-detected

--force

Force creation (drop if exists)

false

Examples

# Create development database
azu db:create
# Created database 'my_app_development'

# Create with custom name
azu db:create --database my_custom_db
# Created database 'my_custom_db'

Troubleshooting

# Permission denied
sudo -u postgres createdb my_app_development

# Database already exists
azu db create --force

# Connection refused
# Check if database server is running
sudo systemctl status postgresql

azu db:reset

Resets the database by dropping, creating, migrating, and optionally seeding.

Basic Usage

# Reset database for current environment
azu db:reset

# Reset with confirmation
azu db:reset --force

Options

Option

Description

Default

--force

Skip confirmation prompt

false

Examples

# Reset development database
azu db:reset
# Are you sure? [y/N]: y
# Reset database 'my_app_development'

# Reset without confirmation
azu db:reset --force
# Reset database 'my_app_development'

azu db:migrate

Runs pending database migrations using CQL's built-in Migrator to update the database schema.

Key Features:

Uses CQL's Migrator class for type-safe migrations
Automatically updates src/db/schema.cr after running migrations
Tracks migrations in cql_schema_migrations table
Generates temporary Crystal script for execution

Basic Usage

# Run all pending migrations
azu db:migrate

# Run specific number of migrations
azu db:migrate --steps 2

# Run with verbose output
azu db:migrate --verbose

Options

Option

Description

Default

--env <environment>

Target environment

development

--version <version>

Migrate to specific version

latest

--steps <number>

Number of migrations to run

all

--verbose

Show detailed output

false

Examples

# Run all pending migrations
azu db:migrate
# Running migrations...
# ✓ All migrations completed successfully
# ✓ Schema file updated: src/db/schema.cr

# Run specific number of migrations
azu db:migrate --steps 2

# Migrate to specific version
azu db:migrate --version 20240115103045

# View migration status with verbose output
azu db:migrate --verbose

How It Works

CLI generates a temporary Crystal script in the project root
Script loads src/db/schema.cr and all migration files from src/db/migrations/
CQL Migrator runs pending migrations in order using the generated script
Schema file is automatically synchronized with the database
Migration status is tracked in the schema_migrations table
Temporary script is cleaned up after execution

Migration File Example

require "cql"

class CreateUsers < CQL::Migration(20240115103045_i64)
  def up
    schema.table :users do
      primary :id, Int64
      column :name, String
      column :email, String
      timestamps
    end
    schema.users.create!
  end

  def down
    schema.users.drop!
  end
end

azu db:rollback

Rolls back database migrations using CQL's Migrator. Automatically updates the schema file after rollback.

Basic Usage

# Rollback last migration
azu db:rollback

# Rollback multiple migrations
azu db:rollback --steps 3

# Rollback to specific version
azu db:rollback --version 20240115103045

Options

Option

Description

Default

--steps <number>

Number of migrations to rollback

--version <version>

Rollback to specific version

--verbose

Show detailed output

false

Examples

# Rollback last migration
azu db:rollback
# Rolling back 1 migration(s)...
# ✓ Rollback completed successfully
# ✓ Schema file updated: src/db/schema.cr

# Rollback last 3 migrations
azu db:rollback --steps 3

# Rollback to specific version (all migrations after this version)
azu db:rollback --version 20240115103045

How It Works

CLI generates a temporary Crystal script in the project root
Script loads src/db/schema.cr and all migration files from src/db/migrations/
CQL Migrator executes the down method for each migration in reverse order
Removes migration records from schema_migrations table
Schema file is automatically synchronized with the database
Temporary script is cleaned up after execution

azu db:reset

Drops, creates, and migrates the database in one command.

Basic Usage

# Reset database for current environment
azu db:reset

# Reset with seed data
azu db:reset --seed

Options

Option

Description

Default

--seed

Run seed data after reset

false

--force

Skip confirmation prompt

false

Examples

# Reset development database
azu db:reset
# Dropped database 'my_app_development'
# Created database 'my_app_development'
# == 20231201000001 CreateUsers: migrating ========================
# == 20231201000001 CreateUsers: migrated (0.1234s) ===============

# Reset with seed data
azu db:reset --seed
# ... database operations ...
# Seeding database...
# Created 10 users
# Created 25 posts

azu db:setup

Sets up the database by creating it (if it doesn't exist) and running all migrations.

Basic Usage

# Setup database for current environment
azu db:setup

# Setup with seed data
azu db:setup --seed

Options

Option

Description

Default

--seed

Run seed data after setup

false

Examples

# Setup development database
azu db:setup
# Database already exists
# Running migrations...
# ✓ Database setup completed successfully

# Setup with seed data
azu db:setup --seed
# Database already exists
# Running migrations...
# Seeding database...
# ✓ Database setup completed successfully

azu db:drop

Drops the database for the current environment.

Basic Usage

# Drop database for current environment
azu db:drop

# Drop with force (no confirmation)
azu db:drop --force

Options

Option

Description

Default

--force

Skip confirmation prompt

false

Examples

# Drop development database
azu db:drop
# Are you sure you want to drop database 'my_app_development'? [y/N]: y
# Dropped database 'my_app_development'

# Drop without confirmation
azu db:drop --force
# Dropped database 'my_app_development'

azu db:seed

Runs seed data to populate the database with initial data.

Basic Usage

# Run seed data for current environment
azu db:seed

Examples

# Run all seed files
azu db:seed
# Seeding database...
# Created 10 users
# Created 25 posts
# Created 5 categories

Seed File Structure

# db/seeds/users.cr
require "../src/models/**"

# Create admin user
admin = User.create!(
  name: "Admin User",
  email: "admin@example.com",
  role: "admin"
)

puts "Created admin user: #{admin.email}"

# Create sample users
10.times do |i|
  user = User.create!(
    name: "User #{i + 1}",
    email: "user#{i + 1}@example.com",
    role: "user"
  )
  puts "Created user: #{user.email}"
end

azu generate migration

Creates a new CQL migration file with proper Schema DSL syntax.

Basic Usage

# Generate a migration
azu generate migration CreateUsers name:string email:string

# Generate a simple migration
azu generate migration AddIndexToUsers

Examples

# Generate migration with attributes
azu generate migration CreateUsers name:string email:string age:int32
# Created: src/db/migrations/20240115103045_create_users.cr

# Generated content uses CQL Schema DSL:
require "cql"

class CreateUsers < CQL::Migration(20240115103045_i64)
  def up
    schema.table :users do
      primary :id, Int64
      column :name, String
      column :email, String
      column :age, Int32
      timestamps
    end
    schema.users.create!

    # Indexes automatically added if needed
    schema.alter :users do
      create_index :email_idx, [:email], unique: true
    end
  end

  def down
    schema.users.drop!
  end
end

Supported Attribute Types

string, text → String
int32, integer → Int32
int64 → Int64
float, float64 → Float64
bool, boolean → Bool
datetime, time → Time
date → Date
json → JSON::Any
uuid → UUID
references, belongs_to → Foreign key (Int64)

azu db:status

Shows the current migration status.

Basic Usage

# Show migration status
azu db:status

Examples

# Check migration status
azu db:status
# Migration Status:
#   [✓] 20231201000001_create_users
#   [✓] 20231201000002_add_email_to_users
#   [ ] 20231201000003_add_phone_to_users

Database Adapters

PostgreSQL

# Install PostgreSQL adapter
# Add to shard.yml:
# dependencies:
#   cql:
#     github: azutoolkit/cql
#     version: ~> 0.8.0

# Configuration
export DATABASE_URL="postgres://username:password@localhost/my_app_development"

MySQL

# Install MySQL adapter
# Add to shard.yml:
# dependencies:
#   cql:
#     github: azutoolkit/cql
#     version: ~> 0.8.0

# Configuration
export DATABASE_URL="mysql://username:password@localhost/my_app_development"

SQLite

# Install SQLite adapter
# Add to shard.yml:
# dependencies:
#   cql:
#     github: azutoolkit/cql
#     version: ~> 0.8.0

# Configuration
export DATABASE_URL="sqlite://./db/development.db"

Common Workflows

Development Workflow

# Start new feature
azu db:migrate

# Make changes to models
# Generate new migration
azu generate migration add_field_to_table

# Run migration
azu db:migrate

# If something goes wrong
azu db:rollback

# Reset for clean slate
azu db:reset --seed

Testing Workflow

# Setup test database
azu db:setup

# Run tests
crystal spec

# Clean up
azu db:reset

Production Workflow

# Deploy to production
azu db:migrate

# If migration fails
azu db:rollback

# Check current status
azu db:status

Troubleshooting

Connection Issues

# Check database server status
sudo systemctl status postgresql

# Test connection
psql -h localhost -U username -d my_app_development

# Check environment variables
echo $DATABASE_URL

Permission Issues

# Create database user
sudo -u postgres createuser -s username

# Grant permissions
sudo -u postgres psql -c "GRANT ALL PRIVILEGES ON DATABASE my_app_development TO username;"

Migration Issues

# Check migration status
azu db:status

# Reset migrations
azu db:reset

# Check migration files
ls -la src/db/migrations/

Seed Issues

# Run seed data
azu db:seed

# Check seed files
ls -la src/db/

# Check seed file content
cat src/db/seed.cr

Best Practices

1. Environment Management

# Use different databases for each environment
azu db:setup
# Database name will be based on project name and environment

2. Migration Safety

# Always backup before migrations
pg_dump my_app_production > backup.sql

# Test migrations in development first
azu db:migrate

# Check migration status
azu db:status

3. Seed Data

# Keep seeds idempotent
# Use create_or_find instead of create

# Separate seeds by environment
db/seeds/
├── development/
├── test/
└── production/

4. Database URLs

# Use DATABASE_URL for all environments
export DATABASE_URL="postgres://username:password@localhost/my_app_development"

# Use .env files for local development
echo "DATABASE_URL=postgres://..." > .env

The database commands provide a complete workflow for managing your Azu application's database, from creation to migration to seeding.

Next Steps:

Migration Generator - Create database migrations
Model Generator - Create database models
Development Workflows - Learn database workflows

PreviousSession Management Commands NextOpenAPI Commands

Last updated 7 days ago