CQL (Crystal Query Language)

A high-performance, type-safe ORM for Crystal applications that combines compile-time safety with runtime performance. Unlike traditional ORMs that catch errors at runtime, CQL validates your queries, relationships, and data access patterns before your code executes.

"We migrated our Rails API to Crystal + CQL and saw response times drop from 200ms to 45ms while handling 3x more concurrent users." - Production User

Complete Documentation →

Why Choose CQL

Performance

4x faster than ActiveRecord and Eloquent in real-world scenarios
75% less memory usage compared to Ruby/PHP ORMs
Zero-allocation queries for maximum throughput
Compile-time optimizations eliminate runtime overhead

Type Safety

Catch errors at compile time - invalid queries fail before deployment
Full IDE autocompletion support for queries and relationships
Safe refactoring - rename columns/tables with confidence
No runtime surprises - association errors caught early

Developer Experience

Familiar ActiveRecord-style API - easy migration from Rails/Laravel
Rich query DSL with readable, type-safe syntax
Automatic schema synchronization - database changes tracked and versioned
Built-in performance monitoring with N+1 query detection

Core Features

Type-Safe ORM: Leverage Crystal's static type system for compile-time safety
High Performance: 4x faster than traditional ORMs with compile-time optimizations
Active Record Pattern: Intuitive Active Record API with full CRUD operations
Smart Relationships: Support for belongs_to, has_one, has_many, and many_to_many with automatic N+1 prevention
Comprehensive Validations: Built-in validation system with custom validator support
Lifecycle Callbacks: Before/after hooks for validation, save, create, update, and destroy
Intelligent Migrations: Schema evolution tools with automatic rollback support
Schema Dump: Reverse-engineer existing databases into CQL schema definitions
Flexible Querying: Fluent query builder with complex joins, subqueries, and raw SQL support
Transaction Support: Full ACID transaction support with nested transactions (savepoints)
Optimistic Locking: Built-in support for optimistic concurrency control
Query Scopes: Reusable query scopes for common filtering patterns
Advanced Caching: Multi-layer caching with Redis and memory cache support
Performance Monitoring: Built-in query profiling, N+1 detection, and optimization suggestions
Multi-Database: Support for PostgreSQL, MySQL, and SQLite with dialect-specific optimizations
Flexible Primary Keys: Support for Int32, Int64, UUID, and ULID primary keys

Performance Comparison

Real-world benchmarks (1M records, complex queries):

| Operation | CQL | ActiveRecord | Eloquent | Improvement | | ----------------- | ----- | ------------ | -------- | --------------- | | Simple SELECT | 0.8ms | 3.2ms | 4.1ms | 4x faster | | Complex JOIN | 2.1ms | 8.7ms | 12.3ms | 4-6x faster | | Bulk INSERT | 15ms | 89ms | 124ms | 6-8x faster | | Memory Usage | 12MB | 48MB | 67MB | 75% less |

Database Support

| Database | Support Level | Special Features | | -------------- | ------------- | --------------------------------- | | PostgreSQL | Full | JSONB, Arrays, Advanced Types | | MySQL | Full | Complete MySQL support | | SQLite | Full | Perfect for development & testing |

Installation

Add CQL and your database driver to your shard.yml:

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

  # Choose your database driver:
  pg: # For PostgreSQL
    github: will/crystal-pg
  mysql: # For MySQL
    github: crystal-lang/crystal-mysql
  sqlite3: # For SQLite
    github: crystal-lang/crystal-sqlite3

Then install dependencies:

shards install

Quick Start

1. Define Your Schema

require "cql"
require "sqlite3"  # or "pg" or "mysql"

# Define your database schema with compile-time validation
BlogDB = CQL::Schema.define(
  :blog_database,
  adapter: CQL::Adapter::SQLite,
  uri: "sqlite3://db/blog.db"
) do
  table :users do
    primary :id, Int64
    text :username
    text :email
    text :first_name, null: true
    text :last_name, null: true
    boolean :active, default: "1"
    timestamps
  end

  table :posts do
    primary :id, Int64
    text :title
    text :content
    boolean :published, default: "0"
    bigint :user_id
    timestamps

    # Type-safe foreign key relationships
    foreign_key [:user_id], references: :users, references_columns: [:id]
  end
end

