Troubleshooting

Quick solutions for common issues when working with CQL.

Setup & Installation Issues

Shard Installation Fails

Error: Failed to resolve dependencies

Solution:

1. Check Crystal version compatibility: crystal --version

2. Update shard.yml with compatible version:

   dependencies:
     cql:
       github: azutoolkit/cql
       version: "~> 1.0"

3. Install with: shards install

Database Driver Not Found

Error: can't load file 'pg'

Solution: Add the database driver to shard.yml:

dependencies:
  # PostgreSQL
  pg:
    github: will/crystal-pg
  # MySQL
  mysql:
    github: crystal-lang/crystal-mysql
  # SQLite
  sqlite3:
    github: crystal-lang/crystal-sqlite3

Database Connection Problems

Connection Refused

Error: Connection refused (Errno)

Solutions:

1. Verify database is running:

   # PostgreSQL
   pg_isready -h localhost -p 5432
   
   # MySQL
   mysqladmin ping -h localhost

2. Check connection string format:

   # Correct
   "postgres://username:password@localhost:5432/database"
   "mysql://username:password@localhost:3306/database"
   "sqlite3:///path/to/database.db"

3. Test connection manually:

   begin
     DB.open("postgres://user:pass@localhost/dbname") do |db|
       result = db.scalar("SELECT 1")
       puts "Connection successful: #{result}"
     end
   rescue ex
     puts "Connection failed: #{ex.message}"
   end

Authentication Failed

Error: password authentication failed

Solutions:

1. Test credentials manually:

   psql -h localhost -U username -d database_name
   mysql -h localhost -u username -p database_name

2. Use environment variables:

   uri: ENV["DATABASE_URL"]? || "postgres://localhost:5432/myapp_dev"

3. Create database user if needed:

   -- PostgreSQL
   CREATE USER myapp_user WITH PASSWORD 'password';
   GRANT ALL PRIVILEGES ON DATABASE myapp_dev TO myapp_user;
   
   -- MySQL
   CREATE USER 'myapp_user'@'localhost' IDENTIFIED BY 'password';
   GRANT ALL PRIVILEGES ON myapp_dev.* TO 'myapp_user'@'localhost';

Schema & Migration Issues

Column Not Found Error

Error: undefined method 'name' for #<NamedTuple(id: Int64)>

Solution: Ensure schema matches database:

# Complete schema definition
table :users do
  primary :id, Int64
  column :name, String
  column :email, String
  timestamps
end

Verify with: crystal run db/migrate.cr

Migration Fails - Column Exists

Error: column "email" already exists

Solution: Check before adding columns:

class AddEmailToUsers < CQL::Migration
  def up
    unless column_exists?(:users, :email)
      alter_table :users do
        add_column :email, String
      end
    end
  end
end

Query & Model Issues

Type Mismatch Errors

Error: no overload matches 'User#new'

Solution: Use proper type casting:

# Safe casting
user = User.new(
  name: params["name"]?.try(&.as(String)) || "",
  email: params["email"]?.try(&.as(String)) || "",
  age: params["age"]?.try(&.as(String).to_i?) || 0
)

Records Not Found

Error: DB::NoResultsError

# ❌ Missing column
table :users do
  primary :id, Int64
  # Missing: column :name, String
end

# ✅ Complete definition
table :users do
  primary :id, Int64
  column :name, String
  column :email, String
  timestamps
end

1. Check if migration was run:

   # Run pending migrations
   crystal run db/migrate.cr

2. Verify table structure:

   -- PostgreSQL
   \d users
   
   -- MySQL
   DESCRIBE users;
   
   -- SQLite
   .schema users

❌ Issue: Migration Fails with Column Already Exists

Error: PQ::PQError: ERROR: column "email" of relation "users" already exists

Solutions:

1. Check existing columns before adding:

   # In migration
   alter_table :users do
     add_column :email, String unless column_exists?(:email)
   end

