Surreal simple querybuilder

A simple query-builder for the Surreal Query Language, for SurrealDB. Aims at being simple to use and not too verbose first.

#[derive(Debug, Serialize, Deserialize, Default)]
pub struct IUser {
  #[serde(skip_serializing_if = "Option::is_none")]
  pub id: Option<Id>,
  pub handle: String,
  pub messages: ForeignVec<IMessage>,
}

model!(User {
  id,
  pub handle,
  pub messages
});

impl IUser {
  pub fn find_by_handle(handle: &str) -> ApiResult<Vec<Self>> {
    use surreal_simple_querybuilder::queries::select;
    use schema::model as user;

    let (query, params) = select("*", user, (Where(user.handle, handle), Fetch([&*user.messages]))?;
    let items = DB.query(query).bind(params).await?.take(0)?;

    items
  }
}

Summary

• Surreal simple querybuilder
• Summary
• Why a query-builder
• SQL injections
• Compiler requirements/features
• Examples
• Premade queries with dynamic parameters (queries feature)
  • Why dynamic parameters
  • Limitations \& recommandations for premade queries \& params
• The model macro (model feature)
  • public \& private fields in models
  • Relations between your models
  • Partials builder generation
• The NodeBuilder traits (querybuilder feature)
• The QueryBuilder type (querybuilder feature)
• The ForeignKey and Foreign types (foreign feature)
  • ForeignKey and loaded data during serialization
• Using the querybuilder in combination of the official SurrealDB client

Why a query-builder

Query builders allow you to dynamically build your queries with some compile time checks to ensure they result in valid SQL queries. Unlike ORMs, query-builders are built to be lightweight and easy to use, meaning you decide when and where to use one. You could stick to hard coded string for the simple queries but use a builder for complex ones that require parameters & variables and may change based on these variables for example.

While the crate is first meant as a query-building utility, it also comes with macros and generic types that may help you while managing you SQL models in your rust code. Refer to the node macro and the Foreign type example

SQL injections

The strings you pass to the query builder are not sanitized in any way. Please use parameters in your queries like SET username = $username with surrealdb parameters to avoid injection issues. However the crate comes with utility functions to easily create parameterized fields, refer to the NodeBuilder trait.

Compiler requirements/features

The crate uses const expressions for its model creation macros in order to use stack based arrays with sizes deduced by the compiler. For this reason any program using the crate has to add the following at the root of the main file:

#![allow(incomplete_features)]
#![feature(generic_const_exprs)]

Examples

Keep in mind all of the demonstrated features can be used independently of the rest. They can all be combined if you want to, but if you prefer a lightweight solution then it is possible as well.

By default only the querybuilder is available, other modules require you to enable their respective crate features.

• A series of examples are available to offer a guided introduction to the core features of the crate
• An all-in-one example can be found in the alternate surrealdb-architecture repository
• For an explanation of what each component in the crate does, refer to the chapters below.

Premade queries with dynamic parameters (queries feature)

The crate offers a set of premade queries you can access in surreal_simple_querybuilder::queries::*; or in the prelude for easier access.

use surreal_simple_querybuilder::prelude::*;

fn main() {
  let (query, _bindings) = select("*", "user", ());

  assert_eq!(query, "SELECT * FROM user");
}

these pre-made query functions accept all types of parameters to further extend the queries. If dynamic values (variables) are passed among these parameters then the functions will automatically add them to the list of bindings:

use surreal_simple_querybuilder::prelude::*;
use serde_json::json;

fn main() {
  let (query, bindings) = select("*", "user", Where(json!({ "name": "John" })));

  assert_eq!(query, "SELECT * FROM user WHERE name = $name");

  // 👇 the bindings were updated with the $name variable
  assert_eq!(bindings.get("name"), Some("John".to_owned())); 
}

Why dynamic parameters

At a first glance these pre-made queries offer nothing the querybuilder doesn't, but in reality they allow you to easily make functions in your backends (for example) that you can extend if need be.

The first scenario that comes to mind is a standard function to retrieve books by the author:

impl Book {
  fn find_by_author_id(id: &str) -> Vec<Self> {
    // ...
  }
}

In some cases you'll need the list of books and nothing else, another time you'll need the results to be paginated, and sometimes you'll want to fetch the author data on top of the books. Considering you may also want to have the books with both pagination and fetch this could potentially result in at least 4 different functions & queries to write.

With the dynamic parameters you can update your find function to accept optional parameters so that only 1 simple function is needed:

use serde_json::json;

impl Book {
  fn find_by_author_id<'a>(id: &str, params: impl QueryBuilderInjecter<'a> + 'a) -> Vec<Self> {
    let filter = Where(json!({"author": id}));
    let combined_params = (filter, params);

    let (query, params) = select("*", "Book", combined_params).unwrap();

    DB.query(query)
      .bind(params)
      .await.unwrap()
      .get(..).unwrap()
  }
}

So you can now do:

let books = Book::find_by_author_id("User:john", ());
let paginated_books = Book::find_by_author_id("User:john", Pagination(0..25));
let paginated_books_with_author_data = Book::find_by_author_id(
  "User:john",
  (
    Pagination(0..25),
    Fetch(["author"])
  )
);

