cql v0.0.435

CQL Toolkit is a comprehensive library designed to simplify and enhance the management and execution of SQL queries in Crystal. This toolkit provides utilities for building, validating, and executing SQL statements with ease, ensuring better performance and code maintainability.

Crystal CI Codacy Badge

๐Ÿš€ CQL (Crystal Query Language)

cql-banner

The High-Performance, Type-Safe ORM that Crystal Developers Love

CQL is a powerful Object-Relational Mapping (ORM) library for the Crystal programming language that combines blazing-fast performance with compile-time safety. Unlike traditional ORMs that catch errors at runtime, CQL validates your queries, relationships, and data access patterns before your code even runs.

"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 Developers Choose CQL

โšก Performance That Actually Matters

  • 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 That Prevents Bugs

  • Catch Errors at Compile Time - Invalid queries fail before deployment
  • IDE Autocompletion - Full IntelliSense support for queries and relationships
  • Refactoring Safety - Rename columns/tables with confidence
  • No More Runtime Surprises - Association errors caught early

๐Ÿ—๏ธ Developer Experience That Scales

  • Familiar ActiveRecord-style API - Easy migration from Rails/Laravel
  • Rich Query DSL - Write complex queries with readable, type-safe syntax
  • Automatic Schema Sync - Database changes tracked and versioned
  • Built-in Performance Monitoring - N+1 query detection and optimization hints

๐ŸŽฏ 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.374"

  # 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 - Build Something Amazing in 5 Minutes

1. Define Your Schema (Type-Safe Schema Definition)

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 (With Built-in Validations & Relationships)

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, Post, 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, :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 (Type-Safe Operations)

# 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 That Scale

Type-Safe Schema Definition

# Enterprise-grade 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

Powerful 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!

Enterprise-Grade 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 :password_confirmation, confirmation: :password

  # Custom business logic validators
  use CustomPasswordValidator
  use BusinessRuleValidator
end

# Comprehensive error handling
user = User.new("", "invalid-email")
unless user.valid?
  user.errors.each do |error|
    puts "๐Ÿšซ #{error.field}: #{error.message}"
  end
end

Smart Relationships (No More N+1 Queries)

struct User
  # Type-safe relationship definitions
  has_one :profile, UserProfile
  has_many :posts, Post, foreign_key: :user_id
  has_many :comments, Comment
end

struct Post
  belongs_to :user, User, :user_id
  has_many :comments, Comment
  many_to_many :tags, Tag, join_through: :post_tags
end

# Efficient association loading (automatic N+1 prevention)
user = User.find(1.to_i64)
user.posts.create(title: "New Post", content: "Content")
user.posts.size                         # Efficient count without loading all records
user.posts.any?                         # Check existence without memory overhead

# Smart eager loading
users_with_posts = User.includes(:posts, :profile)
                      .where(active: true)
                      .all
# Single query instead of N+1 queries!

Advanced Transaction Management

# Simple atomic transactions
User.transaction do |tx|
  user = User.create!(username: "john", email: "john@example.com")
  user.posts.create!(title: "First Post", content: "Hello!")

  # Automatic rollback on any exception
  raise "Error!" if some_condition  # Everything safely rolls back
end

# Nested transactions with savepoints (PostgreSQL)
User.transaction do |outer_tx|
  user = User.create!(username: "alice", email: "alice@example.com")

  User.transaction(outer_tx) do |inner_tx|
    # Independent rollback scope
    risky_operation()
  rescue
    inner_tx.rollback  # Only inner transaction rolls back
  end

  # Outer transaction continues safely
end

Intelligent Schema Migrations

# Version-controlled database evolution
class CreateUsersTable < CQL::Migration(20240101120000)
  def up
    schema.users.create!
  end

  def down
    schema.users.drop!
  end
end

class AddEmailToUsers < CQL::Migration(20240102120000)
  def up
    schema.alter :users do
      add_column :email, String, null: false
      create_index :idx_users_email, [:email], unique: true
    end
  end

  def down
    schema.alter :users do
      drop_index :idx_users_email
      drop_column :email
    end
  end
end

# Safe migration management
migrator = CQL::Migrator.new(BlogDB)
migrator.up           # Apply all pending migrations
migrator.down(1)      # Rollback last migration safely
migrator.status       # Check migration status

