Introduction

Welcome to this Book on Vantage. Before we dive in, few assumptions:

You have read the Introduction (README): https://github.com/romaninsh/vantage
You have cloned this repository, installed postgres and ran 0-intro example.
You have interest in software development efficiency, safety and maintainability.
You have a bit of patience to get to the bottom of various components of Vantage as they are explained.
You might need some Rust experience, but I'll try to provide pointers and keep things simple.

To get us on the same page, I'll need to go over a basic principles of enterprise software requirements. Those won't apply to majority of Rust projects, but they are crucial in commercial software development.

If you are familiar with the concepts (such as from work by Martin Fowler or Eric Evans), you can skip this section.

The Purpose of separation of concerns and complexity abstraction

Terms concern separation and complexity abstraction may be new to you, so I'm going to look at the enterprise software more generally and also explain why languages such as Java, C# and TypeScript are considered suitable, while Rust so far had no traction.

Majority of code for the enterprise is designed to "codify" business logic. As company grows, business rules become more complex. When you open a bank account, you can choose from few account types, but under the hood banks have hundreds of legacy account types. A lot of hidden use-cases are needed for regulations or to support legacy systems.

In order to keep the code maintainable, old code must be pruned without affecting stability of remaining code. Refactoring will always face resistance from product leads, who prefer rolling out new features.

Companies prefer systems and frameworks which are more modular and easier to maintain. In many cases, developers who originally wrote the code would no longer be with the company and a lot of code is written by junior developers too.

Separation of Concerns

Within your average product team (say - 20 developers) engineers who share the code, engineers would find preference towards a certain sub-sections of your app. Some developers may prefer working on the "order processing" while others will dive into "onboarding" flow. Those are called areas of concern. The practice of separating those areas is called SoC - Separation of Concerns.

Rust generally relies on sub-crates to separate concerns, but there is one area, where those areas intersect - and that is the "persistence layer".

Persistence layer

As a business software is dealing with events - it needs to store it's state. Data needs to persist between execution as well as propogate between containers. Business software is state-less - when request comes in, the data is loaded, processed and stored back.

Request can come in from an API interface or event queue. Similarly data can be loaded/stored from Database, APIs or Caches.

It is quite common that in order to process a single request the data would need to be loaded and stored several times. Quite often external API would rely on intermediate APIs (middleware) to achieve the task.

Idempotence

Any request can fail. It can fail before any data is loaded or it can fail after the data is processed and stored. Idempotence is a design pattern that ensures that any requset can be retried by the caller. If the original request was successful, retry will also be successful, but it will not cause any duplicates. This is called Retry safety.

To give you an example, request to send payment of $10 should not send a total of $20 if it is "retried" due to communication issue inside a middleware.

Complexity Abstraction

I have just went through some of the essential design principles of enterprise software systems. If this is new to you, it may sound excessively complex.

Well, there is another trick we use in enterprise system and it is complexity abstraction. An application can be built in layers, where each layer hides complexity.

In fact - when you were going through the introduction code, you have experienced complexity abstraction first hand. You saw a simple and readable code, that was hiding complexity through abstraction.

Rust is a complex language. Rust also does not provide any obvious way to implement complexity abstraction.

Vantage has a ready-to-use way to do this in a consistent way - for instance SoftDelete extension could provide retry safety for your delete operation.

Safety and Performance

In my opinion, the most important aspect of enterprise software is safety. As humans we all make mistakes. Quite often in a pursuit of performance, we sacrifice safety.

A seasoned developer may write a chunk of SQL code that quickly and efficiently performs a complex aggregation. When junior developer comes in, they may introduce a serious safety bug just by trying to tweak or replicate this complex SQL code.

Vantage offers a way to generate performant SQL code without sacrificing safety.

Impact of Change

I've seen many projects which were stuck with a "legacy" data structure because the codebase was riddled with hardcoded SQL queries. As business logic evolves, it will require changes in the data structure.

Persistence abstraction of Vantage creates a layer for backward-compatibility. Operations like splitting a table into two tables, moving columns between tables can be handled without affecting change in business logic.

Testability

It is crucial that business logic is testable. However - quite often the logic only works when it has access to the data. The integration tests usually provides test-data to a test-suite. Generally those are slow, they can't track code coverage and they are hard to debug.

Vantage provides a way to test business logic through unit tests. By mocking your data source, you can focus on the business logic. More importantly you can adopt test-driven development and create standards for your code with test coverage. Use of faster unit-tests also reduces your release cycle time - a metric that companies are actively looking at.

Business Appps with Vantage

Vantage addresses many of the challenges of enterprise software development.

Ready to learn how? The answers are coming.

Data Sets

Traditional ORMs operate with records. If you have used SeaORM or Diesel - you need to temporarily "un-learn" those. Vantage syntax may look similar, but it's very different to the classic ORM pattern:

Vantage operates with Data Sets. A set can contain either a single record, no records or a huge number of records. Set represents records in remote database (or API) and does not store anything in memory.
Vantage executes operations remotely. Changing multiple records in ORM involves fetching all the records, modifying them and saving them back. Vantage prefers to execute changes remotely, if underlying DataSource supports it.

As a developer, you will always know when Vantage interacts with the database, because those operations are async. Majority of Vantage operations - like traversing relationship, calculating sub-queries - those are sync.

`sql::Table` and `sql::Query`

Vantage implements sql::Table and sql::Query - because we all love SQL. However you can define other data sources - such as NoSQL, REST API, GraphQL, etc. Those extensions do not need to be in same crate as Vantage. For the rest of this book I will only focus on SQL as a predominant use-case.

