Queries

Cot provides a Query interface that allows you to write queries to the database. In this guide, we’ll cover the basics of writing queries after you’ve setup your database and models. For a complete reference, see the Query docs.

For the rest of this guide, we’ll use the following models which show a simple e-commerce application.

use cot::db::ForeignKey;
use cot::db::{Auto, LimitedString};
use cot::common_types::Email;

#[model]
pub struct Customer {
    #[model(primary_key)]
    id: Auto<i64>,
    #[model(unique)]
    email: Email,
    full_name: LimitedString<128>,
    is_verified: bool,
}

#[model]
pub struct Product {
    #[model(primary_key)]
    id: Auto<i64>,
    #[model(unique)]
    sku: LimitedString<64>,
    name: LimitedString<255>,
    price_cents: i64,
    stock: i32,
    is_available: bool,
}

#[model]
pub struct Order {
    #[model(primary_key)]
    id: Auto<i64>,
    customer: ForeignKey<Customer>,
    product: ForeignKey<Product>,
    quantity: i32,
    is_fulfilled: bool,
}

Creating an object

To create a new model instance, cot provides the insert method. In the example below, we create a new Customer instance and save it to the database.

use cot::db::{Auto, Database};
use cot::common_types::Email;

async fn create_customer(db: Database) -> cot::Result<()> {
    let mut customer = Customer {
        id: Auto::default(),
        email: Email::new("[email protected]").unwrap(),
        full_name: LimitedString::new("Jon Doe").unwrap(),
        is_verified: false,
    };
    customer.insert(&db).await?;
}

Keep in mind that the insert method will return a UniqueViolation error if a record with the same primary key already exists. The example below shows an attempt to insert a new Customer instance with the same primary key as an existing one.

use cot::db::{Auto, Database};
use cot::common_types::Email;

async fn create_customer(db: Database) -> cot::Result<()> {
    let mut customer1 = Customer {
        id: Auto::fixed(1),
        email: Email::new("[email protected]").unwrap(),
        full_name: LimitedString::new("Jon Doe").unwrap(),
        is_verified: false,
    };
    customer1.insert(&db).await?;

    // This will fail with a UniqueViolation error.
    let mut customer2 = Customer {
        id: Auto::fixed(1),
        email: Email::new("[email protected]").unwrap(),
        full_name: LimitedString::new("Jon Doe").unwrap(),
        is_verified: false,
    };
    customer2.insert(&db).await?;
}

Creating multiple objects

If you need to create multiple objects, cot provides the bulk_insert method for this purpose. It is recommended to prefer bulk_insert for multiple insertions as it performs the operations in a single database query which is much more efficient than performing multiple individual insert or save calls.

The example below shows how to create multiple Customer instances.

use cot::db::{Auto, Database};
use cot::common_types::Email;

async fn create_customers(db: Database) -> cot::Result<()> {
    let mut customers = vec![
        Customer {
            id: Auto::default(),
            email: Email::new("[email protected]").unwrap(),
            full_name: LimitedString::new("Jane Doe").unwrap(),
            is_verified: false,
        },
        Customer {
            id: Auto::default(),
            email: Email::new("[email protected]").unwrap(),
            full_name: LimitedString::new("Jon Doe").unwrap(),
            is_verified: false,
        },
    ];

    Customer::bulk_insert(&db, &mut customers).await?;
}

Keep in mind that bulk_insert takes a mutable slice of models, because it needs to update the primary keys of the inserted models with the values generated by the database.

Similarly, there is also bulk_insert_or_update method, which works like bulk_insert, but updates the existing rows if they conflict with the new ones.

Updating an object

Cot provides the update method to update an existing model instance. The example below shows how to update the full_name field of a Customer instance.

customer.full_name = LimitedString::new("Jane Doe").unwrap();
customer.update(db).await?;

Creating or Updating an object

Cot provides the save method to create a new model instance if it doesn’t exist, or update it if it does. In the example below, we create a Customer instance if it doesnt exist and then update it’s verified status.

use cot::db::{Auto, Database};
use cot::common_types::Email;

async fn save_customer(db: Database) -> cot::Result<()> {
    let mut customer = Customer {
        id: Auto::default(),
        email: Email::new("[email protected]").unwrap(),
        full_name: LimitedString::new("Jon Doe").unwrap(),
        is_verified: false,
    };
    customer.save(&db).await?;

    // update the customer's verified status
    customer.is_verified = true;
    customer.save(&db).await?;
}

Saving ForeignKey fields

Saving a foreign key field is similar to saving a regular field, and Cot provides two variants for foreign key fields: ForeignKey::Model and ForeignKey::PrimaryKey. The key thing to keep in mind when saving any foreign key field is that the referenced model instance must already exist in the database before the relation can be saved.

`ForeignKey::Model`

