In this guide, we'll cover the BelongsTo
relationship using CQL's Active Record syntax. We'll start with an Entity-Relationship Diagram (ERD) to illustrate how this relationship works and continuously build upon this diagram as we introduce new relationships in subsequent guides.
BelongsTo
Relationship?The BelongsTo
association in a database indicates that one entity (a record) refers to another entity by holding a foreign key to that record. For example, a Comment
belongs to a Post
, and each comment references the Post
it is associated with by storing the post_id
as a foreign key.
Let's say you have a blog system where:
A Post can have many Comments.
A Comment belongs to one Post.
We'll start by implementing the BelongsTo
relationship from the Comment
to the Post
.
We'll first define the posts
and comments
tables using CQL’s schema DSL.
posts table: Contains the blog post data (title, body, and published date).
comments table: Contains the comment data and a foreign key post_id
which references the posts
table.
Next, we'll define the Post
and Comment
structs in CQL.
In the Comment
model, we specify the belongs_to :post
association, which links each comment to its parent post by using the post_id
foreign key.
Now that we have db_contextd the Post
and Comment
models with a belongs_to
relationship, let's see how to create and query records in CQL.
We instantiate a Comment
and associate it with the post by creating a post
.
The post record is created and saved in the database.
And the returned id is then associtated to the comment.
Once we have a comment, we can retrieve the associated post using the belongs_to
association.
In this example, comment.post
will fetch the Post
associated with that Comment
.
In this guide, we’ve covered the basics of the belongs_to
relationship in CQL. We:
Defined the Post
and Comment
tables in the schema.
Created the corresponding models, specifying the belongs_to
relationship in the Comment
model.
Showed how to create and query records using the belongs_to
association.
In the next guides, we'll build on this ERD and introduce other types of relationships like has_one
, has_many
, and many_to_many
. Stay tuned for the next part where we'll cover the has_many
relationship!
Feel free to play around with this setup and extend the models or experiment with more queries to familiarize yourself with CQL's Active Record capabilities.
In this guide, we'll cover the HasOne
relationship using CQL's Active Record syntax. Like in the previous BelongsTo
guide, we’ll start with an Entity-Relationship Diagram (ERD) to visually represent how the HasOne
relationship works and build on the structure we already introduced with the BelongsTo
relationship.
HasOne
Relationship?The HasOne
relationship indicates that one entity (a record) is related to exactly one other entity. For example, a User can have one Profile associated with it. This relationship is a one-to-one mapping between two entities.
Let's say we have a system where:
A User can have one Profile.
A Profile belongs to one User.
We will represent this one-to-one relationship using CQL’s HasOne
and BelongsTo
associations.
We'll define the users
and profiles
tables in the schema using CQL.
users table: Stores user details like name
and email
.
profiles table: Stores profile details like bio
and avatar_url
. It has a user_id
foreign key referencing the users
table.
Let’s define the User
and Profile
models in CQL, establishing the HasOne
and BelongsTo
relationships.
The has_one :profile
association in the User
model indicates that each user has one profile.
The belongs_to :user
association in the Profile
model links each profile to a user by its user_id
.
Now that we have define the User
and Profile
models with a has_one
and belongs_to
relationship, let's see how to create and query records in CQL.
First, we create a User
and save it to the database.
Then, we create a Profile
and associate it with the user by passing user.id
as the user_id
.
Once a user and their profile have been created, you can retrieve the profile using the has_one
association.
Here, user.profile
fetches the profile associated with the user.
Similarly, you can retrieve the associated user from the profile.
In this example, profile.user
fetches the User
associated with that Profile
.
You can update the profile associated with a user in the same way you would update any other record.
Here, we retrieve the profile associated with the user, modify its bio
, and save the changes.
You can also delete the associated profile, but note that this does not automatically delete the user.
Similarly, deleting the user will not automatically delete the associated profile unless cascade rules are explicitly set in the database.
In this guide, we explored the has_one
relationship in CQL. We:
Define the User
and Profile
tables in the schema.
Created corresponding models, specifying the has_one
relationship in the User
model and the belongs_to
relationship in the Profile
model.
Demonstrated how to create, query, update, and delete records using the has_one
and belongs_to
associations.
In the next guide, we’ll extend the ERD and cover the has_many
relationship, which is commonly used when one entity is associated with multiple records (e.g., a post having many comments).
Feel free to experiment with the has_one
relationship by adding more fields to your models, setting up validations, or extending your schema with more complex relationships.
In this guide, we'll walk through using CQL with Crystal for setting up a database schema, defining records (models), establishing relationships between them, and handling migrations
Getting Started with Active Record, Relations, and Migrations in CQL
In this guide, we'll walk through using CQL with Crystal for setting up a database schema, defining records (models), establishing relationships between them, and handling migrations. This will be a foundational guide for developers who are familiar with Object-Relational Mapping (ORM) concepts from other frameworks like ActiveRecord (Rails), Ecto, or Hibernate, but are now learning CQL with Crystal.
Before getting started, ensure you have the following:
Crystal language installed (latest stable version).
PostgreSQL or MySQL set up locally or in the cloud.
CQL installed in your Crystal project.
You can add CQL to your project by including it in your shard.yml
:
Run shards install
to add the library to your project.
First, you need to configure CQL in your application. Let's create a basic config/database.cr
file for connecting to a PostgreSQL database:
Replace the database connection string with your actual PostgreSQL or MySQL credentials.
CQL uses migrations to manage the database schema, similar to Rails' ActiveRecord or Ecto's migrations.
Create a migration: Let's define a users
table in this migration:
Create a file in the db/migrate
directory, for example, 20230817000000_create_users.cr
Here, we create a users
table with columns name
, email
, and timestamps
.
Run the migration: After defining your migration, you can run it with:\
This command will execute all pending migrations and update your database schema.
Now that the schema is ready, we can define the User
record (model) that maps to the users
table.
Create the Record:
The User
model is mapped to the users
table. Here, we've define the fields for id
, name
, email
, and timestamps. We also added basic validations for name
and email
.
Create Records: You can now create user records using the User
model.
This will insert a new record into the users
table.
Query Records: You can query users using the User
model.
User.all
fetches all users, and User.find(1)
fetches the user with ID 1
.
CQL supports associations similar to ActiveRecord, Ecto, and other ORMs. Let's define some common relationships such as has_many
and belongs_to
.
Example: Users and Posts
Migration for Posts:
Create a new migration for the posts
table.
Edit the migration to add the posts
table, which has a foreign key to the users
table:
db_context the Post Model:
Now, let's define the Post
record and establish the relationships.
Here, the Post
model includes a foreign key user_id
and define a belongs_to
association to the User
model.
db_context the User
model's association:
Update the User
model to reflect the relationship with Post
.
This define a has_many
association on User
so that each user can have multiple posts.
Working with Relations:
Create a user and associate posts with them:
Access posts through the user:
CQL migrations allow you to create and alter your database schema easily. Here are some common migration tasks:
Adding Columns:
If you need to add a new column to an existing table, generate a migration:
Update the migration to add the age
column:
Rolling Back Migrations:
If something goes wrong with a migration, you can roll it back using:
This will undo the last migration that was applied.
This guide has provided a basic overview of using CQL with Crystal to define records (models), create relationships, and handle migrations. You've learned how to:
Set up CQL and connect it to a database.
Create and run migrations to define your schema.
Define records and establish relationships using has_many
and belongs_to
.
Manage your database schema with migrations.
With this foundation, you can now expand your models, add validations, and explore more advanced querying and relationships in CQL.
In the following guide, we'll take a closer look at the different relationships you can establish between models in CQL: BelongsTo
, HasOne
, HasMany
, and ManyToMany
. These relationships allow you to associate models with one another, making it easy to retrieve related data, enforce foreign key constraints, and maintain data integrity.
We'll use simple examples with CQL's DSL to help you understand how to define and use these associations effectively.
The Cql::Record
module in the CQL toolkit is a crucial part of the Object-Relational Mapping (ORM) system in Crystal. It allows you to define models that map to tables in your database and provides a wide array of functionalities for querying, inserting, updating, and deleting records. In this guide, we'll explore how the Cql::Record
module works and how to use it effectively.
Record
Module?The Cql::Record
module is a mixin that provides your Crystal structs with the ability to interact with database tables, treating them as Active Record-style models. This means that each model represents a table in your database, and each instance of that model represents a row within that table.
To start working with CQL models, you first need to define your database schema and map models (Crystal structs) to tables within that schema.
Let's assume we have two tables: posts
and comments
. Each post can have many comments, and each comment belongs to one post.
posts table: Contains columns id
, title
, body
, and published_at
.
comments table: Contains columns id
, post_id
(foreign key), and body
.
Record
Now, let's define the Post
and Comment
models that map to the posts
and comments
tables.
Record
ModuleThe Cql::Record
module adds several useful methods and features to your model:
1. Defining Models with define
Each model must be linked to a schema and a table using the define
method.
This associates the Post
struct with the posts
table in the AcmeDB
schema.
The Record
module provides convenient methods for querying the database.
Fetching All Records:
This retrieves all the records from the posts
table.
Fetching a Record by ID:
This retrieves the post with ID 1
. If the record is not found, nil
is returned.
Fetching the First or Last Record:
These methods fetch the first and last records in the table, respectively.
Fetching Records with Conditions:
These methods allow you to filter records by specific fields.
You can create new records using the create
method.
This creates a new post and returns the id
of the newly created record.
You can update existing records by passing the record’s id
and the fields to update.
This updates the post with ID 1
to have the new title "Updated Post Title".
To delete records, you can use the delete
method:
This deletes the post with ID 1
.
The Record
module also allows you to define associations between models. In our example, we defined a belongs_to
relationship in the Comment
model:
This means that each comment is associated with one post.
You can also define other associations like has_many
and has_one
:
This would go into the Post
model to define that each post can have multiple comments.
The Record
module also provides instance-level methods for interacting with individual records:
To insert a new record into the database or update an existing one, you can use the save
method:
If the record has an id
, it will update the record. Otherwise, it will create a new record.
You can also update specific fields on an existing record using the update
method:
To delete a record from the database:
This deletes the current record.
You can reload the current state of the record from the database using reload!
:
This updates the attributes of the record with the latest values from the database.
You can access and manipulate the record’s attributes using the attributes
method:
You can also set the attributes:
Let's combine everything we've learned to build a simple blog system where posts can have many comments.
Creating a Post:
Adding Comments to the Post:
Fetching Comments for a Post:
The Cql::Record
module provides powerful tools for working with database records in a Crystal application. It simplifies the process of defining models, querying records, and managing associations. By leveraging the capabilities of CQL's Active Record-style ORM, you can build complex applications with ease.
With Cql::Record
, you have access to:
Easy schema and model definition.
A rich set of query and manipulation methods.
Powerful association handling (belongs_to
, has_many
,
ManyToMany
Relationship GuideIn this guide, we’ll cover the ManyToMany
relationship using CQL's Active Record syntax. This is a more complex relationship compared to HasOne
and HasMany
, and it’s commonly used when two entities have a many-to-many relationship, such as posts and tags where:
A Post can have many Tags.
A Tag can belong to many Posts.
To model this, we need an intermediate (or join) table that connects these two entities.
ManyToMany
Relationship?A ManyToMany
relationship means that multiple records in one table can relate to multiple records in another table. For example:
A Post can have multiple Tags (e.g., "Tech", "News").
A Tag can belong to multiple Posts (e.g., both Post 1 and Post 2 can have the "Tech" tag).
We’ll use a scenario where:
A Post can have many Tags.
A Tag can belong to many Posts.
We will represent this many-to-many relationship using a join table called PostTags.
We’ll define the posts
, tags
, and post_tags
tables in the schema using CQL’s DSL.
posts table: Stores post details such as title
, body
, and published_at
.
tags table: Stores tag names.
post_tags table: A join table that connects posts
and tags
via their foreign keys post_id
and tag_id
.
Let’s define the Post
, Tag
, and PostTag
models in CQL, establishing the ManyToMany
relationship.
In the Post
model, we define:
has_many :post_tags
to establish the association between Post
and the join table PostTag
.
has_many :tags, through: :post_tags
to associate Post
with Tag
through the join table.
Similarly, in the Tag
model, we db_context:
has_many :post_tags
to associate Tag
with PostTag
.
has_many :posts, through: :post_tags
to associate Tag
with Post
through the join table.
The PostTag
model links each Post
and Tag
by storing their respective IDs.
Now that we’ve db_contextd the Post
, Tag
, and PostTag
models, let’s explore how to create and query records in a ManyToMany
relationship.
In this example:
We create a Post
and save it to the database.
We create two Tags
("Tech" and "Programming") and save them.
We create records in the PostTag
join table to associate the Post
with these two Tags
.
Once a post has tags associated with it, you can retrieve them using the ManyToMany
association.
Here, post.tags
retrieves all the tags associated with the post.
Similarly, you can retrieve all posts associated with a tag.
Here, tag.posts
retrieves all the posts associated with the tag.
You can associate more tags with an existing post by creating new entries in the PostTag
join table.
To disassociate a tag from a post, you need to delete the corresponding record from the PostTag
join table.
You can also perform advanced queries using the ManyToMany
relationship, such as finding posts with a specific tag or fetching tags for multiple posts.
To find all posts associated with a specific tag, you can filter the posts by the tag name.
You can fetch all tags associated with multiple posts as follows:
In this guide, we explored the ManyToMany
relationship in CQL. We:
Define the Post
, Tag
, and PostTag
tables in the schema
Created corresponding models, specifying the ManyToMany
relationship between Post
and Tag
through the PostTag
join table.
Demonstrated how to create, query, update, and delete records in a ManyToMany
relationship.
This concludes our series of guides on relationships in CQL Active Record, covering BelongsTo
, HasOne
, HasMany
, and ManyToMany
. Feel free to experiment by extending your models, adding validations, or implementing more complex queries to suit your needs.
HasMany
Relationship GuideIn this guide, we’ll focus on the HasMany
relationship using CQL's Active Record syntax. Like the previous BelongsTo
and HasOne
relationships, we’ll start with an Entity-Relationship Diagram (ERD) to visually explain how the HasMany
relationship works and build on our previous schema.
HasMany
Relationship?The HasMany
relationship indicates that one entity (a record) is related to multiple other entities. For example, a Post can have many Comments. This relationship is a one-to-many mapping between two entities.
In a blogging system:
A Post can have many Comments.
Each Comment belongs to one Post.
This is a common one-to-many relationship where one post can have multiple comments, but each comment refers to only one post.
We’ll define the posts
and comments
tables in the schema using CQL’s DSL.
posts table: Stores post details like title
, body
, and published_at
.
comments table: Stores comment details with a foreign key post_id
that references the posts
table.
Let’s db_context the Post
and Comment
models and establish the HasMany
and BelongsTo
relationships in CQL.
The has_many :comments
association in the Post
model defines that each post can have multiple comments.
The belongs_to :post
association in the Comment
model links each comment to a post by using the post_id
foreign key.
Now that we have defined the Post
and Comment
models with a HasMany
and BelongsTo
relationship, let’s create and query records in CQL.
First, we create a Post
and save it to the database.
Then, we create two Comments
and associate them with the post by passing post.id
as the post_id
for each comment.
Once a post has comments, you can retrieve all the comments using the HasMany
association.
Here, post.comments
retrieves all the comments associated with the post, and we loop through them to print each comment’s body.
You can also retrieve the post associated with a comment using the BelongsTo
association.
In this example, comment.post
fetches the post that the comment belongs to.
You can add a new comment to an existing post as follows:
If you delete a post, you may want to delete all associated comments as well. However, by default, this will not happen unless you specify cascade deletion in your database.
You can also perform advanced queries using the HasMany
relationship. For example, finding posts with a certain number of comments or filtering comments for a post based on specific conditions.
You can load posts along with their comments in one query:
If you want to query for specific comments associated with a post, you can filter them as follows:
In this guide, we’ve explored the HasMany
relationship in CQL. We:
Defined the Post
and Comment
tables in the schema.
Created corresponding models, specifying the HasMany
relationship in the Post
model and the BelongsTo
relationship in the Comment
model.
Demonstrated how to create, query, update, and delete records using the HasMany
and BelongsTo
associations.
In the next guide, we’ll build upon this ERD and cover the ManyToMany
relationship, which is useful when two entities are associated with many of each other (e.g., a post can have many tags, and a tag can belong to many posts).
Feel free to experiment with the HasMany
relationship by adding more fields, filtering queries, or extending your schema to handle more complex use cases.