quokka/helper/
database.rs

1//!
2//! An export for the repository_\* marcos.
3//!
4//! # Arguments
5//!
6//! The repository macros all support the same set of arguments:
7//! - `query` - The SQL query which will be executed
8//! - `write` - A flag that makes the function use the read-write connection
9//! - `db_field` - An expression to get the DB connection
10//!
11//! ## A note on `write`
12//!
13//! To make effective use of multi-node database clusters which come with read-only
14//! replicas this macro, by default takes the connection of the [Database::ro](crate::state::Database::ro)
15//! method. While this might be the same as the [Database::rw](crate::state::Database::rw)
16//! (dependending on the configuration) it is supposed to be a read only connection.
17//!
18//! Passing the `write` attribute calls the [Database::rw](crate::state::Database::rw) instead.
19//!
20//! ## A note on `db_field`
21//!
22//! By default the macro uses the `database` field or argument.
23//!
24//! It will use a struct field if a receiver (`&self`) is defined, otherwise expects an argument
25//! to the function, that is called `database`. Either way the type of the database has to be
26//! the [Database](crate::state::Database) state. If you directly want to pass the connection
27//! into your function or struct, you can define an expression using this attribute (eg.
28//! db_field = "&self.ro_connection").
29//!
30//! # Return
31//!
32//! The exact return type is influenced by the repository macro that is used, but generally said
33//! the return type must be a [Result] with the [Result::Err] variant implementing
34//! [std::convert::From]<[sqlx::Error]>.
35//!
36//! So you can directly return a [sqlx::Error].
37//!
38//! The [crate::Error] also implement the [std::convert::From], so you can also this one. It will
39//! resolve the [sqlx::Error::RowNotFound] variant to a 404 error, which makes it easy to just
40//! return a [repository_query_one] result from a controller.
41//!
42//! The [Result::Ok] varies by the used repository function.
43//!
44//! - The [repository_execute] only is expected to be a [u64] indicating the amount of affected rows.
45//! - The [repository_query_one] expects a struct that implements a [sqlx::FromRow].
46//! - The [repository_query_all] expects a struct that implements the [sqlx::FromRow] trait which can be
47//!   [collect](std::iter::Iterator::collect)ed from an [Iterator].
48//! - The [repository_query_optional] expects an [Option] of a struct that implements the [sqlx::FromRow].
49//!
50//! # Examples
51//!
52//! ## Fetch one
53//!
54//! Create a repository function which fetches data from a query using the [sqlx::query::Query::fetch_one] method.
55//! It will fetch a single row, which might not exist.
56//!
57//! ```
58//! use quokka::{Result, helper::database::repository_query_one, state::Database};
59//!
60//! #[derive(sqlx::FromRow)]
61//! struct User {
62//!     id: i32,
63//!     username: String,
64//! }
65//!
66//! #[repository_query_one(query = "UPDATE \"user\" SET username = {username} WHERE id = {id} RETURNING *", write)]
67//! async fn update_username(database: Database, id: i32, username: &str) -> Result<User>;
68//! ```
69//!
70//! ## Fetch optional
71//!
72//! Create a repository function which fetches data from a query using the [sqlx::query::Query::fetch_optional] method.
73//! It will fetch a single row, which might not exist. When using the [crate::Result] for the return result though, it
74//! will return an error with 404 if the [repository_query_one] macro is used and no row exists.
75//!
76//! ```
77//! use quokka::{Result, helper::database::repository_query_optional, state::Database};
78//!
79//! #[derive(sqlx::FromRow)]
80//! struct User {
81//!     id: i32,
82//!     username: String,
83//! }
84//!
85//! #[repository_query_optional(query = "SELECT * FROM \"user\" WHERE id = {id}")]
86//! async fn update_username(database: Database, id: i32) -> Result<Option<User>>;
87//! ```
88//!
89//! ## Fetch all
90//!
91//! Create a repository function which fetches data from a query using the [sqlx::query::Query::fetch_all] method.
92//! It returns all fetched rows.
93//!
94//! ```
95//! use quokka::{Result, helper::database::repository_query_all, state::Database};
96//!
97//! #[derive(sqlx::FromRow)]
98//! struct User {
99//!     id: i32,
100//!     username: String,
101//! }
102//!
103//! #[repository_query_all(query = "SELECT * FROM \"user\"")]
104//! async fn update_username(database: Database, id: i32, username: &str) -> Result<Vec<User>>;
105//! ```
106//!
107//! ## Execute without data
108//!
109//! Create a repository function which executes a query using the [sqlx::query::Query::execute] method.
110//! It will return the count of affected rows.
111//!
112//! ```
113//! use quokka::{Result, helper::database::repository_execute, state::Database};
114//!
115//! #[derive(sqlx::FromRow)]
116//! struct User {
117//!     id: i32,
118//!     username: String,
119//! }
120//!
121//! #[repository_execute(query = "UPDATE \"user\" SET username = {username} WHERE id = {id}", write)]
122//! async fn update_username(database: Database, id: i32, username: &str) -> Result<u64>;
123//! ```
124//!
125//!
126
127use std::future::Future;
128
129pub use quokka_macros::{
130    repository_execute, repository_execute as execute, repository_query_all,
131    repository_query_all as query_all, repository_query_one, repository_query_one as query_one,
132    repository_query_optional, repository_query_optional as query_optional,
133};
134use sqlx::{query::Query, query_builder::Separated, QueryBuilder};
135
136use crate::{
137    state::{Database, FromState, ProvideState},
138    Error, Result,
139};
140
141///
142/// Provides paginated access to any entity of your liking by using the [PaginatedResult].
143///
144#[derive(Clone, Debug)]
145pub struct PaginatedSearch {
146    database: Database,
147}
148
149#[derive(Clone, Debug, serde::Deserialize, serde::Serialize)]
150pub struct PaginatedResult<E> {
151    pub page: u64,
152    pub total_pages: u64,
153    pub total_items: u64,
154    pub per_page: u64,
155    pub next_page: Option<u64>,
156    pub previous_page: Option<u64>,
157    pub items: Vec<E>,
158}
159
160pub trait SearchCriteria: Send {
161    fn extend_query_builder(&self, qb: &mut QueryBuilder<'_, sqlx::Postgres>);
162}
163
164/// `AND`s any amount of statements
165pub struct AndCriteria {
166    pub criteria: Vec<Box<dyn SearchCriteria>>,
167}
168
169/// `OR`s any amount of statements
170pub struct OrCriteria {
171    pub criteria: Vec<Box<dyn SearchCriteria>>,
172}
173
174/// Checks a column aainst a value using the `=` operator
175pub struct EqCriteria {
176    pub column: &'static str,
177    pub value: Box<dyn Bindable<sqlx::Postgres>>,
178}
179
180/// Checks a column aainst a value using the `!=` operator
181pub struct NeCriteria {
182    pub column: &'static str,
183    pub value: Box<dyn Bindable<sqlx::Postgres>>,
184}
185
186/// Checks a column aainst a value using the `LIKE` operator
187pub struct LikeCriteria {
188    pub column: &'static str,
189    pub value: Box<dyn Bindable<sqlx::Postgres>>,
190}
191
192/// Checks a column aainst a value using the `>` operator
193pub struct GtCriteria {
194    pub column: &'static str,
195    pub value: Box<dyn Bindable<sqlx::Postgres>>,
196}
197
198/// Checks a column aainst a value using the `>=` operator
199pub struct GteCriteria {
200    pub column: &'static str,
201    pub value: Box<dyn Bindable<sqlx::Postgres>>,
202}
203
204/// Checks a column aainst a value using the `<` operator
205pub struct LtCriteria {
206    pub column: &'static str,
207    pub value: Box<dyn Bindable<sqlx::Postgres>>,
208}
209
210/// Checks a column aainst a value using the `<=` operator
211pub struct LteCriteria {
212    pub column: &'static str,
213    pub value: Box<dyn Bindable<sqlx::Postgres>>,
214}
215
216/// Checks a column aainst a set of values using the `IN` operator
217pub struct InCriteria {
218    pub column: &'static str,
219    pub values: Vec<Box<dyn Bindable<sqlx::Postgres>>>,
220}
221
222/// Checks that a column `IS NOT NULL`
223pub struct NotNullCriteria {
224    pub column: &'static str,
225}
226
227/// Checks that a column `IS NULL`
228pub struct IsNullCriteria {
229    pub column: &'static str,
230}
231
232impl PaginatedSearch {
233    ///
234    /// Searches for a set of entities using the given criteria.
235    ///
236    #[tracing::instrument(skip(self, search, pagination))]
237    pub async fn search<
238        E: for<'r> sqlx::FromRow<'r, sqlx::postgres::PgRow>,
239        S: SearchCriteria + Send,
240    >(
241        &self,
242        table_name: &'static str,
243        search: impl Into<Option<S>>,
244        pagination: impl Into<Option<PaginationQuery>>,
245    ) -> Result<PaginatedResult<E>> {
246        let search: Option<S> = search.into();
247        let pagination: Option<PaginationQuery> = pagination.into();
248        let table_name = table_name
249            .chars()
250            .filter(|char| char.is_alphanumeric() || char.eq(&'_'))
251            .collect::<String>();
252
253        let mut data_query =
254            QueryBuilder::<'_, sqlx::Postgres>::new(format!("SELECT * FROM \"{table_name}\" "));
255
256        let mut count_query = QueryBuilder::<'_, sqlx::Postgres>::new(format!(
257            "SELECT COUNT(*) FROM \"{table_name}\" "
258        ));
259
260        if let Some(search) = search {
261            data_query.push(" WHERE ");
262            count_query.push(" WHERE ");
263
264            search.extend_query_builder(&mut data_query);
265            search.extend_query_builder(&mut count_query);
266        }
267
268        if let Some(order) = pagination.as_ref().and_then(|value| value.order.as_ref()) {
269            let order_column = order
270                .0
271                .chars()
272                .skip_while(|char| !char.is_alphabetic())
273                .filter(|char| char.is_alphanumeric() || '_'.eq(char))
274                .collect::<String>();
275
276            data_query.push(format!(" ORDER BY {} {} ", order_column, order.1));
277        }
278
279        if let Some(page_size) = pagination
280            .as_ref()
281            .and_then(|value| value.page_size.as_ref())
282        {
283            data_query.push(format!(" LIMIT {} ", page_size));
284        }
285
286        if let (Some(page), Some(page_size)) = (
287            pagination.as_ref().and_then(|value| value.page.as_ref()),
288            pagination
289                .as_ref()
290                .and_then(|value| value.page_size.as_ref()),
291        ) {
292            data_query.push(format!(" OFFSET {} ", page * page_size));
293        }
294
295        let data = async {
296            data_query
297                .build()
298                .fetch_all(self.database.ro())
299                .await
300                .map_err(Error::wrap_error("Unable to search for entity", 500))
301        };
302
303        let pagination_info = async {
304            count_query
305                .build()
306                .fetch_one(self.database.ro())
307                .await
308                .map_err(Error::wrap_error("Unable to count entities", 500))
309        };
310
311        let (data, pagination_info) = futures::future::try_join(data, pagination_info).await?;
312
313        let items = data
314            .iter()
315            .map(E::from_row)
316            .collect::<std::result::Result<Vec<_>, sqlx::Error>>()
317            .map_err(Error::wrap_error("Unable to map entity", 500))?;
318
319        use sqlx::Row;
320
321        let page = pagination
322            .as_ref()
323            .and_then(|value| value.page)
324            .unwrap_or_default();
325        let per_page = pagination
326            .as_ref()
327            .and_then(|value| value.page_size)
328            .unwrap_or_default();
329
330        let total_items = pagination_info
331            .try_get::<i64, _>(0)
332            .map_err(Error::wrap_error("Unable to get entity count", 500))?
333            as u64;
334
335        let total_pages = if per_page == 0 {
336            1
337        } else {
338            total_items.div_ceil(per_page)
339        };
340
341        Ok(PaginatedResult {
342            total_items,
343            total_pages,
344            next_page: if total_pages == 0 {
345                None
346            } else if page < (total_pages - 1) {
347                Some(page + 1)
348            } else {
349                None
350            },
351            previous_page: if page > 0 { Some(page - 1) } else { None },
352            page,
353            items,
354            per_page,
355        })
356    }
357}
358
359#[derive(Clone, Debug, Default, serde::Deserialize, serde::Serialize)]
360pub enum PaginationOrder {
361    #[default]
362    Ascending,
363    Descending,
364}
365
366///
367/// This type contains meta information for a pagination
368///
369#[derive(Clone, Debug, Default, serde::Deserialize, serde::Serialize)]
370pub struct PaginationQuery {
371    pub page: Option<u64>,
372    #[serde(alias = "per_page")]
373    pub page_size: Option<u64>,
374    #[serde(default)]
375    pub order: Option<(String, PaginationOrder)>,
376}
377
378///
379/// This trait will be used for everything that wants to play around with your entity on a very basic level.
380///
381/// To get access to filtering the [PaginatedSearch] should be used as this trait already handles the entity itself most of the time.
382///
383pub trait BaseRepository {
384    /// The entity that this repository will output
385    type Entity: for<'r> sqlx::FromRow<'r, sqlx::postgres::PgRow>
386        + serde::de::DeserializeOwned
387        + serde::Serialize;
388
389    /// The type of the primary key. It can consist of a tuple of multiple types if a compound key is used
390    type PkType: for<'r> sqlx::Type<sqlx::Postgres>;
391
392    ///
393    /// Gets a list of entities respecting the [PaginationQuery]
394    ///
395    /// To do a filtered list use the [PaginatedSearch] helper
396    ///
397    /// # Important Note
398    ///
399    /// The `order_by` is supposed to be only a column name. Whoever implements this trait can rely on that. That said whoever calls this
400    /// method has to make sure, no SQL data is inserted here.
401    ///
402    fn get_entities(
403        &self,
404        page: i32,
405        page_size: i32,
406        order_by: &'static str,
407        direction: PaginationOrder,
408    ) -> impl Future<Output = Result<Vec<Self::Entity>>> + Send;
409
410    ///
411    /// Gets a single entity by it's primary key(s)
412    ///
413    fn get_entity(&self, pk: Self::PkType) -> impl Future<Output = Result<Self::Entity>> + Send;
414
415    ///
416    /// Updates an entity. Reads the primary key(s) from the input
417    ///
418    fn update_entity(&self, entity: Self::Entity) -> impl Future<Output = Result<u64>> + Send;
419
420    ///
421    /// Creates a new entity. The implementation has to make sure, that the primary key(s) are handled properly (eg. by ignoring them)
422    ///
423    fn create_entity(
424        &self,
425        entity: Self::Entity,
426    ) -> impl Future<Output = Result<Self::Entity>> + Send;
427
428    ///
429    /// Deletes the given entity. Returns the amount of affected rows (ideally it's a 0 if the entity does not exist anymore or a 1 if the
430    /// PK was matched and the entry deleted)
431    ///
432    fn delete_entity(&self, pk: Self::PkType) -> impl Future<Output = Result<u64>> + Send;
433}
434
435/// An object-safe bindble value for sqlx
436pub trait Bindable<DB: sqlx::Database>: Send + Sync {
437    fn bind_to_query_builder<'q>(&self, qb: &mut QueryBuilder<'q, DB>)
438    where
439        Self: 'q;
440
441    fn bind_to_query<'q>(
442        &self,
443        query: Query<'q, DB, sqlx::postgres::PgArguments>,
444    ) -> Query<'q, DB, sqlx::postgres::PgArguments>
445    where
446        Self: 'q;
447
448    fn bind_to_separated<'qb, 'args>(
449        &self,
450        separated: &mut Separated<'qb, 'args, DB, &'static str>,
451    ) where
452        Self: 'qb,
453        Self: 'args;
454}
455
456impl<
457        T: for<'e> sqlx::encode::Encode<'e, sqlx::Postgres>
458            + sqlx::Type<sqlx::Postgres>
459            + Send
460            + Sync
461            + Clone,
462    > Bindable<sqlx::Postgres> for T
463{
464    fn bind_to_query_builder<'q>(&self, qb: &mut QueryBuilder<'q, sqlx::Postgres>)
465    where
466        Self: 'q,
467    {
468        qb.push_bind(self.clone());
469    }
470
471    fn bind_to_query<'q>(
472        &self,
473        query: Query<'q, sqlx::Postgres, sqlx::postgres::PgArguments>,
474    ) -> Query<'q, sqlx::Postgres, sqlx::postgres::PgArguments>
475    where
476        Self: 'q,
477    {
478        query.bind(self.clone())
479    }
480
481    fn bind_to_separated<'qb, 'args>(
482        &self,
483        separated: &mut Separated<'qb, 'args, sqlx::Postgres, &'static str>,
484    ) where
485        Self: 'qb,
486        Self: 'args,
487    {
488        separated.push_bind(self.clone());
489    }
490}
491
492impl std::fmt::Display for PaginationOrder {
493    fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
494        match self {
495            PaginationOrder::Ascending => f.write_str("ASC"),
496            PaginationOrder::Descending => f.write_str("DESC"),
497        }
498    }
499}
500
501impl<S> FromState<S> for PaginatedSearch
502where
503    S: ProvideState<Database>,
504{
505    fn from_state(state: &S) -> Self {
506        Self {
507            database: ProvideState::<Database>::provide(state),
508        }
509    }
510}
511
512impl Default for AndCriteria {
513    fn default() -> Self {
514        Self::new()
515    }
516}
517
518impl AndCriteria {
519    pub fn new() -> Self {
520        Self {
521            criteria: Vec::new(),
522        }
523    }
524
525    pub fn and(mut self, criteria: impl SearchCriteria + 'static) -> Self {
526        self.criteria.push(Box::new(criteria));
527
528        self
529    }
530}
531
532impl Default for OrCriteria {
533    fn default() -> Self {
534        Self::new()
535    }
536}
537
538impl OrCriteria {
539    pub fn new() -> Self {
540        Self {
541            criteria: Vec::new(),
542        }
543    }
544
545    pub fn or(mut self, criteria: impl SearchCriteria + 'static) -> Self {
546        self.criteria.push(Box::new(criteria));
547
548        self
549    }
550}
551
552impl EqCriteria {
553    pub fn new(column: &'static str, value: impl Bindable<sqlx::Postgres> + 'static) -> Self {
554        Self {
555            column,
556            value: Box::new(value),
557        }
558    }
559}
560
561impl NeCriteria {
562    pub fn new(column: &'static str, value: impl Bindable<sqlx::Postgres> + 'static) -> Self {
563        Self {
564            column,
565            value: Box::new(value),
566        }
567    }
568}
569
570impl LikeCriteria {
571    pub fn new(column: &'static str, value: impl Bindable<sqlx::Postgres> + 'static) -> Self {
572        Self {
573            column,
574            value: Box::new(value),
575        }
576    }
577}
578
579impl GtCriteria {
580    pub fn new(column: &'static str, value: impl Bindable<sqlx::Postgres> + 'static) -> Self {
581        Self {
582            column,
583            value: Box::new(value),
584        }
585    }
586}
587
588impl GteCriteria {
589    pub fn new(column: &'static str, value: impl Bindable<sqlx::Postgres> + 'static) -> Self {
590        Self {
591            column,
592            value: Box::new(value),
593        }
594    }
595}
596
597impl LtCriteria {
598    pub fn new(column: &'static str, value: impl Bindable<sqlx::Postgres> + 'static) -> Self {
599        Self {
600            column,
601            value: Box::new(value),
602        }
603    }
604}
605
606impl LteCriteria {
607    pub fn new(column: &'static str, value: impl Bindable<sqlx::Postgres> + 'static) -> Self {
608        Self {
609            column,
610            value: Box::new(value),
611        }
612    }
613}
614
615impl NotNullCriteria {
616    pub fn new(column: &'static str) -> Self {
617        Self { column }
618    }
619}
620
621impl IsNullCriteria {
622    pub fn new(column: &'static str) -> Self {
623        Self { column }
624    }
625}
626
627impl InCriteria {
628    pub fn new<I: Bindable<sqlx::Postgres> + 'static>(
629        column: &'static str,
630        values: impl IntoIterator<Item = I>,
631    ) -> Self {
632        Self {
633            column,
634            values: values
635                .into_iter()
636                .map(|value| Box::new(value) as Box<dyn Bindable<sqlx::Postgres>>)
637                .collect::<Vec<_>>(),
638        }
639    }
640}
641
642impl SearchCriteria for AndCriteria {
643    fn extend_query_builder(&self, qb: &mut QueryBuilder<'_, sqlx::Postgres>) {
644        let mut peekable = self.criteria.iter().peekable();
645        qb.push(" ( ");
646        while let Some(criteria) = peekable.next() {
647            criteria.extend_query_builder(qb);
648
649            if peekable.peek().is_some() {
650                qb.push(" AND ");
651            }
652        }
653        qb.push(" ) ");
654    }
655}
656
657impl SearchCriteria for OrCriteria {
658    fn extend_query_builder(&self, qb: &mut QueryBuilder<'_, sqlx::Postgres>) {
659        let mut peekable = self.criteria.iter().peekable();
660        qb.push(" ( ");
661        while let Some(criteria) = peekable.next() {
662            criteria.extend_query_builder(qb);
663
664            if peekable.peek().is_some() {
665                qb.push(" OR ");
666            }
667        }
668        qb.push(" ) ");
669    }
670}
671
672impl SearchCriteria for EqCriteria {
673    fn extend_query_builder(&self, qb: &mut QueryBuilder<'_, sqlx::Postgres>) {
674        self.value
675            .bind_to_query_builder(qb.push(self.column).push(" = "));
676    }
677}
678
679impl SearchCriteria for NeCriteria {
680    fn extend_query_builder(&self, qb: &mut QueryBuilder<'_, sqlx::Postgres>) {
681        self.value
682            .bind_to_query_builder(qb.push(self.column).push(" != "));
683    }
684}
685
686impl SearchCriteria for LikeCriteria {
687    fn extend_query_builder(&self, qb: &mut QueryBuilder<'_, sqlx::Postgres>) {
688        self.value
689            .bind_to_query_builder(qb.push(self.column).push(" LIKE "));
690    }
691}
692
693impl SearchCriteria for GtCriteria {
694    fn extend_query_builder(&self, qb: &mut QueryBuilder<'_, sqlx::Postgres>) {
695        self.value
696            .bind_to_query_builder(qb.push(self.column).push(" > "));
697    }
698}
699
700impl SearchCriteria for GteCriteria {
701    fn extend_query_builder(&self, qb: &mut QueryBuilder<'_, sqlx::Postgres>) {
702        self.value
703            .bind_to_query_builder(qb.push(self.column).push(" >= "));
704    }
705}
706
707impl SearchCriteria for LtCriteria {
708    fn extend_query_builder(&self, qb: &mut QueryBuilder<'_, sqlx::Postgres>) {
709        self.value
710            .bind_to_query_builder(qb.push(self.column).push(" < "));
711    }
712}
713
714impl SearchCriteria for LteCriteria {
715    fn extend_query_builder(&self, qb: &mut QueryBuilder<'_, sqlx::Postgres>) {
716        self.value
717            .bind_to_query_builder(qb.push(self.column).push(" <= "));
718    }
719}
720
721impl SearchCriteria for NotNullCriteria {
722    fn extend_query_builder(&self, qb: &mut QueryBuilder<'_, sqlx::Postgres>) {
723        qb.push(self.column).push(" IS NOT NULL ");
724    }
725}
726
727impl SearchCriteria for IsNullCriteria {
728    fn extend_query_builder(&self, qb: &mut QueryBuilder<'_, sqlx::Postgres>) {
729        qb.push(self.column).push(" IS NULL ");
730    }
731}
732
733impl SearchCriteria for InCriteria {
734    fn extend_query_builder(&self, qb: &mut QueryBuilder<'_, sqlx::Postgres>) {
735        qb.push(self.column)
736            .push(" IN ")
737            .push_tuples(&self.values, |mut builder, value| {
738                value.bind_to_separated(&mut builder);
739            });
740    }
741}
742
743impl ProvideState<Database> for sqlx::PgPool {
744    fn provide(&self) -> Database {
745        Database::from(self.clone())
746    }
747}
748
749#[cfg(test)]
750mod tests {
751    use sqlx::PgPool;
752
753    use crate::{
754        helper::database::{
755            AndCriteria, EqCriteria, GtCriteria, GteCriteria, InCriteria, IsNullCriteria,
756            LikeCriteria, LtCriteria, LteCriteria, NeCriteria, NotNullCriteria, OrCriteria,
757            PaginatedSearch, PaginationQuery,
758        },
759        state::FromState,
760        Result,
761    };
762
763    #[derive(Clone, Debug, serde::Deserialize, serde::Serialize, sqlx::FromRow)]
764    pub struct Article {
765        pub id: i32,
766        pub created_at: time::OffsetDateTime,
767        pub updated_at: time::OffsetDateTime,
768        pub title: String,
769        pub content: String,
770    }
771
772    #[sqlx::test(
773        fixtures(path = "../../fixtures", scripts("articles")),
774        migrations = "./migrations_test"
775    )]
776    async fn test_plain_search(db: PgPool) -> Result<()> {
777        let search = PaginatedSearch::from_state(&db);
778        let articles = search
779            .search::<Article, EqCriteria>("article", None, None)
780            .await?;
781
782        assert_eq!(articles.items.len(), 5);
783        assert_eq!(articles.page, 0);
784        assert_eq!(articles.total_pages, 1);
785        assert_eq!(articles.next_page, None);
786        assert_eq!(articles.previous_page, None);
787
788        Ok(())
789    }
790
791    #[sqlx::test(
792        fixtures(path = "../../fixtures", scripts("articles")),
793        migrations = "./migrations_test"
794    )]
795    async fn test_search_by_id(db: PgPool) -> Result<()> {
796        let search = PaginatedSearch::from_state(&db);
797        let articles = search
798            .search::<Article, EqCriteria>("article", Some(EqCriteria::new("id", 1)), None)
799            .await?;
800
801        assert_eq!(articles.items.len(), 1, "Wrong size");
802        assert_eq!(articles.items.first().unwrap().id, 1, "Wrong id");
803        assert_eq!(articles.page, 0, "Wrong page");
804        assert_eq!(articles.total_pages, 1, "Wrong total pages");
805        assert_eq!(articles.next_page, None, "Wrong next page");
806        assert_eq!(articles.previous_page, None, "Wrong previous page");
807
808        Ok(())
809    }
810
811    #[sqlx::test(
812        fixtures(path = "../../fixtures", scripts("articles")),
813        migrations = "./migrations_test"
814    )]
815    async fn test_search_paged(db: PgPool) -> Result<()> {
816        let search = PaginatedSearch::from_state(&db);
817        let articles = search
818            .search::<Article, EqCriteria>(
819                "article",
820                None,
821                Some(PaginationQuery {
822                    page: 2.into(),
823                    page_size: 1.into(),
824                    ..Default::default()
825                }),
826            )
827            .await?;
828
829        assert_eq!(articles.items.len(), 1);
830        assert_eq!(articles.page, 2);
831        assert_eq!(articles.total_pages, 5);
832        assert_eq!(articles.next_page, Some(3));
833        assert_eq!(articles.previous_page, Some(1));
834
835        Ok(())
836    }
837
838    #[sqlx::test(
839        fixtures(path = "../../fixtures", scripts("articles")),
840        migrations = "./migrations_test"
841    )]
842    async fn test_search_page_min(db: PgPool) -> Result<()> {
843        let search = PaginatedSearch::from_state(&db);
844        let articles = search
845            .search::<Article, EqCriteria>(
846                "article",
847                None,
848                Some(PaginationQuery {
849                    page: 0.into(),
850                    page_size: 1.into(),
851                    ..Default::default()
852                }),
853            )
854            .await?;
855
856        assert_eq!(articles.items.len(), 1);
857        assert_eq!(articles.page, 0);
858        assert_eq!(articles.total_pages, 5);
859        assert_eq!(articles.next_page, Some(1));
860        assert_eq!(articles.previous_page, None);
861
862        Ok(())
863    }
864
865    #[sqlx::test(
866        fixtures(path = "../../fixtures", scripts("articles")),
867        migrations = "./migrations_test"
868    )]
869    async fn test_search_page_max(db: PgPool) -> Result<()> {
870        let search = PaginatedSearch::from_state(&db);
871        let articles = search
872            .search::<Article, EqCriteria>(
873                "article",
874                None,
875                Some(PaginationQuery {
876                    page: 4.into(),
877                    page_size: 1.into(),
878                    ..Default::default()
879                }),
880            )
881            .await?;
882
883        assert_eq!(articles.items.len(), 1);
884        assert_eq!(articles.page, 4);
885        assert_eq!(articles.total_pages, 5);
886        assert_eq!(articles.next_page, None);
887        assert_eq!(articles.previous_page, Some(3));
888
889        Ok(())
890    }
891
892    #[sqlx::test(
893        fixtures(path = "../../fixtures", scripts("articles")),
894        migrations = "./migrations_test"
895    )]
896    async fn test_eq_criteria(db: PgPool) -> Result<()> {
897        let search = PaginatedSearch::from_state(&db);
898        let entities = search
899            .search::<Article, _>("article", EqCriteria::new("id", 3), None)
900            .await?;
901
902        assert_eq!(entities.items.len(), 1);
903        assert_eq!(entities.items.first().unwrap().id, 3);
904
905        Ok(())
906    }
907
908    #[sqlx::test(
909        fixtures(path = "../../fixtures", scripts("articles")),
910        migrations = "./migrations_test"
911    )]
912    async fn test_ne_criteria(db: PgPool) -> Result<()> {
913        let search = PaginatedSearch::from_state(&db);
914        let entities = search
915            .search::<Article, _>("article", NeCriteria::new("id", 3), None)
916            .await?;
917
918        assert_eq!(entities.items.len(), 4);
919        assert_ne!(entities.items.first().unwrap().id, 3);
920
921        Ok(())
922    }
923
924    #[sqlx::test(
925        fixtures(path = "../../fixtures", scripts("articles")),
926        migrations = "./migrations_test"
927    )]
928    async fn test_gt_criteria(db: PgPool) -> Result<()> {
929        let search = PaginatedSearch::from_state(&db);
930        let entities = search
931            .search::<Article, _>("article", GtCriteria::new("id", 3), None)
932            .await?;
933
934        assert_eq!(
935            entities
936                .items
937                .into_iter()
938                .map(|item| item.id)
939                .collect::<Vec<_>>()
940                .as_slice(),
941            &[4, 5]
942        );
943
944        Ok(())
945    }
946
947    #[sqlx::test(
948        fixtures(path = "../../fixtures", scripts("articles")),
949        migrations = "./migrations_test"
950    )]
951    async fn test_gte_criteria(db: PgPool) -> Result<()> {
952        let search = PaginatedSearch::from_state(&db);
953        let entities = search
954            .search::<Article, _>("article", GteCriteria::new("id", 3), None)
955            .await?;
956
957        assert_eq!(
958            entities
959                .items
960                .into_iter()
961                .map(|item| item.id)
962                .collect::<Vec<_>>()
963                .as_slice(),
964            &[3, 4, 5]
965        );
966
967        Ok(())
968    }
969
970    #[sqlx::test(
971        fixtures(path = "../../fixtures", scripts("articles")),
972        migrations = "./migrations_test"
973    )]
974    async fn test_lt_criteria(db: PgPool) -> Result<()> {
975        let search = PaginatedSearch::from_state(&db);
976        let entities = search
977            .search::<Article, _>("article", LtCriteria::new("id", 3), None)
978            .await?;
979
980        assert_eq!(
981            entities
982                .items
983                .into_iter()
984                .map(|item| item.id)
985                .collect::<Vec<_>>()
986                .as_slice(),
987            &[1, 2]
988        );
989
990        Ok(())
991    }
992
993    #[sqlx::test(
994        fixtures(path = "../../fixtures", scripts("articles")),
995        migrations = "./migrations_test"
996    )]
997    async fn test_lte_criteria(db: PgPool) -> Result<()> {
998        let search = PaginatedSearch::from_state(&db);
999        let entities = search
1000            .search::<Article, _>("article", LteCriteria::new("id", 3), None)
1001            .await?;
1002
1003        assert_eq!(
1004            entities
1005                .items
1006                .into_iter()
1007                .map(|item| item.id)
1008                .collect::<Vec<_>>()
1009                .as_slice(),
1010            &[1, 2, 3]
1011        );
1012
1013        Ok(())
1014    }
1015
1016    #[sqlx::test(
1017        fixtures(path = "../../fixtures", scripts("articles")),
1018        migrations = "./migrations_test"
1019    )]
1020    async fn test_like_criteria(db: PgPool) -> Result<()> {
1021        let search = PaginatedSearch::from_state(&db);
1022        let entities = search
1023            .search::<Article, _>("article", LikeCriteria::new("title", "% Article 2"), None)
1024            .await?;
1025
1026        assert_eq!(entities.items.first().unwrap().title, "Test Article 2");
1027
1028        Ok(())
1029    }
1030
1031    #[sqlx::test(
1032        fixtures(path = "../../fixtures", scripts("articles")),
1033        migrations = "./migrations_test"
1034    )]
1035    async fn test_not_null_criteria(db: PgPool) -> Result<()> {
1036        let search = PaginatedSearch::from_state(&db);
1037        let entities = search
1038            .search::<Article, _>("article", NotNullCriteria::new("title"), None)
1039            .await?;
1040
1041        assert_eq!(entities.items.len(), 5);
1042
1043        Ok(())
1044    }
1045
1046    #[sqlx::test(
1047        fixtures(path = "../../fixtures", scripts("articles")),
1048        migrations = "./migrations_test"
1049    )]
1050    async fn test_is_null_criteria(db: PgPool) -> Result<()> {
1051        let search = PaginatedSearch::from_state(&db);
1052        let entities = search
1053            .search::<Article, _>("article", IsNullCriteria::new("title"), None)
1054            .await?;
1055
1056        assert_eq!(entities.items.len(), 0);
1057
1058        Ok(())
1059    }
1060
1061    #[sqlx::test(
1062        fixtures(path = "../../fixtures", scripts("articles")),
1063        migrations = "./migrations_test"
1064    )]
1065    async fn test_in_criteria(db: PgPool) -> Result<()> {
1066        let search = PaginatedSearch::from_state(&db);
1067        let entities = search
1068            .search::<Article, _>("article", InCriteria::new("id", [1, 3, 5]), None)
1069            .await?;
1070
1071        assert_eq!(
1072            entities
1073                .items
1074                .into_iter()
1075                .map(|item| item.id)
1076                .collect::<Vec<_>>()
1077                .as_slice(),
1078            &[1, 3, 5]
1079        );
1080
1081        Ok(())
1082    }
1083
1084    #[sqlx::test(
1085        fixtures(path = "../../fixtures", scripts("articles")),
1086        migrations = "./migrations_test"
1087    )]
1088    async fn test_and_criteria(db: PgPool) -> Result<()> {
1089        let search = PaginatedSearch::from_state(&db);
1090        let entities = search
1091            .search::<Article, _>(
1092                "article",
1093                AndCriteria::new()
1094                    .and(LikeCriteria::new("title", "%Article%"))
1095                    .and(LikeCriteria::new("title", "%3")),
1096                None,
1097            )
1098            .await?;
1099
1100        assert_eq!(
1101            entities
1102                .items
1103                .into_iter()
1104                .map(|item| item.id)
1105                .collect::<Vec<_>>()
1106                .as_slice(),
1107            &[3]
1108        );
1109
1110        Ok(())
1111    }
1112
1113    #[sqlx::test(
1114        fixtures(path = "../../fixtures", scripts("articles")),
1115        migrations = "./migrations_test"
1116    )]
1117    async fn test_or_criteria(db: PgPool) -> Result<()> {
1118        let search = PaginatedSearch::from_state(&db);
1119        let entities = search
1120            .search::<Article, _>(
1121                "article",
1122                OrCriteria::new()
1123                    .or(EqCriteria::new("id", 3))
1124                    .or(EqCriteria::new("id", 4)),
1125                None,
1126            )
1127            .await?;
1128
1129        assert_eq!(
1130            entities
1131                .items
1132                .into_iter()
1133                .map(|item| item.id)
1134                .collect::<Vec<_>>()
1135                .as_slice(),
1136            &[3, 4]
1137        );
1138
1139        Ok(())
1140    }
1141}
quokka/helper/database.rs

quokka/helper/
database.rs