2. Use conditional migrations:

   class AddEmailToUsers < CQL::Migration
     def up
       unless column_exists?(:users, :email)
         alter_table :users do
           add_column :email, String
         end
       end
     end
   
     def down
       if column_exists?(:users, :email)
         alter_table :users do
           drop_column :email
         end
       end
     end
   end

3. Reset and rebuild database (development only):

   # ⚠️ This will destroy all data!
   crystal run db/drop.cr
   crystal run db/create.cr
   crystal run db/migrate.cr

---

🔍 Query & Model Issues

❌ Issue: Type Mismatch Errors

Error: no overload matches 'User#new' with type Hash(String, DB::Any)

Solution:

1. Ensure proper type casting:

   # ❌ Wrong type handling
   user = User.new(params)  # params might have wrong types
   
   # ✅ Proper type casting
   user = User.new(
     name: params["name"].as(String),
     email: params["email"].as(String),
     age: params["age"].as(String).to_i
   )

2. Use safe casting with validation:

   # ✅ Safe casting
   def create_user(params)
     user = User.new(
       name: params["name"]?.try(&.as(String)) || "",
       email: params["email"]?.try(&.as(String)) || "",
       age: params["age"]?.try(&.as(String).to_i?) || 0
     )
   
     # Validate before saving
     if user.valid?
       user.save!
     else
       puts "Validation errors: #{user.errors.full_messages}"
     end
   end

❌ Issue: Records Not Found

Error: DB::NoResultsError

Debugging Steps:

1. Use safe find methods:

   # ❌ Unsafe - raises if not found
   user = User.find!(id)
   
   # ✅ Safe - returns nil if not found
   user = User.find(id)
   if user
     puts "Found: #{user.name}"
   else
     puts "User not found with ID: #{id}"
   end

2. Check your query conditions:

   # Debug your queries
   query = User.where(email: email)
   puts "SQL: #{query.to_sql}"  # See the generated SQL
   
   result = query.first
   puts result ? "Found user" : "No user found"

3. Verify data exists:

   # Check if any data exists
   total_users = User.count
   puts "Total users in database: #{total_users}"
   
   # List all users (for debugging)
   User.all.each_with_index do |user, i|
     puts "#{i + 1}. #{user.name} (#{user.email})"
   end

❌ Issue: Association Loading Problems

Error: undefined method 'posts' for User

Solution:

1. Verify association is defined:

   struct User
     include CQL::ActiveRecord::Model(Int64)
     db_context UserDB, :users
   
     # ✅ Define the association
     has_many :posts, Post, foreign_key: :user_id
   
     property id : Int64?
     property name : String
     property email : String
   end

2. Check foreign key setup:

   struct Post
     include CQL::ActiveRecord::Model(Int64)
     db_context UserDB, :posts
   
     # ✅ Make sure foreign key column exists
     property user_id : Int64?
     property title : String
     property content : String
   
     belongs_to :user, User
   end

---

⚠️ Runtime Errors

❌ Issue: Validation Errors Not Displayed

user = User.new(name: "", email: "invalid")
user.save  # Returns false but no error details shown

Solution:

1. Always check validation errors:

   user = User.new(name: "", email: "invalid")
   
   if user.save
     puts "✅ User saved successfully!"
   else
     puts "❌ Validation failed:"
     user.errors.full_messages.each do |error|
       puts "  - #{error}"
     end
   end

2. Use save! for development debugging:

   begin
     user.save!  # Will raise with detailed error
   rescue CQL::RecordInvalid => ex
     puts "Validation errors:"
     ex.record.errors.full_messages.each { |msg| puts "  - #{msg}" }
   end

❌ Issue: Transaction Rollback Problems

# Transaction doesn't rollback as expected

Solution:

1. Ensure exceptions are raised:

   UserDB.transaction do
     user1.save!  # Use ! methods to raise on failure
     user2.save!
   
     # Explicitly raise for business logic errors
     raise "Business rule violation" if some_condition
   end

