Generated Schema Example

A comprehensive demonstration of CQL's automatic schema generation system, showing how to create, manage, and use auto-generated schema files that stay synchronized with your database structure.

🎯 What You'll Learn

This example teaches you how to:

Generate schema files automatically from existing databases
Keep schema files synchronized with database changes
Use generated schemas in Active Record models
Manage schema versioning and updates
Integrate schema generation into your development workflow
Handle schema conflicts and resolution
Customize schema generation options
Deploy applications with generated schemas

🚀 Quick Start

# Run the generated schema example
crystal examples/generated_schema.cr

📁 Code Structure

examples/
├── generated_schema.cr                  # Main generated schema example
├── existing_database.db                 # Existing SQLite database
├── generated_schema_file.cr             # Auto-generated schema file
└── schema_models.cr                     # Models using generated schema

🔧 Key Features

1. Schema Generation Setup

# Setup Schema Generation
puts "Setting up CQL Schema Generation..."

# Create a schema for an existing database
ExistingDB = CQL::Schema.define(
  :existing_database,
  adapter: CQL::Adapter::SQLite,
  uri: "sqlite3://./examples/existing_database.db"
)

# Generate schema file from existing database
puts "Generating schema file from existing database..."
generated_schema = CQL.generate_schema_file(
  ExistingDB,
  file_path: "examples/generated_schema_file.cr",
  schema_name: :GeneratedSchema,
  schema_symbol: :generated_schema
)

puts "Schema file generated successfully!"

2. Existing Database Structure

# This example assumes an existing database with the following structure:
#
# Table: users
# - id (INTEGER PRIMARY KEY)
# - name (TEXT)
# - email (TEXT UNIQUE)
# - created_at (DATETIME)
#
# Table: posts
# - id (INTEGER PRIMARY KEY)
# - user_id (INTEGER)
# - title (TEXT)
# - content (TEXT)
# - published (BOOLEAN DEFAULT 0)
# - created_at (DATETIME)
# - updated_at (DATETIME)
# - FOREIGN KEY (user_id) REFERENCES users(id)
#
# Table: comments
# - id (INTEGER PRIMARY KEY)
# - post_id (INTEGER)
# - user_id (INTEGER)
# - content (TEXT)
# - created_at (DATETIME)
# - FOREIGN KEY (post_id) REFERENCES posts(id)
# - FOREIGN KEY (user_id) REFERENCES users(id)

3. Generated Schema File

# The generated schema file (examples/generated_schema_file.cr) will contain:
GeneratedSchema = CQL::Schema.define(
  :generated_schema,
  adapter: CQL::Adapter::SQLite,
  uri: "sqlite3://./examples/existing_database.db") do
  table :users do
    primary :id, Int32
    text :name
    text :email
    datetime :created_at
  end

  table :posts do
    primary :id, Int32
    text :title
    text :content
    bigint :user_id
    boolean :published, default: "0"
    datetime :created_at
    datetime :updated_at
    foreign_key [:user_id], references: :users, references_columns: [:id]
  end

  table :comments do
    primary :id, Int32
    text :content
    bigint :post_id
    bigint :user_id
    datetime :created_at
    foreign_key [:post_id], references: :posts, references_columns: [:id]
    foreign_key [:user_id], references: :users, references_columns: [:id]
  end
end

🏗️ Schema Generation Architecture

📊 Schema Generation Examples

Basic Schema Generation

# Generate schema from existing database
puts "Generating schema from existing database..."

# Create schema definition for existing database
ExistingDB = CQL::Schema.define(
  :existing_database,
  adapter: CQL::Adapter::SQLite,
  uri: "sqlite3://./examples/existing_database.db"
)

# Generate schema file
generated_schema = CQL.generate_schema_file(
  ExistingDB,
  file_path: "examples/generated_schema_file.cr",
  schema_name: :GeneratedSchema,
  schema_symbol: :generated_schema
)

puts "Schema file generated: #{generated_schema}"
puts "File path: examples/generated_schema_file.cr"

Schema Generation with Options

# Generate schema with custom options
puts "\nGenerating schema with custom options..."

