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.

class CreateUsersTable < CQL::Migration(1)
  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

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.

schema = CQL::Schema.build(:my_db, adapter: CQL::Adapter::SQLite, uri: "sqlite3://db.sqlite3") do |s|
  ...
end

migrator = CQL::Migrator.new(schema)

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:

migrator.up

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:

migrator.up_to(1_i64)

This will apply all migrations up to version 1_i64.

Rolling Back Migrations

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

migrator.down

You can also roll back to a specific migration version:

migrator.down_to(1_i64)

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:

migrator.redo

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:

  Copy

  migrator.print_applied_migrations

• List Pending Migrations:

  Copy

  migrator.print_pending_migrations

• List Rolled Back Migrations:

  Copy

  migrator.print_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:

last_migration = migrator.last
puts last_migration

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:

class CreateMoviesTable < CQL::Migration(2)
  def up
    schema.alter :movies do
      add_column :title, String
      add_column :release_year, Int32
    end
  end

  def down
    schema.alter :movies do
      drop_column :title
      drop_column :release_year
    end
  end
end

class CreateActorsTable < CQL::Migration
  self.version = 3_i64

  def up
    schema.alter :actors do
      add_column :name, String
    end
  end

  def down
    schema.alter :actors do
      drop_column :name
    end
  end
end

# Apply the migrations
migrator.up

• 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.

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 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:

Then, run the following command to install the dependencies:

Getting Started

1. Define a Schema

Define the schema for your database tables:

2. Executing Queries

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

3. Inserting Data

Insert new records into the database:

4. Updating Data

Update existing records:

5. Deleting Data

Delete records from the database:

6. Using the Repository Pattern

Utilize the repository pattern for organized data management:

7. Active Record Pattern

Work with your data using the Active Record pattern:

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:

Step 2: Install Shards

Run the following command to install the dependencies:

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:

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).

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.

Once you've defined your database schema using CQL's Schema.build, 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.

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.

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

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.

Summary

Initializing the database after schema creation is a simple process with CQL. After defining your tables and relationships using CQL::Schema.build, 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.

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:

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

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

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

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

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

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

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

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

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

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

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:

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.

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:

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

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

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

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

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

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

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

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

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

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

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

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

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:

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::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.

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:

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

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

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:

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.

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:

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:

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.

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:

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.

The Repository Pattern is a design pattern that provides an abstraction layer between the data access logic and the business logic in an application. In CQL, the Cql::Repository(T, Pk) class implements the repository pattern, allowing you to interact with your database models in a clean and maintainable way.

Benefits of the Repository Pattern

• Separation of Concerns: It abstracts the database operations, so business logic remains unaware of how data is fetched or stored.

• Maintainability: With a repository, changes to the underlying database structure can be made without affecting business logic.

• Testability: It becomes easier to write unit tests for your business logic because the repository can be mocked or stubbed.

Repository Basics in CQL

In CQL, a Repository is tied to a specific table in the database. It provides an interface for CRUD (Create, Read, Update, Delete) operations, along with support for more advanced functionalities like pagination and counting records.

Example: Defining a Repository

To define a repository, create a class that inherits from Cql::Repository(T, Pk) where:

• T is the type of the model or struct that represents the table.

• Pk is the primary key type of the table (e.g., Int64, UUID).

class UserRepository < Cql::Repository(User, Int64)
  def initialize(@schema : Cql::Schema, @table : Symbol)
  end
end

This creates a repository for the users table with a primary key of type Int64.

CRUD Operations with a Repository

Once a repository is defined, you can perform common database operations using its built-in methods.

Create a New Record

user_repo = UserRepository.new(schema, :users)
user_repo.create(name: "Alice", email: " [email protected]")

Fetch All Records

users = user_repo.all

Find a Record by Primary Key

user = user_repo.find(1)

Update a Record

user_repo.update(1, name: "Alice Updated")

Delete a Record

user_repo.delete(1)

Advanced Features

Pagination

The repository provides built-in support for paginated queries:

page_1_users = user_repo.page(1, per_page: 10)

Counting Records

To count all records in a table, use the count method:

user_count = user_repo.count

Query Customization

You can extend the repository to support custom queries by adding methods:

class UserRepository < Cql::Repository(User, Int64)
  def find_by_email(email : String)
    query.from(:users).where(email: email).first!(as: User)
  end
end

This allows you to keep the database logic encapsulated within the repository class, maintaining clean separation between data access and application logic.

When to Use the Repository Pattern

• Large Applications: For applications with complex data access logic, the repository pattern helps organize the codebase.