Vantage is quite fluid in the way how you use table and query, you can use one to compliment or create another, but they serve different purpose:

sql::Table has a structure - fields, joins and relations are defined dynamically.
sql::Query on other hand is transient. It consists of smaller pieces which we call sql::Chunk.
sql::Table can create and return sql::Query object from various methods

sql::Chunk trait that is implemented by sql::Query (or other arbitrary expressions) acts as a glue betwene tables. For instance when you run traverse relations:

#![allow(unused)]
fn main() {
let client = Client::table().with_id(1);

let order_lines = client.ref_orders().ref_order_lines();
}

Vantage executes field query operation on a client set for field id.
Vantage creates orders table and adds condition on client_id field.
Vantage executes field query operation on orders table for field id.
Vantage creates order_lines table and adds condition on order_id field.

Vantage weaves between sql::Table and sql::Query without reaching out to the database behind a simple and intuitive code.

ReadableDataSet and WritableDataSet

Vantage provides two traits: ReadableDataSet and WritableDataSet. As name suggests - you can fetch records from Readable set. You can add, modify or delete records in Writable set.

Vantage implements those traits:

sql::Table - implements both ReadableDataSet and WritableDataSet.
sql::Query - implements ReadableDataSet only.

Operating with Data sets

At the very basic level - you can iterate through a readable data set.

#![allow(unused)]
fn main() {
let set_of_clients = Client::table();

for client in set_of_clients.get().await? {
    println!("{}", client.name);
}
}

There are more ways to fetch data from ReadableDataSet:

get - returns all records in a Vec using default entity type
get_as - return all records using a custom type
get_all_untyped - return all records as a raw JSON object
get_some and get-some_as - return only one record (or none)
get_row_untyped - return single record as a raw JSON object
get_col_untyped - return only a single column as a raw JSON values
get_one_untyped - return first column of a first row as a raw JSON value

In most cases you would use get and get_some:

#![allow(unused)]
fn main() {
let client = Client::table().with_id(1);

let Some(client_data) = client.get_some().await? else { // fetch single record
    // handle error
};

for client_order in client.orders().get().await? { // fetch multiple records
    println!("{}", client_order.id);
}
}

Creating Queries from Tables

Sometimes you do not want result, but would prefer a query object instead. This gives you a chance to tweak a query or use it elsewhere.

Vantage provides trait TableWithQueries that generates Query objects for you:

get_empty_query - returns a query with conditions and joins, but no fields
get_select_query - like get_empty_query but adds all physical fields
get_select_query_for_field_names - Provided with a slice of field names and expressions, only includes those into a query.
get_select_query_for_field - Provided a query for individual field or expression, which you have to pass through an argument.
get_select_query_for_fields - Provided a query for multiple fields

There are generally two things you can do with a query:

Tweak and execute it
Use it as a Chunk elsewhere

Tweaking and Executing a query

#![allow(unused)]
fn main() {
let vip_orders = Client::table().add_condition(Client::table().is_vip().eq(true)).ref_orders();

let query = vip_orders
    .get_select_query_for_field_names(&["id", "client_id", "client"]) // excluding `total` here
    .with_column("total".to_string(), expr!("sum({})", vip_orders.total())) // add as aggregate
    .with_column("order_count".to_string(), expr!("count(*)"))
    .with_group_by(vip_orders.client_id());

let result = postgres().query_raw(&query).await?;
}

Using a query as a `Chunk` elsewhere

#![allow(unused)]
fn main() {
// TODO - hypothetical example, not implemented in bakery_model

let product_123 = Product::table().with_code("PRD-123");
let john = Client::table().with_email("john@example.com");

let new_order = Order::table()
    .insert(Order {
        product: product_123,
        client: john,
        quantity: 1,
    }).await?;
}

#![allow(unused)]
fn main() {
// TODO: test
let john = Client::table().with_email("john@example.com");

let order = Order::table()
    .with_condition(Order::table().client_id().in(john.get_select_query_for_field(john.id())))
}

Method get_ref() does exactly that, when you traverse relationships.

Conclusion

DataSet is a powerful concept that sets aside Vantage from the usual ORM pattern. sql::Table and sql::Query are the building blocks you interract with most often in Vantage.

Understanding this would allow you to implement missing features (such as table grouping) and eventually devleop extensions for your data model.

Table

I have introduced sql::Table in context of Data Sets, however before diving deep into Table I must introduce SqlTable trait - a dyn-safe version of Table.

Table type takes 2 generic parameters: DataSource and Entity. This is similar to Vec<T> where T is a generic parameter. In other words Table<Postgres, User> is not the same as Table<Postgres, Order>.

Table also have methods returning Self for example with_column:

#![allow(unused)]
fn main() {
let users = Table::new("users", postgres());
let users = users.with_column("id"); // users has same type
}