custom_schema = CQL.generate_schema_file(
  ExistingDB,
  file_path: "examples/custom_schema.cr",
  schema_name: :CustomGeneratedSchema,
  schema_symbol: :custom_generated_schema,
  include_indexes: true,
  include_foreign_keys: true,
  include_check_constraints: true,
  format_output: true
)

puts "Custom schema file generated: #{custom_schema}"

Schema Validation

# Validate generated schema against database
puts "\nValidating generated schema..."

# Load the generated schema
require "./examples/generated_schema_file"

# Validate schema consistency
is_consistent = CQL.verify_schema(GeneratedSchema)
puts "Schema consistency: #{is_consistent}"

if is_consistent
  puts "✅ Generated schema is consistent with database"
else
  puts "❌ Schema inconsistency detected"
  puts "You may need to regenerate the schema file"
end

🔧 Using Generated Schemas

Active Record Models with Generated Schema

# Load the generated schema
require "./examples/generated_schema_file"

# Define models using the generated schema
class User
  include CQL::ActiveRecord::Model(Int32)
  db_context GeneratedSchema, :users

  property id : Int32?
  property name : String
  property email : String
  property created_at : Time?

  # Relationships
  has_many :posts, Post, foreign_key: :user_id
  has_many :comments, Comment, foreign_key: :user_id

  def initialize(@name : String, @email : String, @created_at : Time? = nil)
  end
end

class Post
  include CQL::ActiveRecord::Model(Int32)
  db_context GeneratedSchema, :posts

  property id : Int32?
  property title : String
  property content : String
  property user_id : Int32
  property published : Bool
  property created_at : Time?
  property updated_at : Time?

  # Relationships
  belongs_to :user, User, foreign_key: :user_id
  has_many :comments, Comment, foreign_key: :post_id

  def initialize(@title : String, @content : String, @user_id : Int32, @published : Bool = false, @created_at : Time? = nil, @updated_at : Time? = nil)
  end
end

class Comment
  include CQL::ActiveRecord::Model(Int32)
  db_context GeneratedSchema, :comments

  property id : Int32?
  property content : String
  property post_id : Int32
  property user_id : Int32
  property created_at : Time?

  # Relationships
  belongs_to :post, Post, foreign_key: :post_id
  belongs_to :user, User, foreign_key: :user_id

  def initialize(@content : String, @post_id : Int32, @user_id : Int32, @created_at : Time? = nil)
  end
end

CRUD Operations with Generated Schema

# Test CRUD operations with generated schema
puts "\nTesting CRUD operations with generated schema..."

# Create a user
user = User.new("John Doe", "john@example.com")
user.save!
puts "Created user: #{user.name} (ID: #{user.id})"

# Create a post
post = Post.new("My First Post", "This is the content of my first post.", user.id.not_nil!, true)
post.save!
puts "Created post: #{post.title} (ID: #{post.id})"

# Create a comment
comment = Comment.new("Great post!", post.id.not_nil!, user.id.not_nil!)
comment.save!
puts "Created comment: #{comment.content} (ID: #{comment.id})"

# Query with relationships
posts = Post.includes(:user).all
posts.each do |post|
  puts "Post: #{post.title} by #{post.user.try(&.name) || 'Unknown'}"
end

comments = Comment.includes(:user, :post).all
comments.each do |comment|
  puts "Comment: #{comment.content} by #{comment.user.try(&.name) || 'Unknown'} on #{comment.post.try(&.title) || 'Unknown Post'}"
end

📊 Schema Generation Options

Generation Configuration

# Configure schema generation options
generation_config = CQL::SchemaGenerationConfig.new
generation_config.include_indexes = true
generation_config.include_foreign_keys = true
generation_config.include_check_constraints = true
generation_config.include_defaults = true
generation_config.format_output = true
generation_config.add_comments = true
generation_config.include_timestamps = true

# Generate schema with configuration
configured_schema = CQL.generate_schema_file(
  ExistingDB,
  file_path: "examples/configured_schema.cr",
  schema_name: :ConfiguredSchema,
  schema_symbol: :configured_schema,
  config: generation_config
)

puts "Configured schema generated: #{configured_schema}"

Schema Generation Modes

# Different generation modes
modes = ["full", "minimal", "readonly", "migration_ready"]

