To get started with CQL in your Crystal project, follow these steps:

Step 1: Add Dependency

First, add CQL to your project by including it in the shard.yml file:

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

Step 2: Install Shards

Run the following command to install the dependencies:

shards install

Step 3: Configure Database

Set up your database connection by specifying the adapter and connection URL. This is done by configuring the database in your code, as follows:

"postgres://user:password@localhost:5432/database_name"

In this example, we’re using PostgreSQL. You can change the URL according to your database (MySQL, SQLite, etc.).

Step 4: Create the Schema

Now you can define your schema and run migrations (explained in later sections).

CQL is a powerful library designed to simplify and enhance the management and execution of SQL queries in the Crystal programming language. It provides utilities for building, validating, and executing SQL statements, ensuring better performance and code maintainability.

Features

• Query Builder: Programmatically create complex SQL queries.

• Insert, Update, Delete Operations: Perform CRUD operations with ease.

• Repository Pattern: Manage your data more effectively using CQL::Repository(T).

• Active Record Pattern: Work with your data models using CQL::Record(T).

Installation

Add this to your application's shard.yml:

dependencies:
  cql:
    github: azutoolkit/cql

Then, run the following command to install the dependencies:

shards install

Getting Started

1. Define a Schema

Define the schema for your database tables:

AcmeDB2 = CQL::Schema.build(
  :acme_db,
  adapter: CQL::Adapter::Postgres,
  uri: ENV["DATABASE_URL"]) do
  table :movies do
    primary :id, Int64, auto_increment: true
    text :title
  end

  table :screenplays do
    primary :id, Int64, auto_increment: true
    bigint :movie_id
    text :content
  end

  table :actors do
    primary :id, Int64, auto_increment: true
    text :name
  end

  table :directors do
    primary :id, Int64, auto_increment: true
    bigint :movie_id
    text :name
  end

  table :movies_actors do
    primary :id, Int64, auto_increment: true
    bigint :movie_id
    bigint :actor_id
  end
end

2. Executing Queries

With the schema in place, you can start executing queries:

q = AcmeDB.query
user = q.from(:users).where(id: 1).first!(as: User)
puts user.name

3. Inserting Data

Insert new records into the database:

q = CQL::Query.new(schema)
q.insert
  .into(:users)
  .values(name: "Jane Doe", email: "jane@example.com")
  .last_insert_id

4. Updating Data

Update existing records:

u = AcmeDB.update
u.table(:users)
  .set(name: "Jane Smith")
  .where(id: 1)
  .commit

5. Deleting Data

Delete records from the database:

d = AcmeDB.delete
d.from(:users).where(id: 1).commit

6. Using the Repository Pattern

Utilize the repository pattern for organized data management:

user_repository = CQL::Repository(User, Int64).new(schema, :users)

# Create a new user
user_repository.create(name: "Jane Doe", email: "jane@example.com")

# Fetch all users
users = user_repository.all
users.each { |user| puts user.name }

# Find a user by ID
user = user_repository.find!(1)
puts user.name

# Update a user by ID
user_repository.update(1, name: "Jane Smith")

7. Active Record Pattern

Work with your data using the Active Record pattern:

struct Actor < CQL::Record(Int64)

  db_context AcmeDB2, :actors

  getter id : Int64?
  getter name : String

  def initialize(@name : String)
  end
end

struct Movie < CQL::Record(Int64)

  db_context AcmeDB2, :movies

  has_one :screenplay, Screenplay
  many_to_many :actors, Actor, join_through: :movies_actors
  has_many :directors, Director, foreign_key: :movie_id

  getter id : Int64?
  getter title : String

  def initialize(@title : String)
  end
end

struct Director < CQL::Record(Int64)

  db_context AcmeDB2, :directors

  getter id : Int64?
  getter name : String
  belongs_to :movie, foreign_key: :movie_id

  def initialize(@name : String)
  end
end

struct Screenplay < CQL::Record(Int64)

  db_context AcmeDB2, :screenplays

  belongs_to :movie, foreign_key: :movie_id

  getter id : Int64?
  getter content : String

  def initialize(@movie_id : Int64, @content : String)
  end
end

struct MoviesActors < CQL::Record(Int64)

  db_context AcmeDB2, :movies_actors

  getter id : Int64?
  getter movie_id : Int64
  getter actor_id : Int64

  has_many :actors, Actor, :actor_id

  def initialize(@movie_id : Int64, @actor_id : Int64)
  end
end

Once you've defined your database schema using CQL's Schema.define, the next step is to initialize the database by creating the necessary tables and structures. In this guide, we will walk through how to define your schema and use Schema.init to initialize the database.

Real-World Example: Defining the Database Schema

Here's an example where we define a schema for a movie database using CQL. The schema includes tables for movies, screenplays, actors, directors, and a join table movies_actors to link movies and actors.

AcmeDB2 = CQL::Schema.define(
  :acme_db,
  adapter: CQL::Adapter::Postgres,
  uri: ENV["DATABASE_URL"]
) do
  table :movies do
    primary :id, Int64, auto_increment: true
    text :title
  end

  table :screenplays do
    primary :id, Int64, auto_increment: true
    bigint :movie_id
    text :content
  end

  table :actors do
    primary :id, Int64, auto_increment: true
    text :name
  end

  table :directors do
    primary :id, Int64, auto_increment: true
    bigint :movie_id
    text :name
  end

  table :movies_actors do
    primary :id, Int64, auto_increment: true
    bigint :movie_id
    bigint :actor_id
  end
end

Explanation

In the example above, we define:

• movies: Stores information about movies, including an auto-incrementing id and a title.

• screenplays: Stores the screenplay contents for movies, linking to the movies table with movie_id.

• actors: Stores actor names, with an auto-incrementing id.

• directors: Stores director names, associated with a movie via movie_id.

• movies_actors: A join table to link movies and actors, establishing a many-to-many relationship between movies and actors.

Initializing the Database

Once the schema has been defined, the next step is to initialize the database. This is done by calling the Schema.init method (or in our case, AcmeDB2.init), which creates the tables based on the schema.

Steps to Initialize

1. Set up the environment: Ensure that the DATABASE_URL environment variable is correctly set to point to your database connection string (for PostgreSQL in this case).

2. Call Schema.init: After defining the schema, you initialize the database by calling the init method on the schema object.

AcmeDB2.init

This command creates all the tables and applies the structure you defined in the schema to your actual database.

Full Example: Defining and Initializing the Database

# db_context the schema
AcmeDB2 = CQL::Schema.define(
  :acme_db,
  adapter: CQL::Adapter::Postgres,
  uri: ENV["DATABASE_URL"]
) do
  table :movies do
    primary :id, Int64, auto_increment: true
    text :title
  end

  table :screenplays do
    primary :id, Int64, auto_increment: true
    bigint :movie_id
    text :content
  end

  table :actors do
    primary :id, Int64, auto_increment: true
    text :name
  end

  table :directors do
    primary :id, Int64, auto_increment: true
    bigint :movie_id
    text :name
  end

  table :movies_actors do
    primary :id, Int64, auto_increment: true
    bigint :movie_id
    bigint :actor_id
  end
end

# Initialize the database
AcmeDB2.init

Generated Database Tables

What Happens During Initialization?

When AcmeDB2.init is called, the following happens:

• The database connection is established using the URI provided in the schema (e.g., the PostgreSQL database connection).

• CQL creates the tables (movies, screenplays, actors, directors, and movies_actors) in the database if they don't already exist.

• Primary keys, relationships, and any constraints are applied as defined in the schema.

Verifying the Initialization

After calling AcmeDB2.init, you can verify the tables are created in your database by using a PostgreSQL client or a GUI tool such as pgAdmin. You should see the tables with their respective columns and relationships as defined in the schema.

-- Example SQL to verify the tables were created
SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';

-- To check the structure of a table, use:
\d movies  -- This shows the structure of the 'movies' table

Summary

Initializing the database after schema creation is a simple process with CQL. After defining your tables and relationships using CQL::Schema.define, you can call the init method to apply your schema to the actual database.

This method ensures that your database is correctly structured and ready to use, allowing you to focus on developing the application logic without worrying about manual database setup.

AcmeDB2.init  # Initializes the schema and sets up the database

The CQL::Insert class in the CQL (Crystal Query Language) module is a powerful tool designed to simplify the process of inserting records into a database. As a software developer, you'll find this class essential for building INSERT queries in a clean, readable, and chainable way.

In this guide, we’ll walk through the core functionality, explain each method in detail, and provide real-world examples to help you understand how to integrate CQL::Insert into your applications.

Key Features

1. Insert records into any table with ease.

2. Insert multiple records in a single query.

3. Insert records using data fetched from another query.

4. Get the last inserted ID after an insert.

5. Chainable, intuitive syntax for building complex queries.

Real-World Example: Inserting User Data

Let’s start with a simple example of inserting a new user into the users table.

insert
  .into(:users)
  .values(name: "Alice", email: "alice@example.com", age: 29)
  .commit

This example demonstrates how you can insert a new user with their name, email, and age using the values method, followed by commit to execute the insert.

Core Methods

Let's dive into the individual methods and how you can use them.

1. into(table : Symbol)

Purpose: Specifies the table into which the data will be inserted. This is your starting point for any insert operation.

• Parameter: table (Symbol) — The name of the table to insert into.

• Returns: Insert object (enabling chaining).

Example:

insert.into(:users)

In the above code, we're targeting the users table. This is where subsequent data will be inserted.

2. values(**fields)

Purpose: Specifies the data (fields and values) to insert. The values method can accept either a hash or keyword arguments.