Generic parameters and methods that return Self cannot be defined in dyn-safe traits. (See https://doc.rust-lang.org/reference/items/traits.html#object-safety for more info), whihc is why I created sql::SqlTable trait.

Table gives you convenience and you get unique type for your business entities, but if you need to deal with generic Table you can use SqlTable trait:

#![allow(unused)]
fn main() {
fn get_some_table() -> Box<dyn SqlTable> {
    if some_condition() {
        Box::new(Table::new("users", postgres()))
    } else {
        Box::new(Table::new("orders", postgres()))
    }
}
}

To convert Box<dyn SqlTable> back to Table<Postgres, User>, you can use downcasting:

#![allow(unused)]
fn main() {
let user: Table<Postgres, User> = get_some_table().as_any_ref().downcast_ref().unwrap();
}

If some_condition() was false and "orders" table was returned you get type missmatch and downcast will fail. That's just how Rust type system works, so only downcast when you are 100% sure that you are using the right type.

This works really for defining custom ref_* methods for entity traversal:

#![allow(unused)]
fn main() {
fn ref_orders(users: &Table<Postgres, User>) -> Table<Postgres, Order> {
    users.get_ref("orders").unwrap().as_any_ref().downcast_ref().unwrap()
    //       ^ returns Box<dyn SqlTable>
}
}

There is a more convenient method get_ref_as, that would downcast it for you:

#![allow(unused)]
fn main() {
fn ref_orders(users: &Table<Postgres, User>) -> Table<Postgres, Order> {
    users.get_ref_as("orders").unwrap()
    //       ^ returns Table<Postgres, _>
}
}

Let me collect some facts about Table and SqlTable:

sql::SqlTable is a dyn-safe trait implementing most basic features of a table
sql::Table is a struct implementing SqlTable trait and some additional features (such as ReadableDataSet)
When Table must refer to another table in a generic way, it will be using dyn SqlTable
sql::Table type relies on 2 generic parameters: DataSource and Entity
DataSource describes your SQL flavour and can affect how queries are built etc.
Entity can be implemented by any struct.

To reinforce your understanding of how this all works together, lets compare 3 examples. First I define a function that generates a report for Table<Postgres, Order>:

#![allow(unused)]
fn main() {
fn generate_order_report(orders: &Table<Postgres, Order>) -> Result<String> {
    ...
}

generate_order_report(Order::table()); // Table<Postgres, Order>
// generate_order_report(Client::table());
// does not compile ^
}

I'd like to test my method using MockDataSource and therefore I want it to work with any DataSource:

#![allow(unused)]
fn main() {
async fn generate_order_report<D: DataSource>(orders: Table<D, Order>) -> Result<String> {
    ...
}

let orders = Order::mock_table(&mock_orders);
generate_any_report(orders).await?;  // Table<MockDataSource, Order>

}

What if my code should work with any entity, but I don't wish to deal with SqlTable?

#![allow(unused)]
fn main() {
fn generate_any_report<D: DataSource, E: Entity>(table: Table<D, E>) -> Result<String> {
    ...
}

generate_any_report(Order::table()).await?;  // Table<Postgres, Order>
generate_any_report(Client::table()).await?; // Table<Postgres, Client>

let orders = Order::mock_table(&mock_data);
generate_any_report(orders).await?;  // Table<MockDataSource, Order>
}

(The nerdy explanation here is that Rust compiler will create 3 copies of generate_any_report function for each D and E combinations you use in the code).

Creating

A simplest way to create a table object would be Table::new:

#![allow(unused)]
fn main() {
let users = Table::new("users", postgres())
}

The type of users variable shall be Table<Postgres, EmptyEntity>. If instead of EmptyEntity you'd like to use User you can use new_with_entity method.

DataSource type is inferred from the second argument to new() - return type of postgres() function.

Lets look at how we can define our own Entity:

#![allow(unused)]
fn main() {
#[derive(Clone, Debug, Serialize, Deserialize, Default)]
struct User {
    id: i64,
    name: String,
}
impl Entity for User {}

let users = Table::new_with_entity::<User>("users", postgres());
}

Rust will infer type when it can, so:

#![allow(unused)]
fn main() {
fn user_table() -> Table<Postgres, User> {
    Table::new_with_entity("users", postgres())
}
}

Finally, rather than implementing a stand-alone method like that, we can implement it on the User struct:

#![allow(unused)]
fn main() {
impl User {
    fn table() -> Table<Postgres, User> {
        Table::new_with_entity("users", postgres())
    }
}
}

Since table structure is the same throughout the application, lets add columns into the table:

#![allow(unused)]
fn main() {
impl User {
    pub fn table() -> Table<Postgres, User> {
        Table::new_with_entity("users", postgres())
            .with_column("id")
            .with_column("name")
    }
}
}

Implementing custom traits for the entity

In Rust you can define an arbitrary trait and implement it on any type. Lets define trait UserTable and implement it on Table<Postgres, User>:

#![allow(unused)]
fn main() {
trait UserTable: SqlTable {
    fn name(&self) -> Arc<Column> {
        self.get_column("name").unwrap()
    }
}
impl UserTable for Table<Postgres, User> {}
}

Now we can call name() method on type Table<Postgres, User> to access name column more directly:

#![allow(unused)]
fn main() {
let user = User::table();
let name_column = user.name();
}

We can also modify our generate_order_report() function into a custom trait:

#![allow(unused)]
#![allow(async_fn_in_trait)]
fn main() {
pub trait OrderTableReports {
    async fn generate_report(&self) -> Result<String>;
}

// was: async fn generate_order_report<D: DataSource>(orders: Table<D, Order>) -> Result<String>

impl<D: DataSource> OrderTableReports for Table<D, Order> {
    async fn generate_report(&self) -> Result<String> {
        ...
    }
}
}

Conclusion

I have explained about sql::Table struct and SqlTable trait and which of the two should be used. Also I have explained how to create custom traits and extend Table for specific entity type.

In my next chapters I'll refer to Table but in most cases you should understand that most features would also work with SqlTable trait.

This chapter was Rust-intensive, but you should now understand how entity types are used in Vantage.

Table Columns

Table allows you to define and reference columns. A Column is another struct in Vantage. Using columns outside of table context is convenient, such as for defining an expression:

#![allow(unused)]
fn main() {
let users = Table::new("users", postgres())
    .with_column("id")
    .with_column("name")
    .with_column("surname")
    .with_expression("full_name", |t| {
        expr!(
            "concat_ws({}, {}, {})",
            " ",
            t.get_column("name").unwrap(),
            t.get_column("surname").unwrap()
        )
    });
}

Columns can also be used in conditions:

#![allow(unused)]
fn main() {
let condition = users.get_column("name").unwrap().eq("John");
let john = user.with_condition(condition);
}

When you use user.get(), with User { name, surname, full_name }, Table needs to ensure query would include both columns and expression too. More broadly, lets talk about what can be deserialised into a User entity fields:

Field could be:

Column - There is a physical SQL column name and surname.
Expression - No physical column, but full_name is defined through a SQL expression.
Joined Columns - Table could be joined with another table, combining columns.
Callback - Value is calculated after record is fetched using a callback.

Lets dive into the following example scenario:

users: Table<Postgres, User> has a column name and surname, using table "user"
I added an expression full_name that combines name and surname columns
I also joined "user_address" table, that contains street and city columns
I also define callback for calculating post_code by fetching from external API or cache.

After above operations the following is true:

users has 3 columns
users has 1 expression
users has 1 joined table, which has another 2 columns
users has 1 callback
users has 6 fields: id, name, surname, full_name, user_address_street, user_address_city
users can deserialise into User struct with 7 fields: id, name, surname, full_name, user_address_street, user_address_city and post_code

Working with Table Columns: SqlTable

Column operations are implemented in TableWithColumns trait:

add_column - adds a column to the table
columns - returns all physical columns
add_id_column and add_title_column - adds a column but also labels it
id - return id column
title - return title column
id_with_table_alias - return id column with table alias
get_column - return a column by name
get_column_with_table_alias - return a column by name with table alias
search_for_field - similar to get_column but will look for lazy expressions and columns from joined tables.

Working with Table Columns: Table<D, E>

Table<D, E> implements some additional methods for convenience:

with_column - adds a column to the table and returns it
with_title_column - adds a title column to the table and returns it
with_id_column - adds an id column to the table and returns it

#![allow(unused)]
fn main() {
let users = Table::new("users", postgres())
    .with_id_column("id")
    .with_title_column("name")
    .with_column("role_name");
}

Extending Entity with column getters

it is common practice to define field getters like this:

#![allow(unused)]
fn main() {
pub trait ClientTable: SqlTable {
    fn name(&self) -> Arc<Column> {
        self.get_column("name").unwrap()
    }
    fn contact_details(&self) -> Arc<Column> {
        self.get_column("contact_details").unwrap()
    }
    fn bakery_id(&self) -> Arc<Column> {
        self.get_column("bakery_id").unwrap()
    }
}
}

This makes it easier to reference columns:

#![allow(unused)]
fn main() {
expr!(
    "concat_ws({}, {}, {})",
    " ",
    //t.get_column("name").unwrap(),
    //t.get_column("surname").unwrap()
    t.name(),
    t.surname()
)
}

Conclusion

Ability to specify columns not by name but through a dedicated method makes use of Rust type system and avoids typos at compile time. Fields in a query can be defined through different means.

Swapping Column into Expression allow you to restructure your field names without changing the code, also you can bring columns from across the entities, but for that we will need to learn more about Expressions

Expressions and Queries

In Vantage, query is a dynamic representation of a SQL query. You already saw how sql::Table is creating sql::Query, now it's time to learn how sql::Query works.

Query owes it's flexibility to Expressions or more specifically to a Chunk trait. Any type implementing Chunk can be part of a Query. Expression is just a simplest implementation of Chunk trait.

The main reason for using Expression is separation of SQL statements and its parameters. Treating SQL as a string introduces a possibility for SQL injections:

#![allow(unused)]
fn main() {
let query = format!(
  "SELECT * FROM product WHERE name = \"{}\"",
  user_name
);
}

What if user_name contains " character? Expression is able to handle this:

#![allow(unused)]
fn main() {
let expression = Expression::new(
    "SELECT * FROM product WHERE name = {}",
    vec![json!("DeLorian Doughnut")]);

writeln!(expression.preview());
}

Expression holds statement and parameters separatelly. Here are some methods of Expression:

expr!() - macro for creating new expression
new() - constructor, used by the macro
Expression::empty() - an empty expression
sql() - return SQL statement (without parameters)
sql_final() - returns SQL but will replace {} placeholders with $1, $2 as requested by an underlying SQL access library
params() - return array of parameters
preview() - will insert parameters into the statement and show it for preview purposes. Do not use for executing queries!
split() - returns statement and parameters as a tuple
from_vec() - combines multiple expressions into one using delimeter (like concat_ws)

`expr!()` macro

Parameters in expressions can have of several types, like i64 or String or &str:

#![allow(unused)]
fn main() {
let expression = expr!("INSERT INTO user (name, age) VALUES ({}, {})", "John", 30);
}

This macro relies on serde_json::json! macro to convert parameters to serde_json::Value.

ExpressionArc

Expression implements Chunk trait, however it can only hold static parameters. Sometimes we want our expressions to be able to hold other Chunk types. This is where ExpressionArc comes in:

ExpressionArc is similar to Expression but can contain Arc<Box> as a parameter. It even has a similar macro:

#![allow(unused)]
fn main() {
let expression = expr_arc!("INSERT INTO user (name, age) VALUES ({}, {})", "John", 30);
}

Now, we can also pass nested expressions to expr_arc!:

#![allow(unused)]
fn main() {
let expression = expr_arc!("INSERT INTO {} (name, age) VALUES ({}, {})", expr!("user"), expr!("John"), 30);
}

Overview of ExpressionArc methods:

from_vec() - combines multiple Chunks into single expression using a delimiter (like concat_ws)
fx() - handy way to create a function call: fx!("UPPER", vec!["Hello"])

Just like Expression, ExpressionArc implements Chunk, so can be nested. This feature is crucial for building queries.

Query type

A Query will consists of many parts, each being a Chunk. When query needs to be rendered, it will render all of its parts recursively:

#![allow(unused)]
fn main() {
// example from Query::render_delete()
Ok(expr_arc!(
    format!("DELETE FROM {}{{}}", table),
        self.where_conditions.render_chunk()
    ).render_chunk())
}

Obviously you can extend this easily or even have your own version of Query. Generally it's not needed, as Query is very flexible and diverse. It can also hold other queries recursively.

Locate bakery_model/examples/3-query-builder.rs for an example of a super-complex query syntax.

Query Overview:

Let me establish a pattern first:

set_table() - sets table for a query
set_source() - similar to set_table() but QuerySource can be a table, another query or an expression
with_table() - similar to set_table() but returns a modified Self

As with Table type earlier - set_table() adn set_source() are implemented as part of dyn-safe SqlQuery trait. On other hand with_table() is only implemented by Query struct.

Here are some other methods:

new() - returns a blank query
set_distinct(), with_distinct() - includes DISTINCT keyword into a query
set_type(), with_type() - sets query type (INSERT, UPDATE, DELETE, SELECT)
add_with(), with_with() - adds a WITH subquery to a query
add_field(), with_field() - adds a field to a query
with_field_arc() - accepts Arc<Box<dyn Chunk>> as a field
with_column_field() - simplified way to add a table columns to a query
without_fields() - removes all fields from a query
with_where_condition(), with_having_condition() - adds a condition to a query.
with_condition(), with_condition_arc() - accepts impl Chunk and Arc<Box<dyn Chunk>> as a condition
with_join() - adds a join to a query
with_group_by(), add_group_by() - adds a group by to a query
with_order_by(), add_order_by() - adds an order by to a query
with_set_field(), set_field_value() - sets a field value for INSERT, UPDATE or REPLACE queries

Query relies on several sub-types: QuerySource, QueryConditions, JoinQuery etc.

How Query uses Expressions ?

Lets look at some examples, which combine Expressions and Query:

#![allow(unused)]
fn main() {
let expr1 = expr!("name = {}", "John");
let expr2 = expr!("age > {}", 30);

let query = Query::new()
    .with_table("users", None)
    .with_column_field("id")
    .with_column_field("name")
    .with_condition(expr1)
    .with_condition(expr2);

writeln!(query.preview());

// renders into: SELECT id, name FROM users WHERE name = 'John' AND age > 30
}

Lets continue to build out and make query part of a bigger query2:

#![allow(unused)]
fn main() {
// query is from example above
let query2 = Query::new()
    .with_table("orders", None)
    .with_condition(expr_arc!("user_id in {}",
        query
            .clone()
            .without_fields()
            .with_column_field("id")
    ));

writeln!(query2.preview());

// renders into: SELECT * FROM orders WHERE user_id in (SELECT id FROM users WHERE name = 'John' AND age > 30)
}

Summary

Vantage's query system leverages Expressions and the Chunk trait to build dynamic, safe, and composable SQL queries. Expressions separate SQL statements from parameters, preventing injection risks, while ExpressionArc extends this flexibility by supporting nested expressions.

Queries are constructed from multiple Chunks, allowing complex operations like subqueries, joins, and conditions to be rendered recursively.

Query methods like with_table, with_field, and with_condition make query building very simple and customizable, while macros like expr! and expr_arc! simplify additional ways to extend queries.

Next, I'll explain how Expressions and Query can be part of Table field expressions.

Expressions in a Table

We now know, that Query can accept expressions pretty much anywhere, but what about Table?

`add_expression()` and `with_expression()`

Table treats expressions lazily. Unless the Entity you are using has a field that matches expression field name, it will not be evaluated.

To define expression use:

add_expression - define a callback returning Expression for a field
with_expression - just like add_expression but returns modifield Self

Lets define a field that returns current time:

#![allow(unused)]
fn main() {
table.with_expression("current_time", || expr!("now()"));
}

In our introduction example, we came across a field: total:

#![allow(unused)]
fn main() {
// lineitem.rs
table.with_expression("total", |t: &Table<Postgres, LineItem>| {
    t.price().render_chunk().mul(t.quantity())
})
}

Chunks and Expressions

Any Chunk can be converted into Expression but executing render_chunk(). If you call render_chunk() on a Query, it will flattern itself into a static Expression type.

a callback that with_expression accepts, is expected to return Expression, but you can use various ways on how to build it.

For instance, we can get a query from related table:

#![allow(unused)]
fn main() {
.with_many("line_items", "order_id", || Box::new(LineItem::table()))
.with_expression("total", |t| {
    let item = t.sub_line_items();
    item.sum(item.total()).render_chunk()
})

// you also need this:
pub trait OrderTable: SqlTable {}
impl OrderTable for Table<Postgres, Order> {
    fn sub_line_items(&self) -> Table<Postgres, LineItem> {
        self.get_subquery_as("line_items").unwrap()
    }
}
}

Relationship between entities is defined by with_many and with_one methods. Traversing this relationship can be done by get_ref() and get_subquery() methods.

We have already explored get_ref() earlier, but how is get_subquery() different?

get_ref() - given a DataSet - return related DataSet: SELECT * FROM line_items WHERE order_id IN (SELECT id FROM ord)
get_subquery() - Will return Table with a condition linking to the current table: SELECT * FROM line_items WHERE order_id = ord.id

get_subquery() only makes sense if you make it part of the Order table query. It's perfect for us to aggregate sum of totals:

#![allow(unused)]
fn main() {
let item = t.sub_line_items();
item.sum(item.total()).render_chunk()
}

Here item is of type Table<Postgres, LineItem> which means we can use Table::sum() and custom method Table<Postgres, LineItem>::total().

Method sum returns Query (impl Chunk) which we can convert into Expression using render_chunk().

BTW - sum accepts Chunk as an argument, and in our case that is just fine, because total() returns a chunk and not a Column.

Another Example

As our last example, lets look at LineItem implementation of a price field. This filed is implemented through expression again, as we don't have a physical column fro it:

#![allow(unused)]
fn main() {
.with_one("client", "client_id", || Box::new(Client::table()))
.with_expression("price", |t| {
    let product = t.get_subquery_as::<Product>("product").unwrap();
    product.field_query(product.price()).render_chunk()
})
}

I haven't defined method like sub_line_items before, so I'm using get_subquery_as directly. There is also get_subquery but it returns Box. I want to use product.price() so instead I'm using get_subquery_as and specifying entity type explicitly.

As far as Vantage is concerned, it sees with_one and with_many relationships equally. I need to think about this though. If subquery could return multiple rows, I'd need to have them limited, aggregated or wrapped into a string somehow (like using ExpressionArc::fx())

In this case I don't have to worry about that. I just need to query a single field (that happens to be a column price).

Expressions in Conditions

Previously I have used the following code:

#![allow(unused)]
fn main() {
let set_of_clients = Client::table();

let condition = set_of_clients.is_paying_client().eq(&true);
let paying_clients = set_of_clients.with_condition(condition);
}

Condition is a struct, that implements Chunk trait. You can probably guess that Table will apply those Conditions into resulting Queries and that's it.

Condition consists of field , operation and value:

field: Can be a Column, Expression, another Condition or a serde_json::Value
operation is just a string
value is a Chunk

The reason why we don't simply throw everything into Expression - our Column might need to use an alias (and sometimes we change alias as we join tables)

Operations

Operations is another Trait that allows type to participate in SQL operations. Both Column and Expression and Value implement Operations, this allows this:

#![allow(unused)]
fn main() {
let condition = column.eq(&"John")

// or

let condition = expr!("now()").lt(&yesterday)
}

Conditions created by the operation can be used in with_condition() method.

Conclusion

In Vantage - expressions and operations create a powerful way to extend of high-level Table features such as conditions and expression fields.

Functionality is composable of underlying components. If Table does not implement a feature you like, you can easily build it using Expressions and Operations.

Joins

Join can happen between two tables with one-to-some(one) relationship.

A good example for us is if we add a new table called inventory that joins product table:

CREATE TABLE inventory (
    product_id SERIAL PRIMARY KEY,
    stock INT DEFAULT NULL
);

In this case, inventory does not particularly useful ol its own, so we can make it part of the Products type:

#![allow(unused)]
fn main() {
impl Product {
    pub fn table_with_inventory() -> Self {
        Product::table()
            .with_alias("p")
            .with_join(
                Table::new("inventory", postgres())
                    .with_alias("i")
                    .with_id_field("product_id")
                    .with_field("stock"),
                "id",
            )
        }
    }
}
}