The dynamic parameters & premade queries are made with the model macro in mind, you don't necessarily need it but if you wanted, both systems can be used for some compile time checks + dynamic parameters to enjoy the extra freedom dynamic parameters provide while being sure all of the fields & nodes you reference in them are valid thanks to the models. A complete example on how to combine both system is available here.

Alternatively if the use of a generic argument is not your cup of tea, you can use an enum that implements QueryBuilderInjecter. The surrealdb-architecture repository demonstrates how to setup one.

Limitations & recommandations for premade queries & params

The short example and complete test case demonstrate the premade queries can work in 99% of the cases and can seriously simplify the code you write. However there are limitations one must be aware of before going too deep into the premade queries.

The premade queries and composable parameters are made for those simple cases where you just want to select/create/etc... elements without complex filtering in the WHERE clause or anything. For example selecting books by one of their field is perfect for the premade queries as you can add a fetch clause without having to rewrite anything. It allows you to have somewhat generic functions in your codebase for the simple cases.

But as soon as it gets complex, the QueryBuilder type should be used instead of the pre-made queries. It will offer both better performances & more predictable results (nesting lots of params may yield unexpected queries). Note that you can still use a query-builder and pass it params (aka injecters) if needed:

use surreal_simple_querybuilder::prelude::*;

let params = (
  Where(("name", "john")),
  Fetch(["articles"])
);

let query = QueryBuilder::new()
  .select("*")
  .from("user")
  .injecter(&params) // <-- pass the injecter to the builder
  .build();

let _params = bindings(params); // <-- get the variables so you can bind them

assert(query, "SELECT * FROM user WHERE name = $name FETCH articles");

And as you can see, even in the more complex cases the params can still be used but the pre-made queries should not however.

The model macro (model feature)

The model macro allows you to quickly create structs (aka models) with fields that match the nodes of your database.

example

```rust use surreal_simple_querybuilder::prelude::*;

struct Account { id: Option, handle: String, password: String, email: String, friends: Foreign> }

model!(Account { id, handle, password, friends> });

fn main() { // the schema module is created by the macro use schema::model as account;

let query = format!("select {} from {account}", account.handle);
assert_eq!("select handle from Account", query);

} ```

This allows you to have compile time checked constants for your fields, allowing you to reference them while building your queries without fearing of making a typo or using a field you renamed long time ago.

public & private fields in models

The QueryBuilder type offers a series of methods to quickly list the fields of your models in SET or UPDATE statements so you don't have to write the fields and the variable names one by one. Since you may not want to serialize some of the fields like the id for example the model macro has the pub keyword to mark a field as serializable. Any field without the pub keyword in front of it will not be serialized by these methods.

model!(Project {
  id, // <- won't be serialized
  pub name, // <- will be serialized
})

fn example() {
  use schema::model as project;

  let query = QueryBuilder::new()
    .set_model(project)
    .build();

  assert_eq!(query, "SET name = $name");
}

Relations between your models

If you wish to include relations (aka edges) in your models, the model macro has a special syntax for them:

mod account {
  use surreal_simple_querybuilder::prelude::*;
  use super::project::schema::Project;

  model!(Account {
    id,

    ->manage->Project as managed_projects
  });
}

mod project {
  use surreal_simple_querybuilder::prelude::*;
  use super::project::schema::Project;

  model!(Project {
    id,
    name,

    <-manage<-Account as authors
  });
}

fn main() {
    use account::schema::model as account;

    let query = format!("select {} from {account}", account.managed_projects);
    assert_eq!("select ->manage->Project from Account");

    let query = format!("select {} from {account}", account.managed_projects().name.as_alias("project_names"))
    assert_eq!("select ->manage->Project.name as project_names from Account", query);
  }

Partials builder generation

The macro supports condition flags you can pass to generate more code for you. One of them is the generation of a "Partial" builder. A partial type is a copy of the model you created where all fields are Option<serde_json::Value> set with the serde flag to skip the fields that are None during serialization.

Such a partial builder can be used like so:

// notice the `with(partial)`
model!(Project with(partial) {
  id,
  pub name
});

let partial_user = PartialProject::new()
  .name("John Doe");

This partial type comes handy when constructing queries with nested fields thanks to its ok() method:

let partial_post = PartialPost::new()
  .title("My post title")
  .author(PartialUser::new().name("John Doe"))
  .ok()?;

which will output the following flattened json:

{
  "title": "My post title",
  "author.name": "John Doe"
}

If you'd like a normal nested object then you can skip the ok call and past the object to the serialize function of your choice.

You can then use the builder in your queries:

let user = DB.update(user_id)
  .merge(PartialUser::new()
    .name("Jean")
    .posts(vec![post1_id, post2_id])
    .ok()?
  ).await?

// ...

let filter = Where(PartialPost::new()
  .title("My post title")
  .author(PartialUser::new().name("John Doe"))
  .ok()?);

let posts = queries.select("*", "post", filter).await?;

Note that partial builders are an alternative syntax to building the json objects using the serde_json::json! macro combined with the models. The above example is the same as the following example, so pick whatever solution you prefer: ```rust let user = DB.update(user_id) .merge(json!({ model.name: "Jean", model.posts: vec![post1_id, post2_id] })).await?

// ...

// the wjson! macro is a shortcut to Where(json!()) let filter = wjson!({ model.title: "My post title", model.author().name: "John Doe" });

let posts = select("*", "post", filter).