• Parameter: fields (Hash or keyword arguments) — A mapping of column names to their values.

• Returns: Insert object (for chaining).

Real-World Example 1: Adding a Single User

insert
  .into(:users)
  .values(name: "Bob", email: "bob@example.com", age: 35)
  .commit

Here, we’re adding a new user named Bob, specifying their name, email, and age.

Real-World Example 2: Adding Multiple Users in One Query

insert
  .into(:users)
  .values([
    {name: "John", email: "john@example.com", age: 30},
    {name: "Jane", email: "jane@example.com", age: 25}
  ]).commit

This example demonstrates how you can insert multiple users in a single query. It’s efficient and reduces database round trips.

3. last_insert_id(as type : PrimaryKeyType = Int64)

Purpose: Retrieves the ID of the last inserted row. This is incredibly useful when you need to work with the inserted record immediately after an insert, especially in cases where the primary key is automatically generated.

• Parameter: type (default: Int64) — The data type of the returned ID.

• Returns: The last inserted ID as Int64 or the specified type.

Example:

last_id = insert
  .into(:users)
  .values(name: "Charlie", email: "charlie@example.com", age: 22)
  .last_insert_id
puts last_id  # Outputs the last inserted ID

4. query(query : Query)

Purpose: Instead of manually specifying values, you can use data fetched from another query to populate the insert. This is useful in situations like copying data from one table to another.

• Parameter: query — A query object that fetches the data to insert.

• Returns: Insert object (for chaining).

Real-World Example: Copying Data from One Table to Another

Imagine you want to copy user data from an archive table (archived_users) to the main users table.

insert
  .into(:users)
  .query(select.from(:archived_users).where(active: true))
  .commit

In this example, we’re selecting all active users from archived_users and inserting them into the users table in one go.

5. back(*columns : Symbol)

Purpose: After an insert operation, you may want to return certain columns, like an ID or timestamp. The back method allows you to specify which columns should be returned.

• Parameter: columns — One or more symbols representing the columns to return.

• Returns: Insert object (for chaining).

Example:

insert
  .into(:users)
  .values(name: "David", email: "david@example.com", age: 28)
  .back(:id)
  .commit

In this case, after inserting the new user, we’re returning the id of the newly inserted row.

6. commit

Purpose: Executes the built INSERT query and commits the transaction to the database. This is the final step in your insert operation.

• Returns: The result of the insert, typically the number of affected rows or the last inserted ID.

Example:

insert
  .into(:users)
  .values(name: "Eva", email: "eva@example.com", age: 31)
  .commit

This will execute the INSERT statement and commit it to the database, saving the new user’s data.

Advanced Example: Using Insert with Chained Operations

Let’s consider a more advanced example where you insert a new user, return their ID, and use it in subsequent operations.

user_id = insert
  .into(:users)
  .values(name: "Frank", email: "frank@example.com", age: 27)
  .last_insert_id

# Now use `user_id` in another query, e.g., to insert into a related table
insert
  .into(:user_profiles)
  .values(user_id: user_id, bio: "Software developer from NYC", twitter: "@frankdev")
  .commit

In this example, after inserting the new user, we immediately get the user_id and use it to insert data into the user_profiles table.

Handling Errors

In case an insert operation fails, the commit method automatically logs the error and raises an exception. This allows you to catch and handle the error as needed in your application.

Example:

begin
  insert
    .into(:users)
    .values(name: nil)  # This will likely fail due to a NOT NULL constraint
    .commit
rescue ex
  puts "Insert failed: #{ex.message}"
end

Conclusion

The CQL::Insert class provides a flexible, chainable interface for inserting data into a database, whether you're inserting a single record, multiple records, or even records based on the results of a query. Its intuitive API makes it easy to use in real-world applications, and with error handling built-in, it helps ensure robust database operations.

With its simple syntax and powerful features, CQL::Insert streamlines your database interactions, allowing you to focus on building great features in your Crystal applications.

Purpose of CQL

CQL (Crystal Query Language) is a powerful tool designed for developers working with the Crystal programming language. It provides a streamlined interface for interacting with SQL databases, combining the flexibility of raw SQL with the safety and clarity of Crystal’s type system. CQL simplifies complex database operations, making it easier to perform CRUD (Create, Read, Update, Delete) operations, manage database schemas, and execute advanced queries.

Key Features

• Database-agnostic: CQL supports multiple databases, ensuring flexibility in project development.

• Active Record Pattern: Integrates seamlessly with Crystal structs, allowing developers to work with database records as native Crystal objects.

• Query Builder: Provides an intuitive API for constructing complex SQL queries, including joins, subqueries, and transactions.

• Associations: CQL supports defining relationships between tables (has_many, belongs_to, many_to_many), making it easy to navigate related data.

• Migrations: Facilitates schema evolution by allowing developers to create and manage database migrations.

Supported Databases

CQL is designed to work with a range of SQL databases, including:

• PostgreSQL: Full support with advanced features like JSONB, arrays, and full-text search.

• MySQL: Support for common MySQL operations, though more advanced features may be limited.

• SQLite: Lightweight database support for development and testing environments.

Use Cases

CQL is ideal for developers looking to integrate Crystal with SQL databases, providing a robust toolset for building data-driven applications. Whether you are developing a small-scale application or a large enterprise system, CQL offers the performance and scalability needed to manage your database operations efficiently.

CQL’s core concepts revolve around providing developers with tools to efficiently interact with databases through well-structured APIs. These concepts are fundamental to building and manipulating data models, performing queries, and managing transactions. Here's a high-level overview:

• Schemas: Define the structure of the database, including tables, columns, and data types.

• CRUD Operations: Simplify creating, reading, updating, and deleting records in the database.

• Query Builder: Enables constructing SQL queries using Crystal code with features like joins and subqueries.

• Transactions: Ensure safe and atomic execution of multiple database operations.

The CQL::Query class is designed to simplify the creation and execution of SQL queries. By using a structured, chainable API, you can build complex SQL queries while maintaining clean and readable code.

This guide walks you through how to create, modify, and execute queries using real-world examples. We'll also explore various methods for selecting, filtering, joining, and ordering data, with a focus on practical use cases.

Key Features

1. Select columns and filter records using simple, chainable methods.

2. Join tables for complex queries involving multiple relationships.

3. Aggregate data using functions like COUNT, SUM, and AVG.

4. Order and limit your result sets for more precise control.

5. Fetch results as objects or raw data for immediate use in your application.

Real-World Example: Fetching User Data

Let's begin by selecting user data from a users table:

query = CQL::Query.new(schema)
query.select(:name, :age).from(:users).where(name: "Alice").all(User)

This query will return all users with the name "Alice", casting the result to the User type.

Core Methods

Below is a breakdown of the key methods in the CQL::Query class and how you can use them in your applications.

1. select(*columns : Symbol)

Purpose: Specifies the columns to select in the query.

• Parameters: columns — One or more symbols representing the columns you want to select.

• Returns: Query object for chaining.

Real-World Example: Selecting Columns

query = CQL::Query.new(schema)
query.select(:name, :email).from(:users)

This query selects the name and email columns from the users table.

2. from(*tables : Symbol)

Purpose: Specifies the tables to query from.

• Parameters: tables — One or more symbols representing the tables to query from.

• Returns: Query object for chaining.

Real-World Example: Querying a Table

query.from(:users)

This query selects from the users table.

3. where(hash : Hash(Symbol, DB::Any))

Purpose: Adds filtering conditions to the query.

• Parameters: hash — A key-value hash representing the column and its corresponding value for the WHERE clause.

• Returns: Query object for chaining.

Real-World Example: Filtering Data

query.from(:users).where(name: "Alice", age: 30)

This will generate a query with the WHERE clause: WHERE name = 'Alice' AND age = 30.

4. all(as : Type)

Purpose: Executes the query and returns all matching results, casting them to the specified type.

• Parameters: as — The type to cast the results to.

• Returns: An array of the specified type.

Real-World Example: Fetching All Results

users = query.select(:name, :email).from(:users).all(User)

This will return all users as an array of User objects.

5. first(as : Type)

Purpose: Executes the query and returns the first matching result, casting it to the specified type.

• Parameters: as — The type to cast the result to.

• Returns: The first matching result of the query.

Real-World Example: Fetching the First Result

user = query.select(:name, :email).from(:users).where(name: "Alice").first(User)

This returns the first user with the name "Alice".

6. count(column : Symbol = :*)

Purpose: Adds a COUNT aggregate function to the query, counting the specified column.

• Parameters: column — The column to count (default is *, meaning all rows).

• Returns: Query object for chaining.

Real-World Example: Counting Rows

count = query.from(:users).count(:id).get(Int64)

This will count the number of rows in the users table and return the result as an Int64.

7. join(table : Symbol, on : Hash)

Purpose: Adds a JOIN clause to the query, specifying the table and the condition for joining.

• Parameters:

  • table: The table to join.

  • on: A hash representing the join condition, mapping columns from one table to another.

• Returns: Query object for chaining.

Real-World Example: Joining Tables

query
  .from(:users)
  .join(:orders, on: {users.id => orders.user_id})

This query joins the users table with the orders table on the condition that users.id equals orders.user_id.

8. order(*columns : Symbol)

Purpose: Specifies the columns by which to order the results.

• Parameters: columns — The columns to order by.

• Returns: Query object for chaining.

Real-World Example: Ordering Results

query.from(:users).order(:name, :age)

This orders the query results by name first and then by age.

9. limit(value : Int32)

Purpose: Limits the number of rows returned by the query.

• Parameters: value — The number of rows to return.

• Returns: Query object for chaining.

Real-World Example: Limiting Results

query.from(:users).limit(10)

