CRUD Operations

Master CQL's CRUD operations with comprehensive examples and best practices. Learn to Create, Read, Update, and Delete records using both Active Record and Repository patterns with type-safe, database

Create, Read, Update, Delete – The fundamental building blocks of database interactions in CQL

CQL provides powerful, type-safe CRUD operations that work seamlessly across PostgreSQL, MySQL, and SQLite. Whether you prefer the Active Record pattern for domain-rich applications or the Repository pattern for data-centric architectures, CQL has you covered.

Table of Contents

• CRUD Operations

  • Table of Contents

  • Quick Start

  • Create Operations

    • Instance Creation with new + save

    • Direct Creation with create

    • Find or Create

  • Read Operations

    • Finding by Primary Key

    • Finding by Attributes

    • Aggregations and Counting

  • ✏️ Update Operations

    • Load, Modify, and Save

    • Bulk Updates

  • Delete Operations

    • Individual Deletion

    • Bulk Deletion

  • CRUD Flow Diagram

  • Repository Pattern

  • Performance Tips

    • Efficient Queries

    • Indexing Strategy

  • Best Practices

    • Do's

    • Don'ts

  • Further Reading

---

Quick Start

First, let's set up a basic model to work with:

# Example model for demonstrations
struct User
  include CQL::ActiveRecord::Model(Int64)
  db_context UserDB, :users

  property id : Int64?
  property name : String
  property email : String
  property age : Int32
  property active : Bool = true
  property created_at : Time?
  property updated_at : Time?

  # Validations
  validate :name, presence: true, size: 2..100
  validate :email, presence: true, match: EMAIL_REGEX
  validate :age, gt: 0, lt: 150
end

---

Create Operations

Instance Creation with new + save

The most fundamental approach - create an instance and save it:

# Using save (returns true/false)
user = User.new(
  name: "Alice Johnson",
  email: "[e-mail]",
  age: 28
)

if user.save
  puts "✅ User created with ID: #{user.id}"
  puts "📧 Email: #{user.email}"
else
  puts "❌ Failed to save user"
  puts "🚨 Errors: #{user.errors.full_messages.join(", ")}"
end

# Using save! (raises on failure)
begin
  user = User.new(
    name: "Bob Smith",
    email: "[e-mail]",
    age: 35
  )
  user.save!
  puts "✅ User '#{user.name}' saved with ID: #{user.id}"
rescue CQL::RecordInvalid => ex
  puts "❌ Validation failed: #{ex.record.errors.full_messages.join(", ")}"
rescue Exception => ex
  puts "💥 Save failed: #{ex.message}"
end

Direct Creation with create

Create and save in one step:

# Using create! (recommended - raises on failure)
begin
  user = User.create!(
    name: "Carol Davis",
    email: "[e-mail]",
    age: 42,
    active: true
  )
  puts "🎉 Created user: #{user.name} (ID: #{user.id})"

  # You can also use a hash
  attrs = {
    name: "David Wilson",
    email: "[e-mail]",
    age: 31
  }
  user2 = User.create!(attrs)
  puts "🎉 Created user: #{user2.name} (ID: #{user2.id})"

rescue CQL::RecordInvalid => ex
  puts "❌ Validation failed: #{ex.record.errors.full_messages.join(", ")}"
rescue Exception => ex
  puts "💥 Creation failed: #{ex.message}"
end

Find or Create

Avoid duplicates with find_or_create_by:

# Find existing user or create new one
user = User.find_or_create_by(
  email: "[e-mail]",
  name: "Unique User",
  age: 25
)

puts user.persisted? ? "📦 Found existing user" : "🆕 Created new user"
puts "👤 User: #{user.name} (#{user.email})"

---

Read Operations

Finding by Primary Key

# Find by ID (returns User? - nil if not found)
user = User.find(1)
if user
  puts "👤 Found user: #{user.name}"
else
  puts "❌ User not found"
end

# Find by ID or raise exception
begin
  user = User.find!(1)
  puts "👤 Found user: #{user.name}"
rescue DB::NoResultsError
  puts "❌ No user with ID 1"
end

Finding by Attributes

# Find first matching user
user = User.find_by(email: "[e-mail]")
puts user ? "📧 Found: #{user.name}" : "❌ No user with that email"

# Find with multiple conditions
admin_user = User.find_by(active: true, age: 25)

# Find or raise exception
begin
  user = User.find_by!(email: "[e-mail]")
  puts "✅ Found required user: #{user.name}"
rescue DB::NoResultsError
  puts "❌ Required user not found!"
end

Aggregations and Counting