# Create tables (with automatic validation)
BlogDB.users.create!
BlogDB.posts.create!

2. Create Models

struct User
  getter id : Int64?
  getter username : String
  getter email : String
  getter first_name : String?
  getter last_name : String?
  getter? active : Bool = true
  getter created_at : Time?
  getter updated_at : Time?

  # Compile-time validated relationships
  has_many :posts, foreign_key: :user_id

  # Built-in validations with clear error messages
  validate :username, presence: true, size: 2..50
  validate :email, required: true, match: /\A[\w+\-.]+@[a-z\d\-.]+\.[a-z]+\z/i

  def initialize(@username : String, @email : String,
                 @first_name : String? = nil, @last_name : String? = nil)
  end

  def full_name
    if first_name && last_name
      "#{first_name} #{last_name}"
    else
      username
    end
  end
end

struct Post
  getter id : Int64?
  getter title : String
  getter content : String
  getter? published : Bool = false
  getter user_id : Int64
  getter created_at : Time?
  getter updated_at : Time?

  # Type-safe relationships prevent association errors
  belongs_to :user, User, foreign_key: :user_id

  # Comprehensive validations
  validate :title, presence: true, size: 1..100
  validate :content, presence: true

  def initialize(@title : String, @content : String, @user_id : Int64)
  end
end

3. Work with Your Data

# Create with automatic validation
user = User.new("alice_j", "alice@example.com", "Alice", "Johnson")
if user.save
  puts "✅ User created with ID: #{user.id}"
else
  puts "❌ Validation errors: #{user.errors.map(&.message)}"
end

# Type-safe queries with IntelliSense support
alice = User.find_by(username: "alice_j")
active_users = User.where(active: true).all

# Create associated records (no N+1 queries!)
post = user.posts.create(title: "My First Post", content: "Hello, World!")

# Safe transactions with automatic rollback
User.transaction do |tx|
  user = User.create!(username: "bob", email: "bob@example.com")
  post = user.posts.create!(title: "Bob's Post", content: "Content here")

  # If anything fails, everything rolls back automatically
  # No partial data corruption!
end

# Advanced querying with type safety
published_posts = Post.where(published: true)
                     .joins(:user)
                     .where(users: {active: true})
                     .order(created_at: :desc)
                     .limit(10)
                     .all

# Complex queries made simple
recent_active_authors = User.joins(:posts)
                           .where("posts.created_at > ?", 1.week.ago)
                           .where(active: true)
                           .distinct
                           .count

puts "Found #{recent_active_authors} active authors this week"

Advanced Features

Type-Safe Schema Definition

# Schema with advanced features
BlogDB = CQL::Schema.define(:blog, adapter: CQL::Adapter::Postgres, uri: ENV["DATABASE_URL"]) do
  table :products do
    primary :id, UUID                    # UUID primary keys
    text :name
    decimal :price, precision: 10, scale: 2
    text :metadata                       # JSON columns
    timestamps

    # Optimized indexing
    index :name, unique: true
    index [:price, :created_at]          # Composite indexes for performance
  end
end

Active Record Pattern

struct Product
  include CQL::ActiveRecord::Model(UUID)
  db_context BlogDB, :products

  getter id : UUID?
  getter name : String
  getter price : Float64
  getter created_at : Time?
  getter updated_at : Time?

  # Custom validations with clear error messages
  validate :name, presence: true, size: 2..100
  validate :price, gt: 0.0, lt: 1_000_000.0

  def initialize(@name : String, @price : Float64)
  end
end

# CRUD operations with validation
product = Product.create!(name: "Laptop", price: 999.99)
product = Product.find(product.id.not_nil!)

# Efficient querying with type safety
affordable_products = Product.where("price < ?", 1000.0)
                             .order(:name)
                             .limit(50)
                             .all

# Safe updates with validation
product.price = 899.99
product.save!  # Validates before saving

# Safe deletion
product.destroy!

Validations

struct User
  include CQL::ActiveRecord::Model(Int64)

  # Built-in validations with internationalization support
  validate :name, presence: true, size: 2..50
  validate :email, required: true, match: /\A[\w+\-.]+@[a-z\d\-.]+\.[a-z]+\z/i
  validate :age, gt: 0, lt: 120
  validate :passwo

Cql

Install / Use

README