This limits the query to return only the first 10 rows.

10. get(as : Type)

Purpose: Executes the query and returns a scalar value, such as the result of an aggregate function (e.g., COUNT, SUM).

• Parameters: as — The type to cast the result to.

• Returns: The scalar result of the query.

Real-World Example: Getting a Scalar Value

total_users = query.from(:users).count(:id).get(Int64)

This returns the total number of users as an Int64.

11. each(as : Type, &block)

Purpose: Iterates over each result, yielding each row to the provided block.

• Parameters:

  • as: The type to cast each row to.

  • &block: The block to execute for each row.

• Returns: Nothing (used for iteration).

Real-World Example: Iterating Over Results

query.from(:users).each(User) do |user|
  puts user.name
end

This will print the name of each user in the users table.

12. distinct

Purpose: Sets the DISTINCT flag to return only unique rows.

• Returns: Query object for chaining.

Real-World Example: Fetching Distinct Results

query.from(:users).distinct

This will generate a query that returns only distinct rows from the users table.

Putting It All Together

Let's create a real-world example that combines several methods. Suppose you want to fetch the first 5 users who have placed an order, ordered by their name, and return the result as User objects:

query = CQL::Query.new(schema)

users = query
  .select(:name, :email)
  .from(:users)
  .join(:orders, on: {users.id => orders.user_id})
  .where(active: true)
  .order(:name)
  .limit(5)
  .all(User)

users.each do |user|
  puts "Name: #{user.name}, Email: #{user.email}"
end

In this query:

• We select the name and email columns from users.

• We join the orders table to ensure the user has placed an order.

• We filter for active users (where(active: true)).

• We order the results by name.

• We limit the results to 5 users.

Conclusion

The CQL::Query class offers a flexible and intuitive API for building and executing SQL queries in your Crystal applications. With methods for selecting, joining, filtering, and aggregating data, you can handle even the most complex queries with ease.

Whether you're building basic queries or handling complex database operations, the CQL::Query class provides the tools you need to write clean, efficient, and maintainable code.

The CQL::AlterTable class in the CQL framework provides a structured way to make schema changes to your database tables. It allows you to perform actions like adding, dropping, and renaming columns, as well as adding foreign keys, renaming tables, and creating indexes. This guide walks you through the most common use cases with real-world examples that software developers can apply in their projects.

Why Use CQL::AlterTable?

When your application evolves, you often need to modify your database structure. CQL::AlterTable lets you:

• Add new columns to accommodate growing data needs.

• Remove or rename columns as your data model refines.

• Enforce foreign key relationships and maintain referential integrity.

• Create or remove indexes for performance tuning.

Real-World Example: Modifying the Users Table

Let’s start with a basic example where we modify the users table by adding a new column email, removing the age column, and renaming the email column to user_email. Given the AcmeDB2 schema with a Users table:

AcmeDB2 = CQL::Schema.build(
  :acme_db,
  adapter: CQL::Adapter::Postgres,
  uri: ENV["DATABASE_URL"]
) do
  table :users do
    primary
    text name, size: 150
  end
end

alter = AlterTable.new(users_table, schema)

AcmeDB.alter :users do
  add_column(:email, "string", null: false, unique: true)
  drop_column(:age)
  rename_column(:email, :user_email)
end

This example:

• Adds a new email column that cannot be NULL and must be unique.

• Drops the age column, removing it from the table.

• Renames the email column to user_email.

Core Methods in CQL::AlterTable

1. add_column(name : Symbol, type : Any, **options)

Purpose: Adds a new column to the table with flexible options for setting the column's properties, such as type, default value, uniqueness, etc.

• Parameters:

  • name: The name of the column to add.

  • type: The data type of the column (e.g., String, Int32).

  • Additional options:

    • null: Whether the column can be NULL (default: true).

    • default: The default value for the column.

    • unique: Whether the column should have a unique constraint (default: false).

    • size: Optionally specify the size of the column (for strings or numbers).

    • index: Whether the column should be indexed (default: false).

Real-World Example: Adding an Email Column

alter.add_column(:email, "string", null: false, unique: true)

This adds an email column to the table, ensures it is NOT NULL, and enforces a uniqueness constraint.

2. drop_column(name : Symbol)

Purpose: Removes an existing column from the table.

• Parameters:

  • name: The name of the column to drop.

Real-World Example: Dropping a Column

alter.drop_column(:age)

This removes the age column from the table.

3. rename_column(old_name : Symbol, new_name : Symbol)

Purpose: Renames an existing column in the table.

• Parameters:

  • old_name: The current name of the column.

  • new_name: The new name for the column.

Real-World Example: Renaming a Column

alter.rename_column(:email, :user_email)

This renames the email column to user_email.

4. change_column(name : Symbol, type : Any)

Purpose: Changes the data type of an existing column.

• Parameters:

  • name: The name of the column.

  • type: The new data type for the column.

Real-World Example: Changing a Column’s Type

alter.change_column(:age, "string")

This changes the age column’s type from whatever it was (likely an Int32) to a string.

5. rename_table(new_name : Symbol)

Purpose: Renames the entire table.

• Parameters:

  • new_name: The new name for the table.

Real-World Example: Renaming a Table

alter.rename_table(:customers)

This renames the table from users to customers.

6. foreign_key(name : Symbol, columns : Array(Symbol), table : Symbol, references : Array(Symbol), **options)

Purpose: Adds a foreign key constraint to the table.

• Parameters:

  • name: The name of the foreign key.

  • columns: The columns in the current table to use as the foreign key.

  • table: The referenced table.

  • references: The columns in the referenced table.

  • Options:

    • on_delete: Action to take on delete (default: NO ACTION).

    • on_update: Action to take on update (default: NO ACTION).

Real-World Example: Adding a Foreign Key

alter.foreign_key(:fk_movie_id, [:movie_id], :movies, [:id], on_delete: "CASCADE")

This adds a foreign key fk_movie_id on the movie_id column, linking it to the id column in the movies table. On delete, it cascades the delete.

7. drop_foreign_key(name : Symbol)

Purpose: Removes a foreign key constraint from the table.

• Parameters:

  • name: The name of the foreign key to remove.

Real-World Example: Dropping a Foreign Key

alter.drop_foreign_key(:fk_movie_id)

This drops the foreign key constraint fk_movie_id from the table.

8. create_index(name : Symbol, columns : Array(Symbol), unique : Bool = false)

Purpose: Adds an index to the specified columns.

• Parameters:

  • name: The name of the index.

  • columns: The columns to index.

  • unique: Whether the index should enforce uniqueness (default: false).

Real-World Example: Creating a Unique Index

alter.create_index(:index_users_on_email, [:email], unique: true)

This creates a unique index on the email column, ensuring that email addresses are unique across the table.

9. drop_index(name : Symbol)

Purpose: Removes an index from the table.

• Parameters:

  • name: The name of the index to remove.

Real-World Example: Dropping an Index

alter.drop_index(:index_users_on_email)

This drops the index index_users_on_email from the table.

10. to_sql(visitor : Expression::Visitor)

Purpose: Generates the SQL string corresponding to the current set of table alterations.

• Parameters:

  • visitor: The SQL generator that converts the actions to SQL.

Real-World Example: Generating SQL for Table Alterations

sql = alter.to_sql(visitor)
puts sql

This will generate the SQL statements that correspond to the actions taken (e.g., adding columns, dropping columns).

Putting It All Together

Here’s an advanced example where we modify the users table by adding and removing columns, renaming the table, and creating an index:

alter = AlterTable.new(users_table, schema)

sql = AcmeDB2.alter :users do
  add_column(:email, "string", null: false, unique: true)
  drop_column(:age)
  rename_table(:customers)
  create_index(:index_customers_on_email, [:email], unique: true)
end.to_sql

puts sql

This code:

1. Adds a email column with NOT NULL and UNIQUE constraints.

2. Drops the age column.

3. Renames the users table to customers.

4. Creates a unique index on the email column.

5. Generates the SQL statements that implement these actions.

Conclusion

The CQL::AlterTable class provides a simple and flexible interface for modifying your database schema. With its chainable methods, you can easily add, drop, or modify columns, rename tables, and manage foreign keys and indexes. This makes managing your database schema effortless, allowing you to focus on building robust, scalable applications.

Database migrations are essential for managing changes to your schema over time in a controlled manner. In CQL, migrations are handled through the Migration and Migrator classes. This guide will help you understand how to create, apply, rollback, and manage migrations using CQL::Migrator in your projects.

Why Use Migrations?

Migrations allow you to:

• Apply changes to your database schema over time.

• Roll back changes in case of errors or updates.

• Track applied and pending changes, ensuring consistency across environments.

Real-World Example: Creating and Applying Migrations

Let’s start with a simple example. Suppose we need to add a users table to our database with two columns: name and age.

Explanation

• The up method: defines the changes to apply when the migration is run (e.g., adding new columns).

• The down method: defines how to revert the changes (e.g., dropping columns).

• Versioning: Each migration is assigned a version number, which ensures migrations are run in the correct order.

Initializing the Schema and Migrator

Before applying migrations, you need to set up the schema and create an instance of the Migrator.

The migrator, upon initialization, automatically creates a schema_migrations table to track which migrations have been applied.

Applying Migrations

To apply all pending migrations, simply call the up method on the migrator object:

This will apply all pending migrations in order of their version numbers.

Applying Migrations Up to a Specific Version

You can also apply migrations up to a specific version:

This will apply all migrations up to version 1_i64.

Rolling Back Migrations

To roll back the last migration, use the down method:

You can also roll back to a specific migration version:

This rolls back all migrations down to version 1_i64.

