Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
crystal --versioncrystal init app myapp
cd myapp# shard.yml
dependencies:
cql:
github: azutoolkit/cql
version: "~> 0.0.435"
# Choose your database driver
sqlite3:
github: crystal-lang/crystal-sqlite3
# OR
pg:
github: will/crystal-pg
# OR
mysql:
github: crystal-lang/crystal-mysqlshards install# src/myapp.cr
require "cql"
require "sqlite3"
MyDB = CQL::Schema.define(
:myapp,
adapter: CQL::Adapter::SQLite,
uri: "sqlite3://./app.db"
) do
table :users do
primary :id, Int64, auto_increment: true
column :name, String
column :email, String
timestamps
end
end
struct User
include CQL::ActiveRecord::Model(Int64)
db_context MyDB, :users
property id : Int64?
property name : String
property email : String
property created_at : Time?
property updated_at : Time?
end
MyDB.build
user = User.create!(name: "Alice", email: "alice@example.com")
puts "Created user: #{user.name}"crystal run src/myapp.cr# Production setup
AppDB = CQL::Schema.define(
:production_db,
adapter: CQL::Adapter::Postgres,
uri: ENV["DATABASE_URL"]? || "postgresql://username:password@localhost:5432/myapp_production",
pool_size: 20,
checkout_timeout: 10.seconds
) do
table :users do
primary :id, Int64, auto_increment: true
column :name, String, size: 100, null: false
column :email, String, size: 255, null: false
column :active, Bool, default: true
timestamps
unique_constraint [:email]
index [:active, :created_at]
end
end# Production with SSL
"postgresql://user:pass@prod-server:5432/myapp_prod?sslmode=require"
# Development
"postgresql://user:pass@localhost:5432/myapp_dev"AppDB = CQL::Schema.define(
:mysql_db,
adapter: CQL::Adapter::MySql,
uri: ENV["DATABASE_URL"]? || "mysql://username:password@localhost:3306/myapp_development",
pool_size: 15,
charset: "utf8mb4"
) do
table :users do
primary :id, Int64, auto_increment: true
column :name, String, size: 100
column :email, String, size: 320
timestamps
index [:email], unique: true
end
end# Multi-environment setup
database_url = case ENV["CRYSTAL_ENV"]?
when "test" then "sqlite3://:memory:"
when "production" then "sqlite3://#{ENV["DATA_PATH"]? || "./"}/production.db"
else "sqlite3://./db/development.db"
end
AppDB = CQL::Schema.define(
:app_db,
adapter: CQL::Adapter::SQLite,
uri: database_url
) do
table :users do
primary :id, Int64, auto_increment: true
column :name, String
column :email, String
timestamps
unique_constraint [:email]
end
end# config/database.cr
module DatabaseConfig
ENV_NAME = ENV["CRYSTAL_ENV"]? || "development"
DATABASE_URLS = {
"development" => "sqlite3://./db/development.db",
"test" => "sqlite3://:memory:",
"production" => ENV["DATABASE_URL"]? || raise("DATABASE_URL required")
}
def self.adapter_for(url : String)
case url
when .starts_with?("postgresql://") then CQL::Adapter::Postgres
when .starts_with?("mysql://") then CQL::Adapter::MySql
when .starts_with?("sqlite3://") then CQL::Adapter::SQLite
else raise "Unsupported database URL: #{url}"
end
end
POOL_SIZES = {
"development" => 5,
"test" => 1,
"production" => 25
}
end
database_url = DatabaseConfig::DATABASE_URLS[DatabaseConfig::ENV_NAME]
adapter = DatabaseConfig.adapter_for(database_url)
pool_size = DatabaseConfig::POOL_SIZES[DatabaseConfig::ENV_NAME]
AppDB = CQL::Schema.define(
:app_database,
adapter: adapter,
uri: database_url,
pool_size: pool_size
)src/
├── config/
│ ├── database.cr
│ └── environments/
│ ├── development.cr
│ ├── test.cr
│ └── production.cr
├── models/
│ ├── user.cr
│ └── post.cr
└── myapp.cr# test_connection.cr
require "./config/database"
puts "Testing database connection..."
begin
AppDB.build
puts "✅ Database connection successful"
puts "Tables: #{AppDB.tables.keys}"
if AppDB.tables.has_key?(:users)
user = User.create!(name: "Test", email: "test@example.com")
puts "✅ Created test user: #{user.name}"
user.delete!
puts "✅ CRUD operations working"
end
rescue ex
puts "❌ Connection failed: #{ex.message}"
exit 1
end# docker-compose.yml
version: "3.8"
services:
postgres:
image: postgres:15
environment:
POSTGRES_PASSWORD: password
POSTGRES_DB: myapp_development
ports:
- "5432:5432"
volumes:
- postgres_data:/var/lib/postgresql/data
mysql:
image: mysql:8.0
environment:
MYSQL_ROOT_PASSWORD: password
MYSQL_DATABASE: myapp_development
ports:
- "3306:3306"
volumes:
- mysql_data:/var/lib/mysql
volumes:
postgres_data:
mysql_data:docker-compose up -d postgres
# Use: postgresql://postgres:password@localhost:5432/myapp_development# Reinstall dependencies
rm -rf lib/ shard.lock
shards installConnection refused (localhost:5432)# PostgreSQL
pg_isready -h localhost -p 5432
# MySQL
mysqladmin -h localhost -u root ping# PostgreSQL (macOS with Homebrew)
brew services start postgresql
# PostgreSQL (Linux)
sudo systemctl start postgresql
# MySQL
sudo systemctl start mysql# Check your URL format
DATABASE_URL = "postgres://localhost:5432/myapp" # Port is correct?password authentication failed for user "myuser"# Check username and password
DATABASE_URL = "postgres://username:password@localhost/myapp"psql -U postgres
ALTER USER myuser WITH PASSWORD 'newpassword';database "myapp_development" does not exist# PostgreSQL
createdb myapp_development
# Or in psql
psql -U postgres -c "CREATE DATABASE myapp_development"Dir.mkdir_p("./db") unless Dir.exists?("./db")too many connections for role "myuser"ALTER ROLE myuser CONNECTION LIMIT 100;SSL connection is requiredDATABASE_URL = "postgres://user:pass@host/db?sslmode=require"begin
MyDB.init
puts "Connection successful"
rescue ex
puts "Connection failed"
puts "Error type: #{ex.class}"
puts "Message: #{ex.message}"
# Print sanitized connection string
puts "URL: #{DATABASE_URL.gsub(/:[^:@]+@/, ":****@")}"
end# scripts/test_connection.cr
require "cql"
require "pg"
url = ARGV[0]? || ENV["DATABASE_URL"]? || "postgres://localhost/myapp"
puts "Testing connection to: #{url.gsub(/:[^:@]+@/, ":****@")}"
begin
db = CQL::Schema.define(:test, adapter: CQL::Adapter::Postgres, uri: url) do
end
db.init
db.exec("SELECT 1")
puts "Connection successful!"
rescue ex
puts "Connection failed: #{ex.message}"
exit 1
endcrystal scripts/test_connection.cr# Test if host is reachable
ping database-host.example.com
# Test if port is open
nc -zv localhost 5432dependencies:
cql:
github: azutoolkit/cql
version: ~> 0.0.435
# Keep your existing database driver or add one
pg:
github: will/crystal-pg
version: "~> 0.26.0"shards install# src/cql_config.cr
require "cql"
require "pg"
# Use your existing database URL
DATABASE_URL = ENV["DATABASE_URL"]? || "postgres://localhost/myapp_development"
AppDB = CQL::Schema.define(
:app_db,
adapter: CQL::Adapter::Postgres,
uri: DATABASE_URL
) do
# We'll define existing tables here
end
# Migration configuration
CQL_MIGRATOR_CONFIG = CQL::MigratorConfig.new(
schema_file_path: "src/schemas/app_schema.cr",
schema_name: :AppSchema,
auto_sync: true
)AppDB = CQL::Schema.define(
:app_db,
adapter: CQL::Adapter::Postgres,
uri: DATABASE_URL
) do
# Define existing tables (read-only, won't create them)
table :users do
primary :id, Int64, auto_increment: true
text :email, null: false
text :name
text :password_digest
timestamps
end
table :products do
primary :id, Int64, auto_increment: true
text :name, null: false
text :description
decimal :price, precision: 10, scale: 2
integer :stock, default: 0
timestamps
end
end# src/models/user.cr
struct User
include CQL::ActiveRecord::Model(Int64)
db_context AppDB, :users
property id : Int64?
property email : String
property name : String?
property password_digest : String?
property created_at : Time?
property updated_at : Time?
def initialize(@email : String, @name : String? = nil)
end
end# src/models/product.cr
struct Product
include CQL::ActiveRecord::Model(Int64)
db_context AppDB, :products
property id : Int64?
property name : String
property description : String?
property price : BigDecimal?
property stock : Int32 = 0
property created_at : Time?
property updated_at : Time?
def initialize(@name : String, @price : BigDecimal? = nil)
end
end# src/app.cr (or your main file)
require "./cql_config"
require "./models/*"
# Initialize CQL connection
AppDB.init
# Your existing application code continues...mkdir -p migrations# migrations/001_create_orders.cr
class CreateOrders < CQL::Migration(1)
def up
schema.table :orders do
primary :id, Int64, auto_increment: true
column :user_id, Int64, null: false
column :total, Float64
column :status, String, default: "pending"
timestamps
foreign_key [:user_id], references: :users, references_columns: [:id]
index [:user_id]
index [:status]
end
schema.orders.create!
end
def down
schema.orders.drop!
end
end# src/migrate.cr
require "./cql_config"
require "../migrations/*"
AppDB.init
migrator = AppDB.migrator(CQL_MIGRATOR_CONFIG)
case ARGV[0]?
when "up"
migrator.up
puts "Migrations applied"
when "down"
migrator.down
puts "Last migration rolled back"
when "status"
puts "Applied: #{migrator.applied_migrations.size}"
puts "Pending: #{migrator.pending_migrations.size}"
else
puts "Usage: crystal src/migrate.cr [up|down|status]"
endcrystal src/migrate.cr up# Before (raw SQL)
result = db.query("SELECT * FROM users WHERE email = $1", email)
# After (CQL)
user = User.find_by(email: email)# Before
db.exec("INSERT INTO users (email, name) VALUES ($1, $2)", email, name)
# After
user = User.create!(email: email, name: name)# Before
results = db.query(<<-SQL
SELECT u.*, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id
ORDER BY order_count DESC
LIMIT 10
SQL
)
# After
top_users = User.all.sort_by { |u| -u.orders.count }.first(10)# Update User model
struct User
# ...existing code...
has_many :orders, Order, :user_id
end
# Now you can do
user = User.find(1)
user_orders = user.orders.all# migrations/000_baseline.cr
class Baseline < CQL::Migration(0)
def up
# This migration assumes tables already exist
# It just establishes the baseline for CQL
end
def down
# Cannot rollback baseline
raise "Cannot rollback baseline migration"
end
end# In your migrate script
migrator = AppDB.migrator(CQL_MIGRATOR_CONFIG)
# Mark baseline as applied without running
migrator.mark_as_applied(0) unless migrator.applied?(0)struct User
include CQL::ActiveRecord::Model(Int64)
db_context AppDB, :users
# Map property to different column name
@[CQL::Column(name: "user_email")]
property email : String
end# Handle existing timestamp format
property created_at : Time?
def created_at_formatted
created_at.try(&.to_s("%Y-%m-%d %H:%M:%S"))
enddependencies:
cql:
github: azutoolkit/cql
version: ~> 0.0.435
# Choose your database driver:
pg: # PostgreSQL
github: will/crystal-pg
version: "~> 0.26.0"shards installrequire "cql"
require "pg"
# Define your database
MyDB = CQL::Schema.define(
:my_db,
adapter: CQL::Adapter::Postgres,
uri: "postgres://localhost/myapp"
) do
end
# Define a model
struct User
include CQL::ActiveRecord::Model(Int64)
db_context MyDB, :users
property id : Int64?
property name : String
property email : String
def initialize(@name : String, @email : String)
end
end
# Use it
MyDB.init
user = User.create!(name: "Alice", email: "alice@example.com")
puts "Created user #{user.id}: #{user.name}"Blog Engine
├── Users (authors and commenters)
├── Categories (post organization)
├── Posts (blog content)
└── Comments (reader engagement)crystal init app blog_engine
cd blog_enginename: blog_engine
version: 0.1.0
dependencies:
cql:
github: azutoolkit/cql
version: ~> 0.0.435
sqlite3:
github: crystal-lang/crystal-sqlite3
version: "~> 0.18.0"
targets:
blog_engine:
main: src/blog_engine.crshards installmkdir -p src/models
mkdir -p src/schemas
mkdir -p migrations
mkdir -p dbblog_engine/
├── shard.yml
├── src/
│ ├── blog_engine.cr # Main entry point
│ ├── database.cr # Database configuration
│ ├── models/ # Active Record models
│ └── schemas/ # Auto-generated schemas
├── migrations/ # Database migrations
└── db/ # SQLite database files# src/database.cr
require "cql"
require "sqlite3"
# Database connection URL - easily switchable via environment
DATABASE_URL = ENV["DATABASE_URL"]? || "sqlite3://./db/blog_development.db"
# Define the schema connection
BlogDB = CQL::Schema.define(
:blog_db,
adapter: CQL::Adapter::SQLite,
uri: DATABASE_URL
) do
# Tables will be defined through migrations
end
# Migration configuration
MIGRATOR_CONFIG = CQL::MigratorConfig.new(
schema_file_path: "src/schemas/blog_schema.cr",
schema_name: :BlogSchema,
auto_sync: true
)# src/blog_engine.cr
require "./database"
require "./models/*"
module BlogEngine
VERSION = "0.1.0"
def self.setup
# Initialize database connection
BlogDB.init
puts "Database connected"
end
def self.migrate
require "../migrations/*"
migrator = BlogDB.migrator(MIGRATOR_CONFIG)
pending = migrator.pending_migrations.size
if pending > 0
puts "Running #{pending} pending migration(s)..."
migrator.up
puts "Migrations complete"
else
puts "Database is up to date"
end
end
end
# Run setup when executed directly
if PROGRAM_NAME.includes?("blog_engine")
BlogEngine.setup
end# src/setup.cr
require "./blog_engine"
puts "Setting up Blog Engine..."
puts "========================="
BlogEngine.setup
BlogEngine.migrate
puts ""
puts "Setup complete!"# src/verify.cr
require "./database"
begin
BlogDB.init
puts "Database connection: OK"
rescue ex
puts "Database connection: FAILED"
puts "Error: #{ex.message}"
exit 1
endcrystal src/verify.cr# Production configuration example
BlogDB = CQL::Schema.define(
:blog_db,
adapter: CQL::Adapter::Postgres,
uri: ENV["DATABASE_URL"]
) do
endUSERS CATEGORIES
├── id (PK) ├── id (PK)
├── username (unique) ├── name
├── email (unique) ├── slug (unique)
├── first_name ├── created_at
├── last_name └── updated_at
├── active
├── created_at
└── updated_at
POSTS COMMENTS
├── id (PK) ├── id (PK)
├── title ├── content
├── content ├── post_id (FK)
├── published ├── user_id (FK, nullable)
├── views_count ├── created_at
├── user_id (FK) └── updated_at
├── category_id (FK)
├── created_at
└── updated_at# migrations/001_create_users.cr
class CreateUsers < CQL::Migration(1)
def up
schema.table :users do
primary :id, Int64, auto_increment: true
column :username, String, null: false
column :email, String, null: false
column :first_name, String, null: true
column :last_name, String, null: true
column :active, Bool, default: true
timestamps
index [:email], unique: true
index [:username], unique: true
end
schema.users.create!
end
def down
schema.users.drop!
end
end# migrations/002_create_categories.cr
class CreateCategories < CQL::Migration(2)
def up
schema.table :categories do
primary :id, Int64, auto_increment: true
column :name, String, null: false
column :slug, String, null: false
timestamps
index [:slug], unique: true
end
schema.categories.create!
end
def down
schema.categories.drop!
end
end# migrations/003_create_posts.cr
class CreatePosts < CQL::Migration(3)
def up
schema.table :posts do
primary :id, Int64, auto_increment: true
column :title, String, null: false
column :content, String, null: false
column :published, Bool, default: false
column :views_count, Int64, default: 0_i64
column :user_id, Int64, null: false
column :category_id, Int64, null: true
timestamps
foreign_key [:user_id], references: :users, references_columns: [:id], on_delete: :cascade
foreign_key [:category_id], references: :categories, references_columns: [:id], on_delete: :set_null
index [:user_id]
index [:category_id]
index [:published]
end
schema.posts.create!
end
def down
schema.posts.drop!
end
end# migrations/004_create_comments.cr
class CreateComments < CQL::Migration(4)
def up
schema.table :comments do
primary :id, Int64, auto_increment: true
column :content, String, null: false
column :post_id, Int64, null: false
column :user_id, Int64, null: true # Allow anonymous comments
timestamps
foreign_key [:post_id], references: :posts, references_columns: [:id], on_delete: :cascade
foreign_key [:user_id], references: :users, references_columns: [:id], on_delete: :set_null
index [:post_id]
index [:user_id]
end
schema.comments.create!
end
def down
schema.comments.drop!
end
end# src/setup.cr
require "./blog_engine"
puts "Setting up Blog Engine..."
puts "========================="
BlogEngine.setup
BlogEngine.migrate
puts ""
puts "Setup complete!"crystal src/setup.crSetting up Blog Engine...
=========================
Database connected
Running 4 pending migration(s)...
Migrations complete
Setup complete!# src/verify_schema.cr
require "./database"
require "../migrations/*"
BlogDB.init
migrator = BlogDB.migrator(MIGRATOR_CONFIG)
puts "Migration Status"
puts "================"
puts "Applied: #{migrator.applied_migrations.size}"
puts "Pending: #{migrator.pending_migrations.size}"
puts ""
puts "Applied migrations:"
migrator.applied_migrations.each do |m|
puts " - Migration #{m}"
endcrystal src/verify_schema.crUsers ─────────┬─── has many ───> Posts
│
└─── has many ───> Comments
Categories ────────── has many ───> Posts
Posts ─────────┬─── belongs to ──> User
├─── belongs to ──> Category (optional)
└─── has many ────> Comments
Comments ──────┬─── belongs to ──> Post
└─── belongs to ──> User (optional)# src/models/user.cr
struct User
include CQL::ActiveRecord::Model(Int64)
db_context BlogDB, :users
# ... existing properties ...
# Validations
validate :username, presence: true, size: (3..50)
validate :email, presence: true, match: /\A[\w+\-.]+@[a-z\d\-]+(\.[a-z\d\-]+)*\.[a-z]+\z/i
# Custom validation
def validate
super
validate_username_uniqueness
validate_email_uniqueness
end
private def validate_username_uniqueness
existing = User.find_by(username: @username)
if existing && existing.id != @id
errors.add(:username, "has already been taken")
end
end
private def validate_email_uniqueness
existing = User.find_by(email: @email)
if existing && existing.id != @id
errors.add(:email, "has already been taken")
end
end
end# src/models/post.cr
struct Post
include CQL::ActiveRecord::Model(Int64)
db_context BlogDB, :posts
# ... existing properties ...
# Validations
validate :title, presence: true, size: (1..200)
validate :content, presence: true
def validate
super
validate_title_length
end
private def validate_title_length
if @title.size > 200
errors.add(:title, "is too long (maximum 200 characters)")
end
end
end# Test validations
user = User.new(username: "ab", email: "invalid")
if user.valid?
user.save
else
puts "Validation errors:"
user.errors.each do |field, messages|
messages.each do |message|
puts " #{field}: #{message}"
end
end
end# src/models/user.cr
struct User
# ... existing code ...
# Callbacks
before_save :normalize_email
after_create :send_welcome_notification
private def normalize_email
@email = @email.downcase.strip
end
private def send_welcome_notification
# In a real app, this would send an email or create a notification
puts "Welcome, #{display_name}! Your account has been created."
end
end# src/models/post.cr
struct Post
# ... existing code ...
# Callbacks
before_save :update_timestamps
after_save :notify_subscribers
private def update_timestamps
now = Time.utc
@created_at ||= now
@updated_at = now
end
private def notify_subscribers
if @published && changes.includes?(:published)
# Notify subscribers about new post
puts "Notifying subscribers about: #{@title}"
end
end
end# src/models/category.cr
struct Category
# ... existing code ...
before_save :generate_slug_if_blank
private def generate_slug_if_blank
if @slug.blank?
@slug = generate_slug(@name)
end
end
enddef create_post_with_comments(author : User, title : String, content : String, comment_texts : Array(String))
Post.transaction do
post = Post.create!(
title: title,
content: content,
user_id: author.id.not_nil!,
published: true
)
comment_texts.each do |text|
Comment.create!(
content: text,
post_id: post.id.not_nil!,
user_id: author.id
)
end
puts "Created post with #{comment_texts.size} comments"
post
end
enddef register_user(username : String, email : String, bio : String?)
User.transaction do
user = User.create!(
username: username,
email: email
)
# Create welcome post
Post.create!(
title: "Welcome to my blog!",
content: "Hi, I'm #{user.display_name}. #{bio || "Welcome to my blog!"}",
user_id: user.id.not_nil!,
published: true
)
user
end
rescue ex
puts "Registration failed: #{ex.message}"
nil
end# src/seeders.cr
require "./blog_engine"
module Seeders
def self.run
puts "Seeding database..."
# Create users
users = create_users
puts "Created #{users.size} users"
# Create categories
categories = create_categories
puts "Created #{categories.size} categories"
# Create posts
posts = create_posts(users, categories)
puts "Created #{posts.size} posts"
# Create comments
comments = create_comments(users, posts)
puts "Created #{comments.size} comments"
puts "Seeding complete!"
end
private def self.create_users
[
{username: "alice", email: "alice@example.com", first_name: "Alice", last_name: "Johnson"},
{username: "bob", email: "bob@example.com", first_name: "Bob", last_name: "Smith"},
{username: "charlie", email: "charlie@example.com", first_name: "Charlie", last_name: "Brown"},
].map do |attrs|
User.create!(
username: attrs[:username],
email: attrs[:email],
first_name: attrs[:first_name],
last_name: attrs[:last_name]
)
end
end
private def self.create_categories
["Technology", "Lifestyle", "Travel", "Food", "Programming"].map do |name|
Category.create!(name: name)
end
end
private def self.create_posts(users : Array(User), categories : Array(Category))
posts = [] of Post
users.each do |user|
rand(2..5).times do |i|
category = categories.sample
posts << Post.create!(
title: "#{user.display_name}'s Post ##{i + 1}",
content: "This is a sample blog post by #{user.full_name}. " * 10,
user_id: user.id.not_nil!,
category_id: category.id,
published: rand < 0.8, # 80% published
views_count: rand(0_i64..500_i64)
)
end
end
posts
end
private def self.create_comments(users : Array(User), posts : Array(Post))
comments = [] of Comment
posts.select(&.published?).each do |post|
rand(0..5).times do
commenter = rand < 0.9 ? users.sample : nil # 10% anonymous
comments << Comment.create!(
content: "Great post! " + ["I learned a lot.", "Thanks for sharing.", "Very helpful!"].sample,
post_id: post.id.not_nil!,
user_id: commenter.try(&.id)
)
end
end
comments
end
end
# Run if executed directly
if PROGRAM_NAME.includes?("seeders")
BlogEngine.setup
Seeders.run
endcrystal src/seeders.cr# src/monitoring.cr
require "./blog_engine"
module Monitoring
def self.print_stats
puts ""
puts "Blog Statistics"
puts "==============="
puts ""
# Basic counts
puts "Content:"
puts " Users: #{User.count} (#{User.where(active: true).count} active)"
puts " Categories: #{Category.count}"
puts " Posts: #{Post.count} (#{Post.where(published: true).count} published)"
puts " Comments: #{Comment.count}"
puts ""
# Engagement
puts "Engagement:"
total_views = Post.where(published: true).sum(:views_count)
avg_views = Post.where(published: true).count > 0 ?
total_views / Post.where(published: true).count : 0
puts " Total views: #{total_views}"
puts " Average views per post: #{avg_views}"
puts ""
# Top content
puts "Top Posts by Views:"
Post.where(published: true)
.order(views_count: :desc)
.limit(5)
.all
.each do |post|
author = post.user.try(&.display_name) || "Unknown"
puts " #{post.views_count} views - \"#{post.title}\" by #{author}"
end
puts ""
# Most active users
puts "Most Active Authors:"
User.all
.map { |u| {user: u, count: u.posts.where(published: true).count} }
.sort_by { |h| -h[:count] }
.first(5)
.each do |h|
puts " #{h[:count]} posts - #{h[:user].full_name}"
end
end
end
# Run if executed directly
if PROGRAM_NAME.includes?("monitoring")
BlogEngine.setup
Monitoring.print_stats
end# src/demo.cr
require "./blog_engine"
require "./seeders"
require "./monitoring"
puts "="*60
puts "CQL Blog Engine Demo"
puts "="*60
puts ""
# Setup
BlogEngine.setup
BlogEngine.migrate
# Seed data if database is empty
if User.count == 0
Seeders.run
end
# Show statistics
Monitoring.print_stats
# Demonstrate queries
puts ""
puts "Recent Published Posts:"
puts "-"*40
Post.where(published: true)
.order(created_at: :desc)
.limit(5)
.all
.each do |post|
author = post.user.try(&.full_name) || "Unknown"
comment_count = post.comments.count
puts ""
puts "#{post.title}"
puts " by #{author}"
puts " #{post.word_count} words | #{post.views_count} views | #{comment_count} comments"
puts " #{post.excerpt(100)}"
end
puts ""
puts "="*60
puts "Demo complete!"
puts "="*60crystal src/demo.cr# src/database.cr
adapter = if ENV["CRYSTAL_ENV"]? == "production"
CQL::Adapter::Postgres
else
CQL::Adapter::SQLite
end
uri = ENV["DATABASE_URL"]? || "sqlite3://./db/development.db"
BlogDB = CQL::Schema.define(:blog_db, adapter: adapter, uri: uri) do
end# Production
export CRYSTAL_ENV=production
export DATABASE_URL=postgres://user:pass@host:5432/blog_productioncrystal init app myapp
cd myappdependencies:
cql:
github: azutoolkit/cql
version: ~> 0.0.435
# Choose your database driver:
pg: # For PostgreSQL
github: will/crystal-pg
version: "~> 0.26.0"
# OR
mysql: # For MySQL
github: crystal-lang/crystal-mysql
version: "~> 0.14.0"
# OR
sqlite3: # For SQLite
github: crystal-lang/crystal-sqlite3
version: "~> 0.18.0"shards install# src/database.cr
require "cql"
require "pg"
DATABASE_URL = ENV["DATABASE_URL"]? || "postgres://username:password@localhost:5432/myapp_development"
AcmeDB = CQL::Schema.define(
:acme_db,
adapter: CQL::Adapter::Postgres,
uri: DATABASE_URL
) do
# Tables will be defined through migrations
end# src/database.cr
require "cql"
require "sqlite3"
AcmeDB = CQL::Schema.define(
:acme_db,
adapter: CQL::Adapter::SQLite,
uri: "sqlite3://./db/development.db"
) do
# Tables will be defined through migrations
end# src/database.cr
require "cql"
require "mysql"
AcmeDB = CQL::Schema.define(
:acme_db,
adapter: CQL::Adapter::MySql,
uri: "mysql://username:password@localhost:3306/myapp_development"
) do
# Tables will be defined through migrations
endmkdir -p migrations# migrations/001_create_users.cr
class CreateUsers < CQL::Migration(1)
def up
schema.table :users do
primary :id, Int64, auto_increment: true
column :name, String, null: false
column :email, String, null: false
column :active, Bool, default: false
timestamps # Creates created_at and updated_at columns
index [:email], unique: true
end
schema.users.create!
end
def down
schema.users.drop!
end
end# src/models/user.cr
struct User
include CQL::ActiveRecord::Model(Int64)
# Connect to the database and table
db_context AcmeDB, :users
# Define your model attributes
property id : Int64?
property name : String
property email : String
property active : Bool = false
property created_at : Time?
property updated_at : Time?
# Constructor for creating new instances
def initialize(@name : String, @email : String, @active : Bool = false)
end
end# src/setup.cr
require "./database"
require "./models/*"
require "../migrations/*"
# Configure automatic schema file synchronization
config = CQL::MigratorConfig.new(
schema_file_path: "src/schemas/acme_schema.cr",
schema_name: :AcmeSchema,
auto_sync: true
)
# Initialize the database
AcmeDB.init
# Create migrator and apply migrations
migrator = AcmeDB.migrator(config)
migrator.up
puts "Database initialized with #{migrator.applied_migrations.size} migrations"crystal src/setup.cr# src/myapp.cr
require "./database"
require "./models/*"
AcmeDB.init
# CREATE: Add new users
user = User.new(name: "Alice Johnson", email: "alice@example.com", active: true)
if user.save
puts "User created with ID: #{user.id}"
else
puts "Failed to create user"
end
# Or create and save in one step
bob = User.create!(
name: "Bob Smith",
email: "bob@example.com",
active: true
)
puts "Created user: #{bob.name}"
# READ: Find users
found_user = User.find(1)
if found_user
puts "Found user: #{found_user.name}"
end
# Find by attributes
alice = User.find_by(email: "alice@example.com")
puts "User: #{alice.try(&.name)}"
# Query with conditions
active_users = User.where(active: true).all
puts "Active users: #{active_users.size}"
# UPDATE: Modify users
if found_user
found_user.active = false
found_user.save
puts "User updated"
end
# DELETE: Remove users
inactive = User.where(active: false).first
inactive.try(&.delete!)
puts "Inactive user deleted"crystal src/myapp.cr# migrations/002_create_posts.cr
class CreatePosts < CQL::Migration(2)
def up
schema.table :posts do
primary :id, Int64, auto_increment: true
column :title, String, null: false
column :body, String, null: false
column :user_id, Int64, null: false
timestamps
foreign_key [:user_id], references: :users, references_columns: [:id], on_delete: :cascade
index [:user_id]
end
schema.posts.create!
end
def down
schema.posts.drop!
end
end# src/models/post.cr
struct Post
include CQL::ActiveRecord::Model(Int64)
db_context AcmeDB, :posts
property id : Int64?
property title : String
property body : String
property user_id : Int64
property created_at : Time?
property updated_at : Time?
# Associations
belongs_to :user, User, :user_id
def initialize(@title : String, @body : String, @user_id : Int64)
end
end# src/models/user.cr (updated)
struct User
include CQL::ActiveRecord::Model(Int64)
db_context AcmeDB, :users
property id : Int64?
property name : String
property email : String
property active : Bool = false
property created_at : Time?
property updated_at : Time?
# Associations
has_many :posts, Post, :user_id
def initialize(@name : String, @email : String, @active : Bool = false)
end
end# Create user and posts
user = User.create!(name: "Alice", email: "alice@example.com")
post1 = Post.create!(
title: "My First Post",
body: "This is my first blog post!",
user_id: user.id.not_nil!
)
post2 = Post.create!(
title: "Another Post",
body: "More content here",
user_id: user.id.not_nil!
)
# Access associated records
user_posts = user.posts.all
puts "#{user.name} has #{user_posts.size} posts"
# Access parent record
post = Post.find(1)
if post
author = post.user
puts "Post '#{post.title}' by #{author.try(&.name)}"
endstruct User
include CQL::ActiveRecord::Model(Int64)
db_context AcmeDB, :users
property id : Int64?
property name : String
property email : String
property active : Bool = false
property created_at : Time?
property updated_at : Time?
has_many :posts, Post, :user_id
# Add validations
validate :name, presence: true, size: (1..100)
validate :email, presence: true, match: /@/
def initialize(@name : String, @email : String, @active : Bool = false)
end
endUser.transaction do |tx|
user = User.create!(name: "Charlie", email: "charlie@example.com")
post = Post.create!(
title: "Welcome Post",
body: "Thanks for joining!",
user_id: user.id.not_nil!
)
# If any operation fails, everything is rolled back
puts "User and welcome post created successfully"
endmyapp/
├── shard.yml
├── src/
│ ├── myapp.cr # Main application
│ ├── database.cr # Database configuration
│ ├── setup.cr # Migration runner
│ ├── models/
│ │ ├── user.cr # User model
│ │ └── post.cr # Post model
│ └── schemas/
│ └── acme_schema.cr # Auto-generated schema
├── migrations/
│ ├── 001_create_users.cr
│ └── 002_create_posts.cr
└── db/
└── development.db # SQLite database file (if using SQLite)begin
AcmeDB.init
puts "Database connection successful"
rescue ex
puts "Database connection failed: #{ex.message}"
endmigrator = AcmeDB.migrator(config)
puts "Applied migrations: #{migrator.applied_migrations.size}"
puts "Pending migrations: #{migrator.pending_migrations.size}"
# Reset database (development only)
migrator.down_to(0) # Rollback all
migrator.up # Reapply all# src/models/user.cr
struct User
include CQL::ActiveRecord::Model(Int64)
db_context BlogDB, :users
property id : Int64?
property username : String
property email : String
property first_name : String?
property last_name : String?
property active : Bool = true
property created_at : Time?
property updated_at : Time?
# Relationships
has_many :posts, Post, :user_id
has_many :comments, Comment, :user_id
def initialize(
@username : String,
@email : String,
@first_name : String? = nil,
@last_name : String? = nil,
@active : Bool = true
)
end
# Custom methods
def full_name : String
if first_name && last_name
"#{first_name} #{last_name}"
elsif first_name
first_name.not_nil!
else
username
end
end
def display_name : String
first_name || username
end
end# src/models/category.cr
struct Category
include CQL::ActiveRecord::Model(Int64)
db_context BlogDB, :categories
property id : Int64?
property name : String
property slug : String
property created_at : Time?
property updated_at : Time?
# Relationships
has_many :posts, Post, :category_id
def initialize(@name : String, @slug : String? = nil)
@slug ||= generate_slug(@name)
end
# Generate URL-friendly slug from name
private def generate_slug(text : String) : String
text.downcase
.gsub(/[^a-z0-9\s-]/, "")
.gsub(/\s+/, "-")
.gsub(/-+/, "-")
.strip("-")
end
end# src/models/post.cr
struct Post
include CQL::ActiveRecord::Model(Int64)
db_context BlogDB, :posts
property id : Int64?
property title : String
property content : String
property published : Bool = false
property views_count : Int64 = 0_i64
property user_id : Int64
property category_id : Int64?
property created_at : Time?
property updated_at : Time?
# Relationships
belongs_to :user, User, :user_id
belongs_to :category, Category, :category_id
has_many :comments, Comment, :post_id
def initialize(
@title : String,
@content : String,
@user_id : Int64,
@category_id : Int64? = nil,
@published : Bool = false,
@views_count : Int64 = 0_i64
)
end
# Custom methods
def published? : Bool
published
end
def draft? : Bool
!published
end
def word_count : Int32
content.split.size
end
def reading_time_minutes : Int32
(word_count / 200.0).ceil.to_i # Assuming 200 words per minute
end
def excerpt(length : Int32 = 150) : String
if content.size <= length
content
else
content[0, length].rstrip + "..."
end
end
def increment_views!
@views_count += 1
save
end
end# src/models/comment.cr
struct Comment
include CQL::ActiveRecord::Model(Int64)
db_context BlogDB, :comments
property id : Int64?
property content : String
property post_id : Int64
property user_id : Int64? # Nullable for anonymous comments
property created_at : Time?
property updated_at : Time?
# Relationships
belongs_to :post, Post, :post_id
belongs_to :user, User, :user_id
def initialize(
@content : String,
@post_id : Int64,
@user_id : Int64? = nil
)
end
# Custom methods
def anonymous? : Bool
user_id.nil?
end
def author_name : String
if u = user
u.display_name
else
"Anonymous"
end
end
end# src/blog_engine.cr
require "./database"
require "./models/user"
require "./models/category"
require "./models/post"
require "./models/comment"
module BlogEngine
VERSION = "0.1.0"
def self.setup
BlogDB.init
puts "Database connected"
end
def self.migrate
require "../migrations/*"
migrator = BlogDB.migrator(MIGRATOR_CONFIG)
pending = migrator.pending_migrations.size
if pending > 0
puts "Running #{pending} pending migration(s)..."
migrator.up
puts "Migrations complete"
else
puts "Database is up to date"
end
end
end# src/test_models.cr
require "./blog_engine"
BlogEngine.setup
puts "Testing Models"
puts "=============="
# Create a user
alice = User.new(
username: "alice",
email: "alice@example.com",
first_name: "Alice",
last_name: "Johnson"
)
if alice.save
puts "Created user: #{alice.full_name} (ID: #{alice.id})"
else
puts "Failed to create user"
exit 1
end
# Create a category
tech = Category.new(name: "Technology")
if tech.save
puts "Created category: #{tech.name} (slug: #{tech.slug})"
end
# Create a post
post = Post.new(
title: "Getting Started with Crystal",
content: "Crystal is a wonderful programming language...",
user_id: alice.id.not_nil!,
category_id: tech.id,
published: true
)
if post.save
puts "Created post: #{post.title}"
puts " Word count: #{post.word_count}"
puts " Reading time: #{post.reading_time_minutes} min"
end
# Create a comment
comment = Comment.new(
content: "Great article!",
post_id: post.id.not_nil!,
user_id: alice.id
)
if comment.save
puts "Created comment by: #{comment.author_name}"
end
# Test relationships
puts ""
puts "Testing Relationships"
puts "====================="
# User -> Posts
puts "#{alice.full_name}'s posts:"
alice.posts.all.each do |p|
puts " - #{p.title}"
end
# Post -> User (author)
puts ""
puts "Post author: #{post.user.try(&.full_name)}"
# Post -> Category
puts "Post category: #{post.category.try(&.name)}"
# Post -> Comments
puts "Post comments: #{post.comments.all.size}"
# Comment -> User
puts "Comment author: #{comment.author_name}"
puts ""
puts "All tests passed!"crystal src/test_models.crbelongs_to :user, User, :user_idpost = Post.find(1)
author = post.user # Returns User?has_many :posts, Post, :user_iduser = User.find(1)
all_posts = user.posts.all # Get all posts
published = user.posts.where(published: true).all # Filter
count = user.posts.count # Count postsstruct User
include CQL::ActiveRecord::Model(Int64)
db_context MyDB, :users
property id : Int64?
property name : String
property email : String
property created_at : Time?
property updated_at : Time?
def initialize(@name : String, @email : String)
end
endCQL::ActiveRecord::Model follow this pattern.include CQL::ActiveRecord::Model(Int64)db_context MyDB, :usersproperty id : Int64? # Primary key (nullable for new records)
property name : String # Required field
property active : Bool = false # With default value
property bio : String? # Nullable field
property created_at : Time? # Timestamp
property updated_at : Time? # Timestampdef initialize(@name : String, @email : String)
endstruct Product
include CQL::ActiveRecord::Model(Int64)
db_context StoreDB, :products
property id : Int64?
# ...
endstruct Session
include CQL::ActiveRecord::Model(UUID)
db_context MyDB, :sessions
property id : UUID?
# ...
endstruct Event
include CQL::ActiveRecord::Model(String)
db_context EventDB, :events
property id : String?
# ...
endstruct User
include CQL::ActiveRecord::Model(Int64)
db_context MyDB, :users
property id : Int64?
property name : String
property password : String?
@[DB::Field(ignore: true)]
property password_confirmation : String?
def initialize(@name : String, @password : String? = nil, @password_confirmation : String? = nil)
end
endstruct User
include CQL::ActiveRecord::Model(Int64)
db_context MyDB, :users
property id : Int64?
property first_name : String
property last_name : String
property email : String
def initialize(@first_name : String, @last_name : String, @email : String)
end
def full_name : String
"#{first_name} #{last_name}"
end
def email_domain : String
email.split("@").last
end
end# Create an instance
user = User.new("John", "john@example.com")
# Check attributes
user.name # => "John"
user.id # => nil (not saved yet)
# Save to database
user.save
user.id # => 1 (now assigned)
# Find it again
found = User.find(user.id)New tables needed:
- tags (id, name, slug)
- post_tags (post_id, tag_id) - join table
New indexes:
- Unique index on tags.slug
- Composite index on post_tags# migrations/005_create_tags.cr
class CreateTags < CQL::Migration(5)
def up
schema.table :tags do
primary :id, Int64, auto_increment: true
column :name, String, null: false
column :slug, String, null: false
timestamps
index [:slug], unique: true
end
schema.tags.create!
end
def down
schema.tags.drop!
end
end# migrations/006_create_post_tags.cr
class CreatePostTags < CQL::Migration(6)
def up
schema.table :post_tags do
column :post_id, Int64, null: false
column :tag_id, Int64, null: false
column :created_at, Time
foreign_key [:post_id], references: :posts, references_columns: [:id], on_delete: :cascade
foreign_key [:tag_id], references: :tags, references_columns: [:id], on_delete: :cascade
index [:post_id]
index [:tag_id]
index [:post_id, :tag_id], unique: true
end
schema.post_tags.create!
end
def down
schema.post_tags.drop!
end
end# Run migrations
crystal src/migrate.cr up
# Verify
crystal src/migrate.cr statusstruct Tag
include CQL::ActiveRecord::Model(Int64)
db_context AppDB, :tags
property id : Int64?
property name : String
property slug : String
property created_at : Time?
property updated_at : Time?
has_many :post_tags, PostTag, :tag_id
def initialize(@name : String)
@slug = name.downcase.gsub(/[^a-z0-9]+/, "-").strip("-")
end
end
struct PostTag
include CQL::ActiveRecord::Model(Int64)
db_context AppDB, :post_tags
property post_id : Int64
property tag_id : Int64
property created_at : Time?
belongs_to :post, Post, :post_id
belongs_to :tag, Tag, :tag_id
def initialize(@post_id : Int64, @tag_id : Int64)
end
end# migrations/007_add_featured_to_posts.cr
class AddFeaturedToPosts < CQL::Migration(7)
def up
schema.alter :posts do
add_column :featured, Bool, default: false
add_column :featured_at, Time, null: true
end
schema.alter :posts do
create_index :idx_posts_featured, [:featured]
end
end
def down
schema.alter :posts do
drop_index :idx_posts_featured
drop_column :featured_at
drop_column :featured
end
end
end# migrations/008_normalize_user_emails.cr
class NormalizeUserEmails < CQL::Migration(8)
def up
# Add temporary column
schema.alter :users do
add_column :email_normalized, String, null: true
end
# Migrate data (done in batches for large tables)
schema.exec("UPDATE users SET email_normalized = LOWER(TRIM(email))")
# Verify
count = schema.exec("SELECT COUNT(*) FROM users WHERE email_normalized IS NULL").first
raise "Data migration incomplete" if count > 0
# Swap columns
schema.alter :users do
drop_column :email
end
schema.exec("ALTER TABLE users RENAME COLUMN email_normalized TO email")
# Recreate index
schema.alter :users do
create_index :idx_users_email, [:email], unique: true
end
end
def down
# This migration cannot be easily reversed
raise "Cannot rollback email normalization"
end
enddef up
batch_size = 1000
offset = 0
loop do
result = schema.exec(<<-SQL
UPDATE users
SET email_normalized = LOWER(TRIM(email))
WHERE id IN (
SELECT id FROM users
WHERE email_normalized IS NULL
LIMIT #{batch_size}
)
SQL
)
break if result.rows_affected == 0
offset += batch_size
# Optional: Add a small delay to reduce database load
sleep 0.1
end
end# Rollback last migration
crystal src/migrate.cr down
# Rollback to specific version
crystal src/migrate.cr down_to 5def down
raise "Cannot rollback: #{self.class.name} - data would be lost"
end# scripts/deploy_migrations.cr
require "../src/cql_config"
require "../migrations/*"
puts "Starting migration deployment..."
puts "================================"
AppDB.init
migrator = AppDB.migrator(CQL_MIGRATOR_CONFIG)
pending = migrator.pending_migrations.size
if pending == 0
puts "No pending migrations"
exit 0
end
puts "Pending migrations: #{pending}"
puts ""
# Run with transaction per migration
migrator.pending_migrations.each do |version|
puts "Running migration #{version}..."
begin
migrator.up_to(version)
puts " Success!"
rescue ex
puts " FAILED: #{ex.message}"
puts ""
puts "Migration failed. Database may be in inconsistent state."
puts "Please investigate and fix manually."
exit 1
end
end
puts ""
puts "All migrations completed successfully!"schema.table :posts do
primary :id, Int64, auto_increment: true
column :title, String
timestamps # Adds created_at and updated_at columns
end
schema.posts.create!struct Post
include CQL::ActiveRecord::Model(Int64)
db_context MyDB, :posts
property id : Int64?
property title : String
property created_at : Time?
property updated_at : Time?
def initialize(@title : String)
end
endpost = Post.new("My Post")
post.save!
post.created_at # => 2024-01-15 10:30:00 UTC
post.updated_at # => 2024-01-15 10:30:00 UTC
# Later update
post.title = "Updated Title"
post.save!
post.created_at # => 2024-01-15 10:30:00 UTC (unchanged)
post.updated_at # => 2024-01-15 11:45:00 UTC (updated)struct Post
include CQL::ActiveRecord::Model(Int64)
db_context MyDB, :posts
property id : Int64?
property title : String
property created_at : Time?
property updated_at : Time?
before_create :set_created_at
before_save :set_updated_at
def initialize(@title : String)
end
private def set_created_at
@created_at = Time.utc
true
end
private def set_updated_at
@updated_at = Time.utc
true
end
end# Records created today
Post.where { created_at > Time.utc.at_beginning_of_day }.all
# Records updated in last hour
Post.where { updated_at > 1.hour.ago }.all
# Records created between dates
Post.where { created_at.between(start_date, end_date) }.all
# Order by most recent
Post.order(created_at: :desc).allclass AddTimestampsToPosts < CQL::Migration(20240115)
def up
schema.alter :posts do
add_column :created_at, Time, null: true
add_column :updated_at, Time, null: true
end
# Optionally set existing records to current time
schema.exec("UPDATE posts SET created_at = NOW(), updated_at = NOW() WHERE created_at IS NULL")
end
def down
schema.alter :posts do
drop_column :created_at
drop_column :updated_at
end
end
endstruct Post
# ... properties ...
def created_today? : Bool
created_at.try(&.to_date) == Time.utc.to_date
end
def recently_updated? : Bool
updated_at.try { |t| t > 1.hour.ago } || false
end
def age : Time::Span?
created_at.try { |t| Time.utc - t }
end
def formatted_created_at : String
created_at.try(&.to_s("%B %d, %Y")) || "Unknown"
end
endpost = Post.create!(title: "Test")
post.created_at.nil? # => false
post.updated_at.nil? # => false
sleep 1
post.title = "Updated"
post.save!
post.created_at < post.updated_at # => trueschema.table :users do
primary :id, Int64, auto_increment: true
column :name, String
lock_version :version # Adds integer column with default 1
timestamps
end
schema.users.create!class AddVersionToUsers < CQL::Migration(20240115)
def up
schema.alter :users do
add_column :version, Int32, default: 1, null: false
end
end
def down
schema.alter :users do
drop_column :version
end
end
endstruct User
include CQL::ActiveRecord::Model(Int64)
include CQL::ActiveRecord::OptimisticLocking
db_context MyDB, :users
property id : Int64?
property name : String
property version : Int32?
optimistic_locking version_column: :version
def initialize(@name : String)
end
enduser = User.find!(1)
user.name = "Updated Name"
begin
user.update!
puts "Updated! New version: #{user.version}"
rescue CQL::OptimisticLockError
puts "Someone else updated this record!"
enduser = User.find!(1)
user.name = "My Changes"
begin
user.update!
rescue CQL::OptimisticLockError
user.reload! # Get latest data from database
user.name = "My Changes" # Re-apply changes
user.update! # Try again
enddef update_with_retry(user, max_retries = 3)
attempts = 0
loop do
attempts += 1
user.name = "Updated Name"
begin
user.update!
return true
rescue CQL::OptimisticLockError
if attempts >= max_retries
puts "Max retries reached"
return false
end
puts "Conflict, retrying (#{attempts}/#{max_retries})..."
user.reload!
end
end
endstruct User
include CQL::ActiveRecord::Model(Int64)
include CQL::ActiveRecord::SoftDeletable
include CQL::ActiveRecord::OptimisticLocking
db_context MyDB, :users
property id : Int64?
property name : String
property version : Int32?
optimistic_locking version_column: :version
def initialize(@name : String)
end
end# Load same record twice (simulating two users)
user1 = User.find!(1)
user2 = User.find!(1)
# First update succeeds
user1.name = "User 1's change"
user1.update! # Works, version now 2
# Second update fails
user2.name = "User 2's change"
begin
user2.update! # Raises OptimisticLockError
rescue CQL::OptimisticLockError
puts "Conflict detected!"
end# src/database.cr
require "log"
# Configure logging
Log.setup do |c|
backend = Log::IOBackend.new
c.bind("cql.*", :debug, backend)
end
AppDB = CQL::Schema.define(
:app_db,
adapter: CQL::Adapter::Postgres,
uri: DATABASE_URL
) do
end# src/monitoring/query_monitor.cr
module QueryMonitor
@@queries = [] of QueryRecord
@@enabled = true
struct QueryRecord
property sql : String
property duration_ms : Float64
property timestamp : Time
property source : String
def initialize(@sql, @duration_ms, @source = "")
@timestamp = Time.utc
end
def slow?(threshold_ms = 100.0)
duration_ms > threshold_ms
end
end
def self.enable
@@enabled = true
end
def self.disable
@@enabled = false
end
def self.record(sql : String, duration_ms : Float64, source = "")
return unless @@enabled
@@queries << QueryRecord.new(sql, duration_ms, source)
end
def self.queries
@@queries
end
def self.clear
@@queries.clear
end
def self.slow_queries(threshold_ms = 100.0)
@@queries.select(&.slow?(threshold_ms))
end
def self.total_time_ms
@@queries.sum(&.duration_ms)
end
def self.report
puts "Query Performance Report"
puts "========================"
puts "Total queries: #{@@queries.size}"
puts "Total time: #{total_time_ms.round(2)}ms"
puts ""
if (slow = slow_queries).any?
puts "Slow queries (>100ms):"
slow.each do |q|
puts " #{q.duration_ms.round(2)}ms - #{q.sql[0, 80]}..."
end
puts ""
end
# Group by similar queries (potential N+1)
grouped = @@queries.group_by { |q| normalize_query(q.sql) }
repeated = grouped.select { |_, v| v.size > 1 }
if repeated.any?
puts "Repeated queries (potential N+1):"
repeated.each do |pattern, instances|
puts " #{instances.size}x - #{pattern[0, 60]}..."
end
end
end
private def self.normalize_query(sql : String)
# Replace specific values with placeholders
sql.gsub(/= \d+/, "= ?")
.gsub(/= '[^']*'/, "= ?")
.gsub(/IN \([^)]+\)/, "IN (?)")
end
end# src/monitoring/timed_queries.cr
module TimedQueries
def self.measure(source = "")
start = Time.monotonic
result = yield
duration = (Time.monotonic - start).total_milliseconds
QueryMonitor.record("query", duration, source)
result
end
end
# Usage in your code:
posts = TimedQueries.measure("load_posts") do
Post.where(published: true).all
end# BAD: N+1 problem
posts = Post.where(published: true).all
posts.each do |post|
author = post.user # This triggers a query for EACH post!
puts "#{post.title} by #{author.try(&.name)}"
end
# The monitor would show something like:
# 1x SELECT * FROM posts WHERE published = true
# 10x SELECT * FROM users WHERE id = ?# GOOD: Single query with join
posts = Post.where(published: true).all
user_ids = posts.map(&.user_id).uniq
users_by_id = User.where { id.in(user_ids) }.all.index_by(&.id)
posts.each do |post|
author = users_by_id[post.user_id]?
puts "#{post.title} by #{author.try(&.name)}"
end
# The monitor shows:
# 1x SELECT * FROM posts WHERE published = true
# 1x SELECT * FROM users WHERE id IN (?, ?, ...)# src/monitoring/dashboard.cr
require "./query_monitor"
module PerformanceDashboard
def self.display
puts ""
puts "="*60
puts "CQL Performance Dashboard"
puts "="*60
puts ""
display_query_stats
display_slow_queries
display_n_plus_one_warnings
display_recommendations
end
private def self.display_query_stats
queries = QueryMonitor.queries
return puts "No queries recorded" if queries.empty?
puts "Query Statistics"
puts "-"*40
total = queries.size
total_time = QueryMonitor.total_time_ms
puts "Total queries: #{total}"
puts "Total time: #{total_time.round(2)}ms"
puts "Average time: #{(total_time / total).round(2)}ms"
puts ""
end
private def self.display_slow_queries
slow = QueryMonitor.slow_queries(50.0)
return if slow.empty?
puts "Slow Queries (>50ms)"
puts "-"*40
slow.sort_by(&.duration_ms).reverse.first(5).each do |q|
puts "#{q.duration_ms.round(2)}ms"
puts " #{q.sql[0, 70]}..."
puts ""
end
end
private def self.display_n_plus_one_warnings
grouped = QueryMonitor.queries.group_by { |q|
q.sql.gsub(/= \d+/, "= ?").gsub(/= '[^']*'/, "= ?")
}
warnings = grouped.select { |_, v| v.size > 3 }
return if warnings.empty?
puts "Potential N+1 Queries"
puts "-"*40
warnings.each do |pattern, instances|
puts "Executed #{instances.size} times:"
puts " #{pattern[0, 60]}..."
puts ""
end
end
private def self.display_recommendations
queries = QueryMonitor.queries
return if queries.empty?
puts "Recommendations"
puts "-"*40
# Check for too many queries
if queries.size > 50
puts "- High query count (#{queries.size}). Consider:"
puts " * Caching frequently accessed data"
puts " * Combining queries with joins"
puts ""
end
# Check for slow average
avg = QueryMonitor.total_time_ms / queries.size
if avg > 50
puts "- Slow average query time (#{avg.round(2)}ms). Consider:"
puts " * Adding indexes to filtered columns"
puts " * Optimizing complex queries"
puts ""
end
# Check for N+1
grouped = queries.group_by { |q| q.sql.gsub(/= \d+/, "= ?") }
repeated = grouped.count { |_, v| v.size > 3 }
if repeated > 0
puts "- Detected #{repeated} potential N+1 patterns. Consider:"
puts " * Using eager loading"
puts " * Batching related queries"
puts ""
end
if queries.size <= 50 && avg <= 50 && repeated == 0
puts "Performance looks good!"
end
end
end# spec/performance_spec.cr
describe "Performance" do
before_each do
QueryMonitor.clear
end
it "loads post list efficiently" do
# Create test data
10.times { create_post }
# Load posts
QueryMonitor.enable
posts = Post.where(published: true).all
posts.each { |p| p.user }
# Assert query count
QueryMonitor.queries.size.should be <= 2
# Assert no slow queries
QueryMonitor.slow_queries(100.0).should be_empty
end
end# src/monitoring/metrics.cr
module Metrics
def self.record_query(duration_ms : Float64, query_type : String)
# Send to your metrics system (StatsD, Prometheus, etc.)
# Example with a hypothetical metrics client:
# MetricsClient.timing("cql.query.duration", duration_ms, tags: ["type:#{query_type}"])
end
def self.increment_query_count(query_type : String)
# MetricsClient.increment("cql.query.count", tags: ["type:#{query_type}"])
end
def self.record_slow_query(sql : String, duration_ms : Float64)
# Log slow queries for investigation
Log.warn { "Slow query (#{duration_ms.round(2)}ms): #{sql}" }
end
end# Check if filtering on a column without an index
Post.where(status: "published").all # Is status indexed?# Instead of loading entire records
titles = Post.where(published: true).select(:id, :title).all# Instead of loading all records
Post.find_each(batch_size: 100) do |post|
process(post)
end# Cache results of expensive aggregations
def total_views
@total_views ||= Post.where(published: true).sum(:views_count)
endschema.table :users do
primary :id, Int64, auto_increment: true
column :name, String
timestamps
end
schema.users.create!
schema.table :posts do
primary :id, Int64, auto_increment: true
column :user_id, Int64, null: false
column :title, String
column :body, String
timestamps
foreign_key [:user_id], references: :users, references_columns: [:id]
index [:user_id]
end
schema.posts.create!struct User
include CQL::ActiveRecord::Model(Int64)
db_context MyDB, :users
property id : Int64?
property name : String
has_many :posts, Post, :user_id
def initialize(@name : String)
end
end
struct Post
include CQL::ActiveRecord::Model(Int64)
db_context MyDB, :posts
property id : Int64?
property user_id : Int64
property title : String
property body : String
belongs_to :user, User, :user_id
def initialize(@title : String, @body : String, @user_id : Int64)
end
enduser = User.find(1)
posts = user.posts.all
posts.each do |post|
puts post.title
enduser = User.find(1)
count = user.posts.count
puts "User has #{count} posts"user = User.find(1)
# Published posts only
published = user.posts.where(published: true).all
# Most recent posts
recent = user.posts.order(created_at: :desc).limit(5).alluser = User.create!(name: "John")
# Create posts for user
post1 = Post.create!(
title: "First Post",
body: "Hello world",
user_id: user.id.not_nil!
)
post2 = Post.create!(
title: "Second Post",
body: "Another post",
user_id: user.id.not_nil!
)
user.posts.count # => 2user = User.find(1)
user.posts.where(published: false).each(&.delete!)user = User.find(1)
user.posts.all.each(&.delete!)schema.table :posts do
# ...
foreign_key [:user_id], references: :users, references_columns: [:id], on_delete: :cascade
enduser = User.find(1)
if user.posts.count > 0
puts "User has posts"
else
puts "User has no posts"
enduser = User.create!(name: "Test User")
Post.create!(title: "Post 1", body: "Body 1", user_id: user.id.not_nil!)
Post.create!(title: "Post 2", body: "Body 2", user_id: user.id.not_nil!)
user.posts.count # => 2
user.posts.all.map(&.title) # => ["Post 1", "Post 2"]schema.table :posts do
primary :id, Int64, auto_increment: true
column :title, String
timestamps
end
schema.posts.create!
schema.table :comments do
primary :id, Int64, auto_increment: true
column :body, String
column :post_id, Int64, null: false
timestamps
foreign_key [:post_id], references: :posts, references_columns: [:id]
end
schema.comments.create!struct Comment
include CQL::ActiveRecord::Model(Int64)
db_context MyDB, :comments
property id : Int64?
property body : String
property post_id : Int64
belongs_to :post, Post, :post_id
def initialize(@body : String, @post_id : Int64)
end
endcomment = Comment.find(1)
post = comment.post # Returns Post?
if post
puts "Comment on: #{post.title}"
endpost = Post.create!(title: "My Post")
comment = Comment.new("Great post!", post.id.not_nil!)
comment.save!post = Post.create!(title: "My Post")
comment = Comment.new("Great post!", 0_i64)
comment.post = post # Sets post_id automatically
comment.save!comment = Comment.new("My comment", 0_i64)
post = comment.build_post(title: "New Post")
# Save parent, then child
post.save!
comment.post = post
comment.save!comment = Comment.new("My comment", 0_i64)
post = comment.create_post(title: "New Post")
comment.save! # post_id is now setschema.table :comments do
# ...
column :user_id, Int64, null: true # Optional
end
struct Comment
# ...
property user_id : Int64?
belongs_to :user, User, :user_id
def initialize(@body : String, @post_id : Int64, @user_id : Int64? = nil)
end
end
comment = Comment.find(1)
if user = comment.user
puts "By: #{user.name}"
else
puts "Anonymous"
endpost = Post.create!(title: "Test Post")
comment = Comment.create!(body: "Test Comment", post_id: post.id.not_nil!)
comment.post.try(&.title) # => "Test Post"
comment.post_id == post.id # => truedef fetch_posts(after : Int64?, limit : Int32 = 20)
query = Post.published.order(id: :desc).limit(limit + 1)
if after
query = query.where { id < after }
end
items = query.all
# Check if there are more items
has_more = items.size > limit
items = items.first(limit) if has_more
{
items: items,
next_cursor: has_more ? items.last?.try(&.id) : nil,
has_more: has_more
}
end# First page
result = fetch_posts(nil)
result[:items].each { |post| puts post.title }
# Next page
if cursor = result[:next_cursor]
result = fetch_posts(cursor)
enddef fetch_posts(after : Int64? = nil, before : Int64? = nil, limit : Int32 = 20)
query = Post.published.limit(limit + 1)
if after
items = query.where { id < after }.order(id: :desc).all
{
items: items.first(limit),
next_cursor: items.size > limit ? items[limit - 1].id : nil,
prev_cursor: after,
has_more: items.size > limit
}
elsif before
items = query.where { id > before }.order(id: :asc).all
items = items.reverse # Restore descending order
{
items: items.first(limit),
next_cursor: items.last?.try(&.id),
prev_cursor: items.size > limit ? items.first.id : nil,
has_more: items.size > limit
}
else
items = query.order(id: :desc).all
{
items: items.first(limit),
next_cursor: items.size > limit ? items[limit - 1].id : nil,
prev_cursor: nil,
has_more: items.size > limit
}
end
enddef fetch_activity(before : Time? = nil, limit : Int32 = 20)
query = Activity.order(created_at: :desc).limit(limit + 1)
if before
query = query.where { created_at < before }
end
items = query.all
has_more = items.size > limit
items = items.first(limit) if has_more
{
items: items,
next_cursor: has_more ? items.last?.try(&.created_at) : nil,
has_more: has_more
}
end# Cursor contains both the sort value and ID for uniqueness
struct Cursor
property views : Int64
property id : Int64
def self.parse(s : String) : self?
parts = s.split(":")
return nil unless parts.size == 2
new(parts[0].to_i64, parts[1].to_i64)
rescue
nil
end
def to_s : String
"#{@views}:#{@id}"
end
def initialize(@views, @id)
end
end
def fetch_popular_posts(after : String? = nil, limit : Int32 = 20)
query = Post.published.order(views_count: :desc, id: :desc).limit(limit + 1)
if after && (cursor = Cursor.parse(after))
query = query.where {
(views_count < cursor.views) |
((views_count == cursor.views) & (id < cursor.id))
}
end
items = query.all
has_more = items.size > limit
items = items.first(limit) if has_more
next_cursor = if has_more && (last = items.last?)
Cursor.new(last.views_count, last.id.not_nil!).to_s
end
{items: items, next_cursor: next_cursor, has_more: has_more}
enddef posts_api(params)
result = fetch_posts(params["after"]?.try(&.to_i64))
{
data: result[:items].map(&.to_json),
pagination: {
next_cursor: result[:next_cursor],
has_more: result[:has_more]
}
}
endrequire "base64"
def encode_cursor(id : Int64) : String
Base64.strict_encode("cursor:#{id}")
end
def decode_cursor(cursor : String) : Int64?
decoded = Base64.decode_string(cursor)
if decoded.starts_with?("cursor:")
decoded[7..].to_i64
end
rescue
nil
end# Create test data
10.times do |i|
Post.create!(title: "Post #{i + 1}", body: "...", published: true)
end
# Fetch pages
page1 = fetch_posts(nil, limit: 3)
page1[:items].size # => 3
page1[:has_more] # => true
page2 = fetch_posts(page1[:next_cursor], limit: 3)
page2[:items].size # => 3
page2[:has_more] # => true
# Items don't overlap
page1_ids = page1[:items].map(&.id)
page2_ids = page2[:items].map(&.id)
(page1_ids & page2_ids).empty? # => truestruct User
include CQL::ActiveRecord::Model(Int64)
db_context MyDB, :users
property id : Int64?
property name : String
property email : String
before_save :normalize_email
after_create :send_welcome_email
def initialize(@name : String, @email : String)
end
private def normalize_email
@email = @email.downcase.strip
true # Return true to continue
end
private def send_welcome_email
puts "Sending welcome email to #{@email}"
true
end
endstruct Article
include CQL::ActiveRecord::Model(Int64)
db_context MyDB, :articles
property id : Int64?
property title : String
property published : Bool = false
before_save :check_can_publish
def initialize(@title : String, @published : Bool = false)
end
private def check_can_publish
if @published && @title.empty?
errors.add(:title, "cannot be blank when publishing")
return false # Halt - save won't proceed
end
true
end
end
# This save will fail
article = Article.new(title: "", published: true)
article.save # => false
article.errors.first.message # => "cannot be blank when publishing"before_validation :normalize_data
private def normalize_data
@email = @email.downcase.strip
@name = @name.strip
true
endbefore_create :set_created_at
before_save :set_updated_at
private def set_created_at
@created_at = Time.utc
true
end
private def set_updated_at
@updated_at = Time.utc
true
endbefore_save :generate_slug
private def generate_slug
@slug = @title.downcase.gsub(/[^a-z0-9]+/, "-").strip("-")
true
endafter_create :notify_admin
private def notify_admin
# Send notification (email, webhook, etc.)
puts "New user registered: #{@email}"
true
endbefore_destroy :cleanup_files
private def cleanup_files
# Delete associated files
File.delete(@avatar_path) if @avatar_path && File.exists?(@avatar_path)
true
endstruct User
include CQL::ActiveRecord::Model(Int64)
db_context MyDB, :users
property id : Int64?
property name : String
property email : String
property status : String = "pending"
before_save :normalize_data
before_save :check_permissions
before_save :set_timestamps
def initialize(@name : String, @email : String)
end
private def normalize_data
@email = @email.downcase
true
end
private def check_permissions
if @status == "banned"
errors.add(:status, "banned users cannot be saved")
return false # Halts - set_timestamps won't run
end
true
end
private def set_timestamps
@updated_at = Time.utc
true
end
enduser = User.new("John", " JOHN@EXAMPLE.COM ")
user.save
user.email # => "john@example.com" (normalized by callback)struct User
include CQL::ActiveRecord::Model(Int64)
db_context MyDB, :users
property id : Int64?
property name : String
property email : String
property age : Int32 = 0
validate :name, presence: true
validate :email, required: true, match: /@/
validate :age, gt: 0, lt: 120
def initialize(@name : String, @email : String, @age : Int32 = 0)
end
endvalidate :name, presence: true # Not nil AND not empty
validate :user_id, required: true # Not nil (can be empty)validate :username, size: 3..20 # Between 3 and 20 characters
validate :zip_code, size: 5 # Exactly 5 charactersvalidate :age, gt: 0 # Greater than 0
validate :age, gte: 18 # Greater than or equal to 18
validate :score, lt: 100 # Less than 100
validate :score, lte: 100 # Less than or equal to 100
validate :priority, eq: 1 # Exactly 1validate :email, match: /\A[\w+\-.]+@[a-z\d\-.]+\.[a-z]+\z/i
validate :phone, match: /\A\d{3}-\d{3}-\d{4}\z/validate :status, in: ["active", "inactive", "pending"]
validate :rating, in: 1..5
validate :username, exclude: ["admin", "root", "system"]validate :name, presence: true, message: "Name cannot be blank"
validate :email, match: /@/, message: "Please enter a valid email address"
validate :age, gt: 0, message: "Age must be a positive number"user = User.new("", "invalid-email", -5)
if user.valid?
puts "User is valid"
else
puts "User has errors"
enduser = User.new("", "invalid-email", -5)
unless user.valid?
user.errors.each do |error|
puts "#{error.field}: #{error.message}"
end
enduser = User.new("", "invalid", -5)
begin
user.validate!
rescue ex
puts "Validation failed: #{ex.message}"
endclass PasswordValidator < CQL::ActiveRecord::Validations::CustomValidator
def valid? : Array(CQL::ActiveRecord::Validations::Error)
errors = [] of CQL::ActiveRecord::Validations::Error
password = @record.password
unless password && password.size >= 8
errors << CQL::ActiveRecord::Validations::Error.new(:password, "must be at least 8 characters")
end
unless password && password.matches?(/[A-Z]/)
errors << CQL::ActiveRecord::Validations::Error.new(:password, "must contain an uppercase letter")
end
unless password && password.matches?(/\d/)
errors << CQL::ActiveRecord::Validations::Error.new(:password, "must contain a number")
end
errors
end
end
struct User
include CQL::ActiveRecord::Model(Int64)
db_context MyDB, :users
property id : Int64?
property name : String
property password : String?
use PasswordValidator
def initialize(@name : String, @password : String? = nil)
end
endstruct User
include CQL::ActiveRecord::Model(Int64)
db_context MyDB, :users
property id : Int64?
property email : String
validate :email, presence: true
def validate
super
validate_email_uniqueness
end
private def validate_email_uniqueness
existing = User.find_by(email: @email)
if existing && existing.id != @id
errors.add(:email, "has already been taken")
end
end
def initialize(@email : String)
end
enduser = User.new("", "invalid")
# save returns false if invalid
if user.save
puts "Saved!"
else
puts "Failed: #{user.errors.map(&.message).join(", ")}"
end
# save! raises if invalid
begin
user.save!
rescue ex
puts "Error: #{ex.message}"
end# Test valid data
user = User.new("John Doe", "john@example.com", 30)
user.valid? # => true
# Test invalid data
bad_user = User.new("", "invalid", -5)
bad_user.valid? # => false
bad_user.errors.size # => 3schema.table :users do
primary :id, Int64, auto_increment: true
column :name, String
timestamps
end
schema.users.create!
schema.table :profiles do
primary :id, Int64, auto_increment: true
column :user_id, Int64, null: false
column :bio, String
column :avatar_url, String
foreign_key [:user_id], references: :users, references_columns: [:id]
index [:user_id], unique: true
timestamps
end
schema.profiles.create!struct User
include CQL::ActiveRecord::Model(Int64)
db_context MyDB, :users
property id : Int64?
property name : String
has_one :profile, Profile, :user_id
def initialize(@name : String)
end
end
struct Profile
include CQL::ActiveRecord::Model(Int64)
db_context MyDB, :profiles
property id : Int64?
property user_id : Int64
property bio : String?
property avatar_url : String?
belongs_to :user, User, :user_id
def initialize(@user_id : Int64, @bio : String? = nil, @avatar_url : String? = nil)
end
enduser = User.find(1)
profile = user.profile # Returns Profile?
if profile
puts "Bio: #{profile.bio}"
else
puts "No profile"
enduser = User.create!(name: "John")
profile = Profile.create!(
user_id: user.id.not_nil!,
bio: "Hello world",
avatar_url: "/avatars/john.jpg"
)
user.profile.try(&.bio) # => "Hello world"user = User.find(1)
profile = Profile.new(user.id.not_nil!, "My bio")
profile.save!user = User.find(1)
if profile = user.profile
profile.bio = "Updated bio"
profile.save!
enduser = User.find(1)
user.profile.try(&.delete!)user = User.create!(name: "Test User")
profile = Profile.create!(
user_id: user.id.not_nil!,
bio: "Test bio"
)
user.profile.try(&.bio) # => "Test bio"
profile.user.try(&.name) # => "Test User"struct Post
include CQL::ActiveRecord::Model(Int64)
db_context MyDB, :posts
property id : Int64?
property title : String
property published : Bool = false
property views_count : Int64 = 0
property created_at : Time?
# Scopes
def self.published
where(published: true)
end
def self.draft
where(published: false)
end
def self.popular(min_views = 100)
where { views_count >= min_views }
end
def self.recent(days = 7)
where { created_at > days.days.ago }
end
end# Single scope
Post.published.all
# Chain scopes
Post.published.popular.recent.all
# With other methods
Post.published.order(created_at: :desc).limit(10).allstruct User
def self.by_role(role : String)
where(role: role)
end
def self.created_after(date : Time)
where { created_at > date }
end
def self.age_between(min : Int32, max : Int32)
where { age.between(min, max) }
end
end
# Usage
User.by_role("admin").all
User.created_after(1.month.ago).all
User.age_between(18, 65).allstruct Post
def self.published_if(condition : Bool)
condition ? published : self
end
end
# Usage - only filter if condition is true
show_published = params["published"]? == "true"
Post.published_if(show_published).allstruct Post
def self.active
where(deleted_at: nil).where(published: true)
end
end
# Always start queries from active scope
Post.active.order(created_at: :desc).all# All these work
Post.published.popular.recent.all
Post.recent.published.popular.all
Post.popular.published.allstruct Post
def self.newest_first
order(created_at: :desc)
end
def self.most_popular
order(views_count: :desc)
end
end
Post.published.newest_first.limit(10).all
Post.published.most_popular.limit(10).allstruct Post
def self.featured
published
.where { views_count > 1000 }
.order(views_count: :desc)
end
def self.by_author(user : User)
where(user_id: user.id)
end
def self.in_category(category : Category)
where(category_id: category.id)
end
end
# Usage
Post.featured.limit(5).all
Post.by_author(current_user).published.all
Post.in_category(tech_category).recent.alluser = User.find!(1)
# Use scopes on associated records
user.posts.published.all
user.posts.recent(30).popular(50).allPost.create!(title: "Draft", body: "...", published: false, views_count: 10)
Post.create!(title: "Published", body: "...", published: true, views_count: 200)
Post.create!(title: "Popular", body: "...", published: true, views_count: 500)
Post.published.count # => 2
Post.draft.count # => 1
Post.popular(100).count # => 2
Post.popular(300).count # => 1page = 1
per_page = 10
posts = Post.order(created_at: :desc)
.limit(per_page)
.offset((page - 1) * per_page)
.alldef paginate(query, page : Int32 = 1, per_page : Int32 = 10)
offset = (page - 1) * per_page
query.limit(per_page).offset(offset).all
end
# Usage
posts = paginate(Post.where(published: true), page: 2, per_page: 20)def paginated_results(query, page : Int32 = 1, per_page : Int32 = 10)
total = query.count
total_pages = (total / per_page.to_f).ceil.to_i
offset = (page - 1) * per_page
{
items: query.limit(per_page).offset(offset).all,
page: page,
per_page: per_page,
total: total,
total_pages: total_pages,
has_next: page < total_pages,
has_prev: page > 1
}
end
# Usage
result = paginated_results(Post.published, page: 2)
puts "Page #{result[:page]} of #{result[:total_pages]}"
puts "Showing #{result[:items].size} of #{result[:total]} items"def cursor_paginate(last_id : Int64?, limit : Int32 = 10)
query = Post.order(id: :desc).limit(limit)
if last_id
query = query.where { id < last_id }
end
items = query.all
next_cursor = items.last?.try(&.id)
{items: items, next_cursor: next_cursor}
end
# First page
result = cursor_paginate(nil)
# Next page
result = cursor_paginate(result[:next_cursor])def paginate_by_time(before : Time?, limit : Int32 = 10)
query = Post.order(created_at: :desc).limit(limit)
if before
query = query.where { created_at < before }
end
items = query.all
next_cursor = items.last?.try(&.created_at)
{items: items, next_cursor: next_cursor}
enddef keyset_paginate(after_id : Int64?, limit : Int32 = 10)
query = Post.where(published: true)
.order(views_count: :desc, id: :desc)
.limit(limit)
if after_id
# Get the reference record
ref = Post.find(after_id)
if ref
query = query.where {
(views_count < ref.views_count) |
((views_count == ref.views_count) & (id < after_id))
}
end
end
query.all
enddef list_posts(params)
page = (params["page"]? || "1").to_i
per_page = [(params["per_page"]? || "10").to_i, 100].min # Max 100
query = Post.published.order(created_at: :desc)
result = paginated_results(query, page, per_page)
{
posts: result[:items].map(&.to_json),
meta: {
page: result[:page],
per_page: result[:per_page],
total: result[:total],
total_pages: result[:total_pages]
}
}
end# Create 25 posts
25.times do |i|
Post.create!(title: "Post #{i + 1}", body: "...", published: true)
end
# Test pagination
page1 = Post.order(id: :asc).limit(10).offset(0).all
page2 = Post.order(id: :asc).limit(10).offset(10).all
page3 = Post.order(id: :asc).limit(10).offset(20).all
page1.size # => 10
page2.size # => 10
page3.size # => 5
page1.first.title # => "Post 1"
page2.first.title # => "Post 11"
page3.first.title # => "Post 21"user = User.find(1)
user.try(&.delete!)
puts "User deleted"user = User.find!(1)
user.delete!user = User.find!(1)
user.destroy! # Runs before_destroy and after_destroy callbacksUser.delete!(1)# Delete all inactive users
User.where(active: false).delete!
# Delete posts older than a year
Post.where { created_at < 1.year.ago }.delete!# Delete all comments
Comment.delete_all
# Delete all with condition
User.where(role: "guest").delete_alluser = User.find!(1)
user.delete! # Sets deleted_at timestamp
user.deleted? # => true
user.restore! # Restores the record
user.deleted? # => false
user.force_delete! # Permanently removes from database# If posts have: on_delete: :cascade
user = User.find!(1)
user.delete! # Also deletes all user's postsuser = User.find!(1)
user.posts.all.each(&.delete!)
user.delete!User.transaction do
user = User.find!(1)
user.posts.all.each(&.delete!)
user.comments.all.each(&.delete!)
user.delete!
enduser = User.find!(1)
if user.posts.count > 0
puts "Cannot delete user with posts"
else
user.delete!
enduser = User.find!(1)
user.delete!
User.find(1) # => nilUser.transaction do
user = User.create!(name: "John", email: "john@example.com")
Profile.create!(user_id: user.id.not_nil!, bio: "Hello")
end
# Both records created, or neitherUser.transaction do
user = User.create!(name: "John", email: "john@example.com")
raise "Something went wrong!" # Triggers rollback
Profile.create!(user_id: user.id.not_nil!, bio: "Hello")
end
# Neither record is createdbegin
User.transaction do
user = User.create!(name: "John", email: "john@example.com")
# ... more operations
end
puts "Transaction successful"
rescue ex
puts "Transaction failed: #{ex.message}"
enddef transfer(from_id : Int64, to_id : Int64, amount : BigDecimal)
Account.transaction do
from = Account.find!(from_id)
to = Account.find!(to_id)
raise "Insufficient funds" if from.balance < amount
from.balance -= amount
to.balance += amount
from.save!
to.save!
end
enddef create_post_with_comments(author : User, title : String, comments : Array(String))
Post.transaction do
post = Post.create!(
title: title,
body: "Post body",
user_id: author.id.not_nil!
)
comments.each do |comment_text|
Comment.create!(
body: comment_text,
post_id: post.id.not_nil!,
user_id: author.id
)
end
post
end
endUser.transaction do
user = User.create!(name: "John", email: "john@example.com")
# These are all part of the same transaction
3.times do |i|
Post.create!(
title: "Post #{i + 1}",
body: "Content",
user_id: user.id.not_nil!
)
end
endUser.transaction do
user = User.create!(name: "John", email: "john@example.com")
if some_condition_fails
raise "Validation failed" # Rolls back the transaction
end
# Continue with more operations
endresult = User.transaction do
user = User.create!(name: "John", email: "john@example.com")
profile = Profile.create!(user_id: user.id.not_nil!)
{user: user, profile: profile}
end
puts "Created user: #{result[:user].name}"begin
User.transaction do
# Operations that might fail
user = User.create!(name: "John", email: "john@example.com")
external_api.create_account(user) # Might fail
end
rescue ex
# Handle or log the error
Log.error { "Transaction failed: #{ex.message}" }
raise ex
endinitial_count = User.count
begin
User.transaction do
User.create!(name: "Test", email: "test@example.com")
raise "Rollback!"
end
rescue
end
User.count == initial_count # => true (rolled back)MyDB = CQL::Schema.define(
:my_db,
adapter: CQL::Adapter::Postgres,
uri: ENV["DATABASE_URL"]
) do
# Pool settings
pool_size 25
checkout_timeout 5.seconds
retry_attempts 3
endpool_size 25 # Default: 5checkout_timeout 5.seconds # Default: 5 secondsretry_attempts 3 # Default: 1pool = case ENV["APP_ENV"]?
when "production" then 25
when "staging" then 10
else 5
end
MyDB = CQL::Schema.define(:my_db, adapter: CQL::Adapter::Postgres, uri: db_url) do
pool_size pool
end# Check current pool status
stats = MyDB.pool_stats
puts "Available: #{stats[:available]}"
puts "In use: #{stats[:busy]}"
puts "Size: #{stats[:size]}"# Simulate concurrent connections
10.times do
spawn do
User.count
puts "Connection successful"
end
end
sleep 1.second# Header partial
current_user = User.find(session[:user_id]) # Query 1
# Sidebar partial
current_user = User.find(session[:user_id]) # Query 2 (duplicate!)
# Main content
current_user = User.find(session[:user_id]) # Query 3 (duplicate!)class RequestCacheMiddleware
include HTTP::Handler
def call(context)
CQL::RequestCache.with_cache do
call_next(context)
end
end
end
# Add to your middleware stack
server = HTTP::Server.new([
RequestCacheMiddleware.new,
# ... other middleware
MyApp.new
])CQL::RequestCache.with_cache do
user1 = User.find(1) # Executes query
user2 = User.find(1) # Returns cached result
user3 = User.find(1) # Returns cached result
# Only 1 query executed!
endCQL::RequestCache.with_cache do
# All these are cached
User.find(1)
Post.where(user_id: 1).all
Comment.where(post_id: 5).count
endCQL::RequestCache.with_cache do
# Force fresh data
user = User.uncached.find(1)
endCQL::RequestCache.with_cache do
user = User.find(1)
# After an update, clear cache
user.update!(name: "New Name")
CQL::RequestCache.clear
user = User.find(1) # Fresh query
endbefore_all do |env|
CQL::RequestCache.start
end
after_all do |env|
CQL::RequestCache.stop
endabstract class BrowserAction < Lucky::Action
around :with_request_cache
def with_request_cache
CQL::RequestCache.with_cache { yield }
end
endclass ApplicationController < Amber::Controller::Base
around_action :with_request_cache
private def with_request_cache
CQL::RequestCache.with_cache { yield }
end
endCQL::RequestCache.on_hit do |query|
Log.debug { "Cache hit: #{query}" }
end
CQL::RequestCache.on_miss do |query|
Log.debug { "Cache miss: #{query}" }
endCQL::RequestCache.with_cache do
start = Time.monotonic
100.times { User.find(1) }
elapsed = Time.monotonic - start
puts "100 finds in #{elapsed.total_milliseconds}ms"
# Should be very fast (< 5ms) with caching
# Would be slow (> 100ms) without caching
endrequire "cql"
require "pg"
MyDB = CQL::Schema.define(
:my_db,
adapter: CQL::Adapter::Postgres,
uri: "postgres://username:password@localhost:5432/myapp_development"
) do
end
MyDB.initrequire "cql"
require "sqlite3"
MyDB = CQL::Schema.define(
:my_db,
adapter: CQL::Adapter::SQLite,
uri: "sqlite3://./db/development.db"
) do
end
MyDB.initrequire "cql"
require "mysql"
MyDB = CQL::Schema.define(
:my_db,
adapter: CQL::Adapter::MySql,
uri: "mysql://username:password@localhost:3306/myapp_development"
) do
end
MyDB.initDATABASE_URL = ENV["DATABASE_URL"]? || "postgres://localhost/myapp_development"
MyDB = CQL::Schema.define(
:my_db,
adapter: CQL::Adapter::Postgres,
uri: DATABASE_URL
) do
endpostgres://username:password@host:port/database
postgres://user:pass@localhost:5432/myapp
postgres://localhost/myapp # Uses default user and portmysql://username:password@host:port/database
mysql://user:pass@localhost:3306/myappsqlite3://path/to/database.db
sqlite3://./db/development.db
sqlite3://:memory: # In-memory databasebegin
MyDB.init
puts "Connected successfully"
rescue ex
puts "Connection failed: #{ex.message}"
exit 1
endMyDB = CQL::Schema.define(
:my_db,
adapter: CQL::Adapter::Postgres,
uri: "postgres://user:pass@localhost:5432/myapp?sslmode=require"
) do
enddb_path = "./db/development.db"
Dir.mkdir_p(File.dirname(db_path)) unless Dir.exists?(File.dirname(db_path))
MyDB = CQL::Schema.define(
:my_db,
adapter: CQL::Adapter::SQLite,
uri: "sqlite3://#{db_path}"
) do
endPrimaryDB = CQL::Schema.define(
:primary,
adapter: CQL::Adapter::Postgres,
uri: ENV["PRIMARY_DATABASE_URL"]
) do
end
AnalyticsDB = CQL::Schema.define(
:analytics,
adapter: CQL::Adapter::Postgres,
uri: ENV["ANALYTICS_DATABASE_URL"]
) do
end
# Use different schemas for different models
struct User
db_context PrimaryDB, :users
end
struct PageView
db_context AnalyticsDB, :page_views
endMyDB.init
# Run a simple query
MyDB.exec("SELECT 1")
puts "Database connection verified"MyDB.on_query do |sql, duration|
Log.info { "[CQL] (#{duration.total_milliseconds.round(2)}ms) #{sql}" }
endSLOW_QUERY_THRESHOLD = 100.milliseconds
MyDB.on_query do |sql, duration|
if duration > SLOW_QUERY_THRESHOLD
Log.warn { "[SLOW QUERY] (#{duration.total_milliseconds.round(2)}ms) #{sql}" }
end
endmodule QueryMetrics
@@query_count = Atomic(Int64).new(0)
@@total_time = Atomic(Int64).new(0)
def self.record(duration : Time::Span)
@@query_count.add(1)
@@total_time.add(duration.total_microseconds.to_i64)
end
def self.stats
{
count: @@query_count.get,
total_ms: @@total_time.get / 1000.0,
avg_ms: @@total_time.get / [@@query_count.get, 1].max / 1000.0
}
end
def self.reset
@@query_count.set(0)
@@total_time.set(0)
end
end
MyDB.on_query do |sql, duration|
QueryMetrics.record(duration)
endclass QueryTracker
property queries = [] of {String, Time::Span}
def record(sql : String, duration : Time::Span)
@queries << {sql, duration}
end
def total_time
@queries.sum(&.[1])
end
def count
@queries.size
end
end
# In your request middleware
tracker = QueryTracker.new
MyDB.with_query_callback(tracker.method(:record)) do
# Handle request
response = handle_request(request)
Log.info { "Request completed: #{tracker.count} queries in #{tracker.total_time.total_milliseconds}ms" }
response
endmodule QueryAnalyzer
@@patterns = Hash(String, {count: Int32, total_time: Time::Span}).new
def self.record(sql : String, duration : Time::Span)
# Normalize query by removing specific values
pattern = sql.gsub(/= \d+/, "= ?")
.gsub(/= '[^']*'/, "= ?")
.gsub(/IN \([^)]+\)/, "IN (?)")
existing = @@patterns[pattern]? || {count: 0, total_time: Time::Span.zero}
@@patterns[pattern] = {
count: existing[:count] + 1,
total_time: existing[:total_time] + duration
}
end
def self.report
@@patterns.to_a
.sort_by { |_, stats| -stats[:total_time].total_milliseconds }
.first(10)
.each do |pattern, stats|
puts "#{stats[:count]}x (#{stats[:total_time].total_milliseconds.round(2)}ms total): #{pattern[0..100]}"
end
end
end# PostgreSQL
stats = MyDB.exec(<<-SQL
SELECT
relname as table,
seq_scan,
idx_scan,
n_live_tup as rows
FROM pg_stat_user_tables
ORDER BY seq_scan DESC
LIMIT 10
SQL
)
stats.each do |row|
puts "#{row["table"]}: #{row["seq_scan"]} seq scans, #{row["idx_scan"]} idx scans"
endspawn do
loop do
stats = MyDB.pool_stats
Log.info { "Pool: #{stats[:busy]}/#{stats[:size]} connections in use" }
sleep 30.seconds
end
endrequire "prometheus"
query_counter = Prometheus.counter("cql_queries_total", "Total CQL queries")
query_duration = Prometheus.histogram("cql_query_duration_seconds", "Query duration")
MyDB.on_query do |sql, duration|
query_counter.inc
query_duration.observe(duration.total_seconds)
end# Run some queries
User.all
Post.where(published: true).all
User.find(1)
# Check your logs/metrics
QueryMetrics.stats # => {count: 3, total_ms: 15.5, avg_ms: 5.17}class AddIndexes < CQL::Migration(1)
def up
add_index :users, :email
add_index :posts, :user_id
add_index :posts, :published_at
end
def down
remove_index :users, :email
remove_index :posts, :user_id
remove_index :posts, :published_at
end
end# For queries like: WHERE user_id = ? AND status = ?
add_index :orders, [:user_id, :status]add_index :users, :email, unique: true# Instead of
users = User.all
# Use
users = User.select(:id, :name, :email).all# Returns array of emails
emails = User.where(active: true).pluck(:email)
# Returns array of [id, name] tuples
data = User.select(:id, :name).pluck(:id, :name)# Pagination
users = User.limit(20).offset(40).all
# Or use cursor pagination for large datasets
users = User.where { id > last_id }.limit(20).all# Efficient - runs COUNT query
count = User.where(active: true).count
# Inefficient - loads all records
count = User.where(active: true).all.sizeUser.where(needs_update: true).each_batch(1000) do |batch|
batch.each do |user|
user.update!(processed: true)
end
end# Efficient - stops at first match
if User.where(email: email).exists?
# ...
end
# Inefficient - counts all matches
if User.where(email: email).count > 0
# ...
end# Add index for common ordering
add_index :posts, :created_at
# Query uses index
Post.order(created_at: :desc).limit(10).allMyDB.on_query do |sql, duration|
if duration > 100.milliseconds
Log.warn { "Slow query (#{duration}ms): #{sql}" }
end
endresult = MyDB.exec("EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com'")
puts result.firstrequire "spec"
require "../src/models/*"
describe User do
describe "#valid?" do
it "is valid with valid attributes" do
user = User.new("John", "john@example.com")
user.valid?.should be_true
end
it "is invalid without email" do
user = User.new("John", "")
user.valid?.should be_false
end
it "is invalid with malformed email" do
user = User.new("John", "invalid-email")
user.valid?.should be_false
end
end
enddescribe User do
describe "#save" do
it "persists to database" do
user = User.new("John", "john@example.com")
user.save.should be_true
user.id.should_not be_nil
end
it "sets timestamps" do
user = User.new("John", "john@example.com")
user.save
user.created_at.should_not be_nil
end
end
describe ".find" do
it "retrieves a saved user" do
user = User.create!(name: "John", email: "john@example.com")
found = User.find(user.id.not_nil!)
found.should_not be_nil
found.not_nil!.name.should eq("John")
end
end
enddescribe Post do
describe "callbacks" do
it "generates slug before save" do
post = Post.new(title: "Hello World", body: "Content")
post.save
post.slug.should eq("hello-world")
end
it "normalizes title" do
post = Post.new(title: " HELLO ", body: "Content")
post.save
post.title.should eq("Hello")
end
end
enddescribe Post do
describe "relationships" do
it "belongs to user" do
user = User.create!(name: "John", email: "john@example.com")
post = Post.create!(user_id: user.id.not_nil!, title: "Test", body: "Body")
post.user.id.should eq(user.id)
end
it "has many comments" do
post = Post.create!(user_id: 1, title: "Test", body: "Body")
Comment.create!(post_id: post.id.not_nil!, body: "Nice!")
Comment.create!(post_id: post.id.not_nil!, body: "Great!")
post.comments.count.should eq(2)
end
end
enddescribe Post do
describe "scopes" do
before_each do
Post.create!(title: "Published", body: "...", published: true)
Post.create!(title: "Draft", body: "...", published: false)
end
it ".published returns only published posts" do
Post.published.count.should eq(1)
Post.published.first.not_nil!.title.should eq("Published")
end
it ".drafts returns only draft posts" do
Post.drafts.count.should eq(1)
end
end
endmodule TestHelpers
def create_user(name = "Test User", email = "test@example.com")
User.create!(name: name, email: email)
end
def create_post(user : User, title = "Test Post")
Post.create!(user_id: user.id.not_nil!, title: title, body: "Content")
end
end
Spec.before_each do
extend TestHelpers
endmodule Factory
def self.user(attrs = {} of Symbol => String)
User.create!(
name: attrs[:name]? || "Test User #{rand(1000)}",
email: attrs[:email]? || "test#{rand(1000)}@example.com"
)
end
def self.post(user : User? = nil, attrs = {} of Symbol => String)
user ||= self.user
Post.create!(
user_id: user.id.not_nil!,
title: attrs[:title]? || "Test Post",
body: attrs[:body]? || "Test content"
)
end
end
# Usage
user = Factory.user(name: "John")
post = Factory.post(user, title: "My Post")Spec.before_each do
# Truncate tables
MyDB.exec("TRUNCATE users, posts, comments RESTART IDENTITY CASCADE")
end
# Or use transactions
Spec.around_each do |example|
MyDB.transaction do
example.run
raise Rollback.new # Always rollback
end
end# Run all tests
crystal spec
# Run specific file
crystal spec spec/models/user_spec.cr
# Run with verbose output
crystal spec --verbosecreatedb myapp_testmysql -e "CREATE DATABASE myapp_test"# src/config/database.cr
module Config
def self.database_url
case ENV["APP_ENV"]?
when "test"
ENV["TEST_DATABASE_URL"]? || "postgres://localhost/myapp_test"
when "production"
ENV["DATABASE_URL"]
else
ENV["DATABASE_URL"]? || "postgres://localhost/myapp_development"
end
end
end
# src/db.cr
MyDB = CQL::Schema.define(
:my_db,
adapter: CQL::Adapter::Postgres,
uri: Config.database_url
) do
end# .env.test
TEST_DATABASE_URL=postgres://localhost/myapp_test
# Run tests
APP_ENV=test crystal spec# spec/spec_helper.cr
ENV["APP_ENV"] = "test"
ENV["TEST_DATABASE_URL"] = "postgres://localhost/myapp_test"
require "../src/db"
require "../src/models/*"# spec/spec_helper.cr
ENV["APP_ENV"] = "test"
require "../src/db"
# Run migrations
MyDB.migrator.up#!/bin/bash
# bin/test
APP_ENV=test crystal run src/migrate.cr
APP_ENV=test crystal spec "$@"# spec/spec_helper.cr
Spec.before_each do
# PostgreSQL
MyDB.exec(<<-SQL
TRUNCATE users, posts, comments
RESTART IDENTITY CASCADE
SQL
)
# MySQL
# MyDB.exec("SET FOREIGN_KEY_CHECKS = 0")
# MyDB.exec("TRUNCATE users")
# MyDB.exec("SET FOREIGN_KEY_CHECKS = 1")
# SQLite
# MyDB.exec("DELETE FROM users")
# MyDB.exec("DELETE FROM sqlite_sequence WHERE name='users'")
endSpec.around_each do |example|
MyDB.transaction do
example.run
raise DB::Rollback.new
end
endSpec.after_each do
Comment.delete_all
Post.delete_all
User.delete_all
end# spec/spec_helper.cr
TestDB = CQL::Schema.define(
:test_db,
adapter: CQL::Adapter::SQLite,
uri: "sqlite3://:memory:"
) do
end
Spec.before_suite do
# Run migrations on in-memory database
TestDB.migrator.up
end# spec/support/test_schema.cr
TestDB = CQL::Schema.define(:test, adapter: CQL::Adapter::SQLite, uri: "sqlite3://:memory:") do
table :users do
primary_key :id, Int64
column :name, String
column :email, String
timestamps
end
end
# Redefine models to use test database
struct User
include CQL::ActiveRecord::Model(Int64)
db_context TestDB, :users
# ... properties
endworker_id = ENV["TEST_WORKER_ID"]? || "0"
test_db = "myapp_test_#{worker_id}"
TestDB = CQL::Schema.define(:test, adapter: CQL::Adapter::Postgres, uri: "postgres://localhost/#{test_db}") do
end# spec/database_spec.cr
describe "Database" do
it "connects to test database" do
MyDB.exec("SELECT 1").should_not be_nil
end
it "has migrated schema" do
User.count.should eq(0) # Table exists
end
endrelation "users" already existsputs migrator.applied_migrationsmigrator.mark_as_applied(migration_number)schema.users.drop! if schema.table?(:users)relation "users" does not existmigrator.up # Run all pending migrationscolumn "email" of relation "users" already existsdef up
unless schema.column_exists?(:users, :email)
schema.alter :users do
add_column :email, String
end
end
enddef up
schema.alter :users do
drop_column :email if column_exists?(:email)
add_column :email, String, null: false
end
endcannot drop column "user_id" because other objects depend on itdef up
schema.alter :posts do
drop_foreign_key [:user_id]
drop_index :idx_posts_user_id
drop_column :user_id
end
endcolumn "name" contains null valuesschema.alter :users do
add_column :name, String, null: false, default: "Unknown"
enddef up
schema.alter :users do
add_column :name, String, null: true
end
schema.exec("UPDATE users SET name = 'Unknown' WHERE name IS NULL")
schema.alter :users do
change_column :name, String, null: false
end
endinsert or update on table "posts" violates foreign key constraintbigint :user_id, null: trueforeign_key [:user_id], references: :users, on_delete: :set_nullmigrator = MyDB.migrator(config)
puts "Applied migrations: #{migrator.applied_migrations}"
puts "Pending migrations: #{migrator.pending_migrations}"
puts "Current version: #{migrator.current_version}"def reset_database
migrator = MyDB.migrator(config)
puts "Rolling back all migrations..."
migrator.down_to(0)
puts "Running all migrations..."
migrator.up
puts "Database reset complete"
end# List tables
schema.tables-- Remove from migration tracking
DELETE FROM cql_schema_migrations WHERE version = 5;schema.partial_table.drop! if schema.table?(:partial_table)migrator.up# 1 query to fetch posts
posts = Post.all
# N queries - one for each post's author!
posts.each do |post|
puts post.user.name # Triggers a query each time
end# 2 queries total: posts + users
posts = Post.preload(:user).all
posts.each do |post|
puts post.user.name # No additional query
end# Load multiple associations
posts = Post
.preload(:user, :comments)
.all# Fetch posts with comments from active users
posts = Post
.joins(:user)
.where("users.active = ?", true)
.allposts = Post
.select(:id, :title, :user_id)
.allposts = Post.all
# Load all users at once
user_ids = posts.map(&.user_id).uniq
users_by_id = User.where(id: user_ids).index_by(&.id)
posts.each do |post|
user = users_by_id[post.user_id]
puts user.name
endMyDB.on_query do |sql, duration|
Log.debug { sql }
endSELECT * FROM users WHERE id = 1
SELECT * FROM users WHERE id = 2
SELECT * FROM users WHERE id = 3
...# Controller
@posts = Post.preload(:user, :comments).all
# View - no additional queries
<% @posts.each do |post| %>
<p>By: <%= post.user.name %></p>
<p>Comments: <%= post.comments.size %></p>
<% end %># Add column in migration
schema.alter :posts do
add_column :comments_count, Int32, default: 0
end
# Update counter when adding comments
def create_comment(post : Post, content : String)
Comment.create!(content: content, post_id: post.id.not_nil!)
post.comments_count = post.comments.count.to_i32
post.save!
end
# Now use post.comments_count instead of post.comments.count# Instead of
posts.each { |p| p.comments.count } # N+1
# Use
Post.joins(:comments)
.group(:id)
.select("posts.*, COUNT(comments.id) as comments_count")
.allPost Load (2ms) SELECT * FROM posts
User Load (1ms) SELECT * FROM users WHERE id = 1
User Load (1ms) SELECT * FROM users WHERE id = 2
User Load (1ms) SELECT * FROM users WHERE id = 3
... (100 more queries)Post Load (2ms) SELECT * FROM posts
User Load (3ms) SELECT * FROM users WHERE id IN (1, 2, 3, ...)schema.table :posts do
primary :id, Int64, auto_increment: true
column :title, String
timestamps
end
schema.posts.create!
schema.table :tags do
primary :id, Int64, auto_increment: true
column :name, String
timestamps
end
schema.tags.create!
# Join table
schema.table :post_tags do
column :post_id, Int64, null: false
column :tag_id, Int64, null: false
column :created_at, Time
foreign_key [:post_id], references: :posts, references_columns: [:id], on_delete: :cascade
foreign_key [:tag_id], references: :tags, references_columns: [:id], on_delete: :cascade
index [:post_id]
index [:tag_id]
index [:post_id, :tag_id], unique: true
end
schema.post_tags.create!struct Post
include CQL::ActiveRecord::Model(Int64)
db_context MyDB, :posts
property id : Int64?
property title : String
has_many :post_tags, PostTag, :post_id
def initialize(@title : String)
end
# Helper to get tags
def tags : Array(Tag)
tag_ids = post_tags.all.map(&.tag_id)
return [] of Tag if tag_ids.empty?
Tag.where { id.in(tag_ids) }.all
end
# Helper to add a tag
def add_tag(tag : Tag)
PostTag.create!(post_id: id.not_nil!, tag_id: tag.id.not_nil!)
end
# Helper to remove a tag
def remove_tag(tag : Tag)
PostTag.where(post_id: id, tag_id: tag.id).each(&.delete!)
end
endstruct Tag
include CQL::ActiveRecord::Model(Int64)
db_context MyDB, :tags
property id : Int64?
property name : String
has_many :post_tags, PostTag, :tag_id
def initialize(@name : String)
end
# Helper to get posts
def posts : Array(Post)
post_ids = post_tags.all.map(&.post_id)
return [] of Post if post_ids.empty?
Post.where { id.in(post_ids) }.all
end
endstruct PostTag
include CQL::ActiveRecord::Model(Int64)
db_context MyDB, :post_tags
property post_id : Int64
property tag_id : Int64
property created_at : Time?
belongs_to :post, Post, :post_id
belongs_to :tag, Tag, :tag_id
def initialize(@post_id : Int64, @tag_id : Int64)
end
endpost = Post.create!(title: "My Post")
tag1 = Tag.create!(name: "Crystal")
tag2 = Tag.create!(name: "ORM")
# Add tags
post.add_tag(tag1)
post.add_tag(tag2)
post.tags.map(&.name) # => ["Crystal", "ORM"]PostTag.create!(post_id: post.id.not_nil!, tag_id: tag.id.not_nil!)post = Post.find(1)
tags = post.tags
tags.each { |tag| puts tag.name }tag = Tag.find_by(name: "Crystal")
posts = tag.posts if tag
posts.each { |post| puts post.title }crystal_tag = Tag.find_by(name: "Crystal")
if crystal_tag
post_ids = PostTag.where(tag_id: crystal_tag.id).all.map(&.post_id)
crystal_posts = Post.where { id.in(post_ids) }.all
endpost.remove_tag(tag)post.post_tags.all.each(&.delete!)post = Post.create!(title: "Test")
tag = Tag.create!(name: "Test Tag")
post.add_tag(tag)
post.tags.map(&.name) # => ["Test Tag"]
tag.posts.map(&.title) # => ["Test"]
post.remove_tag(tag)
post.tags # => []user = User.find(1)
if user
puts "Found: #{user.name}"
else
puts "Not found"
enduser = User.find!(1) # Raises if not found
puts user.nameuser = User.find_by(email: "john@example.com")
puts user.try(&.name)post = Post.find_by(user_id: 1, published: true)user = User.first
puts user.try(&.name)user = User.last
puts user.try(&.name)users = User.all
users.each { |u| puts u.name }# First by creation date
oldest = User.order(created_at: :asc).first
# Last by creation date
newest = User.order(created_at: :desc).firstexists = User.exists?(email: "john@example.com")
puts "User exists: #{exists}"user = User.find_by(email: "john@example.com")
user ||= User.new("John", "john@example.com")user = User.find(1)
name = user.try(&.name) || "Unknown"User.create!(name: "John", email: "john@example.com")
User.find_by(name: "John") # => User instance
User.find_by(name: "Jane") # => nilbegin
user.validate!
rescue CQL::ActiveRecord::Validations::ValidationError => ex
puts ex.message # Contains validation error messages
endbegin
user.update!
rescue CQL::OptimisticLockError
user.reload! # Get latest version
# Re-apply changes and retry
endbegin
user = User.find!(999)
rescue CQL::RecordNotFound
puts "User not found"
endbegin
User.create!(name: "", email: "")
rescue CQL::RecordInvalid => ex
puts "Failed: #{ex.message}"
enddef down
raise "Cannot rollback: data would be lost"
endLog.setup do |c|
c.bind("cql.*", :debug, Log::IOBackend.new)
endbegin
# operation
rescue ex
puts "Error type: #{ex.class}"
puts "Message: #{ex.message}"
puts "Backtrace: #{ex.backtrace.first(5).join("\n")}"
endunless model.valid?
model.errors.each do |error|
puts "#{error.field}: #{error.message}"
end
endposts = Post
.where(published: true)
.where { views_count > 100 }
.order(created_at: :desc)
.limit(10)
.allUser.where { (active == true) & (verified == true) & (age > 18) }.allUser.where { (role == "admin") | (role == "moderator") | (role == "owner") }.allUser.where { (active == true) & ((role == "admin") | (role == "moderator")) }.all# Get users who have posts
active_author_ids = Post.where(published: true).pluck(:user_id)
authors = User.where { id.in(active_author_ids) }.alltotal = User.count
active = User.where(active: true).counttotal_views = Post.where(published: true).sum(:views_count)avg_age = User.where(active: true).avg(:age)oldest_post = Post.min(:created_at)
most_views = Post.max(:views_count)# Posts per category
Category.all.map do |cat|
{
name: cat.name,
posts: cat.posts.count
}
end# Only fetch needed columns
names = User.where(active: true).pluck(:name)Post.order(published: :desc, created_at: :desc).allpage = 2
per_page = 10
Post.order(created_at: :desc)
.limit(per_page)
.offset((page - 1) * per_page)
.allunique_categories = Post.select(:category_id).distinct.alluser = User.find!(1)
# Posts with conditions
published_posts = user.posts.where(published: true).all
# Ordered posts
recent_posts = user.posts.order(created_at: :desc).limit(5).all# Get top 10 active users with most published posts
users_with_counts = User.where(active: true).all.map do |user|
post_count = user.posts.where(published: true).count
{user: user, post_count: post_count}
end
top_users = users_with_counts
.sort_by { |h| -h[:post_count] }
.first(10)
top_users.each do |h|
puts "#{h[:user].name}: #{h[:post_count]} posts"
endresult = MyDB.exec(<<-SQL
SELECT u.name, COUNT(p.id) as post_count
FROM users u
LEFT JOIN posts p ON p.user_id = u.id AND p.published = true
WHERE u.active = true
GROUP BY u.id
ORDER BY post_count DESC
LIMIT 10
SQL
)# Setup test data
user = User.create!(name: "John", email: "john@example.com", active: true)
Post.create!(title: "Post 1", body: "...", user_id: user.id.not_nil!, published: true, views_count: 150)
Post.create!(title: "Post 2", body: "...", user_id: user.id.not_nil!, published: true, views_count: 50)
# Test query
popular = Post.where(published: true).where { views_count > 100 }.all
popular.size # => 1
popular.first.title # => "Post 1"# Raw SQL approach
result = db.exec("SELECT id, name, email FROM users WHERE id = ?", [1])
row = result.first
user_id = row[0].as(Int64)
user_name = row[1].as(String)
user_email = row[2].as(String)# ORM approach
user = User.find(1)
puts user.name
puts user.emailstruct User # Represents the 'users' table
include CQL::ActiveRecord::Model(Int64)
db_context MyDB, :users
property id : Int64? # Column: id
property name : String # Column: name
property email : String # Column: email
end# Database: posts table has a user_id column
# ORM: Post belongs_to User
post = Post.find(1)
author = post.user # No SQL needed in your code!user.name = 123 # Compile error: expected String, got Int32
user.age = "thirty" # Compile error: expected Int32, got String# Safe - CQL parameterizes the value
User.where(email: user_input)
# Dangerous raw SQL
db.exec("SELECT * FROM users WHERE email = '#{user_input}'")# Create user with validation
user = User.create!(
name: "John",
email: "john@example.com"
)
# Instead of:
# 1. Write INSERT SQL
# 2. Handle errors
# 3. Get the inserted ID
# 4. Validate before insert
# etc.# Works with PostgreSQL, MySQL, or SQLite
User.where(active: true).order(created_at: :desc).all# Add a new field - one place to change
struct User
property phone : String? # Add property
end
# All code using User automatically has access to phoneresult = MyDB.exec(<<-SQL
SELECT u.name, COUNT(p.id) as post_count
FROM users u
LEFT JOIN posts p ON p.user_id = u.id
GROUP BY u.id
HAVING COUNT(p.id) > 10
SQL
)user = User.new("John", "john@example.com")
user.save # The user object saves itselfcrystal specschema.table :users do
primary :id, Int64, auto_increment: true
column :name, String
column :deleted_at, Time, null: true
timestamps
end
schema.users.create!struct User
include CQL::ActiveRecord::Model(Int64)
include CQL::ActiveRecord::SoftDeletable
db_context MyDB, :users
property id : Int64?
property name : String
property created_at : Time?
property updated_at : Time?
# deleted_at is handled automatically
def initialize(@name : String)
end
enduser = User.find(1)
user.delete! # Sets deleted_at to current time
user.deleted? # => true
user.deleted_at # => 2024-01-15 10:30:00 UTCUser.delete!(user_id)User.delete_by!(email: "spam@example.com")User.delete_all # Soft deletes all usersuser.restore!
user.deleted? # => false
user.deleted_at # => nilUser.restore!(user_id)User.restore_allUser.all # Only active users
User.count # Only counts active users
User.find(1) # Returns nil if user is deletedUser.with_deleted.all # All users including deleted
User.with_deleted.find(1) # Finds even if deleted
User.with_deleted.count # Counts all usersUser.only_deleted.all # Only deleted users
User.only_deleted.count # Count of deleted usersuser.force_delete! # Permanently removes from database
User.force_delete!(user_id) # By ID
User.force_delete_all # Permanently delete all recordsschema.alter :users do
create_index :idx_users_deleted_at, [:deleted_at]
enddef cleanup_old_deleted_records
cutoff = 1.year.ago
old_records = User.only_deleted
.where { deleted_at < cutoff }
.all
old_records.each(&.force_delete!)
puts "Deleted #{old_records.size} old records"
endstruct Post
include CQL::ActiveRecord::Model(Int64)
include CQL::ActiveRecord::SoftDeletable
db_context MyDB, :posts
has_many :comments, Comment, :post_id
after_destroy :soft_delete_comments
private def soft_delete_comments
comments.all.each(&.delete!)
true
end
end# Create a user
user = User.create!(name: "John")
# Soft delete
user.delete!
User.count # => 0
User.with_deleted.count # => 1
# Restore
user.restore!
User.count # => 1
# Permanent delete
user.force_delete!
User.with_deleted.count # => 0user = User.new("John", "john@example.com")
if user.save
puts "Created user #{user.id}"
else
puts "Failed: #{user.errors.map(&.message).join(", ")}"
enduser = User.new("John", "john@example.com")
user.save! # Raises if validation fails
puts "Created user #{user.id}"user = User.create(name: "John", email: "john@example.com")
if user.persisted?
puts "Created user #{user.id}"
else
puts "Failed to create"
enduser = User.create!(
name: "John",
email: "john@example.com",
age: 30
)
puts "Created user #{user.id}"users = ["Alice", "Bob", "Charlie"].map do |name|
User.create!(
name: name,
email: "#{name.downcase}@example.com"
)
end
puts "Created #{users.size} users"user = User.create!(name: "John", email: "john@example.com")
post = Post.create!(
title: "My First Post",
body: "Hello world!",
user_id: user.id.not_nil!
)
puts "Created post for user #{user.name}"struct User
property status : String = "pending"
property role : String = "user"
end
user = User.create!(name: "John", email: "john@example.com")
user.status # => "pending"
user.role # => "user"user = User.create!(name: "John", email: "john@example.com")
user.created_at # => 2024-01-15 10:30:00 UTC
user.updated_at # => 2024-01-15 10:30:00 UTCuser = User.new("", "invalid-email")
unless user.save
user.errors.each do |error|
puts "#{error.field}: #{error.message}"
end
enduser = User.create!(name: "John", email: "john@example.com")
user.id.nil? # => false (has ID now)
user.persisted? # => true
user.new_record? # => falseactive_users = User.where(active: true).allusers = User.where(active: true, role: "admin").allusers = User
.where(active: true)
.where(role: "admin")
.all# Greater than
adults = User.where { age > 18 }.all
# Less than
young = User.where { age < 30 }.all
# Greater than or equal
User.where { age >= 18 }.all
# Less than or equal
User.where { age <= 65 }.all
# Not equal
User.where { status != "banned" }.all# Between
User.where { age.between(18, 65) }.all
# In a set
User.where { role.in(["admin", "moderator"]) }.all# Records from last week
User.where { created_at > 1.week.ago }.all
# Records from specific date
User.where { created_at > Time.utc(2024, 1, 1) }.all
# Records between dates
Post.where { published_at.between(start_date, end_date) }.all# Where null
User.where(deleted_at: nil).all
# Where not null (use block)
User.where { deleted_at != nil }.all# Contains
User.where { name.like("%john%") }.all
# Starts with
User.where { email.like("admin%") }.all
# Ends with
User.where { email.like("%@example.com") }.allUser.where { (role == "admin") | (role == "moderator") }.allUser.where { (age > 18) & (active == true) }.allUser.where(active: true).where { age > 18 }.allUser.where(active: true)
.order(created_at: :desc)
.allUser.where(active: true)
.limit(10)
.allcount = User.where(active: true).count
puts "Active users: #{count}"User.create!(name: "John", email: "john@example.com", age: 25, active: true)
User.create!(name: "Jane", email: "jane@example.com", age: 30, active: false)
User.where(active: true).count # => 1
User.where { age > 20 }.count # => 2require "cql"
require "../migrations/*"
MyDB.init
config = CQL::MigratorConfig.new(
schema_file_path: "src/schemas/app_schema.cr",
schema_name: :AppSchema,
auto_sync: true
)
migrator = MyDB.migrator(config)migrator.up
puts "Migrations complete"puts "Applied: #{migrator.applied_migrations.size}"
puts "Pending: #{migrator.pending_migrations.size}"# Run up to migration 5
migrator.up_to(5)# src/migrate.cr
require "./database"
require "../migrations/*"
MyDB.init
config = CQL::MigratorConfig.new(
schema_file_path: "src/schemas/app_schema.cr",
schema_name: :AppSchema,
auto_sync: true
)
migrator = MyDB.migrator(config)
case ARGV[0]?
when "up"
migrator.up
puts "Migrated up"
when "down"
migrator.down
puts "Rolled back one migration"
when "status"
puts "Applied: #{migrator.applied_migrations.size}"
puts "Pending: #{migrator.pending_migrations.size}"
when "version"
puts "Current version: #{migrator.current_version}"
else
puts "Usage: crystal src/migrate.cr [up|down|status|version]"
end# Run all pending migrations
crystal src/migrate.cr up
# Check status
crystal src/migrate.cr status
# Rollback one migration
crystal src/migrate.cr down# src/app.cr
require "./database"
require "../migrations/*"
def setup_database
MyDB.init
config = CQL::MigratorConfig.new(
schema_file_path: "src/schemas/app_schema.cr",
schema_name: :AppSchema,
auto_sync: true
)
migrator = MyDB.migrator(config)
pending = migrator.pending_migrations.size
if pending > 0
puts "Running #{pending} pending migration(s)..."
migrator.up
end
end
setup_database
# ... rest of your appmigrator = MyDB.migrator(config)
migrator.up
# Verify
puts "Applied migrations:"
migrator.applied_migrations.each do |version|
puts " - #{version}"
endclass AddAvatarToUsers < CQL::Migration(5)
def up
schema.alter :users do
add_column :avatar_url, String, null: true
end
end
def down
schema.alter :users do
drop_column :avatar_url
end
end
endclass AddProfileFieldsToUsers < CQL::Migration(6)
def up
schema.alter :users do
add_column :bio, String, null: true
add_column :website, String, null: true
add_column :location, String, null: true
end
end
def down
schema.alter :users do
drop_column :bio
drop_column :website
drop_column :location
end
end
endclass AddActiveToUsers < CQL::Migration(7)
def up
schema.alter :users do
add_column :active, Bool, default: true, null: false
end
end
def down
schema.alter :users do
drop_column :active
end
end
endclass AddSlugToPosts < CQL::Migration(8)
def up
schema.alter :posts do
add_column :slug, String, null: true
end
schema.alter :posts do
create_index :idx_posts_slug, [:slug], unique: true
end
end
def down
schema.alter :posts do
drop_index :idx_posts_slug
drop_column :slug
end
end
endclass AddCategoryToPosts < CQL::Migration(9)
def up
schema.alter :posts do
add_column :category_id, Int64, null: true
create_index :idx_posts_category_id, [:category_id]
foreign_key [:category_id], references: :categories, references_columns: [:id], on_delete: :set_null
end
end
def down
schema.alter :posts do
drop_foreign_key :fk_posts_category_id
drop_index :idx_posts_category_id
drop_column :category_id
end
end
endclass AddTimestampsToPosts < CQL::Migration(10)
def up
schema.alter :posts do
add_column :created_at, Time, null: true
add_column :updated_at, Time, null: true
end
# Optionally set current time for existing records
schema.exec("UPDATE posts SET created_at = NOW(), updated_at = NOW() WHERE created_at IS NULL")
end
def down
schema.alter :posts do
drop_column :created_at
drop_column :updated_at
end
end
endclass AddDeletedAtToUsers < CQL::Migration(11)
def up
schema.alter :users do
add_column :deleted_at, Time, null: true
end
schema.alter :users do
create_index :idx_users_deleted_at, [:deleted_at]
end
end
def down
schema.alter :users do
drop_index :idx_users_deleted_at
drop_column :deleted_at
end
end
endclass AddVersionToUsers < CQL::Migration(12)
def up
schema.alter :users do
add_column :version, Int32, default: 1, null: false
end
end
def down
schema.alter :users do
drop_column :version
end
end
endstruct User
# Existing properties...
# New property
property avatar_url : String?
# Don't forget to update constructor if needed
def initialize(@name : String, @email : String, @avatar_url : String? = nil)
end
endmigrator.up
# Test the new column works
user = User.create!(name: "John", email: "john@example.com", avatar_url: "/avatars/john.png")
user.avatar_url # => "/avatars/john.png"# Cache user by ID for 5 minutes
user = User.cache(5.minutes).find(user_id)
# Cache query results
posts = Post.cache(1.minute)
.where(published: true)
.order(created_at: :desc)
.limit(10)
.allMyDB = CQL::Schema.define(
:my_db,
adapter: CQL::Adapter::Postgres,
uri: ENV["DATABASE_URL"]
) do
cache_store CQL::Cache::Memory.new
default_cache_ttl 5.minutes
endcache_store CQL::Cache::Memory.new(max_size: 1000)cache_store CQL::Cache::Redis.new(
host: ENV["REDIS_HOST"],
port: 6379,
db: 1
)# These generate the same cache key:
User.cache(1.minute).find(1)
User.cache(1.minute).find(1) # Cache hit!
# Different query = different key:
User.cache(1.minute).where(id: 1).first # Different keyposts = Post.cache(1.hour, key: "homepage_posts")
.where(featured: true)
.limit(5)
.all# Clear specific cache
MyDB.cache.delete("homepage_posts")
# Clear all caches
MyDB.cache.clearstruct Post
after_save :invalidate_cache
after_destroy :invalidate_cache
private def invalidate_cache
MyDB.cache.delete("homepage_posts")
MyDB.cache.delete("post:#{@id}")
true
end
enddef find_user(id : Int64, use_cache : Bool = true)
query = User.where(id: id)
query = query.cache(5.minutes) if use_cache
query.first
endstats = MyDB.cache.stats
puts "Hits: #{stats[:hits]}"
puts "Misses: #{stats[:misses]}"
puts "Hit rate: #{stats[:hit_rate]}%"# First call - cache miss
user = User.cache(1.minute).find(1)
puts "First: #{user.name}"
# Second call - cache hit (no query logged)
user = User.cache(1.minute).find(1)
puts "Second: #{user.name}"CRYSTAL_ENV = ENV["CRYSTAL_ENV"]? || "development"require "cql"
CRYSTAL_ENV = ENV["CRYSTAL_ENV"]? || "development"
DATABASE_URL = case CRYSTAL_ENV
when "production"
ENV["DATABASE_URL"]? || raise "DATABASE_URL required in production"
when "test"
ENV["TEST_DATABASE_URL"]? || "postgres://localhost/myapp_test"
else
ENV["DATABASE_URL"]? || "postgres://localhost/myapp_development"
end
ADAPTER = case CRYSTAL_ENV
when "test"
# Use SQLite for faster tests
CQL::Adapter::SQLite
else
CQL::Adapter::Postgres
end
MyDB = CQL::Schema.define(:my_db, adapter: ADAPTER, uri: DATABASE_URL) do
end# config/database.cr
module DatabaseConfig
CRYSTAL_ENV = ENV["CRYSTAL_ENV"]? || "development"
def self.connection_url : String
case CRYSTAL_ENV
when "production"
ENV["DATABASE_URL"]
when "test"
"sqlite3://./db/test.db"
when "development"
"postgres://localhost/myapp_development"
else
raise "Unknown environment: #{CRYSTAL_ENV}"
end
end
def self.adapter
case CRYSTAL_ENV
when "test"
CQL::Adapter::SQLite
else
CQL::Adapter::Postgres
end
end
end
MyDB = CQL::Schema.define(
:my_db,
adapter: DatabaseConfig.adapter,
uri: DatabaseConfig.connection_url
) do
end# config/database/development.cr
module Database::Development
URL = "postgres://localhost/myapp_development"
ADAPTER = CQL::Adapter::Postgres
end
# config/database/test.cr
module Database::Test
URL = "sqlite3://./db/test.db"
ADAPTER = CQL::Adapter::SQLite
end
# config/database/production.cr
module Database::Production
URL = ENV["DATABASE_URL"]
ADAPTER = CQL::Adapter::Postgres
end
# config/database.cr
{% if env("CRYSTAL_ENV") == "production" %}
require "./database/production"
DB_CONFIG = Database::Production
{% elsif env("CRYSTAL_ENV") == "test" %}
require "./database/test"
DB_CONFIG = Database::Test
{% else %}
require "./database/development"
DB_CONFIG = Database::Development
{% end %}
MyDB = CQL::Schema.define(:my_db, adapter: DB_CONFIG::ADAPTER, uri: DB_CONFIG::URL) do
end# Development (default)
crystal run src/app.cr
# Test
CRYSTAL_ENV=test crystal spec
# Production
CRYSTAL_ENV=production DATABASE_URL=postgres://... crystal run src/app.crCRYSTAL_ENV = ENV["CRYSTAL_ENV"]? || "development"
if CRYSTAL_ENV == "development"
# Enable query logging
Log.setup do |c|
c.bind("cql.*", :debug, Log::IOBackend.new)
end
end
if CRYSTAL_ENV == "production"
# Use connection pooling
# Enable SSL
endputs "Running in #{CRYSTAL_ENV} environment"
puts "Database: #{DATABASE_URL.gsub(/:[^:@]+@/, ":****@")}" # Hide passwordmigrator = MyDB.migrator(config)
migrator.down
puts "Rolled back one migration"# Rollback last 3 migrations
3.times { migrator.down }# Rollback to version 5
migrator.down_to(5)# Rollback everything (use with caution!)
migrator.down_to(0)puts "Current version: #{migrator.current_version}"
puts "Applied migrations: #{migrator.applied_migrations.size}"
# Only rollback if there are migrations to rollback
if migrator.applied_migrations.any?
migrator.down
puts "Rolled back to version: #{migrator.current_version}"
else
puts "No migrations to rollback"
end# src/rollback.cr
require "./database"
require "../migrations/*"
MyDB.init
config = CQL::MigratorConfig.new(
schema_file_path: "src/schemas/app_schema.cr",
schema_name: :AppSchema,
auto_sync: true
)
migrator = MyDB.migrator(config)
current = migrator.current_version
puts "Current version: #{current}"
if current == 0
puts "No migrations to rollback"
exit 0
end
print "Rollback migration #{current}? (y/n): "
if gets.try(&.strip) == "y"
migrator.down
puts "Rolled back to version: #{migrator.current_version}"
else
puts "Cancelled"
endclass DropLegacyTable < CQL::Migration(10)
def up
schema.legacy_users.drop!
end
def down
# Can't restore dropped table with data
raise "Cannot rollback: table was dropped with data"
end
endbegin
migrator.down
rescue ex
puts "Rollback failed: #{ex.message}"
enddef refresh_database
migrator = MyDB.migrator(config)
puts "Rolling back all migrations..."
migrator.down_to(0)
puts "Re-running all migrations..."
migrator.up
puts "Database refreshed"
endmigrator = MyDB.migrator(config)
# Record current state
before = migrator.applied_migrations.size
# Rollback
migrator.down
# Verify
after = migrator.applied_migrations.size
puts "Rolled back: #{before} -> #{after} migrations"user = User.find(1)
if user
user.name = "Updated Name"
user.save
puts "Updated!"
enduser = User.find!(1)
user.name = "Updated Name"
user.save! # Raises if validation failsuser = User.find!(1)
user.update!(
name: "New Name",
email: "new@example.com",
age: 31
)# Update all inactive users
User.where(active: false).update!(active: true)
# Update with specific value
Post.where(user_id: 1).update!(published: true)post = Post.find!(1)
post.views_count += 1
post.save!user = User.find!(1)
if user.status == "pending"
user.status = "active"
user.activated_at = Time.utc
user.save!
enduser = User.find!(1)
user.name = "New Name" # Only name changed
user.save! # Only updates name columnuser = User.find!(1)
user.email = "invalid"
unless user.save
puts user.errors.map(&.message).join(", ")
enduser = User.find!(1)
user.name = "Updated"
user.save!
user.updated_at # => Current time# Direct update bypasses callbacks
User.where(id: 1).update!(name: "Direct Update")user = User.find!(1)
original_name = user.name
user.name = "New Name"
user.save!
User.find!(1).name # => "New Name"MyDB = CQL::Schema.define(
:my_db,
adapter: CQL::Adapter::Postgres,
uri: "postgres://user:pass@host/db?sslmode=require"
) do
enduri = "postgres://user:pass@host/db?" + {
"sslmode" => "verify-full",
"sslcert" => "/path/to/client.crt",
"sslkey" => "/path/to/client.key",
"sslrootcert" => "/path/to/ca.crt"
}.map { |k, v| "#{k}=#{v}" }.join("&")
MyDB = CQL::Schema.define(:my_db, adapter: CQL::Adapter::Postgres, uri: uri) do
enduri = "mysql://user:pass@host/db?" + {
"ssl-mode" => "REQUIRED",
"ssl-ca" => "/path/to/ca.pem"
}.map { |k, v| "#{k}=#{v}" }.join("&")
MyDB = CQL::Schema.define(:my_db, adapter: CQL::Adapter::MySQL, uri: uri) do
enduri = "postgres://#{ENV["DB_USER"]}:#{ENV["DB_PASS"]}@#{ENV["DB_HOST"]}/#{ENV["DB_NAME"]}?" +
"sslmode=verify-full&" +
"sslcert=#{ENV["SSL_CERT_PATH"]}&" +
"sslkey=#{ENV["SSL_KEY_PATH"]}&" +
"sslrootcert=#{ENV["SSL_CA_PATH"]}"# PostgreSQL
result = MyDB.exec("SHOW ssl")
puts "SSL enabled: #{result.first["ssl"]}"
# Or check connection info
MyDB.exec("SELECT pg_backend_pid()") do |rs|
puts "Connected with SSL"
enduri = "postgres://user:pass@rds-host.amazonaws.com/db?sslmode=verify-full&sslrootcert=/path/to/rds-ca-2019-root.pem"MyDB = CQL::Schema.define(:my_db, adapter: CQL::Adapter::Postgres, uri: ENV["DATABASE_URL"]) do
enddependencies:
redis:
github: stefanwille/crystal-redis
version: ~> 2.8.0require "cql"
require "redis"
MyDB = CQL::Schema.define(
:my_db,
adapter: CQL::Adapter::Postgres,
uri: ENV["DATABASE_URL"]
) do
cache_store CQL::Cache::Redis.new(
host: "localhost",
port: 6379
)
endcache_store CQL::Cache::Redis.new(
host: ENV["REDIS_HOST"]? || "localhost",
port: (ENV["REDIS_PORT"]? || "6379").to_i,
password: ENV["REDIS_PASSWORD"]?,
db: 1, # Use database 1 for cache
pool_size: 5,
timeout: 2.seconds
)cache_store CQL::Cache::Redis.new(
url: ENV["REDIS_URL"] # redis://user:pass@host:port/db
)cache_store CQL::Cache::Redis.new(
host: "localhost",
prefix: "myapp:cache:"
)MyDB = CQL::Schema.define(:my_db, adapter: CQL::Adapter::Postgres, uri: db_url) do
cache_store CQL::Cache::Redis.new(host: "localhost")
default_cache_ttl 10.minutes
end
# Override per query
User.cache(1.hour).find(1)cache_store CQL::Cache::Redis.new(
host: "localhost",
pool_size: 25,
checkout_timeout: 3.seconds
)cache_store CQL::Cache::RedisCluster.new(
nodes: [
"redis://node1:6379",
"redis://node2:6379",
"redis://node3:6379"
]
)# Manual cache operations
cache = MyDB.cache
# Set value
cache.set("custom_key", data.to_json, ttl: 1.hour)
# Get value
value = cache.get("custom_key")
# Delete
cache.delete("custom_key")
# Clear all (with prefix)
cache.clearstats = MyDB.cache.stats
puts "Hits: #{stats[:hits]}"
puts "Misses: #{stats[:misses]}"
puts "Memory: #{stats[:memory_used]}"redis-cli INFO stats
redis-cli MONITOR# Test connection
if MyDB.cache.connected?
puts "Redis connected"
else
puts "Redis connection failed"
end
# Test caching
User.cache(1.minute).find(1)
User.cache(1.minute).find(1) # Should be cached
puts MyDB.cache.statsuser = User.new("", "invalid-email")
unless user.valid?
user.errors.each do |error|
puts "#{error.field}: #{error.message}"
end
endname: can't be blankuser = User.new("John", "john@example.com") # Not empty# Remove presence validation or make it conditional
validate :bio, presence: true, on: :updateemail: is invaliduser.email = "john@example.com" # Valid email format# Make sure pattern matches expected format
validate :email, match: /\A[\w+\-.]+@[a-z\d\-.]+\.[a-z]+\z/iusername: is too short (minimum is 3 characters)user.username = "john" # At least 3 charactersvalidate :username, size: 2..50 # Allow shorter namesemail: has already been takenuser.email = "different@example.com"existing = User.find_by(email: email)
if existing
# Handle duplicate - update existing or reject
endage: must be greater than 0user.age = 25 # Positive numbervalidate :age, gt: 0, lt: 150user = User.new("", "bad", -5)
if user.valid?
puts "Valid!"
else
puts "Validation failed:"
puts "Errors count: #{user.errors.size}"
user.errors.each do |error|
puts " Field: #{error.field}"
puts " Message: #{error.message}"
puts ""
end
end# Direct database update bypasses model validation
User.where(id: 1).update!(email: "override@example.com")describe User do
it "requires a name" do
user = User.new("", "test@example.com")
user.valid?.should be_false
user.errors.map(&.field).should contain(:name)
end
it "requires valid email format" do
user = User.new("John", "invalid")
user.valid?.should be_false
user.errors.map(&.field).should contain(:email)
end
it "accepts valid data" do
user = User.new("John", "john@example.com")
user.valid?.should be_true
end
enddef create(params)
user = User.new(params[:name], params[:email])
if user.save
{status: "success", user: user.to_json}
else
{
status: "error",
errors: user.errors.map { |e| {field: e.field, message: e.message} }
}
end
endvalidate :email, required: true, message: "Please provide your email address"
validate :password, size: 8..100, message: "Password must be at least 8 characters"MyDB = CQL::Schema.define(
:my_db,
adapter: CQL::Adapter::Postgres, # or SQLite, MySql
uri: "postgres://localhost/myapp"
) do
end
MyDB.initstruct User
include CQL::ActiveRecord::Model(Int64)
db_context MyDB, :users
property id : Int64?
property name : String
property email : String
property created_at : Time?
property updated_at : Time?
def initialize(@name : String, @email : String)
end
end# Create
user = User.create!(name: "John", email: "john@example.com")
# Read
user = User.find(1)
user = User.find_by(email: "john@example.com")
users = User.where(active: true).all
# Update
user.name = "Jane"
user.save!
# Delete
user.delete!# Basic queries
User.all
User.first
User.last
User.count
# Where clauses
User.where(active: true).all
User.where { age > 18 }.all
User.where { name.like("%john%") }.all
# Chaining
User.where(active: true)
.order(created_at: :desc)
.limit(10)
.all# Define
belongs_to :user, User, :user_id
has_one :profile, Profile, :user_id
has_many :posts, Post, :user_id
# Use
user.posts.all
post.uservalidate :name, presence: true
validate :email, required: true, match: /@/
validate :age, gt: 0, lt: 150
validate :status, in: ["active", "pending"]before_save :normalize_data
after_create :send_notification
before_destroy :cleanupclass CreateUsers < CQL::Migration(1)
def up
schema.table :users do
primary :id, Int64, auto_increment: true
column :name, String, null: false
timestamps
end
schema.users.create!
end
def down
schema.users.drop!
end
endconfig = CQL::MigratorConfig.new(
schema_file_path: "src/schemas/app_schema.cr",
schema_name: :AppSchema,
auto_sync: true
)
migrator = MyDB.migrator(config)
migrator.upUser.transaction do
user = User.create!(name: "John", email: "john@example.com")
Profile.create!(user_id: user.id.not_nil!)
endinclude CQL::ActiveRecord::SoftDeletable
user.delete! # Soft delete
user.restore! # Restore
user.force_delete! # Permanent delete
User.with_deleted.all
User.only_deleted.allinclude CQL::ActiveRecord::OptimisticLocking
optimistic_locking version_column: :version
begin
user.update!
rescue CQL::OptimisticLockError
user.reload!
# Retry
end# PostgreSQL
"postgres://user:pass@host:5432/database"
# MySQL
"mysql://user:pass@host:3306/database"
# SQLite
"sqlite3://./db/app.db"
"sqlite3://:memory:"validate :field_name, option: value, option2: value2validate :name, presence: true
validate :email, presence: truevalidate :email, match: /@/
validate :phone, match: /^\d{10}$/
validate :slug, match: /^[a-z0-9-]+$/validate :password, size: 8..128 # Range
validate :username, size: 3..20
validate :tags, size: 1..5 # Array sizevalidate :age, gte: 0 # Greater than or equal to 0
validate :age, lte: 150 # Less than or equal to 150
validate :quantity, gte: 1, lte: 100 # Between 1 and 100
validate :price, gt: 0 # Greater than 0validate :status, in: ["pending", "active", "cancelled"]
validate :role, in: ["admin", "moderator", "user"]
validate :priority, in: [1, 2, 3, 4, 5]validate :username, exclude: ["admin", "root", "system"]
validate :status, exclude: ["deleted"]validate :email, presence: true, match: /@/
validate :username, presence: true, size: 3..20, match: /^[a-z0-9_]+$/
validate :age, presence: true, gte: 0, lte: 150struct User
include CQL::ActiveRecord::Model(Int64)
db_context MyDB, :users
property id : Int64?
property email : String
property username : String
property password : String
property age : Int32?
property role : String = "user"
# Required fields
validate :email, presence: true
validate :username, presence: true
validate :password, presence: true
# Format validations
validate :email, match: /^[^@\s]+@[^@\s]+\.[^@\s]+$/
validate :username, match: /^[a-z0-9_]+$/
# Length validations
validate :username, size: 3..20
validate :password, size: 8..128
# Numeric constraints
validate :age, gte: 0, lte: 150
# Allowed values
validate :role, in: ["admin", "moderator", "user"]
enduser = User.new("test", "test@example.com", "password123")
if user.valid?
user.save
else
user.errors.each do |error|
puts error
end
end
# Or use save! which raises on validation failure
begin
user.save!
rescue CQL::ValidationError => e
puts "Validation failed: #{e.message}"
endstruct Order
include CQL::ActiveRecord::Model(Int64)
property id : Int64?
property total : Float64
property discount : Float64 = 0.0
before_save :validate_discount
private def validate_discount
if @discount > @total
errors.add(:discount, "cannot exceed total")
return false
end
true
end
end┌──────────────┐
│ before_save │ ─── Runs before INSERT or UPDATE
├──────────────┤
│ save │ ─── Database operation
├──────────────┤
│ after_save │ ─── Runs after INSERT or UPDATE
└──────────────┘struct User
include CQL::ActiveRecord::Model(Int64)
before_save :normalize_email
private def normalize_email
@email = @email.downcase.strip
true # Must return true to continue
end
endstruct Order
after_save :update_inventory
private def update_inventory
order_items.each do |item|
item.product.decrement_stock!(item.quantity)
end
true
end
endstruct User
before_create :set_defaults
private def set_defaults
@role ||= "member"
@created_at = Time.utc
true
end
endstruct User
after_create :send_welcome_email
private def send_welcome_email
Mailer.welcome(@email).deliver
true
end
endstruct Post
before_update :track_changes
private def track_changes
@previous_status = @status_was if status_changed?
true
end
endstruct Post
after_update :notify_subscribers
private def notify_subscribers
if @published && !@published_was
subscribers.each do |sub|
Mailer.new_post(sub, self).deliver
end
end
true
end
endstruct User
before_destroy :check_can_delete
private def check_can_delete
if posts.any?
errors.add(:base, "Cannot delete user with posts")
return false
end
true
end
endstruct User
after_destroy :cleanup_files
private def cleanup_files
FileUtils.rm_rf("/uploads/users/#{@id}")
true
end
end# Stops the save operation
private def validate_something
if some_condition_fails
errors.add(:field, "error message")
return false # Halts operation
end
true # Continues operation
endstruct Article
include CQL::ActiveRecord::Model(Int64)
db_context MyDB, :articles
property id : Int64?
property title : String
property slug : String?
property body : String
property published : Bool = false
property published_at : Time?
property view_count : Int32 = 0
# Before any save
before_save :generate_slug
# Only on create
before_create :set_defaults
after_create :notify_admin
# Only on update
before_update :check_publish_state
after_update :invalidate_cache
# On destroy
before_destroy :archive_content
after_destroy :cleanup
private def generate_slug
@slug = @title.downcase.gsub(/[^a-z0-9]+/, "-")
true
end
private def set_defaults
@view_count = 0
true
end
private def notify_admin
AdminMailer.new_article(self).deliver
true
end
private def check_publish_state
if @published && !published_was
@published_at = Time.utc
end
true
end
private def invalidate_cache
Cache.delete("article:#{@id}")
Cache.delete("articles:list")
true
end
private def archive_content
Archive.create!(content: to_json, type: "Article")
true
end
private def cleanup
Cache.delete("article:#{@id}")
true
end
endstruct User # Recommended
include CQL::ActiveRecord::Model(Int64)
endproperty id : Int64? # Correct - nil until saved@[DB::Field(ignore: true)]
property temp_value : String?results = MyDB.exec("SELECT * FROM users WHERE id = ?", [1])Log.setup do |c|
c.bind("cql.*", :debug, Log::IOBackend.new)
endposts = Post.where(published: true).all
user_ids = posts.map(&.user_id).uniq
users = User.where { id.in(user_ids) }.all.index_by(&.id)class AddAvatarToUsers < CQL::Migration(5)
def up
schema.alter :users do
add_column :avatar_url, String, null: true
end
end
def down
schema.alter :users do
drop_column :avatar_url
end
end
endmigrator.down # Rollback one
migrator.down_to(3) # Rollback to version 3DELETE FROM cql_schema_migrations WHERE version = 5;# Bad: N+1
posts.each { |p| puts p.user.name }
# Good: Batch load
user_ids = posts.map(&.user_id).uniq
users = User.where { id.in(user_ids) }.all.index_by(&.id)
posts.each { |p| puts users[p.user_id]?.try(&.name) }migrator.upunless model.valid?
model.errors.each do |e|
puts "#{e.field}: #{e.message}"
end
endUser.transaction do
user = User.create!(...)
Profile.create!(user_id: user.id.not_nil!)
endbefore_each do
# Clean database
User.delete_all
end
it "creates a user" do
user = User.create!(name: "Test", email: "test@example.com")
user.id.should_not be_nil
end┌──────────────┐ ┌──────────────┐ ┌──────────────┐
│ Domain │────>│ Repository │────>│ Database │
│ Objects │<────│ │<────│ │
└──────────────┘ └──────────────┘ └──────────────┘# Entity - no database knowledge
struct User
property id : Int64?
property name : String
property email : String
def full_name
name.split.map(&.capitalize).join(" ")
end
end
# Repository - handles persistence
class UserRepository
def find(id : Int64) : User?
# Database query
end
def save(user : User) : Bool
# Insert or update
end
endclass MockUserRepository
def find(id : Int64) : User?
User.new("Test User", "test@example.com")
end
end
# In tests
service = UserService.new(MockUserRepository.new)abstract class UserRepository
abstract def find(id : Int64) : User?
abstract def save(user : User) : Bool
end
class PostgresUserRepository < UserRepository
# PostgreSQL implementation
end
class ApiUserRepository < UserRepository
# REST API implementation
endstruct User
property id : Int64?
property name : String
property email : String
property created_at : Time?
def initialize(@name : String, @email : String)
end
def valid? : Bool
!name.empty? && email.includes?("@")
end
end# This is internal to the repository
struct UserRecord
include CQL::ActiveRecord::Model(Int64)
db_context MyDB, :users
property id : Int64?
property name : String
property email : String
property created_at : Time?
def initialize(@name : String, @email : String)
end
def to_entity : User
user = User.new(@name, @email)
user.id = @id
user.created_at = @created_at
user
end
def self.from_entity(user : User) : UserRecord
record = new(user.name, user.email)
record.id = user.id
record
end
endclass UserRepository
def find(id : Int64) : User?
record = UserRecord.find(id)
record.try(&.to_entity)
end
def find_by_email(email : String) : User?
record = UserRecord.find_by(email: email)
record.try(&.to_entity)
end
def all : Array(User)
UserRecord.all.map(&.to_entity)
end
def save(user : User) : Bool
record = UserRecord.from_entity(user)
if record.save
user.id = record.id
true
else
false
end
end
def delete(user : User) : Bool
return false unless user.id
record = UserRecord.find(user.id.not_nil!)
record.try(&.delete!) || false
end
def active_users : Array(User)
UserRecord.where(active: true).all.map(&.to_entity)
end
endrepo = UserRepository.new
# Create
user = User.new("John", "john@example.com")
repo.save(user)
# Find
found = repo.find(user.id.not_nil!)
# Query
active = repo.active_users
# Delete
repo.delete(user)# Active Record
user = User.find(1)
user.name = "Updated"
user.save
# Repository
user = user_repo.find(1)
user.name = "Updated"
user_repo.save(user)# Simple entities use Active Record directly
post = Post.find(1)
post.title = "Updated"
post.save
# Complex domain uses Repository
order = order_repo.find(1)
order.add_item(product, quantity)
order_repo.save(order)class AddEmailIndex < CQL::Migration(5)
def up
schema.alter :users do
create_index :idx_users_email, [:email]
end
end
def down
schema.alter :users do
drop_index :idx_users_email
end
end
endclass AddUniqueEmailIndex < CQL::Migration(6)
def up
schema.alter :users do
create_index :idx_users_email, [:email], unique: true
end
end
def down
schema.alter :users do
drop_index :idx_users_email
end
end
endclass AddUserStatusIndex < CQL::Migration(7)
def up
schema.alter :users do
create_index :idx_users_status_created, [:status, :created_at]
end
end
def down
schema.alter :users do
drop_index :idx_users_status_created
end
end
endclass AddPostIndexes < CQL::Migration(8)
def up
schema.alter :posts do
create_index :idx_posts_user_id, [:user_id]
create_index :idx_posts_category_id, [:category_id]
end
end
def down
schema.alter :posts do
drop_index :idx_posts_user_id
drop_index :idx_posts_category_id
end
end
endclass AddFilterIndexes < CQL::Migration(9)
def up
schema.alter :posts do
create_index :idx_posts_published, [:published]
create_index :idx_posts_created_at, [:created_at]
end
end
def down
schema.alter :posts do
drop_index :idx_posts_published
drop_index :idx_posts_created_at
end
end
endclass AddDeletedAtIndex < CQL::Migration(10)
def up
schema.alter :users do
create_index :idx_users_deleted_at, [:deleted_at]
end
end
def down
schema.alter :users do
drop_index :idx_users_deleted_at
end
end
endclass AddUniquePostTag < CQL::Migration(11)
def up
schema.alter :post_tags do
create_index :idx_post_tags_unique, [:post_id, :tag_id], unique: true
end
end
def down
schema.alter :post_tags do
drop_index :idx_post_tags_unique
end
end
endidx_{table}_{column} # Simple index
idx_{table}_{col1}_{col2} # Composite index
idx_{table}_{column}_unique # Unique index (optional suffix)migrator.up
# Index should speed up this query
User.where(email: "john@example.com").first# Method 1: new + save
user = User.new(username: "john", email: "john@example.com")
if user.save
puts "User created with ID: #{user.id}"
else
puts "Failed to create user"
end
# Method 2: create! (raises on failure)
user = User.create!(
username: "jane",
email: "jane@example.com",
first_name: "Jane"
)
# Method 3: create (returns the record, check persisted?)
user = User.create(username: "bob", email: "bob@example.com")
if user.persisted?
puts "User created"
end# Create a user first
author = User.create!(username: "alice", email: "alice@example.com")
# Create a category
tech = Category.create!(name: "Technology")
# Create a post with relationships
post = Post.create!(
title: "My First Post",
content: "Hello, world! This is my first blog post.",
user_id: author.id.not_nil!,
category_id: tech.id,
published: true
)
# Create a comment on the post
comment = Comment.create!(
content: "Welcome to blogging!",
post_id: post.id.not_nil!,
user_id: author.id
)# Create sample categories
categories = ["Technology", "Lifestyle", "Travel", "Food"].map do |name|
Category.create!(name: name)
end
puts "Created #{categories.size} categories"# Find returns nil if not found
user = User.find(1)
if user
puts "Found: #{user.username}"
else
puts "User not found"
end
# Find! raises if not found
begin
user = User.find!(999)
rescue ex
puts "User not found: #{ex.message}"
end# Find first matching record
user = User.find_by(email: "alice@example.com")
puts "Found user: #{user.try(&.username)}"
# Find with multiple conditions
post = Post.find_by(published: true, user_id: 1)# Get all records
all_users = User.all
puts "Total users: #{all_users.size}"
# Filter with where
active_users = User.where(active: true).all
published_posts = Post.where(published: true).all
# Chain conditions
recent_published = Post
.where(published: true)
.order(created_at: :desc)
.limit(10)
.all
# Get first/last
first_user = User.first
last_post = Post.last# Multiple conditions
posts = Post
.where(published: true)
.where(user_id: author.id)
.order(views_count: :desc)
.all
# Using blocks for complex conditions
popular_posts = Post.where { views_count > 100 }.all
recent_posts = Post.where { created_at > 1.week.ago }.all
# Combining conditions
featured = Post
.where(published: true)
.where { views_count > 50 }
.order(created_at: :desc)
.limit(5)
.all# Get user's posts
user = User.find(1)
user_posts = user.posts.all
# Filter user's posts
published_user_posts = user.posts.where(published: true).all
# Get post's comments
post = Post.find(1)
comments = post.comments.all
# Get posts in a category
category = Category.find_by(slug: "technology")
tech_posts = category.posts.where(published: true).all if category# Simple pagination
page = 1
per_page = 10
posts = Post
.where(published: true)
.order(created_at: :desc)
.limit(per_page)
.offset((page - 1) * per_page)
.all
# Get total for pagination info
total = Post.where(published: true).count
total_pages = (total / per_page.to_f).ceil.to_i# Method 1: Change attributes and save
user = User.find(1)
if user
user.first_name = "Alice"
user.last_name = "Smith"
user.save
puts "User updated"
end
# Method 2: update! with attributes
post = Post.find(1)
post.try(&.update!(published: true, views_count: 100_i64))
# Method 3: Using update on query
Post.where(id: 1).update!(published: true)# Increment view count
post = Post.find(1)
if post
post.views_count += 1
post.save
end
# Or use a custom method (defined in model)
post.try(&.increment_views!)# Publish all draft posts by a user
Post.where(user_id: 1, published: false).update!(published: true)
# Deactivate users who haven't posted
# (This would require a more complex query in practice)
User.where(active: true).each do |user|
if user.posts.count == 0
user.active = false
user.save
end
end# Method 1: Find and delete
user = User.find(1)
user.try(&.delete!)
puts "User deleted"
# Method 2: Using destroy (triggers callbacks)
post = Post.find(1)
post.try(&.destroy!)# Delete all comments on a post
Comment.where(post_id: 1).delete!
# Delete all unpublished posts
Post.where(published: false).delete!
# Delete old anonymous comments
Comment.where(user_id: nil).where { created_at < 1.year.ago }.delete!# This deletes the user AND all their posts AND all comments on those posts
user = User.find(1)
user.try(&.delete!)# Count all
total_users = User.count
total_posts = Post.count
# Count with conditions
published_count = Post.where(published: true).count
active_users = User.where(active: true).count
# Count through relationships
user = User.find(1)
post_count = user.posts.count if user# Total views across all published posts
total_views = Post.where(published: true).sum(:views_count)
# Average views
avg_views = Post.where(published: true).avg(:views_count)
# Most views
max_views = Post.where(published: true).max(:views_count)
# Least views
min_views = Post.where(published: true).min(:views_count)# Posts per category
Category.all.each do |category|
count = category.posts.where(published: true).count
puts "#{category.name}: #{count} posts"
end
# Most active authors
User.all.sort_by { |u| -u.posts.count }.first(5).each do |user|
puts "#{user.display_name}: #{user.posts.count} posts"
end# Process all posts in batches
Post.find_each(batch_size: 100) do |post|
# Process each post
puts "Processing: #{post.title}"
enddef dashboard_stats
{
total_users: User.count,
active_users: User.where(active: true).count,
total_posts: Post.count,
published_posts: Post.where(published: true).count,
draft_posts: Post.where(published: false).count,
total_comments: Comment.count,
total_views: Post.sum(:views_count)
}
end
stats = dashboard_stats
puts "Users: #{stats[:total_users]} (#{stats[:active_users]} active)"
puts "Posts: #{stats[:published_posts]} published, #{stats[:draft_posts]} drafts"
puts "Total views: #{stats[:total_views]}"def recent_activity(limit = 10)
posts = Post.where(published: true)
.order(created_at: :desc)
.limit(limit)
.all
posts.map do |post|
{
type: "post",
title: post.title,
author: post.user.try(&.display_name) || "Unknown",
date: post.created_at
}
end
enddef search_posts(query : String)
Post.where(published: true)
.where { title.like("%#{query}%") | content.like("%#{query}%") }
.order(views_count: :desc)
.all
end
results = search_posts("crystal")
puts "Found #{results.size} posts matching 'crystal'"migrations/
├── 001_create_users.cr
├── 002_create_posts.cr
└── 003_add_email_index.cr# migrations/001_create_users.cr
class CreateUsers < CQL::Migration(1)
def up
schema.table :users do
primary :id, Int64, auto_increment: true
column :name, String, null: false
column :email, String, null: false
timestamps
end
schema.users.create!
end
def down
schema.users.drop!
end
endclass CreateUsers < CQL::Migration(1) # Sequential
class CreatePosts < CQL::Migration(2)
class AddIndexes < CQL::Migration(20250125120000) # Timestamp formatdef up
schema.table :posts do
primary :id, Int64, auto_increment: true
column :title, String, null: false
column :body, String, null: false
column :published, Bool, default: false
column :user_id, Int64, null: false
timestamps
foreign_key [:user_id], references: :users, references_columns: [:id]
index [:user_id]
index [:published]
end
schema.posts.create!
end
def down
schema.posts.drop!
endclass AddAvatarToUsers < CQL::Migration(4)
def up
schema.alter :users do
add_column :avatar_url, String, null: true
add_column :bio, String, null: true
end
end
def down
schema.alter :users do
drop_column :avatar_url
drop_column :bio
end
end
endclass AddEmailIndex < CQL::Migration(5)
def up
schema.alter :users do
create_index :idx_users_email, [:email], unique: true
end
end
def down
schema.alter :users do
drop_index :idx_users_email
end
end
endclass AddPostsForeignKey < CQL::Migration(6)
def up
schema.alter :posts do
foreign_key [:user_id], references: :users, references_columns: [:id], on_delete: :cascade
end
end
def down
schema.alter :posts do
drop_foreign_key :fk_posts_user_id
end
end
endclass RenameUserName < CQL::Migration(7)
def up
schema.alter :users do
rename_column :name, :full_name
end
end
def down
schema.alter :users do
rename_column :full_name, :name
end
end
endclass ChangeViewsCount < CQL::Migration(8)
def up
schema.alter :posts do
change_column :views_count, Int64
end
end
def down
schema.alter :posts do
change_column :views_count, Int32
end
end
endclass RemoveOldColumn < CQL::Migration(9)
def up
schema.alter :users do
drop_column :legacy_field
end
end
def down
raise "Cannot reverse: data would be lost"
end
end# Test migration
MyDB.init
migrator = MyDB.migrator
migrator.up
# Check table exists
User.count # Should not raiseclass CreateUsers < CQL::Migration(20250125001401)
def up
# Apply changes
end
def down
# Rollback changes
end
endclass CreateUsers < CQL::Migration(1)
def up
schema.table :users do
primary :id, Int64, auto_increment: true
column :name, String, null: false
column :email, String, null: false
column :active, Bool, default: true
timestamps
index [:email], unique: true
end
schema.users.create!
end
def down
schema.users.drop!
end
endprimary :id, Int64 # Default auto_increment: true
primary :id, Int32, auto_increment: true
primary :uuid, String, auto_increment: falsecolumn :name, String # Required string
column :bio, String, null: true # Nullable
column :active, Bool, default: true # With default
column :email, String, null: false, unique: true
column :score, Int32, default: 0, index: true# String types
varchar :name, size: 255
text :description
# Numeric types
integer :age, null: false, default: 18
bigint :balance, default: 0
float :price, default: 0.0
double :rating
real :measurement
# Other types
boolean :active, default: true
timestamp :created_at
date :birthday
json :metadataschema.table :posts do
primary :id, Int64
column :title, String
timestamps
endschema.table :users do
column :email, String
column :first_name, String
column :last_name, String
index [:email], unique: true # Unique index
index [:email] # Regular index
index [:first_name, :last_name] # Composite index
endschema.alter :users do
create_index :email_idx, [:email], unique: true
drop_index :email_idx
endschema.table :posts do
primary :id, Int64
column :user_id, Int64, null: false
column :category_id, Int64, null: true
foreign_key [:user_id], references: :users, references_columns: [:id]
foreign_key [:category_id], references: :categories, references_columns: [:id]
endforeign_key [:order_id, :product_id],
references: :order_items,
references_columns: [:o_id, :p_id]class AddPhoneToUsers < CQL::Migration(2)
def up
schema.alter :users do
add_column :phone, String, null: true
add_column :age, Int32, null: true, default: 18
end
end
def down
schema.alter :users do
drop_column :phone
drop_column :age
end
end
endschema.alter :users do
# Columns
add_column :phone, String, null: true
drop_column :legacy_field
rename_column :email, :user_email
change_column :age, String
# Indexes
create_index :phone_idx, [:phone]
drop_index :old_idx
# Foreign keys
foreign_key [:department_id], references: :departments, on_delete: :cascade
drop_foreign_key :fk_old_reference
endclass CreateBlogSchema < CQL::Migration(1)
def up
# Create users table
schema.table :users do
primary :id, Int64, auto_increment: true
column :email, String, null: false
column :username, String, null: false
column :role, String, default: "member"
timestamps
index [:email], unique: true
index [:username], unique: true
end
schema.users.create!
# Create posts table
schema.table :posts do
primary :id, Int64, auto_increment: true
column :user_id, Int64, null: false
column :title, String, null: false
column :body, String, null: false
column :published, Bool, default: false
column :views_count, Int64, default: 0
timestamps
foreign_key [:user_id], references: :users, references_columns: [:id]
index [:user_id]
index [:published]
end
schema.posts.create!
end
def down
schema.posts.drop!
schema.users.drop!
end
endmigrator = schema.migrator
# Apply migrations
migrator.up # Apply all pending
migrator.up(1) # Apply 1 migration
# Rollback migrations
migrator.down # Rollback all
migrator.down(1) # Rollback 1 migration
migrator.rollback # Alias for down(1)
# Other operations
migrator.redo # Rollback and reapply last
migrator.up_to(version) # Apply up to specific version
migrator.down_to(version) # Rollback to specific version
# Status
migrator.applied_migrations # List applied migrations
migrator.pending_migrations # List pending migrations
migrator.last # Get last applied migrationstruct User
include CQL::ActiveRecord::Model(Int64)
db_context MyDB, :users
property id : Int64?
property name : String
property email : String
property active : Bool = true
# Business logic in the model
def activate!
@active = true
save
end
def deactivate!
@active = false
save
end
end
# Usage - the model handles its own persistence
user = User.new("John", "john@example.com")
user.save # Model saves itself
user.activate! # Business logic + persistence togetheruser = User.new("John", "john@example.com")
user.save # INSERT
user.name = "Jane"
user.save # UPDATE
user.delete! # DELETEUser.find(1)
User.where(active: true)
User.count
User.allstruct User
validate :email, presence: true, match: /@/
validate :name, presence: true
end
user = User.new("", "invalid")
user.valid? # false
user.save # won't save, returns falsestruct User
before_save :normalize_email
after_create :send_welcome_email
private def normalize_email
@email = @email.downcase.strip
true
end
end# Create a user, save it, find it, update it
user = User.create!(name: "John", email: "john@example.com")
found = User.find(user.id)
found.name = "Jane"
found.savestruct Post
include CQL::ActiveRecord::Model(Int64)
db_context MyDB, :posts
property id : Int64?
property title : String
property body : String
property user_id : Int64
belongs_to :user, User, :user_id
has_many :comments, Comment, :post_id
validate :title, presence: true
before_save :update_slug
endstruct Order
include CQL::ActiveRecord::Model(Int64)
db_context MyDB, :orders
property id : Int64?
property user_id : Int64
property status : String = "pending"
property total : BigDecimal = BigDecimal.new(0)
belongs_to :user, User, :user_id
has_many :order_items, OrderItem, :order_id
validate :status, in: ["pending", "paid", "shipped", "completed", "cancelled"]
before_save :calculate_total
after_save :notify_status_change
def pay!
@status = "paid"
save
end
def ship!
raise "Cannot ship unpaid order" unless status == "paid"
@status = "shipped"
save
end
private def calculate_total
@total = order_items.all.sum(&.subtotal)
true
end
private def notify_status_change
# Send notification
true
end
endgit checkout -b feature/my-new-featuregit clone https://github.com/azutoolkit/cql.git
cd cqlshards installcrystal speccrystal speccrystal tool formatgit commit -m "Add feature: description"git push origin feature/my-new-feature