SQL Expressions in Rust

Hello,

I am currently researching a better way to interface between Rust application and SQL (and non-SQL) servers. In the past I have developed SQL expressions as part of Agile Data.

For my new Rust project (Vantage) I am now looking again at the correct approach to render expressions. Current implementation is lacking async support and this makes it impossible to embed cross-database requests.

First - what is an expression?

At some point in life everyone would write this:

let query = format!(
  "SELECT * FROM product WHERE name = \"{}\"",
  user_name
);

A much better way is to rely on a specialised expression language. Here is SQLx for example:

let row: (i64,) = sqlx::query_as("SELECT $1")
        .bind(150_i64)
        .fetch_one(&pool).await?;

A more sophisticated Expression Engine

For my purposes - conventional SQL expressions are too basic. Some of the features they lack:

• Limited number of types
• Composable
• First class citizen
• Not database-agnostic
• Not recursive
• Must take ownership
• No async support

For types - SQL databases operate with JSON or Geo primitives as well as 3rd party crates like Chrono have types for duration, that you may also want to use inside queries. What if there is no built-in support for your custom type.

Composability of Expressions

Composability is a big requirement for me. My query builders are literally operate with dozens of expressions, connecting them together and rendering. When it comes to supporting various types in Expression engine - then expression itself should be suitable:

let my_expr = expr!("select id from ({})", expr!("select * from user"));

Having named parameters also is great: select {total_sum} from orders.

First class citizen

Expression should be First class citizen anywhere else. Query builder such as sea_query implements order in a very rigid way:

let query = Query::select()
    .order_by(Glyph::Image, Order::Desc)

SQL languages can order by expression, so query builder should be able to accept expression, not only a field:

let query = query.order_by(expr!("qty * (price - {})", 20));

Identifiers like table_name

Expression engines mostly deal with parametric arguments, but they should do a better job with identifiers also. Those are not passed as parameters, but also require validation and possible escaping:

let expr = expr!("select * from {}", Identifier("users"));

Operations

Diesel creates an interesting trend with implementing operations:

let downloads = version_downloads
  .filter(version_id.eq(any(versions)))

Their implementation relies on static field, but operations could work beautifully as an extension to expressions:

let expr = expr.and(expr!("len(name) < {}", 3);

Database Agnostics and Async

Modern reality is that application often have to work with multiple data sources - databases from various vendors, APIs, local caches, etc.

Expression engine should be capable of bridging the gap. The biggest challenge is that a expression usually is a lightweight construct which has no binding to a specific data source. If we bridge between data sources - there should be a possibility to cross this gap.

Consider this query:

select * from user where country_id in (
  select id from country where is_eu
)

What if a "country" table moves elsewhere or is cached differently?

Executing such a query would be an async operation, however manipulating it is not.

Owned vs Shared ownership

Most backend services live for a single request. Rust language works for desktop applications too. Imagine you have a table with a filter controls on top. Changing filter parameter should modify part of a query to fetch table data, but there is no reason really to re-build entire query.

Therefore there should be a concept that would allow part of an expression to be externally modified.

My Current approach

Previously Vantage had "Expr" and "Expr_Arc", however it had no async support and therefore couldn't handle cross-database queries. The implementation worked really well inside query building logic and even with fields, but I needed a more refined approach.

https://github.com/romaninsh/vantage/pull/52

This is a new expression engine implementation and for now it only tackles:

• Composable - lazy expressions can contain other queries
• Extensible parameters - even async
• Full and Lazy ownership

I will continue with the refactor.