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.