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

Searching within a field (pattern matching)

Sometimes an exact match isn’t what you want. You might want to find all customers whose name contains a certain word, or all products whose name starts with a certain prefix. Cot supports this kind of substring, prefix, and suffix search directly in the query! macro (and, if you’re building expressions by hand, on Expr as well).

use cot::db::Database;

async fn search_customers(db: Database) -> cot::Result<()> {
    let customers = query!(Customer, $full_name.contains("Doe")).all(&db).await?;
    println!("Customers: {:?}", customers);
}

Alongside contains, there’s starts_with and ends_with, which anchor the match to the beginning or the end of the field’s value instead of allowing it anywhere:

use cot::db::Database;

async fn search_customers_further(db: Database) -> cot::Result<()> {
    // Matches "Jon Doe", "Jonathan Smith", etc.
    let jons = query!(Customer, $full_name.starts_with("Jon")).all(&db).await?;

    // Matches anyone whose name ends with "Doe"
    let does = query!(Customer, $full_name.ends_with("Doe")).all(&db).await?;

    println!("{:?} {:?}", jons, does);
}

By default, all three of these are case-sensitive, so contains("Doe") won’t match a customer named “jane doe”. If you’d rather the match ignore case, each method has an i-prefixed counterpart: icontains, istarts_with, and iends_with.

use cot::db::Database;

async fn case_insensitive_search(db: Database) -> cot::Result<()> {
    let customers = query!(Customer, $full_name.icontains("doe")).all(&db).await?;
    println!("Customers: {:?}", customers);
}

These combine with the rest of the filter naturally too. Use them alongside boolean and comparison operators just like any other condition:

use cot::db::Database;

async fn verified_does(db: Database) -> cot::Result<()> {
    let customers = query!(Customer, $full_name.icontains("doe") && $is_verified == true)
        .all(&db)
        .await?;
    println!("Customers: {:?}", customers);
}

Matching a custom pattern

contains, starts_with, and ends_with cover most everyday searches, but they can’t express every shape of match. What if you need to check both the start and the end of a value at once, or match a value that follows a specific structure? For cases like this, Cot provides raw_like (and its case-insensitive counterpart, iraw_like), which let you write the match pattern yourself, using a small glob-style syntax:

• * matches zero or more of any character
• ? matches exactly one character
• \ escapes the character after it, so it’s matched literally instead of as a wildcard

Say you’re searching for shipment tracking codes that start with "PKG" and end with "US", with anything in between:

use cot::db::Database;

async fn find_matching_tracking_codes(db: Database) -> cot::Result<()> {
    // Matches "PKG-US", "PKG123-US", "PKGXXUS", and so on
    let shipments = query!(Shipment, $tracking_code.raw_like("PKG*US")).all(&db).await?;
    println!("Shipments: {:?}", shipments);
}

raw_like is more flexible than contains, starts_with, and ends_with, but it also means you’re responsible for escaping any wildcard characters you want to match literally. See the Expr::raw_like docs for the full pattern syntax and escaping rules.

Using the Expr struct directly

As with the other operators in this guide, all of these have an equivalent on Expr for when you’re building queries by hand rather than through the query! macro:

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

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

For the complete list of pattern-matching methods, their case-insensitive counterparts, and the glob pattern syntax used by raw_like, see the Expr and ExprLike docs.

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.

Raw SQL queries

While the Query interface and the query! macro should cover the vast majority of use cases, sometimes you may need to run a query that they don’t support, such as a complex join or a database-specific function. For these cases, Cot provides an escape hatch to run raw SQL directly through the Database struct.

Warning: These methods execute the given SQL string as-is, without any sanitization. Never build the query string by interpolating untrusted input directly into it, as that would expose your application to SQL injection. Use the parameterized variants below whenever the query depends on external data.

raw and raw_with

The raw method executes a raw SQL statement and returns a StatementResult, which contains the number of affected rows (and, when available, the ID of the last inserted row). It’s most useful for statements that don’t return rows, such as CREATE TABLE, INSERT, UPDATE, or DELETE.

use cot::db::Database;

async fn create_table(db: &Database) -> cot::Result<()> {
    db.raw("CREATE TABLE example (id INTEGER PRIMARY KEY, name TEXT)").await?;
}

If your query takes parameters, use raw_with instead of interpolating the values into the query string yourself. The parameters are passed separately and bound safely by the underlying database driver, so this is the preferred way of running raw statements that depend on runtime values.

use cot::db::Database;

async fn deactivate_customer(db: &Database, customer_id: i64) -> cot::Result<()> {
    db.raw_with(
        "UPDATE customer SET is_verified = false WHERE id = ?",
        &[&customer_id],
    ).await?;
}

raw_as and raw_as_with

To run a raw SELECT query and map the returned rows onto a model, use the raw_as method. It runs the query and applies the model’s Model::from_db mapping to every returned row, so the columns selected by the query must match the model’s fields.

This is handy for queries that are difficult or impossible to express with the Query interface, such as joins across multiple tables or aggregate expressions:

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

async fn get_verified_customers(db: &Database) -> cot::Result<()> {
    let customers = db
        .raw_as::<Customer>("SELECT * FROM customer WHERE is_verified = true")
        .await?;
    println!("Verified customers: {:?}", customers);
}

Just like raw_with is the parameterized counterpart of raw, raw_as_with is the parameterized counterpart of raw_as. Prefer it whenever the query depends on runtime values, instead of interpolating them into the query string yourself:

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

async fn get_customers_by_name(db: &Database, full_name: &str) -> cot::Result<()> {
    let customers = db
        .raw_as_with::<Customer>("SELECT * FROM customer WHERE full_name = ?", &[&full_name])
        .await?;
    println!("Customers: {:?}", customers);
}