GitHub - arch-mage/xql: An SQL query builder

5 min read Original article ↗

xql

An SQL query builder for sqlx. Work in progress

Table of Contents

  1. Basic Query Building
    1. Insert statement
    2. Select statement
    3. Update statement
    4. Delete statement
  2. Blanket
    1. Blanket on Expression
    2. Blanket on Table Expression
    3. Blanket on SELECT and VALUES statement
  3. Derive
  4. Execution
  5. Notes on str and String

Basic Query Building

Suppose you have a table like this:

CREATE TABLE book(
  id      INTEGER PRIMARY KEY,
  title   TEXT NOT NULL,
  author  TEXT,
  lang    TEXT,
  year    SMALLINT
);

The CRUD (or ISUD with sql acronym) will be look like this:

INSERT statement.

let book1 = "The Fellowship of the Rings".to_string();
let auth1 = "J. R. R. Tolkien".to_string();
let book2 = "Dune".to_string();
let auth2 = "Frank Herbret".to_string();
let english = "English".to_string();

let values = [
    (1_i32, &book1, &auth1, &english, 1954_i16),
    (2_i32, &book2, &auth2, &english, 1965_i16),
];
let insert = xql::insert("book", ["id", "title", "author", "lang", "year"])
    .values(values)
    .returning(["id"]);

assert_eq!(
    insert.to_string(),
    "INSERT INTO book(id, title, author, lang, year) VALUES \
    (1, 'The Fellowship of the Rings', 'J. R. R. Tolkien', 'English', 1954), \
    (2, 'Dune', 'Frank Herbret', 'English', 1965) \
    RETURNING id",
);

SELECT statement.

let select = xql::select(["id", "title"])
    .from("book")
    .filter(xql::or(xql::eq("id", 1), xql::eq("id", 2)))
    .order_by(xql::desc("year"));

assert_eq!(
    select.to_string(),
    "SELECT id, title FROM book WHERE id = 1 OR id = 2 ORDER BY year DESC"
);

UPDATE statement.

let author = &"Frank Herbert".to_string();
let update = xql::update("book")
    .set("author", author)
    .filter(xql::eq("id", 2))
    .returning(["id"]);

assert_eq!(
    update.to_string(),
    "UPDATE book SET author = 'Frank Herbert' WHERE id = 2 RETURNING id",
);

DELETE statement.

let delete = xql::delete("book")
    .filter(xql::eq("id", 1))
    .returning(["id", "title"]);

assert_eq!(
    delete.to_string(),
    "DELETE FROM book WHERE id = 1 RETURNING id, title",
);

Blanket

There are some blanket implementation for traits that defined in xql::blanket to assist query building.

Blanket on Expression

Most of expr's function defined in xql::ops have method of blanket implementation of xql::blanket::ExprExt.

use xql::blanket::ExprExt;

let cond = "year".greater_than(1900).and("year".less_equal(2000));
assert_eq!(cond.to_string(), "year > 1900 AND year <= 2000");

let query = xql::select(["id"]).from("book").filter(cond);
assert_eq!(query.to_string(), "SELECT id FROM book WHERE year > 1900 AND year <= 2000");

Well, that looks verbose. It can't be helped, because using gt or le will clash with PartialOrd (which can't be disabled even with no_implicit_prelude). This one below will not compile.

use xql::blanket::ExprExt;

let cond = "year".gt(1900).and("year".le(2000));

A work around is to turn the left hand side into Expr first or using a table qualified column reference.

use xql::expr::Expr;
use xql::blanket::ExprExt;

let year = Expr::from("year");
let qualified = ("book", "year");

let cond = year.gt(1900).and(qualified.le(2000));
assert_eq!(cond.to_string(), "year > 1900 AND book.year <= 2000");

Blanket on Table Expression

join family functions have some blanket implementations.

use xql::blanket::ExprExt;
use xql::blanket::TableExprExt;

let table = "book".join("category", ("book", "category_id").eq(("category", "id")));
assert_eq!(table.to_string(), "book JOIN category ON book.category_id = category.id");

Blanket on SELECT and VALUES statement

SELECT and VALUES are the only statements that can use UNION family functions.

use xql::blanket::ResultExt;

let query = xql::select([1, 2]).union(xql::values([(3, 4)]));

assert_eq!(query.to_string(), "SELECT 1, 2 UNION VALUES (3, 4)");

In case you're wondering, ResultExt's name came from xql::stmt::result::Result which is an enum of only Select and Values. Why Result? Well, because naming is hard and it looks good in Stmt enum definition:

enum Stmt {
    Insert,
    Select,
    Update,
    Delete,
    Values,
    Binary,
    Result, // See!? Exactly 6 characters! Perfectly balanced as all things should be!
}

Derive

You can enable derive feature to make query building looks shorter or nicer.

use xql::Schema;

#[derive(Schema)]
struct Book {
    id: i32,
    title: String,
    author: Option<String>,
    lang: Option<String>,
    year: Option<i32>,
}

let shorter = xql::select(Book::columns()).from(Book::table());
assert_eq!(shorter.to_string(), "SELECT book.id, book.title, book.author, book.lang, book.year FROM book");

let nicer = xql::select([Book::id, Book::title, Book::author, Book::lang, Book::year]).from(Book);
assert_eq!(nicer.to_string(), "SELECT book.id, book.title, book.author, book.lang, book.year FROM book");

assert_eq!(shorter, nicer);

The table qualified column will turn to unqualified in INSERT's columns or UPDATE's SET.

use xql::Schema;
use xql::blanket::ExprExt;

#[derive(Schema)]
struct Book {
    id: i32,
    title: String,
    author: Option<String>,
    lang: Option<String>,
    year: Option<i32>,
}

let values = [(&"Dune".to_string(),)];
let insert = xql::insert(Book, [Book::title]).values(values);
assert_eq!(insert.to_string(), "INSERT INTO book(title) VALUES ('Dune')");

let author = "Frank Herbert".to_string();
let update = xql::update(Book).set(Book::author, &author).filter(Book::id.eq(2));
assert_eq!(update.to_string(), "UPDATE book SET author = 'Frank Herbert' WHERE book.id = 2");

Execution

To execute those queries, enable sqlx feature and one of postgres, mysql or sqlite feature.

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

#[cfg(feature = "postgres")]
async fn execute(pool: sqlx::Pool::<sqlx::Postgres>) -> Result<(), sqlx::Error> {

    // sqlx::query(..).fetch_all
    let query = xql::select(["id", "title"]).from("book");
    let rows = xql::exec::fetch_all(query, &pool).await?;

    // sqlx::query_as(..).fetch_all
    let query = xql::select(["id", "title"]).from("book");
    let rows: Vec<Output> = xql::exec::fetch_all_as(query, &pool).await?;

    // sqlx::query_scalar(..).fetch_all
    let query = xql::select(["id"]).from("book");
    let rows: Vec<i32> = xql::exec::fetch_all_scalar(query, &pool).await?;

    // or in blanket form
    use xql::blanket::StmtExt;

    let rows = xql::select(["id", "title"])
        .from("book")
        .fetch_all(&pool).await?;

    let rows: Vec<Output> = xql::select(["id", "title"])
        .from("book")
        .fetch_all_as(&pool)
        .await?;


    let rows: Vec<i32> = xql::select(["id"])
        .from("book")
        .fetch_all_scalar(&pool).await?;

    Ok(())
}

Available variants are: fetch_one, fetch_all, fetch_optional with _as, _scalar or no suffix respectively.

Notes on str and String

You may notice serveral use of &"text".to_string() in the examples above. That's because &str will turn into an identifier while &String will turn into a literal text.