modes.each do |mode|
  puts "\nGenerating schema in #{mode} mode..."

  mode_schema = CQL.generate_schema_file(
    ExistingDB,
    file_path: "examples/#{mode}_schema.cr",
    schema_name: "#{mode.capitalize}Schema".to_sym,
    schema_symbol: "#{mode}_schema".to_sym,
    mode: mode
  )

  puts "#{mode.capitalize} schema generated: #{mode_schema}"
end

🎯 Schema Generation Patterns

Development Workflow Integration

# Integrate schema generation into development workflow
def development_schema_sync
  puts "🔄 Syncing schema for development..."

  # Check if database has changed
  if CQL.schema_changed?(ExistingDB)
    puts "Database schema has changed, regenerating..."

    # Generate updated schema
    updated_schema = CQL.generate_schema_file(
      ExistingDB,
      file_path: "examples/generated_schema_file.cr",
      schema_name: :GeneratedSchema,
      schema_symbol: :generated_schema
    )

    puts "Schema updated: #{updated_schema}"

    # Validate the updated schema
    require "./examples/generated_schema_file"
    is_consistent = CQL.verify_schema(GeneratedSchema)
    puts "Schema consistency after update: #{is_consistent}"
  else
    puts "Database schema is up to date"
  end
end

development_schema_sync

Production Deployment

# Schema generation for production deployment
def production_schema_preparation
  puts "🏭 Preparing schema for production..."

  # Generate production schema
  prod_schema = CQL.generate_schema_file(
    ExistingDB,
    file_path: "examples/production_schema.cr",
    schema_name: :ProductionSchema,
    schema_symbol: :production_schema,
    mode: "production",
    include_indexes: true,
    include_foreign_keys: true,
    format_output: false # Disable formatting for production
  )

  puts "Production schema generated: #{prod_schema}"

  # Validate production schema
  require "./examples/production_schema"
  is_consistent = CQL.verify_schema(ProductionSchema)

  if is_consistent
    puts "✅ Production schema is ready for deployment"
  else
    puts "❌ Production schema validation failed"
    raise "Schema validation failed for production"
  end
end

production_schema_preparation

Schema Versioning

# Schema versioning and management
def schema_versioning_example
  puts "📚 Demonstrating schema versioning..."

  # Generate schema with version information
  versioned_schema = CQL.generate_schema_file(
    ExistingDB,
    file_path: "examples/versioned_schema.cr",
    schema_name: :VersionedSchema,
    schema_symbol: :versioned_schema,
    version: "1.0.0",
    include_version_info: true
  )

  puts "Versioned schema generated: #{versioned_schema}"

  # Check schema version
  schema_version = CQL.get_schema_version(ExistingDB)
  puts "Current database schema version: #{schema_version}"

  # Compare schema versions
  if CQL.schema_version_changed?(ExistingDB, "1.0.0")
    puts "Schema version has changed since 1.0.0"
  else
    puts "Schema version is still 1.0.0"
  end
end

schema_versioning_example

📊 Generated Schema Examples

Full Schema Generation

# Generate complete schema with all features
full_schema = CQL.generate_schema_file(
  ExistingDB,
  file_path: "examples/full_schema.cr",
  schema_name: :FullSchema,
  schema_symbol: :full_schema,
  include_indexes: true,
  include_foreign_keys: true,
  include_check_constraints: true,
  include_defaults: true,
  include_timestamps: true,
  add_comments: true,
  format_output: true
)

puts "Full schema generated with all features"

Minimal Schema Generation

# Generate minimal schema for basic operations
minimal_schema = CQL.generate_schema_file(
  ExistingDB,
  file_path: "examples/minimal_schema.cr",
  schema_name: :MinimalSchema,
  schema_symbol: :minimal_schema,
  include_indexes: false,
  include_foreign_keys: false,
  include_check_constraints: false,
  include_defaults: false,
  add_comments: false,
  format_output: false
)

puts "Minimal schema generated for basic operations"

Read-Only Schema Generation

# Generate read-only schema for reporting
readonly_schema = CQL.generate_schema_file(
  ExistingDB,
  file_path: "examples/readonly_schema.cr",
  schema_name: :ReadOnlySchema,
  schema_symbol: :readonly_schema,
  mode: "readonly",
  include_indexes: true,
  include_foreign_keys: true
)