Redoing Migrations

If you want to rollback and then re-apply the last migration, use the redo method:

This first rolls back the last migration and then re-applies it.

Listing Migrations

You can list applied, pending, and rolled-back migrations with the following commands:

• List Applied Migrations:
• List Pending Migrations:
• List Rolled Back Migrations:

These commands provide a clear view of the current state of your migrations, making it easy to track progress and issues.

Managing Migrations

Checking the Last Applied Migration

You can retrieve information about the last applied migration using:

This gives you details about the last migration that was successfully applied.

Advanced Example: Managing Multiple Migrations

Here’s an example where we define multiple migrations and apply them sequentially:

• Versioning ensures that migrations are applied in the correct order.

• Each migration can be applied and rolled back independently, offering flexibility in managing your database schema.

Conclusion

The CQL::Migrator class makes it easy to manage database migrations in a structured and version-controlled manner. By following this guide, you can:

• Create and apply migrations to modify your schema.

• Roll back changes if needed.

• Track applied and pending migrations to keep your database consistent across environments.

This approach is essential for teams working on large applications where database changes need to be applied safely and consistently over time.

The CQL::Delete class provides a structured and flexible way to build and execute SQL DELETE queries in your Crystal applications. This guide will help you understand how to create delete queries, apply conditions, and execute them to remove records from your database.

Key Features

1. Delete records from any table in a straightforward manner.

2. Filter records to delete using flexible WHERE conditions.

3. Return columns after deletion if needed.

4. Chainable syntax for clean and maintainable query building.

Real-World Example: Deleting a User Record

Let’s start with a simple example of deleting a user from the users table where the id is 1.

delete = CQL::Delete.new(schema)
  .from(:users)
  .where(id: 1)
  .commit

This query deletes the record in the users table where id = 1.

Core Methods

The following section provides a breakdown of the key methods available in the CQL::Delete class and how to use them effectively.

1. from(table : Symbol)

Purpose: Specifies the table from which records will be deleted.

• Parameters: table — A symbol representing the table name.

• Returns: Delete object (for chaining).

Real-World Example: Specifying the Table

delete.from(:users)

This sets the users table as the target for the delete operation.

2. where(**fields)

Purpose: Adds a WHERE clause to filter the records to be deleted.

• Parameters: fields — A key-value hash where keys represent column names and values represent the conditions to match.

• Returns: Delete object (for chaining).

Real-World Example: Filtering by Conditions

delete
  .from(:users)
  .where(id: 1)

This filters the query to only delete the user where id = 1.

3. where(&block)

Purpose: Adds a WHERE clause using a block for more complex filtering conditions.

• Parameters: A block that defines the filtering logic using a filter builder.

• Returns: Delete object (for chaining).

Real-World Example: Using a Block for Conditions

delete
  .from(:users)
  .where { |w| w.age < 30 }

This deletes all users where the age is less than 30.

4. commit

Purpose: Executes the delete query and commits the changes to the database.

• Returns: A DB::Result object representing the result of the query execution.

Real-World Example: Committing the Delete

delete = CQL::Delete.new(schema)
  .from(:users)
  .where(id: 1)
  .commit

This deletes the user from the users table where id = 1 and commits the change.

5. using(table : Symbol)

Purpose: Adds a USING clause to the delete query, useful when deleting records based on conditions from another table.

• Parameters: table — A symbol representing the name of the table to use in the USING clause.

• Returns: Delete object (for chaining).

Real-World Example: Using Another Table for Deletion

delete
  .from(:users)
  .using(:posts)
  .where { |w| w.posts.user_id == w.users.id }

This example deletes users where they are linked to posts based on the condition posts.user_id = users.id.

6. back(*columns : Symbol)

Purpose: Specifies the columns to return after the delete operation.

• Parameters: columns — An array of symbols representing the columns to return.

• Returns: Delete object (for chaining).

Real-World Example: Returning Columns After Deletion

delete
  .from(:users)
  .where(id: 1)
  .back(:name, :email)
  .commit

This deletes the user with id = 1 and returns the name and email of the deleted record.

7. to_sql(gen = @schema.gen)

Purpose: Generates the SQL query and parameters required for the delete operation.

• Parameters: gen — The generator used for SQL generation (default: schema generator).

• Returns: A tuple containing the SQL query string and the parameters.

Real-World Example: Generating SQL for Deletion

delete = CQL::Delete.new(schema)
  .from(:users)
  .where(id: 1)

sql, params = delete.to_sql
puts sql     # "DELETE FROM users WHERE id = $1"
puts params  # [1]

This generates the raw SQL query and its associated parameters without executing it.

Putting It All Together

Let’s combine multiple methods to handle a more advanced use case. Suppose you want to delete a user from the users table where they have no associated posts, and you want to return the deleted user’s name and email:

delete = CQL::Delete.new(schema)

result = delete
  .from(:users)
  .using(:posts)
  .where { |w| w.posts.user_id == w.users.id && w.posts.id.nil? }
  .back(:name, :email)
  .commit

puts result  # This returns the name and email of the deleted user(s).

In this query:

• We specify the users table as the target for deletion.

• We use the posts table to filter users without any posts.

• We return the name and email of the deleted user(s).

Conclusion

The CQL::Delete class provides an intuitive and powerful interface for deleting records in your Crystal applications. With chainable methods for setting conditions, joining tables, and selecting return columns, you can easily construct and execute delete queries with precision and clarity.

Whether you need to delete specific records or perform complex, condition-based deletions, the CQL::Delete class ensures that your queries are efficient and maintainable.

Active Record is a design pattern used in Object-Relational Mapping (ORM) that simplifies database access by linking database tables directly to classes in your application. Each class represents a table, and each instance of the class corresponds to a row in that table. Active Record makes it easy to perform CRUD (Create, Read, Update, Delete) operations on the database.

In the context of CQL (Crystal Query Language) and Crystal, the Active Record pattern can be implemented by leveraging the object-oriented nature of Crystal and the querying capabilities provided by CQL. Here's how you can think about the Active Record pattern using CQL:

Key Concepts of Active Record

1. Table-Class Mapping: Each class corresponds to a table in the database.

2. Row-Object Mapping: Each object is an instance of a class and corresponds to a row in the database table.

3. Database Operations as Methods: Methods on the object handle database interactions (e.g., .save, .delete, .find).

4. Associations: Relationships between tables (e.g., belongs_to, has_many) are handled within the class structure.

5. Validation: Logic to ensure data integrity is embedded within the class.

Implementing Active Record in CQL

1. Define a Model

In Active Record, a model is a class that represents a database table. The class will contain attributes (columns), methods for interacting with the data, and associations.

Here’s an example of a User model:

struct User
  include CQL::ActiveRecord::Model(Int32)
  # Schame name AcmeDB, table name :users
  db_context AcmeDB, :users

  property id : Int32?
  property name : String
  property email : String
  property created_at : Time
  property updated_at : Time
end

In this example:

• struct User is used instead of class User

• Include CQL::Record(User, Int32) specifies that User is the model and the primary key is of type Int32.

• The model still contains the properties (id, name, email, created_at, updated_at), but now we delegate all Active Record-like operations (e.g., save, delete) to CQL::Record.

2. Performing CRUD Operations

In the Active Record pattern, CRUD operations (Create, Read, Update, Delete) are performed directly on the class and instance methods. Here’s how you can implement CRUD with CQL:

Create (INSERT)

To create a new record in the database, instantiate a new object of the model class and call .save to persist it:

user = User.new
user.name = "John Doe"
user.email = "john.doe@example.com"
user.created_at = Time.now
user.updated_at = Time.now
user.save # INSERTS the new user into the database

This will generate an INSERT INTO SQL statement and persist the user in the users table.

Read (SELECT)

To retrieve records from the database, you can use class-level methods like .find or .all. For example:

• Fetch all users:

users = User.all # SELECT * FROM users

• Find a user by id:

user = User.find(1) # SELECT * FROM users WHERE id = 1

Update

To update an existing record, modify the object and call .save again. This will generate an UPDATE SQL statement:

user = User.find(1)
user.name = "Jane Doe"
user.save # UPDATE users SET name = 'Jane Doe' WHERE id = 1

Delete

To delete a record, find the object and call .delete:

user = User.find(1)
user.delete # DELETE FROM users WHERE id = 1

3. Associations

Active Record also simplifies relationships between tables, such as has_many and belongs_to. In CQL, you can implement these relationships like this:

has_many (One-to-Many Relationship)

A User has many Posts. You can db_context the association like this:

struct User < CQL::Record(User, Int32)

  property id : Int32?
  property name : String
  property email : String
  property created_at : Time
  property updated_at : Time

  has_many :posts, Post
end

struct Post
  include CQL::ActiveRecord::Model(Int32)

  property id : Int32?
  property title : String
  property body : String
  property created_at : Time
  property updated_at : Time

  belongs_to :user, User
end

Now you can fetch the posts for a user:

user = User.find(1)
posts = user.posts # SELECT * FROM posts WHERE user_id = 1

belongs_to (Many-to-One Relationship)

The Post class has a belongs_to relationship with User. This means each post belongs to a user:

post = Post.find(1)
user = post.user # SELECT * FROM users WHERE id = post.user_id

Managing HasMany and ManyToMany Collections

Here is a summary of the collection methods provided in the CQL::Relations::Collection and CQL::Relations::ManyCollection classes for managing associations in a one-to-many and many-to-many relationship in CQL:

Collection Methods

1. all:

   • Returns all associated records for the parent record.

   • Example: movie.actors.all

2. reload:

   • Reloads the associated records from the database.

   • Example: movie.actors.reload

