cql v1.1.1

A type-safe query builder and Active Record ORM for Crystal
SQLite · PostgreSQL · MySQL · CockroachDB

Documentation · Tutorials · How-to guides · Reference · API · Examples · Changelog

[!TIP] The full guide is at azutopia.gitbook.io/cql: tutorials, task guides, the reference and the design notes. Its source is in docs/, but it reads best on the site.

Why CQL

The v1 release candidate continues PR #65. See the release audit and validation record for the tested configurations and remaining release gates. The automated release workflow owns the version bump; this branch is not a published v1.0.0.

For the native engine, connect_failure_threshold=3&connect_retry_delay=1 enables a connection circuit breaker: failed connects open it, one probe tests recovery after the delay, and healthy existing connections remain usable. It never retries statements. Use sslmode=verify-full to verify the PostgreSQL server, and size the pool and query timeouts for the application. Pool configuration explains the defaults and failure behavior.

• Mistakes stop the build. Schemas, models and queries are Crystal code, so a misspelled column or a value of the wrong type is a compiler error instead of a runtime one.
• Values are always bound. Every value reaches the database as a parameter, and names that can't be bound, such as index and alias names, are validated.
• PostgreSQL needs no extra shard. CQL ships its own wire-protocol driver, with a fiber-safe pool, streaming, pipelining and COPY.

Take a users table with a text name and an integer age. Neither of these lines compiles:

User.where { nmae == "Ada" }  # misspelled column
User.where { age == "forty" } # a String compared with an Int32 column

Error: undefined local variable or method 'nmae' for User::Columns (with ... yield) and top-level (current scope)
Error: CQL: cannot compare a Int32 column with String using `==`; use a Int32 value or another Int32 column

Features

Installation

Add CQL and your database's driver to shard.yml, then run shards install:

dependencies:
  cql:
    github: azutoolkit/cql
    version: ~> 0.0.861
  sqlite3: # for SQLite
    github: crystal-lang/crystal-sqlite3
  mysql: # for MySQL; used by the optional cql/mysql integration
    github: crystal-lang/crystal-mysql
    version: ~> 0.16.0

[!NOTE] PostgreSQL and CockroachDB need nothing more, because CQL ships the driver. CQL needs Crystal 1.19.1 or later; CI tests Crystal 1.19.1 and 1.22.1.

For MySQL 8.4, use require "cql/mysql" to enable caching_sha2_password and verified TLS. Set ssl-ca=/path/to/ca.pem in the database URL for a private CA; see MySQL 8.4 setup guide.

Quick Start

Save this as src/app.cr:

require "cql"
require "sqlite3"

AppDB = CQL::Schema.define(:app_db, adapter: CQL::Adapter::SQLite, uri: "sqlite3://./app.db") do
  table :users do
    primary :id, Int64, auto_increment: true
    text :name
  end
  table :posts do
    primary :id, Int64, auto_increment: true
    text :title
    bigint :user_id
  end
end

class User
  include CQL::ActiveRecord::Model(Int64)
  db_context AppDB, :users
  property name : String
  has_many :posts, Post, foreign_key: :user_id
  validate :name, presence: true

  def initialize(@name : String)
  end
end

class Post
  include CQL::ActiveRecord::Model(Int64)
  db_context AppDB, :posts
  property title : String
  property user_id : Int64?
  belongs_to :user, User, foreign_key: :user_id

  def initialize(@title : String, @user_id : Int64? = nil)
  end
end

AppDB.users.create! # CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL)
AppDB.posts.create!

ada = User.create!(name: "Ada")                           # INSERT INTO users (name) VALUES (?)
ada.posts.create(title: "Notes on the Analytical Engine") # INSERT INTO posts (title, user_id) VALUES (?, ?)

# Two queries: SELECT ... FROM posts, then SELECT ... FROM users WHERE users.id IN (?)
Post.preload(:user).all.each { |post| puts "#{post.title} by #{post.user.try(&.name)}" }

puts User.where { name == "Ada" }.count # SELECT COUNT(*) FROM users WHERE users.name = ?
puts User.new("").valid?                # the presence validation fails on a blank name

Run it:

crystal run src/app.cr

Notes on the Analytical Engine by Ada
1
false

Next, Your First CQL App adds migrations, updates, deletes and transactions.

How It Works