This is the most common variant, which lets you associate a model instance directly as a foreign key field. The example below shows how to first persist a Customer and a Product instance, then reference them as foreign key fields on an Order.

use cot::db::{Auto, Database};
use cot::common_types::Email;

async fn save_order(db: Database) -> cot::Result<()> {
    let mut customer = Customer {
        id: Auto::default(),
        email: Email::new("[email protected]").unwrap(),
        full_name: LimitedString::new("Jon Doe").unwrap(),
        is_verified: false,
    };
    customer.save(&db).await?;

    let mut product = Product {
        id: Auto::default(),
        sku: LimitedString::new("ABC123").unwrap(),
        name: LimitedString::new("Product 1").unwrap(),
        price_cents: 1000,
        stock: 10,
        is_available: true,
    };
    product.save(&db).await?;

    let mut order = Order {
        id: Auto::default(),
        customer: ForeignKey::Model(Box::new(customer)),
        product: ForeignKey::Model(Box::new(product)),
        quantity: 1,
        is_fulfilled: false,
    };
    order.save(&db).await?;
}

`ForeignKey::PrimaryKey`

This variant lets you set a foreign key field using only the primary key of the referenced model, without needing to have the model instance in hand. This is useful when you already know the ID of the related record. The example below saves an Order referencing existing Customer and Product records by their primary keys directly.

use cot::db::{Auto, Database};

async fn save_order(db: Database) -> cot::Result<()> {
    let mut order = Order {
        id: Auto::default(),
        customer: ForeignKey::PrimaryKey(Auto::fixed(1)),
        product: ForeignKey::PrimaryKey(Auto::fixed(1)),
        quantity: 1,
        is_fulfilled: false,
    };
    order.save(&db).await?;
}

Keep in mind that if the provided primary key does not correspond to an existing record in the database, the save will fail.

Retrieving objects

To retrieve objects from the database, cot provides the query! macro which offers a convenient and declarative way to write queries. The macro takes two arguments: the model to query as the first argument, and a filter condition as the second, written using a natural expression syntax where fields are prefixed with $. The example below shows how to retrieve a Customer instance with the primary key of 5.

use cot::db::Database;

async fn get_customer(db: Database) -> cot::Result<()> {
    let customer = query!(Customer, $id==5).get(&db).await?;
    println!("Customer: {:?}", customer);
}

The query! macro returns a Query object, on which you can call terminal methods (such as get which returns the first matching result, and all which returns all matching results) to retrieve the final results.

Using the Query struct

The query! macro is syntactic sugar for manually constructing a Query with Expr expressions. The Query object can be accessed directly by calling the objects method on the model, and filtered using the filter method. You may prefer this approach when you need more control over how expressions are constructed. The example below is equivalent to the one above:

use cot::db::Database;
use cot::db::query::Expr;

async fn get_customer(db: Database) -> cot::Result<()> {
    let customer = Customer::objects().filter(Expr::eq(Expr::field("id"), Expr::value("5"))).get(&db).await?;
    println!("Customer: {:?}", customer);
}

The filter method takes a filter expression. In the example above, the expression Expr::eq(Expr::field("id"), Expr::value("5")) is evaluated as id = 5.

Retrieving all objects

One way to retrieve all objects of a model is to call the all method after filtering the query results.

use cot::db::Database;
use cot::db::query::Expr;

async fn get_all_customers(db: Database) -> cot::Result<()> {
    let customers = Customer::objects().filter(Expr::gt(Expr::field("id"), Expr::value("5"))).all(&db).await?;
    println!("Customers: {:?}", customers);
}

The example above retrieves all customers with a primary key greater than 5. This returns a list of Customer instances.

Chaining filters

The filter method returns a new Query instance which makes it convenient to chain multiple filters.

use cot::db::Database;
use cot::db::query::Expr;

async fn get_customers(db: Database) -> cot::Result<()> {
    let customers = Customer::objects()
        .filter(Expr::gt(Expr::field("id"), Expr::value("5")))
        .filter(Expr::eq(Expr::field("full_name"), Expr::value("Jon Doe"))).all(&db).await?;
    println!("Customers: {:?}", customers);
}

The example above shows how to retrieve all customers with a primary key greater than 5 and whose full name is Jon Doe.

Note: Although this example works, the idiomatic way to do this is to use the Expr::and expression instead.

Similarly, the query macro returns a new Query instance which can be used to chain multiple filters.

Removing an object

The delete method can be used to remove an object from the database. The example below shows how to remove a Customer instance with the primary key of 5.

use cot::db::Database;

async fn delete_customer(db: Database) -> cot::Result<()> {
    query!(Customer, $id==5).delete(&db).await?;
}

Other Query methods

The methods listed on this page are the most commonly used query methods. For a complete comprehensive list of supported query methods, see the Query docs.

Guide chapters

Search results for ""