The beautiful syntax here is going to give you exactly what you expect:

#![allow(unused)]
fn main() {
let prod_inv = model::Product::table_with_inventory();
writeln!(prod_inv.get_select_query().preview());

// renders into: SELECT p.id, p.name, i.stock FROM product p LEFT JOIN inventory i ON (p.id = i.product_id)
}

How is that possible? Well, Vantage's "with_join" method will consume the table object that you pass and will move its fields into the original table. The joined table will be wrapped into a Join object and will instruct query builder to add join into your query.

There are ways to create different kind of joins too, but api for that is not yet stable.

As you will see later, Vantage operates with joined tables just as good as with a single table.

Now is probably a great time to look into Vantages capabilities of operating with your entities.

So far we only used Table to create select queries, but we can in fact hydrate our entities with data from the database.

Fetching Data

So far we were creating tables and were generating queries from them, but wouldn't it be great if we could fetch data directly from the table object?

If you recall - our table is already associated with DataSource, but when we execute get_select_query() the resulting Query is not associated with any DataSource.

There is a type called AssociatedQuery that can be produced by a table object and it has some beautiful methods to actually fetch data.

Lets explore:

#![allow(unused)]
fn main() {
let low_cal_products = model::LowCalProduct::table();

let count = low_cal_products.count().get_one().await?;
}