• Testing: By abstracting the database access, the repository pattern makes it easier to mock or stub database operations in tests.

• Scalability: As your application grows, repositories allow you to manage data access logic more effectively by keeping it separate from business logic.

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.

The CQL::Update class in the CQL (Crystal Query Language) module is designed to represent and execute SQL UPDATE statements in a clean and structured manner. This guide will walk you through using the class to update records in a database, providing real-world examples and detailed explanations for each method.

Key Features

1. Update records in a database with a simple and readable syntax.

2. Set column values dynamically using hashes or keyword arguments.

3. Filter records with flexible WHERE conditions.

4. Return updated columns after executing the query.

5. Chainable methods for building complex queries effortlessly.

Real-World Example: Updating a User's Data

Let’s start with a simple example of updating a user’s name and age in the users table.

update = CQL::Update.new(schema)
  .table(:users)
  .set(name: "John", age: 30)
  .where { |w| w.id == 1 }
  .commit

This example updates the user with id = 1 to have the name "John" and age 30.

Core Methods

Below is a detailed breakdown of the key methods in the CQL::Update class and how to use them.

1. table(table : Symbol)

Purpose: Specifies the table to update.

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

• Returns: Update object (for chaining).

Real-World Example: Setting the Target Table

update.table(:users)

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

2. set(setters : Hash(Symbol, DB::Any))

Purpose: Specifies the column values to update using a hash.

• Parameters: setters — A hash where keys are column names and values are the new values for those columns.

• Returns: Update object (for chaining).

Real-World Example: Updating Multiple Columns

update
  .table(:users)
  .set(name: "John", age: 30)

This sets the name and age columns to new values for the target record(s).

3. set(**fields)

Purpose: Specifies the column values to update using keyword arguments.

• Parameters: fields — Column-value pairs as keyword arguments.

• Returns: Update object (for chaining).

Real-World Example: Using Keyword Arguments

update
  .table(:users)
  .set(name: "Alice", active: true)

This sets the name to "Alice" and active to true.

4. where(**fields)

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

• Parameters: fields — A hash where keys are column names and values are the conditions to match.

• Returns: Update object (for chaining).

Real-World Example: Filtering by a Condition

update
  .table(:users)
  .set(name: "John", age: 30)
  .where(id: 1)

This adds a condition to only update the user where id = 1.

5. where(&block)

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

• Parameters: Block that db_contexts the condition using a filter builder.

• Returns: Update object (for chaining).

Real-World Example: Using a Block for Conditions

update
  .table(:users)
  .set(name: "John")
  .where { |w| w.id == 1 && w.active == true }

This example updates the user where both id = 1 and active = true.

6. commit

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

• Returns: A DB::Result object, which represents the result of the query execution.

Real-World Example: Committing the Update

update = CQL::Update.new(schema)
  .table(:users)
  .set(name: "John", age: 30)
  .where { |w| w.id == 1 }
  .commit

This commits the changes to the users table, updating the user with id = 1.

7. back(*columns : Symbol)

Purpose: Specifies the columns to return after the update.

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

• Returns: Update object (for chaining).

Real-World Example: Returning Updated Columns

update
  .table(:users)
  .set(name: "John", age: 30)
  .where(id: 1)
  .back(:name, :age)
  .commit

This will return the updated name and age columns after the update.

8. to_sql(gen = @schema.gen)

Purpose: Generates the SQL query and the parameters required for the UPDATE statement.

• 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 an Update

update = CQL::Update.new(schema)
  .table(:users)
  .set(name: "John", age: 30)
  .where(id: 1)

sql, params = update.to_sql
puts sql     # "UPDATE users SET name = $1, age = $2 WHERE id = $3"
puts params  # ["John", 30, 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 update a user's data, but only if they are active, and you want to return their updated email address afterward:

update = CQL::Update.new(schema)

result = update
  .table(:users)
  .set(name: "Charlie", email: "charlie@example.com")
  .where { |w| w.id == 1 && w.active == true }
  .back(:email)
  .commit

puts result  # This will return the updated email address of the user.

In this query:

• We specify the users table.

• We update both the name and email of the user.

• We filter the update to only apply to the active user with id = 1.

• We return the updated email after the update is committed.

Conclusion

The CQL::Update class provides a simple yet powerful interface for building and executing UPDATE queries in a Crystal application. With chainable methods for setting values, applying conditions, and controlling the output, you can easily handle any update operation.

Whether you are updating single records or large batches, the flexibility of CQL::Update ensures that your queries remain clean, maintainable, and efficient.

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 < CQL::Record(User, 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 < CQL::Record(Post, Int32)

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

  belongs_to :user, User
end