3. ids:

   • Returns a list of primary keys for the associated records.

   • Example: movie.actors.ids

4. <<:

   • Adds a new record to the association and persists it to the database.

   • Example: movie.actors << Actor.new(name: "Laurence Fishburne")

5. empty?:

   • Checks if the association has any records.

   • Example: movie.actors.empty?

6. **exists?(**attributes)**:

   • Checks if any associated records exist that match the given attributes.

   • Example: movie.actors.exists?(name: "Keanu Reeves")

7. size:

   • Returns the number of associated records.

   • Example: movie.actors.size

8. **find(**attributes)**:

   • Finds associated records that match the given attributes.

   • Example: movie.actors.find(name: "Keanu Reeves")

9. **create(**attributes)**:

   • Creates a new record with the provided attributes and associates it with the parent.

   • Example: movie.actors.create(name: "Carrie-Anne Moss")

10. create!(record):

    • Creates and persists a new record with the provided attributes, raising an error if it fails.

    • Example: movie.actors.create!(name: "Hugo Weaving")

11. ids=(ids : Array(Pk)):

    • Associates the parent record with the records that match the provided primary keys.

    • Example: movie.actors.ids = [1, 2, 3]

12. delete(record : Target):

    • Deletes the associated record from the parent record if it exists.

    • Example: movie.actors.delete(Actor.find(1))

13. delete(id : Pk):

    • Deletes the associated record by primary key.

    • Example: movie.actors.delete(1)

14. clear:

    • Removes all associated records for the parent record.

    • Example: movie.actors.clear

ManyCollection Additional Methods

• In addition to the methods inherited from Collection, the ManyCollection class also manages associations through a join table in many-to-many relationships.

1. create(record : Target):

   • Associates the parent record with the created record through a join table.

   • Example: movie.actors.create!(name: "Carrie-Anne Moss")

2. delete(record : Target):

   • Deletes the association through the join table between the parent and associated record.

   • Example: movie.actors.delete(Actor.find(1))

3. ids=(ids : Array(Pk)):

   • Associates the parent record with the records matching the primary keys through the join table.

   • Example: movie.actors.ids = [1, 2, 3]

These methods provide powerful ways to interact with and manage associations between records in a CQL-based application using both one-to-many and many-to-many relationships.

4. Validation

Active Record often includes validations to ensure that data meets certain criteria before saving. In CQL, you can add custom validation logic inside the class:

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

  property id : Int32?
  property name : String
  property email : String
  property created_at : Time
  property updated_at : Time

  def validate
    raise "Name can't be empty" if name.empty?
    raise "Email can't be empty" if email.empty?
  end

  def save
    validate
    super # Calls the original save method provided by CQL::Entity
  end
end

In this example, before saving a user, the validate method is called to ensure that the name and email are not empty.

5. Migrations

Although not strictly part of Active Record, migrations are commonly used with it to modify database schemas over time. In CQL, migrations can be written using a migration system to create and alter tables. For example:

class CreateUsersTable < CQL::Migration
  self.version = 1_i64

  def up
    schema.alter :users do
      add_column :name, String
      add_column :age, Int32
    end
  end

  def down
    schema.alter :users do
      drop_column :name
      drop_column :age
    end
  end
end

This migration would create the users table with the specified columns.

How CQL Implements Active Record Principles

• Model Representation: Each class (like User, Post) maps directly to a database table.

• CRUD Operations: Operations like .save, .delete, .find, and .all are built into the CQL framework, allowing for seamless interaction with the database.

• Associations: Relationships between models are defined using macros like has_many and belongs_to, which make querying associated records straightforward.

• Encapsulation of Business Logic: Validation and other business rules can be embedded directly into model classes.

• Database Migrations: Schema changes are managed through migrations, which help keep the database structure synchronized with the application's models.

Example Workflow with Active Record and CQL

1. db_context your models:

   • Create User and Post classes that correspond to users and posts tables.

2. Run Migrations:

   • Use migrations to create or modify the database schema.

3. Perform CRUD operations:

   • Create, read, update, and delete records using model methods like .save and .delete.

4. Manage relationships:

   • db_context associations like has_many and belongs_to to handle relationships between models.

5. Enforce business rules:

   • Use validation methods to ensure data integrity.

By following the Active Record pattern in CQL, you can build a robust data access layer in your Crystal application with minimal effort while keeping your code clean and maintainable.

The Repository pattern is a design pattern that mediates between the domain and data mapping layers using a collection-like interface for accessing domain objects. It centralizes data access logic, promoting a cleaner separation of concerns and making the application easier to test and maintain.

In CQL (Crystal Query Language), while the Active Record pattern is a prominent feature, CQL's design is flexible enough to support the Repository pattern. This allows developers to abstract the underlying data persistence mechanisms further and work with domain objects in a more decoupled manner.

Key Concepts of the Repository Pattern

1. Abstraction of Data Access: The repository provides an abstraction layer over data storage. Consumers of the repository work with domain objects and are unaware of how those objects are persisted or retrieved.

2. Collection-Like Interface: Repositories often expose methods that resemble collection operations, such as add, remove, find, all, etc.

3. Centralized Query Logic: Queries related to a specific aggregate root or entity are encapsulated within its repository.

4. Decoupling: It decouples the domain model from the data access concerns, improving testability and maintainability.

5. Unit of Work (Optional but Common): Repositories are often used in conjunction with a Unit of Work pattern to manage transactions and track changes to objects.

Implementing the Repository Pattern with CQL

CQL provides a generic CQL::Repository(T, Pk) class that can be used as a base for creating specific repositories for your domain entities.

1. Define a Model (Entity)

First, you need a model (often a plain Crystal struct or class) that represents your domain entity. Unlike Active Record models, these entities typically don't include persistence logic themselves.

# Example: A simple User entity
struct User
  property id : Int32?
  property name : String
  property email : String

  def initialize(@name : String, @email : String, @id : Int32? = nil)
  end
end

2. Create a Specific Repository

You create a repository for your entity by inheriting from CQL::Repository(T, Pk), where T is your entity type and Pk is the type of its primary key.

require "cql"

# Define your schema (database connection)
MySchema = CQL::Schema.define(
  :my_app_db,
  adapter: CQL::Adapter::Postgres, # or any other supported adapter
  uri: ENV["DATABASE_URL"]? || "postgres://localhost:5432/my_app_dev"
)

# Define the User entity (as above)
struct User
  property id : Int32?
  property name : String
  property email : String

  def initialize(@name : String, @email : String, @id : Int32? = nil)
  end

  # Constructor to map from a Hash, often useful with database results
  def self.new(attrs : Hash(Symbol, DB::Any))
    new(
      name: attrs[:name].as(String),
      email: attrs[:email].as(String),
      id: attrs[:id]?.as?(Int32)
    )
  end
end

# UserRepository inheriting from CQL::Repository
class UserRepository < CQL::Repository(User, Int32)
  # The constructor takes the schema instance and the table name
  def initialize(schema : CQL::Schema = MySchema, table_name : Symbol = :users)
    super(schema, table_name)
  end

  # You can add custom query methods here
  def find_active_users
    query.where { active == true }.all(User)
  end

  def find_by_email_domain(domain : String)
    query.where { email.like("%@#{domain}") }.all(User)
  end
end

Explanation:

• UserRepository < CQL::Repository(User, Int32): Defines a repository for User entities where the primary key is Int32.

• initialize(schema : CQL::Schema = MySchema, table_name : Symbol = :users): The constructor expects a CQL::Schema instance and the symbol representing the database table name (e.g., :users).

• super(schema, table_name): Calls the constructor of the base CQL::Repository class.

• find_active_users, find_by_email_domain: Custom methods that encapsulate specific query logic using CQL's query builder (query.where(...)). The query method is provided by the base CQL::Repository.

3. Using the Repository

Once defined, you can use the repository to interact with your data:

# Initialize the repository
user_repo = UserRepository.new

# Create a new user
# The `create` method in the base Repository expects a Hash of attributes
new_user_id = user_repo.create({name: "Alice Wonderland", email: "alice@example.com"})
puts "Created user with ID: #{new_user_id}"

# Find a user by ID
alice = user_repo.find(new_user_id.as(Int32))
if alice
  puts "Found user: #{alice.name}"
else
  puts "User not found."
end

# Fetch all users
all_users = user_repo.all
puts "All users:"
all_users.each do |user|
  puts "- #{user.name} (#{user.email})"
end

# Update a user
if alice
  user_repo.update(alice.id.not_nil!, {email: "alice.wonderland@newdomain.com"})
  updated_alice = user_repo.find(alice.id.not_nil!)
  puts "Updated email: #{updated_alice.try &.email}"
end

# Use custom repository methods
active_users = user_repo.find_active_users # Assuming an 'active' column and logic
puts "Active users: #{active_users.size}"

# Delete a user
if alice
  user_repo.delete(alice.id.not_nil!)
  puts "Deleted user #{alice.name}"
end

Methods Provided by CQL::Repository(T, Pk)

The base CQL::Repository class provides several convenient methods for common data operations:

• query: Returns a new CQL::Query instance scoped to the repository's table.

• insert: Returns a new CQL::Insert instance scoped to the repository's table.

• update: Returns a new CQL::Update instance scoped to the repository's table.

• delete: Returns a new CQL::Delete instance scoped to the repository's table.

• build(attrs : Hash(Symbol, DB::Any)) : T: Instantiates a new entity T with the given attributes (does not persist).

• all : Array(T): Fetches all records.

• find(id : Pk) : T?: Finds a record by its primary key, returns nil if not found.

• find!(id : Pk) : T: Finds a record by its primary key, raises DB::NoResultsError if not found.