Table::count() is a method that returns a query for counting rows in a table. Because our LowCalProducts contains a condition, this will return a count of rows that match the condition.

You could use count().preview() still to confirm, that Vantage won't attept to fetch all records and iterate over them, but instead will use a SUM() function to count rows:

SELECT COUNT(*) FROM "product" WHERE "calories" <= 100

Calling get_one will instead allow us to fetch the value directly into a variable. We do not know what type our query would produce, so get_one() returns json::Value and you can use "as_i64" to cast it into a numeric type:

#![allow(unused)]
fn main() {
let count = model::LowCalProduct::table()
    .count()
    .get_one()
    .await?
    .as_i64()?
}

Previously I was using unwrap() and now I am using "?" to unwrap. This is because previously our code was certain that a field woudl exist, since we added it ourselves, but with .get_one() we are not sure about the response. Perhaps query execution would fail, so we need a proper error handling.

Lets explore another method:

#![allow(unused)]
fn main() {
let total_calories = model::LowCalProduct::new()
    .sum(model::Product::calories())
    .get_one()
    .await?
    .as_i64()?
}

Here we are passing a Column object to the sum() method.

Some of those methods will be useful for us later, but for now lets look at the way to fetch data from a table:

#![allow(unused)]
fn main() {
for product in model::LowCalProduct::table().get_all_data().await? {
    println!("{} ({} kcal)", product["name"]?, product["calories"]?);
}
}

