Database

Working with the database is enabled by default using the database feature.

Most web applications do need a database. Uses sqlx for making requests to a PostgreSQL database. To make this even simpler it provides you with some helpers to make it very quick and easy to interact with a database.

The "PaginatedSearch" helper

Quokka provides a way for you to simply query a paginated and extensible set of entites. This is done by SearchCriterias and the PaginatedSearch service. All it needs to be constructed is State: ProvideState<Database>.

The helper itself

The Paginated search allows you to query any kind of entity with fixed set of operators.

use quokka::helper::database::{PaginatedSearch, EqCriteria, PaginatedQuery};
use quokka::Result;
use axum::extract::{Path, Query, State, Json};

async fn my_handler(
    search: State<PaginatedSearch>,
    Path(author_name): Path<String>,
    Query(pagination): Query<PaginatedQuery>
) -> impl Result<Json>) {
    let output = search.search("article", EqCriteria("author", author_name), pagination).await?;

    Ok(Json(output))
}

The entity/table name (first parameter) is always required and needs to be a &'static str. This limitation is in place so that you (the developer) can make sure, that there comes no SQL injections across your nice code.

The criteria and pagination fields can be filled with None values if you don't want to use them.

The SearchCriteria

The SearchCriteria is a trait which extends the query builder with whatever search statement is needed in the WHERE clause to complete the search. As of now there are the following Criteria objects available: AndCriteria, OrCriteria, EqCriteria, NeCriteria, LikeCriteria, GtCriteria, GteCriteria, LtCriteria, LteCriteria, InCriteria, NotNullCriteria, IsNullCriteria. The names are supposed to be quiet explanatory if you are used to logical operations and/or SQL queries. But if you need a clearer explanation of them check out Quokka on docs.rs.

If you need some custom criteria feel free to dig into the SearchCriteria and maybe reference some other, provided criteria structs to figure out how it is implemented and implement it for whatever struct you want.

Query macros

There are some attribute macros to quickly and easy get you going with some custom database queries.

Fetch one

Create a repository function which fetches data from a query using the sqlx::query::Query::fetch_one method. It will fetch a single row, which might not exist.

use quokka::{Result, helper::database::query_one, state::Database};

#[derive(sqlx::FromRow)]
struct User {
    id: i32,
    username: String,
}

#[query_one(query = "UPDATE \"user\" SET username = {username} WHERE id = {id} RETURNING *", write)]
async fn update_username(database: Database, id: i32, username: &str) -> Result<User>;

Fetch optional

Create a repository function which fetches data from a query using the sqlx::query::Query::fetch_optional method. It will fetch a single row, which might not exist. When using the quokka::Result for the return result though, it will return an error with 404 if the query_one macro is used and no row exists.

use quokka::{Result, helper::database::query_optional, state::Database};

#[derive(sqlx::FromRow)]
struct User {
    id: i32,
    username: String,
}

#[query_optional(query = "SELECT * FROM \"user\" WHERE id = {id}")]
async fn update_username(database: Database, id: i32) -> Result<Option<User>>;

Fetch all

Create a repository function which fetches data from a query using the sqlx::query::Query::fetch_all method. It returns all fetched rows.

use quokka::{Result, helper::database::query_all, state::Database};

#[derive(sqlx::FromRow)]
struct User {
    id: i32,
    username: String,
}

#[query_all(query = "SELECT * FROM \"user\"")]
async fn update_username(database: Database, id: i32, username: &str) -> Result<Vec<User>>;

Execute without data

Create a repository function which executes a query using the sqlx::query::Query::execute method. It will return the count of affected rows.

use quokka::{Result, helper::database::execute, state::Database};

#[derive(sqlx::FromRow)]
struct User {
    id: i32,
    username: String,
}

#[execute(query = "UPDATE \"user\" SET username = {username} WHERE id = {id}", write)]
async fn update_username(database: Database, id: i32, username: &str) -> Result<u64>;

Arguments (all macros)

The repository macros all support the same set of arguments:

• query - The SQL query which will be executed
• write - A flag that makes the function use the read-write connection
• db_field - An expression to get the DB connection

A note on query

