An SQL query builder for sqlx. Work in progress
Suppose you have a table like this:
sql
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.```rust let book1 = "The Fellowship of the Rings".tostring(); let auth1 = "J. R. R. Tolkien".tostring(); let book2 = "Dune".tostring(); let auth2 = "Frank Herbret".tostring(); let english = "English".to_string();
let values = [ (1i32, &book1, &auth1, &english, 1954i16), (2i32, &book2, &auth2, &english, 1965i16), ]; let insert = xql::insert("book", ["id", "title", "author", "lang", "year"]) .values(values) .returning(["id"]);
asserteq!( insert.tostring(), "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.```rust let select = xql::select(["id", "title"]) .from("book") .filter(xql::or(xql::eq("id", 1), xql::eq("id", 2))) .order_by(xql::desc("year"));
asserteq!( select.tostring(), "SELECT id, title FROM book WHERE id = 1 OR id = 2 ORDER BY year DESC" ); ```
UPDATE statement.```rust let author = &"Frank Herbert".to_string(); let update = xql::update("book") .set("author", author) .filter(xql::eq("id", 2)) .returning(["id"]);
asserteq!( update.tostring(), "UPDATE book SET author = 'Frank Herbert' WHERE id = 2 RETURNING id", ); ```
DELETE statement.```rust let delete = xql::delete("book") .filter(xql::eq("id", 1)) .returning(["id", "title"]);
asserteq!( delete.tostring(), "DELETE FROM book WHERE id = 1 RETURNING id, title", ); ```
There are some blanket implementation for traits that defined
in xql::blanket to assist query building.
Most of expr's function defined in xql::ops have method of blanket
implementation of xql::blanket::ExprExt.
```rust use xql::blanket::ExprExt;
let cond = "year".greaterthan(1900).and("year".lessequal(2000)); asserteq!(cond.tostring(), "year > 1900 AND year <= 2000");
let query = xql::select(["id"]).from("book").filter(cond); asserteq!(query.tostring(), "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.
```rust,compile_fail 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.
```rust 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)); asserteq!(cond.tostring(), "year > 1900 AND book.year <= 2000"); ```
join family functions have some blanket implementations.
```rust use xql::blanket::ExprExt; use xql::blanket::TableExprExt;
let table = "book".join("category", ("book", "categoryid").eq(("category", "id"))); asserteq!(table.tostring(), "book JOIN category ON book.categoryid = category.id"); ```
SELECT and VALUES statementSELECT and VALUES are the only statements that can use UNION family functions.
```rust use xql::blanket::ResultExt;
let query = xql::select([1, 2]).union(xql::values([(3, 4)]));
asserteq!(query.tostring(), "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:
rust
enum Stmt {
Insert,
Select,
Update,
Delete,
Values,
Binary,
Result, // See!? Exactly 6 characters! Perfectly balanced as all things should be!
}
You can enable derive feature to make query building looks shorter or nicer.
```rust use xql::Schema;
struct Book {
id: i32,
title: String,
author: Option
let shorter = xql::select(Book::columns()).from(Book::table()); asserteq!(shorter.tostring(), "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); asserteq!(nicer.tostring(), "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.
```rust use xql::Schema; use xql::blanket::ExprExt;
struct Book {
id: i32,
title: String,
author: Option
let values = [(&"Dune".tostring(),)]; let insert = xql::insert(Book, [Book::title]).values(values); asserteq!(insert.to_string(), "INSERT INTO book(title) VALUES ('Dune')");
let author = "Frank Herbert".tostring(); let update = xql::update(Book).set(Book::author, &author).filter(Book::id.eq(2)); asserteq!(update.to_string(), "UPDATE book SET author = 'Frank Herbert' WHERE book.id = 2"); ```
To execute those queries, enable sqlx feature and one of postgres, mysql
or sqlite feature.
```rust
struct Output { id: i32, title: String, }
async fn execute(pool: sqlx::Pool::
// 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.
str and StringYou 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.