Next we will look at how to use power of Deserialize trait of your custom type.

Struct Entities

So far we have defined our Product type without any fields. Lets add some fields to it:

#![allow(unused)]
fn main() {
#[derive(Serialize, Deserialize, Clone)]
pub struct Product {
    id: Option<i64>,
    name: String,
    calories: i64,
}
}

You can now use get() to fetch data from the database much more conveniently:

#![allow(unused)]
fn main() {
for product in model::LowCalProduct::table().get<Product>().await? {
    println!("{} ({} kcal)", product.name, product.calories);
}
}

There is now your type safety. You also can see that we can use any type that implements Serialize and Deserialize traits in get() method.

However, now that we have implemented a struct for our entity, we can start modifying our data. Check this code out:

#![allow(unused)]
fn main() {
model::LowCalProduct::table().map<Product>(|product| async move {
    product.calorues += 1;
})().await?;
}

This allows you to iterate over your data as you did before, but map() will also store data back to the database. You just need to remember to have id field in your struct. Here is what happens:

Vantage determines the type of your struct (Product)
Vantage will fetch row from a query that includes your condition
Vantage will deserialize the row into your struct
Vantage will call your closure
Vantage will serialize your struct back to a row
Vantage will replace row in the database with the new values

The map() method does not know if you have changed struct, so it will always try to execute "REPLACE" query and based on your unique id field, it should rewrite the row.