flowchart LR
    models["Schema and models<br/>CQL::Schema.define"] --> build["Build<br/>QueryBuilder(T) → CQL::Query"]
    build --> render["Render<br/>AST → Expression::Generator<br/>with the adapter's dialect"]
    render --> execute["Execute<br/>SQL and bound values on the pool<br/>or the open transaction"]
    execute --> db[("SQLite · PostgreSQL<br/>MySQL · CockroachDB")]
    db --> hydrate["Hydrate<br/>DB::Serializable models"]

Each query method records one clause. At execution, Query#build turns the clauses into an AST, and the Expression::Generator visitor writes SQL in the adapter's dialect, with placeholders (? or $1) where the values go. How CQL Keeps Queries Fast walks through each stage and what it costs.

Database Support

Feature differences, such as upsert, RETURNING, DISTINCT ON, locking, JSON and the minimum versions for CTEs and window functions, are listed in Database Support.

Native PostgreSQL Driver

require "cql" registers CQL's driver with crystal-db for postgres:// and postgresql:// URLs, so CQL::Schema and DB.open use it without the pg shard. The native driver guide covers running alongside pg and opting in to cockroachdb:// for DB.open.

CQL::Postgres::AsyncEngine has its own fiber-safe pool and is a CQL::Executor, so any statement runs on it with using:

DATABASE_URL = "postgres://app:secret@db.internal/app?sslmode=verify-full&pool_size=10"
engine = CQL::Postgres::AsyncEngine.connect(DATABASE_URL)

User.query.where(name: "Ada").using(engine).to_a                                         # ... WHERE users.name = $1
AppDB.query.from(:users).select(:name).where("id > ?", 10).using(engine).all(as: String) # ... WHERE id > $1

engine.transaction(isolation: :serializable) do |tx|
  AppDB.insert.into(:users).values(name: "Ada").using(tx).commit
  tx.savepoint { |inner| AppDB.insert.into(:posts).values(title: "Draft", user_id: 1_i64).using(inner).commit }
end

# 500 rows per fetch through a portal, not the whole result in memory
engine.stream(AppDB.query.from(:users).select(:id), fetch_size: 500) do |rows|
  rows.each { puts rows.read(Int64) }
end

# Several statements, one round trip
results = engine.pipeline([AppDB.query.from(:users).select(:name), AppDB.query.from(:posts).select(:title)])
results[0].as(CQL::Postgres::PipelineResult).all(as: String)

# A whole schema (models, migrations, transactions) on the engine's pool
ShopDB = CQL::Schema.new(:shop, DATABASE_URL, CQL::Adapter::Postgres, source: CQL::Postgres::EngineSource.connect(DATABASE_URL))

Measured Compatibility

The driver passes its live specs on PostgreSQL 11 to 18 and CockroachDB 24.3, and has been run behind PgBouncer, Odyssey, pgcat and pgpool-II.[^live] CI also runs spec/postgres/live_spec.cr against a postgres service container on every push.

Performance

Model reads and writes on in-memory SQLite, from benchmarks/sqlite_bench.cr:[^sqlite]

Bytes per operation repeat from run to run; time per operation depends on the machine. How CQL Keeps Queries Fast explains what each query costs and what is off by default.

Documentation

The guide is published at azutopia.gitbook.io/cql and organized by what you need:

Every public type and method is in the API reference, generated from the doc comments in src/ for each release. There are also installation notes, runnable examples and a playground.

Project Status

[!IMPORTANT] CQL is pre-1.0 (0.0.x). CI tags and releases every merge to master, and a release can change behavior. Read the changelog before you upgrade.

Contributing

Bug reports, questions and pull requests are welcome. Open an issue to report a problem or discuss a change. To work on CQL itself:

shards install --ignore-crystal-version
crystal spec                                                    # SQLite runs live; PostgreSQL specs need DATABASE_URL
crystal tool format --check src spec
crystal run lib/ameba/bin/ameba.cr -- -f progress ./src ./spec  # lint, as CI runs it

[!NOTE] Without a server, the PostgreSQL and Redis specs are pending.

A bug fix comes with a spec that fails without it. A behavior change adds a CHANGELOG.md entry under [Unreleased] and updates the matching page in docs/. Contributing has the full workflow.

License

CQL is released under the MIT License.

[^live]: Live specs and probe scripts run against real servers on one Linux x86_64 machine. Details are in the collapsed sections below. [^pgbench]: One machine, one run each. [^sqlite]: One run of crystal run --release benchmarks/sqlite_bench.cr: one fiber, on a shared 4-core Linux VM.