CQL API

CQL (Crystal Query Language) is the ORM used with Azu for database operations.

Schema Definition

CQL::Schema.define

Create a database schema.

MyDB = CQL::Schema.define(
  :my_db,
  adapter: CQL::Adapter::SQLite,
  uri: "sqlite3://./db/development.db"
) do
  table :users do
    primary :id, Int64
    column :name, String
    column :email, String
    timestamps
  end
end

Parameters:

• name : Symbol - Schema name

• adapter : CQL::Adapter - Database adapter

• uri : String - Connection string

Adapters

Available Adapters

Adapter	URI Format
CQL::Adapter::SQLite	sqlite3://./path/to/db.db
CQL::Adapter::Postgres	postgres://user:pass@host:5432/db
CQL::Adapter::MySql	mysql://user:pass@host:3306/db

Table Definition

table

Define a database table.

table :users do
  primary :id, Int64
  column :name, String
  column :email, String
end

primary

Define primary key.

primary :id, Int64
primary :uuid, String  # For UUID primary keys

column

Define a column.

column :name, String
column :age, Int32?                    # Nullable
column :active, Bool, default: true    # With default
column :created_at, Time, default: -> { Time.utc }

Column Types:

• String - VARCHAR/TEXT

• Int32, Int64 - INTEGER/BIGINT

• Float32, Float64 - REAL/DOUBLE

• Bool - BOOLEAN

• Time - TIMESTAMP

• JSON::Any - JSON

timestamps

Add created_at and updated_at columns.

table :posts do
  primary :id, Int64
  column :title, String
  timestamps  # Adds created_at, updated_at
end

foreign_key

Define a foreign key.

table :posts do
  primary :id, Int64
  column :user_id, Int64
  foreign_key :user_id, :users, :id
end

index

Define an index.

table :users do
  # ...
  index :email, unique: true
  index [:first_name, :last_name]  # Composite
end

Model Definition

CQL::Model

Include in class to make it a model.

class User
  include CQL::Model(User, Int64)
  db_context MyDB, :users

  property id : Int64?
  property name : String
  property email : String
end

Parameters:

• First type: Model class

• Second type: Primary key type

db_context

Set database and table.

db_context MyDB, :users

CRUD Operations

create

Create a new record.

user = User.create!(name: "Alice", email: "[e-mail]")

find

Find by primary key.

user = User.find(1)      # Raises if not found
user = User.find?(1)     # Returns nil if not found

find_by

Find by attributes.

user = User.find_by(email: "[e-mail]")
user = User.find_by?(email: "[e-mail]")

save

Save record (insert or update).

user = User.new(name: "Alice", email: "[e-mail]")
user.save!     # Raises on failure
user.save      # Returns Bool

update

Update attributes.

user.update!(name: "Alice Smith")
user.update(name: "Alice Smith")

destroy

Delete record.

user.destroy!
user.destroy

delete_all

Delete all matching records.

User.delete_all
User.where(active: false).delete_all

Query Methods

all

Get all records.

users = User.all

where

Filter records.

users = User.where(active: true).all
users = User.where("age > ?", 18).all

order

Sort records.

users = User.order(name: :asc).all
users = User.order(created_at: :desc).all

limit / offset

Paginate results.

users = User.limit(10).offset(20).all

count

Count records.

count = User.count
count = User.where(active: true).count

first / last

Get first or last record.

user = User.first
user = User.order(created_at: :desc).first
user = User.last

exists?

Check if records exist.

exists = User.where(email: "[e-mail]").exists?

Associations

belongs_to

class Post
  include CQL::Model(Post, Int64)

  property user_id : Int64

  belongs_to :user, User, foreign_key: :user_id
end

has_many

class User
  include CQL::Model(User, Int64)

  has_many :posts, Post, foreign_key: :user_id
end

has_one

class User
  include CQL::Model(User, Int64)

  has_one :profile, Profile, foreign_key: :user_id
end

Callbacks

Available Callbacks

• before_validation

• after_validation

• before_save

• after_save

• before_create

• after_create

• before_update

• after_update

• before_destroy

• after_destroy

class User
  include CQL::Model(User, Int64)

  before_save :normalize_email
  after_create :send_welcome_email

  private def normalize_email
    @email = email.downcase.strip
  end
end

Scopes

Define reusable query scopes.

class User
  include CQL::Model(User, Int64)

  scope :active, -> { where(active: true) }
  scope :recent, -> { order(created_at: :desc) }
  scope :admins, -> { where(role: "admin") }
end

# Usage
User.active.recent.all
User.admins.count

Transactions

MyDB.transaction do
  user = User.create!(name: "Alice")
  Profile.create!(user_id: user.id)
end

Raw Queries

MyDB.query("SELECT * FROM users WHERE age > ?", 18)
MyDB.exec("UPDATE users SET active = ? WHERE id = ?", true, 1)

See Also

• Query Methods Reference

• Validations Reference

• How to Create Models