You can also use insert() method on your table to add a new row to the database:

#![allow(unused)]
fn main() {
model::Product::table().insert(Product {
    id: None,
    name: "New Product".to_string(),
    calories: 100,
})
.await?;
}

Deleting is also possible, but you need to be careful. delete_all() method will remove all rows.

#![allow(unused)]
fn main() {
model::LowCalProduct::table().delete_all().await?;
}

If you want to delete a specific row, you can set a condition:

#![allow(unused)]
fn main() {
model::Product::table().with_id(1).delete_all();
}

Although a more convenient method delete() exists too:

#![allow(unused)]
fn main() {
model::Product::table().delete(1).await?;
}

I should probably mention, that delete() method will not affect rows in a table, which do not match the condition:

#![allow(unused)]
fn main() {
model::LowCalProduct::table().delete(4).await?;
}

Here a row with id=4 will only be deleted if calories is less than 100 for this row.

This will be useful for us later.

Next I want to tell you about associated entities.

Associated Entities

Do you remember how we used Query type only to discover that there is also AssociatedQuery type?

Well, in the last chapter we used our custom entity type Product and now it turns out you can have an associated entity type too.

AssociatedEntity is the way how your entity can remain linked to the Table object. To save you from headache of object lifetimes, it will actually contain a clone of a table as well as ID field. Therefore your type will no longer need an "id" field.

#![allow(unused)]
fn main() {
#[derive(Serialize, Deserialize, Clone)]
pub struct Product {
    name: String,
    calories: i64,
}
}

Now when we deal with associated entities, we load() and save() them:

#![allow(unused)]
fn main() {
let product = model::Product::table().load<Product>(4).await?;

product.calories = 56;
product.save().await?;
}

AssociatedEntity derefs itself so that you can still access the fields. Additionally the following methods are added:

reload() - reload entity from the database
id() - return id of the entity
delete() - delete the entity from the database
save() - saves the entity to the database
save_into() - saves entity into a different table

Here is example:

#![allow(unused)]
fn main() {
if let Some(product) = model::LowCalProduct::table().load_any::<Product>().await? {
    writeln!("Low-cal Product {} has {} calories", product.name, product.calories);
    product.calories += 100; // no londer LowCalProduct

    // product.save().await?; // will Err because of condition
    product.save_into(model::Product::table()).await?;
}
}

It should no longer be a surprise to you that you can do the exactly same stuff with a table which relies on Join:

#![allow(unused)]

fn main() {
struct ProductInventory {
    name: String,
    stock: i64,
}

if let Some(product) = model::Product::with_inventory().load::<ProductInventory>(4).await? {
    writeln!("Product {} has {} items in stock", product.name, product.stock);
    if product.stock > 0 {
        product.stock -= 1;
        product.save().await?;
    } else {
        product.delete().await?;
    }
}
}

Vantage will automatically understand, which fields you have changed (stock) and will only update those fields. Vantage will also delete the row from both "product" and "inventory" tables if stock is 0.

Your code remains intuitive, while Vantage takes care of the rest, but lets make the code even better:

#![allow(unused)]

fn main() {
impl AssociatedEntity<ProductInventory> {
    pub async fn sell(self, qty: i64) -> Result<()> {
        if qty > self.stock {
            return Err(anyhow::anyhow!("Not enough items in stock"));
        }
        self.stock -= qty;
        if self.stock == 0 {
            self.delete().await?;
        } else {
            self.save().await?;
        }
        Ok(())
    }
}
}

Now you can use your method like this:

#![allow(unused)]
fn main() {
let product = model::Product::with_inventory().load::<ProductInventory>(4).await?;
product.sell(10).await?;
}

Rust never looked so good!

But hey, that's not all. Vantage also supports associations between two tables. Keep reading!

References

Vantage allows you to connect tables together. Lets create two new tables in addition to "Product" that we have already:

#![allow(unused)]
fn main() {
struct Order {
    product: Product,
    client: Client,
}

struct Client {
    name: String,
    contact_details: String,
}
}