You can bind values to to the query at a given position by wrapping a variable in {} (eg. {name}). Prefixing the variable with a # (like {#name}) allows you to streight up push the value into the query without escaping and binding it. Beware that this can cause SQL injections if not handled with proper care and attention.

A note on write

To make effective use of multi-node database clusters which come with read-only replicas this macro, by default takes the connection of the quokka::state::Database::ro method. While this might be the same as the quokka::state::Database::rw (dependending on the configuration) it is supposed to be a read only connection.

Passing the write attribute calls the Database::rw instead.

A note on db_field

By default the macro uses the database field (of a struct) or argument.

It will use a struct field if a receiver (&self) is defined, otherwise expects an argument to the function, that is called database. Either way the type of the database has to be the Database substate. If you directly want to pass the connection into your function or struct, you can define an expression using this attribute (eg. db_field = "&self.ro_connection").

Return

The exact return type is influenced by the repository macro that is used, but generally said the return type must be a Result with the Result::Err variant implementing std::convert::From<sqlx::Error>.

So you can directly return a sqlx::Error.

The quokka::Error also implement the std::convert::From, so you can also this one. It will resolve the sqlx::Error::RowNotFound variant to a 404 error, which makes it easy to just return a query_one result from a controller.

The Result::Ok varies by the used repository function.

• The execute only is expected to be a u64 indicating the amount of affected rows.
• The query_one expects a struct that implements a sqlx::FromRow.
• The query_all expects a struct that implements the sqlx::FromRow trait which can be std::iter::Iterator::collected from an Iterator.
• The query_optional expects an Option of a struct that implements the sqlx::FromRow.

A note on Repositories

Repositories are supposed to make interfacing with the database even easier. The examples so far assumed, that you have a function to which you always have to pass the database connection too. We can keep the database connection in a different spot though by building a repository (getting into a somewhat more object oriented territory).

A repository is nothing special, just a struct with some functions in it. And to make it even more useful a quokka::state::FromState derive can be applied to make it simpler to handle.

use quokka::{Result, helper::database::*, state::{Database, FromState}};

#[derive(sqlx::FromRow)]
struct User {
    id: i32,
    username: String,
}

#[derive(FromState)]
struct UserRepository {
    database: Database,
}

impl UserRepository
{
    #[query_all(query = "SELECT * FROM \"user\"")]
    async fn get_users(&self) -> Result<Vec<User>>;

    #[execute(query = "UPDATE \"user\" SET username = {user.username} WHERE id = {user.id}", write)]
    async fn update_username(&self, user: User) -> Result<u64>;
}

And with that simple struct we can just use this repository everywhere and modify our database.

The "BaseRepository"

To get a minimal, standard interface to other modules, Quokka exports the BaseRepository trait. A set of very basic function used to interface with an entity.

impl BaseRepository for UserRepository {
    type Entity = User;

    type PkType = i32;

    #[quokka::helper::database::query_all(
        query = "SELECT * FROM \"user\" ORDER BY {#order_by} {#direction.to_string()} LIMIT {#page_size} OFFSET {#page_size * page}"
    )]
    async fn get_entities(
        &self,
        page: i32,
        page_size: i32,
        order_by: &'static str,
        direction: PaginationOrder,
    ) -> quokka::Result<Vec<Self::Entity>>;

    #[quokka::helper::database::query_one(query = "SELECT * FROM \"user\" WHERE id = {pk}")]
    async fn get_entity(&self, pk: Self::PkType) -> quokka::Result<Self::Entity>;

    #[quokka::helper::database::execute(
        query = "UPDATE \"user\" SET username = {entity.username} WHERE id = {entity.id}",
        write
    )]
    async fn update_entity(&self, entity: Self::Entity) -> quokka::Result<u64>;

    #[quokka::helper::database::query_one(
    query = "INSERT INTO \"user\" (id, username) VALUES ({entity.id}, {entity.username}) RETURNING *",
        write
    )]
    async fn create_entity(&self, entity: Self::Entity) -> quokka::Result<Self::Entity>;

    #[quokka::helper::database::execute(query = "DELETE FROM \"user\" WHERE id = {pk}", write)]
    async fn delete_entity(&self, pk: Self::PkType) -> quokka::Result<u64>;
}