puts "Read-only schema generated for reporting"

🎯 Best Practices

1. Development Workflow

# Best practices for development workflow
def development_best_practices
  puts "🎯 Development Best Practices..."

  # Always validate schema after generation
  generated_schema = CQL.generate_schema_file(ExistingDB, file_path: "examples/dev_schema.cr")
  require "./examples/dev_schema"
  is_consistent = CQL.verify_schema(DevSchema)

  if is_consistent
    puts "✅ Schema generation successful and validated"
  else
    puts "❌ Schema validation failed - check database structure"
  end

  # Keep schema files in version control
  puts "💡 Remember to commit generated schema files to version control"

  # Use descriptive schema names
  puts "💡 Use descriptive schema names for different environments"
end

2. Production Deployment

# Best practices for production deployment
def production_best_practices
  puts "🏭 Production Best Practices..."

  # Generate production schema with strict validation
  prod_schema = CQL.generate_schema_file(
    ExistingDB,
    file_path: "examples/production_schema.cr",
    schema_name: :ProductionSchema,
    mode: "production"
  )

  # Validate schema before deployment
  require "./examples/production_schema"
  is_consistent = CQL.verify_schema(ProductionSchema)

  if is_consistent
    puts "✅ Production schema validated successfully"
    puts "🚀 Ready for deployment"
  else
    puts "❌ Production schema validation failed"
    raise "Cannot deploy with invalid schema"
  end
end

3. Schema Management

# Best practices for schema management
def schema_management_best_practices
  puts "📚 Schema Management Best Practices..."

  # Version your schemas
  version = "1.0.0"
  versioned_schema = CQL.generate_schema_file(
    ExistingDB,
    file_path: "examples/versioned_schema.cr",
    schema_name: :VersionedSchema,
    version: version
  )

  puts "📝 Schema versioned as #{version}"

  # Document schema changes
  puts "📝 Document any schema changes in your migration files"

  # Test schema with your models
  puts "🧪 Always test generated schemas with your Active Record models"
end

📚 Next Steps

Schema Migration Workflow - Migration-based schema management
PostgreSQL Migration Workflow - PostgreSQL-specific schema generation
Blog Engine - See schema generation in a complete application

Advanced Topics

Schema Management - Complete schema documentation
Migration Guide - Migration-based schema evolution
Configuration Guide - Schema generation configuration

Production Considerations

Schema Validation - Always validate generated schemas before deployment
Version Control - Keep generated schema files in version control
Testing - Test generated schemas with your application models
Documentation - Document schema changes and generation processes
Automation - Automate schema generation in your CI/CD pipeline

🔧 Troubleshooting

Common Schema Generation Issues

Database connection issues - Check database URL and connectivity
Permission problems - Ensure database user has proper permissions
Schema inconsistency - Regenerate schema after database changes
File path issues - Ensure target directory exists and is writable

Debug Schema Generation

# Debug schema generation issues
puts "🔧 Debugging schema generation..."

# Check database connection
begin
  ExistingDB.exec("SELECT 1")
  puts "✅ Database connection successful"
rescue ex
  puts "❌ Database connection failed: #{ex.message}"
end

# Check schema discovery
tables = CQL.discover_tables(ExistingDB)
puts "📋 Discovered tables: #{tables.join(", ")}"

# Check schema generation
begin
  test_schema = CQL.generate_schema_file(
    ExistingDB,
    file_path: "examples/test_schema.cr",
    schema_name: :TestSchema
  )
  puts "✅ Schema generation successful: #{test_schema}"
rescue ex
  puts "❌ Schema generation failed: #{ex.message}"
end

🏁 Summary

This generated schema example demonstrates:

✅ Automatic schema generation from existing databases
✅ Schema synchronization with database changes
✅ Multiple generation modes for different use cases
✅ Schema validation and consistency checking
✅ Active Record integration with generated schemas
✅ Production-ready deployment with proper validation
✅ Development workflow integration for seamless development

Ready to implement schema generation in your CQL application? Start with basic generation and gradually add advanced features as needed! 🚀

PreviousConfiguration Example NextLogger Report Example

Last updated 8 days ago

Was this helpful?