Loading...
Loading...
Loading...
Loading...
Loading...
Now that we have our Movie database schema ready we can proceed to create, read, update and delete records.
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
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.
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.
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.
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.
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.
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).
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.
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.
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.
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.
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.
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.
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.
Consistency: Ensuring the schema matches the database at all times removes ambiguity when writing queries, handling relationships, or performing migrations.
Automatic Data Validation: CQL schemas enforce data types and constraints, such as primary
, auto_increment
, and text
, ensuring data integrity.
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.
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.
Here’s a basic example of how to define a schema in CQL for a movie-related database:
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.
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.
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.
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.
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.
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.
Let’s start with a simple example. Suppose we need to add a users
table to our database with two columns: name
and age
.
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.
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.
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
.
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
.
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.
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.
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.
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.
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::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.
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.
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
.
Cql::AlterTable
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.
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.
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
.
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
.
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
.
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.
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.
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.
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.
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).
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:
Adds a email
column with NOT NULL
and UNIQUE
constraints.
Drops the age
column.
Renames the users
table to customers
.
Creates a unique index on the email
column.
Generates the SQL statements that implement these actions.
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::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.
Insert records into any table with ease.
Insert multiple records in a single query.
Insert records using data fetched from another query.
Get the last inserted ID after an insert.
Chainable, intuitive syntax for building complex queries.
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.
Let's dive into the individual methods and how you can use them.
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.
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.
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:
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.
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.
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.
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.
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:
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 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.
Delete records from any table in a straightforward manner.
Filter records to delete using flexible WHERE
conditions.
Return columns after deletion if needed.
Chainable syntax for clean and maintainable query building.
Let’s start with a simple example of deleting a user from the users
table where the id
is 1.
This query deletes the record in the users
table where id = 1
.
The following section provides a breakdown of the key methods available in the Cql::Delete
class and how to use them effectively.
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
This sets the users
table as the target for the delete operation.
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
This filters the query to only delete the user where id = 1
.
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
This deletes all users where the age
is less than 30.
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
This deletes the user from the users
table where id = 1
and commits the change.
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
This example deletes users where they are linked to posts based on the condition posts.user_id = users.id
.
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
This deletes the user with id = 1
and returns the name
and email
of the deleted record.
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
This generates the raw SQL query and its associated parameters without executing it.
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:
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).
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.
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.
Update records in a database with a simple and readable syntax.
Set column values dynamically using hashes or keyword arguments.
Filter records with flexible WHERE
conditions.
Return updated columns after executing the query.
Chainable methods for building complex queries effortlessly.
Let’s start with a simple example of updating a user’s name and age in the users
table.
This example updates the user with id = 1
to have the name "John" and age 30.
Below is a detailed breakdown of the key methods in the Cql::Update
class and how to use them.
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
This sets the users
table as the target for the update operation.
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
This sets the name
and age
columns to new values for the target record(s).
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
This sets the name
to "Alice" and active
to true
.
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
This adds a condition to only update the user where id = 1
.
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
This example updates the user where both id = 1
and active = true
.
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
This commits the changes to the users
table, updating the user with id = 1
.
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
This will return the updated name
and age
columns after the update.
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
This generates the raw SQL query and its associated parameters without executing it.
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:
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.
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::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.
Select columns and filter records using simple, chainable methods.
Join tables for complex queries involving multiple relationships.
Aggregate data using functions like COUNT
, SUM
, and AVG
.
Order and limit your result sets for more precise control.
Fetch results as objects or raw data for immediate use in your application.
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.
Below is a breakdown of the key methods in the Cql::Query
class and how you can use them in your applications.
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.
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.
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
.
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.
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".
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
.
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
.
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
.
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.
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
.
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.
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.
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.
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.
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.
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 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:
This code defines the users
table and its columns (id
, name
, and age
), which CQL will use to generate the corresponding database structure.
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:
This is similar to defining DbSet<Product>
in EF, which represents the products
table.
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:
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:
This is similar to EF’s Update-Database
command, which applies migrations to the database.
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):
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.
CQL’s migration life cycle aligns closely with EF’s migrations system. In both cases, you:
Define migrations to introduce schema changes.
Apply migrations to update the database schema.
Rollback or redo migrations if needed to manage schema changes.
In EF, you would use commands like Update-Database
, Remove-Migration
, and Add-Migration
to manage these operations.
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:
This retrieves all products where the name is "Laptop", similar to how LINQ queries work in EF:
Both frameworks provide abstractions that avoid writing raw SQL, but CQL maintains a more SQL-like approach in its query building.
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.
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.
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:
Table-Class Mapping: Each class corresponds to a table in the database.
Row-Object Mapping: Each object is an instance of a class and corresponds to a row in the database table.
Database Operations as Methods: Methods on the object handle database interactions (e.g., .save
, .delete
, .find
).
Associations: Relationships between tables (e.g., belongs_to
, has_many
) are handled within the class structure.
Validation: Logic to ensure data integrity is embedded within the class.
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:
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
.
Cql makes no assumptions about table names and it must be explicitly provided. Schama name AcmeDB, Table name :users
define AcmeDB, :users
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:
To create a new record in the database, instantiate a new object of the model class and call .save
to persist it:
This will generate an INSERT INTO
SQL statement and persist the user in the users
table.
To retrieve records from the database, you can use class-level methods like .find
or .all
. For example:
Fetch all users:
Find a user by id
:
To update an existing record, modify the object and call .save
again. This will generate an UPDATE
SQL statement:
To delete a record, find the object and call .delete
:
Active Record also simplifies relationships between tables, such as has_many
and belongs_to
. In CQL, you can implement these relationships like this:
A User
has many Posts
. You can db_context the association like this:
Now you can fetch the posts for a user:
The Post
class has a belongs_to
relationship with User
. This means each post belongs to a user:
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:
all
:
Returns all associated records for the parent record.
Example: movie.actors.all
reload
:
Reloads the associated records from the database.
Example: movie.actors.reload
ids
:
Returns a list of primary keys for the associated records.
Example: movie.actors.ids
<<
:
Adds a new record to the association and persists it to the database.
Example: movie.actors << Actor.new(name: "Laurence Fishburne")
empty?
:
Checks if the association has any records.
Example: movie.actors.empty?
**exists?(**attributes)
**:
Checks if any associated records exist that match the given attributes.
Example: movie.actors.exists?(name: "Keanu Reeves")
size
:
Returns the number of associated records.
Example: movie.actors.size
**find(**attributes)
**:
Finds associated records that match the given attributes.
Example: movie.actors.find(name: "Keanu Reeves")
**create(**attributes)
**:
Creates a new record with the provided attributes and associates it with the parent.
Example: movie.actors.create(name: "Carrie-Anne Moss")
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")
ids=(ids : Array(Pk))
:
Associates the parent record with the records that match the provided primary keys.
Example: movie.actors.ids = [1, 2, 3]
delete(record : Target)
:
Deletes the associated record from the parent record if it exists.
Example: movie.actors.delete(Actor.find(1))
delete(id : Pk)
:
Deletes the associated record by primary key.
Example: movie.actors.delete(1)
clear
:
Removes all associated records for the parent record.
Example: movie.actors.clear
In addition to the methods inherited from Collection
, the ManyCollection
class also manages associations through a join table in many-to-many relationships.
create(record : Target)
:
Associates the parent record with the created record through a join table.
Example: movie.actors.create!(name: "Carrie-Anne Moss")
delete(record : Target)
:
Deletes the association through the join table between the parent and associated record.
Example: movie.actors.delete(Actor.find(1))
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.
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:
In this example, before saving a user, the validate
method is called to ensure that the name and email are not empty.
Alternatively validations can be supported by other shards. For example Schema shard from the Azu Toolkit can be use to db_context validations
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:
This migration would create the users
table with the specified columns.
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.
db_context your models:
Create User
and Post
classes that correspond to users
and posts
tables.
Run Migrations:
Use migrations to create or modify the database schema.
Perform CRUD operations:
Create, read, update, and delete records using model methods like .save
and .delete
.
Manage relationships:
db_context associations like has_many
and belongs_to
to handle relationships between models.
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.