2. Handle rollback explicitly:

   UserDB.transaction do |tx|
     begin
       user1.save!
       user2.save!
   
       # Some complex logic that might fail
       raise "Error" if validation_fails
   
     rescue ex
       puts "Rolling back transaction: #{ex.message}"
       tx.rollback
       raise ex  # Re-raise to exit transaction block
     end
   end

---

⚡ Performance Issues

❌ Issue: Slow Queries

Debugging Steps:

1. Enable query logging:

   # In your schema definition
   schema = CQL::Schema.define(:myapp, adapter: adapter, uri: uri) do
     # Enable logging in development
     log_level = :debug if ENV["ENV"]? == "development"
   end

2. Analyze query performance:

   # Time your queries
   start_time = Time.monotonic
   users = User.where(active: true).limit(100).all
   duration = Time.monotonic - start_time
   puts "Query took: #{duration.total_milliseconds}ms"

3. Check for N+1 queries:

   # ❌ N+1 problem
   users = User.all
   users.each do |user|
     puts user.posts.count  # Triggers one query per user
   end
   
   # ✅ Use eager loading (when available)
   users = User.join(:posts).all
   users.each do |user|
     puts user.posts.count  # Posts already loaded
   end

❌ Issue: High Memory Usage

Solutions:

1. Use pagination for large datasets:

   # ❌ Loading all records
   users = User.all  # Could load millions of records
   
   # ✅ Use pagination
   page_size = 100
   offset = 0
   
   loop do
     batch = User.limit(page_size).offset(offset).all
     break if batch.empty?
   
     batch.each { |user| process_user(user) }
     offset += page_size
   end

2. Use select to limit columns:

   # ❌ Loading all columns
   users = User.all
   
   # ✅ Load only needed columns
   users = User.select(:id, :name, :email).all

---

🛠️ Development Tips

🔍 Debugging Techniques

1. Enable SQL logging:

   # See generated SQL queries
   users = User.where(active: true)
   puts users.to_sql  # Shows: ["SELECT * FROM users WHERE active = ?", [true]]

2. Inspect model state:

   user = User.new(name: "Test")
   puts "New record? #{user.new_record?}"
   puts "Persisted? #{user.persisted?}"
   puts "Valid? #{user.valid?}"
   puts "Errors: #{user.errors.full_messages}"

3. Check database state:

   # Verify database connection
   puts "Tables: #{MySchema.tables.keys}"
   
   # Check record counts
   puts "Users: #{User.count}"
   puts "Posts: #{Post.count}"

⚡ Quick Fixes

1. Reset database in development:

   # ⚠️ Development only!
   rm db/development.sqlite3  # For SQLite
   crystal run db/migrate.cr
   crystal run db/seed.cr     # If you have a seed file

2. Clear and rebuild schema cache:

   # Force schema reload
   MySchema.build

3. Check for pending migrations:

   # In your application
   pending = MySchema.pending_migrations
   if pending.any?
     puts "⚠️ Pending migrations: #{pending.map(&.version)}"
   end

---

🆘 Getting More Help

📚 Additional Resources

• CQL Documentation - Complete documentation

• GitHub Issues - Report bugs or get help

• Crystal Forum - Community discussions

• Crystal Discord - Real-time chat support

🐛 Reporting Issues

When reporting issues, please include:

1. Crystal version: crystal --version

2. CQL version: Check your shard.yml

3. Database type and version

4. Minimal reproduction code

5. Full error message and stack trace

6. Expected vs actual behavior

💡 Best Practices

• Use transactions for multi-step operations

• Validate input before database operations

• Handle exceptions gracefully in production

• Use environment variables for sensitive configuration

• Test database operations with proper fixtures

• Monitor query performance in production

• Keep migrations reversible when possible

---

💡 Pro Tip: Most CQL issues are related to schema mismatches or missing validations. Always verify your schema definitions match your database structure and use proper error handling patterns.

Still stuck? Check the FAQ section or reach out to the community for help! 🤝