Reusable Query Scopes

struct Post
  # Define reusable query patterns
  scope :published, ->{ where(published: true) }
  scope :recent, ->{ where("created_at > ?", 1.week.ago).order(created_at: :desc) }
  scope :by_user, ->(user_id : Int64) { where(user_id: user_id) }
  scope :popular, ->{ where("view_count > ?", 1000) }
end

# Chainable, composable queries
trending_posts = Post.published
                    .recent
                    .popular
                    .limit(10)
                    .all

user_content = Post.by_user(user.id.not_nil!)
                  .published
                  .order(created_at: :desc)
                  .all

Reverse Engineering with Schema Dump

# Import existing databases into CQL
require "cql"

# Connect to legacy database
dumper = CQL::SchemaDump.new(CQL::Adapter::SQLite, "sqlite3://legacy_app.db")

# Generate type-safe CQL schema from existing database
dumper.dump_to_file("src/schemas/legacy_schema.cr", :LegacyDB, :legacy_db)

# Generated schema uses proper CQL methods with type safety:
# text :name               # instead of generic column definitions
# integer :user_id         # with proper type inference
# timestamps               # standardized timestamp handling

dumper.close

# Now use your legacy database with full CQL features!

Multi-Layer Caching Architecture

# Enterprise-grade caching configuration
cache_config = CQL::Cache::CacheConfig.new(
  enabled: true,
  ttl: 1.hour,
  max_size: 10_000,
  compression: true
)

# Memory cache for lightning-fast access
memory_cache = CQL::Cache::MemoryCache.new(max_size: 1000)

# Redis cache for distributed applications
redis_cache = CQL::Cache::RedisCache.new("redis://localhost:6379")

# Fragment caching for expensive operations
fragment_cache = CQL::Cache::FragmentCache.new(memory_cache)

# Intelligent caching with automatic invalidation
result = fragment_cache.cache_fragment("expensive_query", {"user_id" => user.id}) do
  # Expensive database operation cached automatically
  User.joins(:posts, :comments)
      .where(active: true)
      .includes(:profile)
      .complex_aggregation
end

# Tag-based cache invalidation
fragment_cache.invalidate_tags(["user:#{user.id}", "posts"])

Built-in Performance Monitoring

# Comprehensive performance monitoring
monitor = CQL::Performance::PerformanceMonitor.new

# Real-time query monitoring
monitor.on_query_executed do |event|
  if event.duration > 100.milliseconds
    puts "๐ŸŒ Slow query detected: #{event.sql} (#{event.duration}ms)"
    puts "๐Ÿ’ก Consider adding an index or optimizing the query"
  end
end

# Automatic N+1 query detection
detector = CQL::Performance::NPlusOneDetector.new
detector.analyze_queries(queries) do |pattern|
  puts "โš ๏ธ  N+1 Query Pattern Detected:"
  puts "   Model: #{pattern.model}"
  puts "   Association: #{pattern.association}"
  puts "   Suggestion: Use .includes(:#{pattern.association})"
end

# Generate beautiful performance reports
report_generator = CQL::Performance::Reports::HTMLReportGenerator.new
report_generator.generate_report(monitor.events, "performance_report.html")
puts "๐Ÿ“Š Performance report generated: performance_report.html"

๐ŸŽฏ Perfect For Your Use Case

๐Ÿš€ High-Performance APIs

  • RESTful APIs serving millions of requests
  • GraphQL backends with complex data fetching
  • Real-time applications with WebSocket connections
  • Microservices requiring fast data access

๐Ÿข Enterprise Applications

  • Large-scale web applications
  • Complex business logic with data integrity requirements
  • Multi-tenant SaaS platforms
  • Financial and healthcare applications requiring compliance

โ˜๏ธ Cloud-Native Development

  • Container-based deployments
  • Kubernetes-native applications
  • Serverless functions with database access
  • Auto-scaling applications

๐Ÿ”ง Modern Development Workflows

  • CI/CD pipelines with database testing
  • Type-safe development practices
  • Large team collaboration
  • Long-term maintenance and refactoring

๐Ÿ“š Documentation