# Count all users
total_users = User.count
puts "👥 Total users: #{total_users}"

# Count with conditions
active_users = User.query.where(active: true).count
puts "✅ Active users: #{active_users}"

# Find all matching records
all_admins = User.find_all_by(role: "admin")
puts "👑 Found #{all_admins.size} admin users"

---

✏️ Update Operations

Load, Modify, and Save

# Find and update a user
if user = User.find_by(email: "[e-mail]")
  user.name = "Alice Johnson-Smith"
  user.age = 29

  if user.save
    puts "✅ User updated successfully"
  else
    puts "❌ Update failed: #{user.errors.full_messages.join(", ")}"
  end
end

# Using save! for updates
begin
  user = User.find_by!(email: "[e-mail]")
  user.active = false
  user.save!
  puts "✅ User deactivated successfully"
rescue CQL::RecordInvalid => ex
  puts "❌ Update failed: #{ex.record.errors.full_messages.join(", ")}"
end

Bulk Updates

# Update multiple records at once
updated_count = User.query
  .where(active: true, age: 18..25)
  .update_all(role: "student")

puts "🎓 Updated #{updated_count} users to student role"

# Update with conditions
User.query
  .where("created_at < ?", 1.month.ago)
  .update_all(needs_verification: true)

---

Delete Operations

Individual Deletion

# Find and delete a user
if user = User.find_by(email: "[e-mail]")
  if user.delete
    puts "🗑️ User deleted successfully"
  else
    puts "❌ Failed to delete user"
  end
end

# Using delete! (raises on failure)
begin
  user = User.find_by!(email: "[e-mail]")
  user.delete!
  puts "🗑️ Spam user deleted"
rescue Exception => ex
  puts "💥 Delete failed: #{ex.message}"
end

Bulk Deletion

# Delete multiple records
deleted_count = User.query
  .where(active: false, "last_login_at < ?", 6.months.ago)
  .delete_all

puts "🗑️ Deleted #{deleted_count} inactive users"

# Delete with complex conditions
User.query
  .where("email LIKE ?", "[e-mail]")
  .delete_all

---

CRUD Flow Diagram

---

Repository Pattern

For data-centric applications, CQL also supports the Repository pattern:

# Repository interface
abstract class UserRepository
  abstract def find(id : Int64) : User?
  abstract def find_by_email(email : String) : User?
  abstract def save(user : User) : Bool
  abstract def delete(user : User) : Bool
  abstract def all : Array(User)
end

# Concrete implementation
class SqlUserRepository < UserRepository
  def find(id : Int64) : User?
    User.find(id)
  end

  def find_by_email(email : String) : User?
    User.find_by(email: email)
  end

  def save(user : User) : Bool
    user.save
  end

  def delete(user : User) : Bool
    user.delete
  end

  def all : Array(User)
    User.all
  end
end

---

Performance Tips

Efficient Queries

# Use specific selects to reduce data transfer
users = User.query
  .select(:id, :name, :email)
  .where(active: true)
  .limit(100)

# Use includes for eager loading (prevents N+1)
posts = Post.query
  .includes(:user, :comments)
  .where(published: true)

# Use batch operations for large datasets
User.query.where(active: false).find_in_batches(batch_size: 1000) do |batch|
  batch.each { |user| user.update!(needs_verification: true) }
end

Indexing Strategy

# Ensure proper indexing for frequently queried columns
table :users do
  # ... columns ...

  # Index frequently queried columns
  index [:email], unique: true
  index [:active, :created_at]
  index [:role, :active]
end

---

Best Practices

Do's

• Use create! and save! for critical operations where failure should halt execution

• Validate data before saving to ensure data integrity

• Use transactions for multi-step operations that must succeed or fail together

• Handle errors gracefully with proper exception handling

• Use batch operations for large datasets to improve performance

• Implement proper indexing for frequently queried columns

Don'ts

• Don't ignore validation errors - always handle them appropriately

• Don't use find! without exception handling in user-facing code

• Don't perform bulk operations without considering performance impact

• Don't forget to close connections in long-running applications

• Don't use raw SQL unless absolutely necessary - CQL provides type safety

---

Further Reading

For detailed Active Record CRUD operations with advanced features, see:

• Active Record CRUD Operations - Comprehensive Active Record implementation

• Querying & Scopes - Advanced query building

• Complex Queries - Joins, aggregations, and subqueries

• Transactions - Data consistency and ACID compliance

• Validations - Data validation and integrity

---

💡 Tip: CQL's CRUD operations are designed to be intuitive and type-safe. Start with the basic patterns and gradually explore advanced features as your application grows!