• **find_by(**fields) : T?**: Finds the first record matching the given field-value pairs.

• **find_all_by(**fields) : Array(T)**: Finds all records matching the given field-value pairs.

• create(attrs : Hash(Symbol, DB::Any)) : Pk: Creates a new record with the given attributes and returns its primary key.

• **create(**fields) : Pk**: Creates a new record with the given attributes (using named arguments) and returns its primary key.

• update(id : Pk, attrs : Hash(Symbol, DB::Any)): Updates the record with the given id using the provided attributes.

• update(id : Pk, **fields): Updates the record with the given id using the provided attributes (named arguments).

• update_by(where_attrs : Hash(Symbol, DB::Any), update_attrs : Hash(Symbol, DB::Any)): Updates records matching where_attrs with update_attrs.

• update_all(attrs : Hash(Symbol, DB::Any)): Updates all records in the table with the given attributes.

• delete(id : Pk): Deletes the record with the given primary key.

• **delete_by(**fields)**: Deletes records matching the given field-value pairs.

• delete_all: Deletes all records in the table.

• count : Int64: Returns the total number of records in the table.

• **exists?(**fields) : Bool**: Checks if any records exist matching the given field-value pairs.

Advantages of Using the Repository Pattern with CQL

• Improved Testability: You can easily mock repositories in your tests, isolating your domain logic from the database.

• Centralized Data Access Logic: Keeps data access concerns separate from your domain entities and application services.

• Flexibility: While CQL::Repository provides a good starting point, you can customize it or even write your own repository implementations if needed, without altering your domain models significantly.

• Clearer Intent: Queries are named and reside within the repository, making their purpose more explicit.

When to Use the Repository Pattern

• In larger applications where a clear separation between domain logic and data access is crucial.

• When you need to support multiple data sources or switch data storage technologies with minimal impact on the domain layer.

• When your querying logic becomes complex and you want to encapsulate it.

• To improve the testability of your application by allowing easier mocking of data access.

While Active Record can be simpler for straightforward CRUD operations, the Repository pattern offers more structure and decoupling for complex applications, and CQL provides the tools to implement it effectively.

Accelerating Database Iteration

Defining the schema first is a fundamental approach in CQL, helping developers quickly structure their database while keeping their application’s data model in sync with real-world entities. By defining your schema upfront, you can rapidly iterate over your database tables, making it easy to adjust data structures as your application evolves. This method ensures that your schema is the single source of truth, giving you a clear view of how your data is organized and how relationships between different tables are modeled.

Benefits of Defining the Schema First

1. Faster Prototyping: With schemas defined at the outset, you can rapidly experiment with different table structures and relationships, making it easier to adjust your application’s data model without writing complex migrations from scratch.

2. Clear Data Structure: When your schema is predefined, the application’s data structure becomes clearer, allowing developers to conceptualize how data is organized and interact with tables more easily.

3. Consistency: Ensuring the schema matches the database at all times removes ambiguity when writing queries, handling relationships, or performing migrations.

4. Automatic Data Validation: CQL schemas enforce data types and constraints, such as primary, auto_increment, and text, ensuring data integrity.

5. Simplified Query Building: Since the schema is explicit, writing queries becomes easier as you can reference schema objects directly in queries, avoiding mistakes or typos in table or column names.

Difference from Other ORM Libraries

Unlike traditional ORM libraries (e.g., Active Record in Rails or Ecto in Elixir), which often allow defining database models alongside the code and handling schema evolution through migrations, CQL encourages defining the database schema as the first step.

This "schema-first" approach differs from the "code-first" or "migration-based" methodologies in that it avoids relying on automatic migrations or conventions to infer the structure of the database. CQL enforces an explicit and structured approach to schema creation, ensuring the database schema reflects the actual architecture of your application.

Example Schema Definition

Here’s a basic example of how to define a schema in CQL for a movie-related database:

Explanation of Schema Definition

• Database name: :acme_db defines the schema name.

• Adapter: CQL::Adapter::Postgres specifies the database adapter (in this case, PostgreSQL).

• Connection URL: The uri: ENV["DATABASE_URL"] specifies the database connection using environment variables.

Each table is explicitly defined with its columns, such as:

• :movies table has id as the primary key and title as a text column.

• :screenplays, :actors, and :directors define relationships between movies and associated records.

This example shows how easy it is to define tables and manage relationships within the schema, leading to a more organized and coherent database structure that aligns with the application’s needs.

Multiple Schemas: Flexibility and Easy Switching

One significant advantage of CQL is the ability to define and manage multiple schemas within the same application. This is particularly useful in scenarios like multi-tenant applications, where each tenant or environment has a separate database schema. CQL makes switching between schemas seamless, enabling developers to organize different parts of the application independently while maintaining the same connection configuration.

This approach offers the following benefits:

• Clear Separation of Data: Each schema can encapsulate its own set of tables and relationships, allowing better isolation and separation of concerns within the application. For example, you might have a main schema for core business data and a separate analytics schema for reporting.

• Simple Switching: Switching between schemas is as simple as referring to the schema name, thanks to CQL’s structured definition of schemas. This allows dynamic switching at runtime, improving scalability in multi-tenant applications.

Example: Managing Multiple Schemas

In this example, you define multiple schemas, and the application can easily switch between MainDB and AnalyticsDB depending on which database needs to be queried.

Benefits of Multiple Schemas

• Improved Organization: Separate business logic data from other concerns like reporting, testing, or archiving.

• Scalability: Ideal for multi-tenant applications, allowing each tenant to have its schema without interference.

By using CQL’s schema system, you gain not only speed and clarity in your database structure but also flexibility in scaling and organizing your application.

CQL Active Record models provide a rich set of methods for performing CRUD (Create, Read, Update, Delete) operations on your database records. These methods are designed to be intuitive and align with common Active Record patterns.

This guide assumes you have a model defined, for example:

1. Creating Records

There are several ways to create new records and persist them to the database.

new + save / save!

The most fundamental way is to create a new instance of your model using new and then call save or save! to persist it.

• save: Attempts to save the record. Runs validations. If successful, populates the id (if auto-generated) and returns true. If validations fail or another before_save callback halts the chain, it returns false and does not persist the record. The errors collection on the instance can be inspected.

• save!: Similar to save, but raises an exception if the record cannot be saved.

  • Raises CQL::Errors::RecordInvalid if validations fail.

  • May raise other database-related exceptions or CQL::Errors::RecordNotSaved for other save failures.

create / create! (Class Methods)

These class methods provide a convenient way to instantiate and save a record in a single step.

• Model.create(attributes): Creates a new instance with the given attributes (as a Hash or keyword arguments) and attempts to save it. Returns the model instance (which might be invalid and not persisted if save failed) or false if a before_create callback halted the process.

• Model.create!(attributes): Similar to create, but calls save! internally. It will raise an exception (CQL::Errors::RecordInvalid or other) if the record cannot be created and persisted.

find_or_create_by (Class Method)

This method attempts to find a record matching the given attributes. If found, it returns that record. If not found, it creates and persists a new record with those attributes (plus any additional ones provided if the find attributes are a subset of create attributes).

• It uses create! internally for the creation part, so it can raise exceptions if the creation fails (e.g., due to validations).

2. Reading Records

Summary of Common Finders:

• Model.all: Retrieves all records of the model type. Returns Array(Model).
• Model.find?(id : Pk) (or Model.find(id : Pk)): Finds a record by its primary key. Returns Model? (the instance or nil).
• Model.find!(id : Pk): Finds a record by its primary key. Returns Model or raises DB::NoResultsError (or similar if not found).
• Model.find_by(**attributes): Finds the first record matching the given attributes. Returns Model?.
• Model.find_by!(**attributes): Finds the first record matching attributes. Returns Modelor raisesDB::NoResultsError.
• Model.find_all_by(**attributes): Finds all records matching attributes. Returns Array(Model).
• Model.first: Retrieves the first record (ordered by primary key). Returns Model?.

• Model.last: Retrieves the last record (ordered by primary key). Returns Model?.

• Model.count: Returns the total number of records as Int64.

• Model.query.[condition].count: Counts records matching specific conditions.

3. Updating Records

To update existing records, you typically load an instance, modify its attributes, and then save it.

Load, Modify, and save / save!

This is the standard approach:

update / update! (Instance Methods)

These instance methods provide a shortcut to assign attributes and then save the record.

• instance.update(attributes): Assigns the given attributes (Hash or keyword arguments) to the instance and then calls save. Returns true if successful, false otherwise.

• instance.update!(attributes): Assigns attributes and calls save!. Raises an exception if saving fails (e.g., CQL::Errors::RecordInvalid).

Model.update!(id, attributes) (Class Method)

This class method updates a record identified by its primary key with the given attributes. It typically loads the record, updates attributes, and calls save!. Can raise DB::NoResultsError if the ID is not found, or validation/save exceptions.

Batch Updates (Model.update_by, Model.update_all)

These methods allow updating multiple records at once without instantiating each one.

• Model.update_by(conditions_hash, updates_hash): Updates all records matching conditions_hash with the attributes in updates_hash.

• Model.update_all(updates_hash): Updates all records in the table with the attributes in updates_hash. Use with extreme caution!

These methods typically execute a single SQL UPDATE statement and do not instantiate records, run validations, or trigger callbacks.

4. Deleting Records

Records can be deleted individually or in batches.

delete! (Instance Method)

Deletes the specific model instance from the database.

• Runs before_destroy and after_destroy callbacks.

• Returns true if successful. Returns false if a before_destroy callback halts the operation.

Model.delete!(id : Pk) (Class Method)

Deletes the record with the specified primary key directly from the database.