The definition of the tables would be just like in chapter 5-Entity Model, however we will use "has_one" and "has_many" methods:

#![allow(unused)]
fn main() {
impl Product {
    pub fn static_table() -> &'static Table<Postgres> {
        static TABLE: OnceLock<Table<Postgres>> = OnceLock::new();

        TABLE.get_or_init(|| {
            Table::new("product", postgres())
                .with_id_field("id")
                .with_field("name")
                .has_many("orders", "product_id", || Order::table())
        })
    }
}


impl Order {
    pub fn static_table() -> &'static Table<Postgres> {
        static TABLE: OnceLock<Table<Postgres>> = OnceLock::new();

        TABLE.get_or_init(|| {
            Table::new("order", postgres())
                .with_id_field("id")
                .with_field("name")
                .has_one("client", "client_id", || Client::table())
                .has_one("product", "product_id", || Product::table())
        })
    }
}

impl Client {
    pub fn static_table() -> &'static Table<Postgres> {
        static TABLE: OnceLock<Table<Postgres>> = OnceLock::new();

        TABLE.get_or_init(|| {
            Table::new("client", postgres())
                .with_id_field("id")
                .with_field("name")
                .with_field("contact_details")
                .has_many("orders", "client_id", || Order::table())
        })
    }
}
}

Given one Table, Vantage lets you traverse relationships between tables. Lets say we want to see how many orders does product with id=4 have:

#![allow(unused)]
fn main() {
let product = model::Product::table().with_id(4);

let orders_count = product.get_ref("orders").unwrap().count().get_one().await?;
}

Here is what happens under the hood:

A query is generated for the Product where id=4
product query is used as a condition for a order query
order query is executed for counting rows

SELECT COUNT(*) FROM order WHERE (product_id = in (SELECT id FROM product WHERE (id = 4)));

This query may seem a bit redundant, but lets see how many LowCalProduct orders we have:

#![allow(unused)]
fn main() {
let product = model::LowCalProduct::table();

let low_cal_orders = product.get_ref("orders").unwrap().count().get_one().await?;
}

Resulting query now looks like this:

SELECT COUNT(*) FROM order WHERE (product_id IN (SELECT id FROM product WHERE (calories < 100)));

In Vantage relationship traversal is always converting one "set" into another "set".

In fact - if you want to calculate how many clients have placed orders for Low Calory Products, you can do it like this:

#![allow(unused)]
fn main() {
let low_cal_products = model::LowCalProduct::table();
let clients_on_diet = low_cal_products
    .get_ref("orders")
    .unwrap()
    .get_ref("client")
    .unwrap()
    .count()
    .get_one()
    .await?;
}

But lets not stop here. Suppose you wanted to send all clients who are on a diet some email about a new product:

#![allow(unused)]
fn main() {
let low_cal_products = model::LowCalProduct::table();
let clients_on_diet = low_cal_products
    .get_ref("orders")
    .unwrap()
    .get_ref("client")
    .unwrap();

for client in clients_on_diet.get<Client>().await? {
    client.send_email("New low carb product is out!").await?;
}
}

Imagine all the other things you could do. Yet once again, Vantage has more surprises for you.

We have learned about expressions before, right? Well, expressions can be built from subqueries.

Subquery Expressions

Once we have used "has_one" and "has_many" methods, we can now add some expressions as well.

I'll start by extending our "Client::table()" with "total_orders" field. Place that inside static_table() method:

#![allow(unused)]
fn main() {
Table::new("client", postgres())
    .with_id_field("id")
    .with_field("name")
    .with_field("contact_details")
    .has_many("orders", "client_id", || Order::table())
    .with_expression("total_orders", move |t| {
        t.get_ref_related("orders").unwrap().count()
    })
}

What happens here then?

A query is generated for the Client
If "total_orders" field is requested, a callback is called
The callback is passed a Table object ("client"), which has a reference to the "orders" table
get_ref_related() is similar to get_ref(), but is suitable for subquery expressions
get_ref_related() returns a Table object
count() is called on the Table object, producting a SqlChunk object
The SqlChunk object is is aliased into "total_orders" field inside Query
Query is executed

Lets also modify "Client" struct:

#![allow(unused)]
fn main() {
struct Client {
    name: String,
    contact_details: String,
    total_orders: Option<i64>
}
}

Now that we have know how many orders clients have placed, we can use it as a condition.

#![allow(unused)]
fn main() {
let vip_clients = clients.with_condition(clients.field("total_orders").gt(4));
}

Lets also calculate how many low_cal_orders clients have placed:

#![allow(unused)]
fn main() {
.with_expression("low_cal_orders", move |t| {
    t
        .get_ref_related("orders")
        .with_condition(t.get_filed("calories").unwrap().lt(100))
        .unwrap()
        .count()
})
.with_expression("high_cal_orders", move |t| {
    t
        .get_ref_related("orders")
        .with_condition(t
            .get_field("calories")
            .unwrap()
            .gt(100)
            .or(t.get_filed("calories").unwrap().eq(100))
        )
        .unwrap()
        .count()
})
}

We just casually added 2 more expressions to our Client table. Those normally won't be queried unless needed. However, we can use them to calculate conditions:

#![allow(unused)]
fn main() {
let diet_clients = clients
    .with_condition(clients
        .field("low_cal_orders")
        .gt(clients.field("high_cal_orders"))
    );
}

Finally to clean things up a bit, we can move some of this logic into our model/*.rs files.

Overall, you are now familiar with the basics of Vantage and can start building business model abstractions for your application.

Vantage - The Dry ORM