๐Ÿ“– Complete Documentation on GitBook โ†’

Comprehensive guides for every level:

๐Ÿšฆ Getting Started

๐Ÿ—๏ธ Core Features

โšก Advanced Topics

๐Ÿš€ Performance & Production

๐Ÿ“– Reference

๐Ÿงช Development & Testing

Running Tests

# Start PostgreSQL for full test suite
docker run --rm -e POSTGRES_DB=spec -e POSTGRES_PASSWORD=password -p 5432:5432 postgres

# Run comprehensive test suite with PostgreSQL
DATABASE_URL="postgres://postgres:password@localhost:5432/spec" crystal spec

# Quick test with SQLite (default)
crystal spec

# Run specific test categories
crystal spec spec/patterns/active_record/relations/  # Relationship tests
crystal spec spec/cache/                           # Caching tests
crystal spec spec/performance/                     # Performance tests

Database Compatibility

CQL is actively tested and optimized for:

  • PostgreSQL: 12, 13, 14, 15, 16 with full feature support
  • MySQL: 8.0+ with dialect-specific optimizations
  • SQLite: 3.35+ perfect for development and testing

Each adapter supports database-specific features and provides optimal performance.

๐Ÿค Contributing

We love contributions! Here's how to get involved:

๐Ÿš€ Quick Start

  1. Fork the repository on GitHub
  2. Clone your fork: git clone https://github.com/yourusername/cql.git
  3. Create your feature branch: git checkout -b my-awesome-feature
  4. Make your changes and add comprehensive tests
  5. Run the test suite: crystal spec
  6. Commit your changes: git commit -am 'Add awesome feature'
  7. Push to your branch: git push origin my-awesome-feature
  8. Create a Pull Request with a clear description

๐Ÿ’ก Contribution Ideas

  • ๐Ÿ› Bug Fixes - Help us squash bugs and improve reliability
  • โšก Performance - Optimize queries, reduce memory usage, improve speed
  • ๐Ÿ“š Documentation - Improve guides, add examples, fix typos
  • ๐Ÿงช Tests - Add test coverage, create integration scenarios
  • ๐ŸŽฏ Features - Implement new ORM features and database support
  • ๐Ÿ”ง Developer Experience - Improve error messages, add tooling

๐Ÿ“‹ Development Guidelines

  • โœ… Follow Crystal coding conventions and style guidelines
  • ๐Ÿงช Add comprehensive tests for new features and bug fixes
  • ๐Ÿ“– Update documentation for API changes and new features
  • ๐Ÿ—„๏ธ Ensure compatibility across all supported databases (PostgreSQL, MySQL, SQLite)
  • ๐Ÿ“ Use meaningful commit messages following conventional commits
  • โšก Add performance benchmarks for query-related features
  • ๐Ÿ”’ Consider security implications for new features

๐ŸŽฏ Areas We Need Help With

  • Database adapter improvements and new database support
  • Query optimization and performance enhancements
  • Documentation improvements and examples
  • Testing across different Crystal versions
  • Integration with popular Crystal web frameworks

๐Ÿ“„ License

CQL is released under the MIT License. Feel free to use it in personal and commercial projects.


๐Ÿš€ Ready to Build Something Amazing?

Join thousands of developers building fast, type-safe applications with CQL

# Install CQL and start building your next high-performance app
shards install

# Try our interactive examples
crystal examples/run_examples.cr

# Your next breakthrough application starts here! ๐ŸŽฏ

๐Ÿ“– Get Started Now โ†’ โ€ข ๐Ÿ”ง Try Examples โ†’ โ€ข ๐Ÿ’ฌ Join Community โ†’


Built with โค๏ธ for the Crystal community

CQL provides the productivity of modern ORMs with the performance and type safety that Crystal developers deserve. Whether you're building a simple web application or a complex enterprise system, CQL gives you the tools to work with your data efficiently and safely.

Performance โ€ข Type Safety โ€ข Developer Experience

Repository

cql

Owner
Statistic
  • 15
  • 0
  • 0
  • 1
  • 7
  • 2 days ago
  • July 12, 2024
License

MIT License

Links
Synced at

Thu, 10 Jul 2025 03:37:01 GMT

Languages