• This method typically does not run Active Record callbacks (before_destroy, after_destroy) as it usually issues a direct SQL DELETE command.

• It might raise an exception if the record doesn't exist or if there's a database error.

Batch Deletes (Model.delete_by!, Model.delete_all)

These class methods delete multiple records based on conditions or all records from a table.

• Model.delete_by!(attributes_hash): Deletes all records matching the attributes_hash.

• Model.delete_all: Deletes all records from the model's table. Use with extreme caution!

These methods typically execute direct SQL DELETE statements and do not instantiate records or run Active Record callbacks.

Always be careful with batch delete operations, especially delete_all, as they can lead to irreversible data loss if not used correctly.

Software design patterns are general, reusable solutions to commonly occurring problems within a given context in software design. In the realm of Object-Relational Mapping (ORM) and database interaction, several established patterns help structure how applications access and manipulate data.

This section explores some of the key data access and ORM patterns that can be implemented or are relevant when working with Crystal Query Language (CQL). Understanding these patterns can help you design more maintainable, scalable, and understandable data access layers in your Crystal applications.

We will delve into the following patterns:

• Active Record: An object that wraps a row in a database table or view, encapsulates database access, and adds domain logic on that data.

• Entity Framework (Conceptual Overview): While CQL is not a direct port of Microsoft's Entity Framework, this section will discuss some conceptual similarities or how related ideas (like a rich object context and LINQ-style querying) might apply or inspire usage with CQL.

• Repository: Mediates between the domain and data mapping layers using a collection-like interface for accessing domain objects.

Each pattern offers different trade-offs in terms of simplicity, flexibility, testability, and separation of concerns. Explore the specific guides to understand how they can be applied with CQL.

In the context of CQL (Crystal Query Language), Entity Framework (EF) serves as a useful comparison to help developers understand how CQL and its features (like migrations, schema management, and object-relational mapping) work. Just as EF simplifies database interactions in .NET applications, CQL does the same for Crystal applications. Let’s break down the key concepts and approaches of EF and see how they align with CQL’s functionalities.

1. Development Patterns

In Entity Framework, developers have three approaches to database design: Database-First, Code-First, and Model-First. CQL shares some similarities, especially with the Code-First and Database-First approaches, but with Crystal-specific tooling.

CQL's Schema-First Approach (Similar to Code-First)

CQL primarily uses a Schema-First approach, where you define your database schema using Crystal code and CQL builds and manages the database based on this schema. This is similar to EF’s Code-First approach, where the developer defines the entity classes and EF generates the database schema.

Example in CQL:

schema = CQL::Schema.build(:my_db, adapter: CQL::Adapter::SQLite, uri: "sqlite3://db.sqlite3") do |s|
  table :users do
    primary :id, Int32
    column :name, String
    column :age, Int32
  end
end
schema.init

This code defines the users table and its columns (id, name, and age), which CQL will use to generate the corresponding database structure.

2. Core Concepts in CQL (Similar to EF Concepts)

Schema and Tables (Equivalent to EF’s DbContext and DbSet)

In EF, a DbContext is used to manage the entities (mapped to database tables), and each DbSet represents a collection of entities (mapped to individual tables). Similarly, in CQL:

• CQL::Schema manages the structure of your database.

• Tables are defined within the schema using table blocks.

Example:

schema = CQL::Schema.build(:my_db, adapter: CQL::Adapter::SQLite, uri: "sqlite3://db.sqlite3") do |s|
  table :products do
    primary :id, Int32
    column :name, String
    column :price, Float64
  end
end

This is similar to defining DbSet<Product> in EF, which represents the products table.

Migrations (Similar to EF Migrations)

In CQL, migrations are a key feature, similar to EF’s migrations, for managing database schema changes over time. Migrations allow you to evolve your schema, apply changes, roll them back, and maintain a history of modifications.

Example migration in CQL:

class CreateUsersTable < CQL::Migration
  self.version = 1_i64

  def up
    schema.alter :users do
      add_column :name, String
      add_column :age, Int32
    end
  end

  def down
    schema.alter :users do
      drop_column :name
      drop_column :age
    end
  end
end

In EF, migrations are handled using commands like Add-Migration and Update-Database. In CQL, migrations are applied and managed using the CQL::Migrator class, which provides methods for applying, rolling back, and listing migrations.

Applying migrations in CQL:

migrator = CQL::Migrator.new(schema)
migrator.up  # Apply all pending migrations

This is similar to EF’s Update-Database command, which applies migrations to the database.

Entities and Relationships (Similar to EF Entities and Navigation Properties)

In CQL, database tables and columns are defined as part of the schema, and relationships like foreign keys can be established. This is comparable to how entities and relationships between them are managed in EF.

Example in CQL (adding a foreign key):

schema = CQL::Schema.build(:my_db, adapter: CQL::Adapter::SQLite, uri: "sqlite3://db.sqlite3") do |s|
  table :orders do
    primary :id, Int32
    bigint :user_id
    text :order_date
    foreign_key :user_id, :users, :id, on_delete: "CASCADE"
  end
end

This defines an orders table with a foreign key relationship to the users table, similar to how EF handles one-to-many or many-to-many relationships.

3. Life Cycle of Migrations and Schema Changes

CQL’s migration life cycle aligns closely with EF’s migrations system. In both cases, you:

1. Define migrations to introduce schema changes.

2. Apply migrations to update the database schema.

3. Rollback or redo migrations if needed to manage schema changes.

Example Migration Workflow in CQL:

migrator.up   # Apply pending migrations
migrator.down # Rollback the last migration
migrator.redo # Rollback and reapply the last migration

In EF, you would use commands like Update-Database, Remove-Migration, and Add-Migration to manage these operations.

4. Querying the Database (CQL vs. EF’s LINQ to Entities)

In EF, developers use LINQ to query entities and interact with the database. CQL also allows querying the database but through SQL-like syntax, aligning with Crystal’s programming style.

Example query in CQL:

AcmeDB
  .query
  .from(:products)
  .select(:name, :price)
  .where(name: "Laptop")
  .all(Product)

This retrieves all products where the name is "Laptop", similar to how LINQ queries work in EF:

var laptops = dbContext.Products.Where(p => p.Name == "Laptop").ToList();

Both frameworks provide abstractions that avoid writing raw SQL, but CQL maintains a more SQL-like approach in its query building.

5. Advantages of Using CQL for Crystal Developers

• Productivity: Like EF, CQL reduces the need for writing raw SQL by allowing developers to work with objects (schemas, tables, columns).

• Schema Management: CQL migrations simplify managing database changes, ensuring your schema evolves without breaking changes.

• Consistency: CQL’s Schema-First approach ensures the database schema is in sync with your application’s Crystal code, similar to EF’s Code-First approach.

Conclusion

CQL provides similar ORM-like features for Crystal that Entity Framework does for .NET. Whether using migrations, schema definitions, or querying data, both CQL and EF streamline database interactions, making it easier to manage complex applications. By understanding the core parallels between these two systems, Crystal developers can better leverage CQL’s powerful schema management and migration tools to build scalable and maintainable database-driven applications.

Welcome to CQL Active Record! This guide will help you set up CQL in your Crystal project, connect to a database, define your first model, and perform basic CRUD, and first query. Use idiomatic Crystal and CQL, and provide clear, step-by-step examples.

1. Installation

Add CQL to your shard.yml:

dependencies:
  cql:
    github: your-org/cql
    version: ~> 1.0

Then install dependencies:

shards install

2. Database Setup

Configure your database connection. For example, to use PostgreSQL:

require "cql"
require "cql/pg"

# Define a database context
AcmeDB = CQL::DBContext.new(
  CQL::PG::Driver.new(
    host: "localhost",
    user: "postgres",
    password: "password",
    database: "acme_db"
  )
)

3. Defining a Model

Create a model that maps to a table:

struct User
  include CQL::ActiveRecord::Model(Int32)
  db_context AcmeDB, :users

  property id : Int32?
  property name : String
  property email : String
  property active : Bool = false
  property created_at : Time?
  property updated_at : Time?
end

4. Running Migrations

Create your tables using migrations (optional but recommended):

class CreateUsers < CQL::Migration
  self.version = 1_i64

  def up
    schema.create :users do
      primary_key :id, :serial
      column :name, :string
      column :email, :string
      column :active, :bool, default: false
      column :created_at, :timestamp
      column :updated_at, :timestamp
    end
  end

  def down
    schema.drop :users
  end
end

Run your migrations using your preferred migration runner.

5. Basic CRUD Operations

Create

user = User.new(name: "Alice", email: "alice@example.com")
user.save

Read

user = User.query.where(name: "Alice").first(User)
puts user.try(&.email)

Update

user = User.query.where(name: "Alice").first(User)
if user
  user.active = true
  user.save
end

Delete

user = User.query.where(name: "Alice").first(User)
user.try(&.delete)

6. Your First Query

# Find all active users
active_users = User.query.where(active: true).all(User)
active_users.each { |u| puts u.name }

Next Steps

• Defining Models

• Querying

• Complex Queries

• Validations, Callbacks, and More

This guide explores how to leverage database transactions effectively within your Crystal applications using CQL's Active Record pattern. We will use a practical banking domain example throughout to illustrate key concepts and best practices.

This documentation covers the functionality provided by the @transactions.md and @transactional.cr modules within the library.

1. Introduction to Transactions in CQL

What are Transactions?

In database systems, a transaction is a single unit of work. This unit comprises one or more operations that are treated as an indivisible sequence. The core principle is that either all operations within the transaction complete successfully (commit) or none of them do (rollback).

Imagine a simple task like transferring money between two bank accounts. This isn't just one database operation; it typically involves:

1. Debiting the sender's account.

2. Crediting the recipient's account.

If the debit succeeds but the credit fails (e.g., due to a network error or a constraint violation), the system would be in an inconsistent state – money is gone from one account but didn't appear in the other. Transactions prevent this by ensuring that both steps must succeed together. If the credit fails, the debit is automatically undone.

Why are they important in domain modeling?

In application development, especially when dealing with complex business logic and multiple related data changes, transactions are crucial for:

• Maintaining Data Consistency: Ensuring that your database adheres to all predefined rules and constraints.

• Preventing Partial Updates: Avoiding scenarios where only a portion of a multi-step operation completes, leaving data in an invalid state.

• Handling Concurrency: Providing a mechanism to manage simultaneous access and modifications to data by multiple users or processes (though concurrency requires careful consideration of isolation levels and potential deadlocks).

• Simplifying Error Recovery: If an error occurs within a transaction, you know the database will revert to its state before the transaction began, making error handling and recovery more predictable.

Transactions provide the crucial ACID properties:

• Atomicity: The transaction is a single unit; either it completes entirely or has no effect.

• Consistency: A transaction brings the database from one valid state to another.

• Isolation: Concurrent transactions do not interfere with each other. Each sees the database as if it were running alone.

• Durability: Once a transaction is committed, the changes are permanent and survive system failures.

Supported Features in the library

CQL's Active Record implementation simplifies transaction management in Crystal. By including the CQL::ActiveRecord::Transactional module in your model, you gain access to the transaction class method. This method provides a block-based interface for executing a series of database operations within a single transaction.

Use Case: Bank Transfer

To demonstrate transactions, we will use a simplified banking application.

Overview of the domain

The core operation is transferring money between accounts. This operation must be atomic – the debit from one account and the credit to another must happen together. We also need to track these operations for auditing purposes.

Entities Involved

• BankAccount: Stores account details and balance.

• Transaction: Records deposits, withdrawals, and transfers.

• AuditLog: Provides a detailed log of system activities.

(Detailed schema and model definitions are omitted for brevity, but assume standard columns like id, balance, amount, type, created_at, etc., as introduced in the schema section of previous versions).

3. Writing Transactional Logic with CQL

The core logic for banking operations like withdrawals, deposits, and transfers involves updating one or more BankAccount records, creating a Transaction record, and often creating an AuditLog record. These steps must be performed atomically. This is where the BankAccount.transaction block becomes essential.

The BankAccount.transaction Block

Any database operations performed using CQL Active Record methods inside a BankAccount.transaction do ... end block are treated as a single unit by the database.

• When the block is entered, a database transaction is started (BEGIN).

• When the block completes without raising an exception, the transaction is committed (COMMIT), making all changes permanent.

• If any exception is raised within the block, the transaction is automatically rolled back (ROLLBACK), discarding all changes made during the transaction.

• You can also explicitly call tx.rollback on the yielded transaction object tx to manually roll back.

This ensures that if any step of a multi-operation process fails, the entire set of operations is undone, maintaining data integrity.

Transaction Logic Examples

Here are the core transaction blocks for our banking operations, demonstrating the use of CQL features within the atomic unit:

Withdrawal Transaction

This transaction debits a BankAccount and records the event.

Why a Transaction is Useful Here: While a withdrawal is simpler than a transfer, using a transaction ensures that the account balance update and the recording of the transaction/audit log happen together. If the record-keeping fails, the balance change is undone.

Deposit Transaction

This transaction credits a BankAccount and records the event.

Why a Transaction is Useful Here: Similar to withdrawal, it guarantees that the balance update and the logging/recording of the deposit happen together.

Transfer Transaction (Highlighting Atomicity)

This is the prime example where transactions are critical. Money must be debited from one account and credited to another and records created, all or nothing.

Why a Transaction is Crucial Here: This is the quintessential transaction use case. The debit and credit must happen together. If the debit succeeds but the credit fails (or vice versa, or if the transaction/audit log creation fails), the transaction ensures that all changes are undone, preventing money from being lost or duplicated. It guarantees Atomicity.

Handling Failures and Rollback

As shown in the conceptual examples, any unhandled exception raised within the BankAccount.transaction do |tx| ... end block will automatically trigger a database rollback. This is a key feature that ensures atomicity.

You can also explicitly roll back the transaction using the transaction object yielded to the block:

When tx.rollback is called, the database driver is instructed to perform a rollback. Any subsequent operations within the block before it exits will also be part of the rolled-back transaction.

Querying Transaction History and Audit Logs using CQL DSL

Use CQL's query builder (.query, .where, .order, .all, .exists?) to inspect the records created by successful transactions:

Explicit Commit and Rollback

While CQL transactions automatically commit if the block finishes without an unhandled exception and automatically roll back if an exception occurs, you can also explicitly control the outcome using tx.commit and tx.rollback on the yielded transaction object.

Explicit Rollback:

You can manually trigger a rollback at any point within the transaction block. This is useful if business logic dictates that a transaction should not proceed, even if no technical error (exception) has occurred.

Explicit Commit:

Similarly, you can explicitly commit a transaction before the end of the block. This can be useful in more complex scenarios, though it's less common than explicit rollback or relying on the implicit commit.

Important Note:

As stated in the Crystal DB documentation: After commit or rollback are used, the transaction is no longer usable. The connection is still open but any statement will be performed outside the context of the terminated transaction. This means you should typically not perform further database operations relying on that specific tx object after calling tx.commit or tx.rollback.

4. Nested Transactions (Savepoints)

CQL supports nested transactions using database savepoints. This is useful for sub-operations within a larger transaction that might need to be rolled back independently without affecting the outer transaction.

To create a nested transaction, pass an existing transaction object (e.g., outer_tx) to the Model.transaction method: Model.transaction(outer_tx) do |inner_tx| ... end. This creates a SAVEPOINT.

Here are common scenarios:

Scenario 1: Successful Inner and Outer Commit

Both nested and outer operations are committed.

Scenario 2: Inner Rollback (Explicit inner_tx.rollback), Outer Commit

Only inner operations roll back. Outer operations commit.

After inner_tx.rollback, the inner_tx object is no longer usable for DB operations in that context.

Scenario 3: Inner Rollback (using raise DB::Rollback), Outer Commit

DB::Rollback in the inner transaction rolls back only inner operations. The exception is handled internally, allowing the outer transaction to commit.

Scenario 4: Inner Failure (Standard Exception), Entire Transaction Rolls Back

A standard exception in the inner block, if not caught and handled within that inner block, rolls back both inner and outer transactions.

Scenario 5: Outer Rollback After Inner Success

If the inner transaction completes but the outer transaction subsequently rolls back, all changes (inner and outer) are discarded.

Key Summary Points:

• Savepoints: Nested transactions (Model.transaction(outer_tx)) use database SAVEPOINTs.

• inner_tx.rollback: Rolls back only the inner transaction to its savepoint. The outer transaction can continue and commit.

• raise DB::Rollback in Inner: Same effect as inner_tx.rollback. The DB::Rollback exception is handled by the inner transaction logic and doesn't cause the outer transaction to fail.

• Other Exceptions in Inner: If not caught within the inner block, will roll back the inner transaction and propagate to roll back the outer transaction.

• Outer Rollback: If the outer transaction rolls back, all work (including successful inner transactions) is undone.

• Commit: Inner transaction changes are permanent only if the outermost transaction commits.

• Database Driver Dependency: Behavior relies on the database and driver supporting savepoints.

Use nested transactions judiciously for sub-units needing independent rollback within a larger atomic operation, as they add complexity.

5. Best Practices

Follow these best practices when working with transactions in CQL:

• Keep transactions short and focused: Minimize the amount of work inside a transaction block. Long-running transactions hold locks longer, increasing contention and deadlocks.

• Validate data before entering transactions: Perform necessary validation before starting the transaction to avoid unnecessary rollbacks.

• Encapsulate logic: Wrap complex transactional logic in dedicated methods or Service Objects for organization and testability.

• Handle exceptions: Catch exceptions outside the block for better logging and error handling, while relying on the automatic rollback inside.

• Ensure Consistent Resource Access Order: Access multiple records within a transaction in a consistent order (e.g., by primary key ID) to reduce deadlocks.

• Consider Isolation Levels: Understand and potentially configure isolation levels for advanced concurrency.

• Avoid Nested Transactions: Rely on the single outer transaction.

6. Troubleshooting and Gotchas

• Deadlocks: Caused by transactions waiting for each other's locks. Consistent resource ordering helps.

• Silent Transaction Failures: Ensure errors inside transactions are handled and reported properly outside the block.

• Connection Issues: Implement retry logic for transient database connection problems.

• Misunderstanding Rollback: Rollback affects all database operations within the transaction block.

By diligently applying these principles and patterns, you can effectively use CQL's transaction capabilities to build robust and reliable applications that maintain data integrity even in the face of errors or concurrent access.

In this guide, we'll cover the HasOne relationship using CQL's Active Record syntax. Like in the previous BelongsToguide, we'll start with an Entity-Relationship Diagram (ERD) to visually represent how the HasOne relationship works and build on the structure we already introduced with the BelongsTo relationship.

What is a HasOne Relationship?

The HasOne relationship indicates that one entity (a record) is related to exactly one other entity. For example, a User can have one Profile associated with it. This relationship is a one-to-one mapping between two entities.

Example Scenario: Users and Profiles

Let's say we have a system where:

• A User can have one Profile.

• A Profile belongs to one User.

We will represent this one-to-one relationship using CQL's HasOne and BelongsTo associations.

Defining the Schema