SeaQuery just made writing raw SQL more enjoyable
You enjoy writing raw SQL queries, SeaQuery just made it better!
We've created a new raw_query! macro with neat features to make writing raw SQL queries more ergononmic.
The biggest headache when crafting complex queries is parameter binding, whether you use ? or $N assigning parameters manually is laborious and error-prone.
SeaQuery's new raw_query! macro is designed to solve this exact problem.
Gistโ
let a = 1;
struct B { b: i32 }
let b = B { b: 2 };
let c = "A";
let d = vec![3, 4, 5];
let query = sea_query::raw_query!(
PostgresQueryBuilder,
r#"SELECT ("size_w" + {a}) * {b.b} FROM "glyph"
WHERE "image" LIKE {c} AND "id" IN ({..d})"#
);
assert_eq!(
query.sql,
r#"SELECT ("size_w" + $1) * $2 FROM "glyph"
WHERE "image" LIKE $3 AND "id" IN ($4, $5, $6)"#
);
assert_eq!(
query.values,
Values(vec![1.into(), 2.into(), "A".into(), 3.into(), 4.into(), 5.into()])
);
There are several features packed into the snippet above!
Let's have a quick overview and we'll dive into the details:
- named parameter:
{a}injected - nested parameter access:
{b.b}inner access - array expansion:
{..d}expanded into three parameters
There are two more features that will be showcased later:
- tuple expansion:
{values.0:2} - repeating group:
{..(values.0),}
Motivationโ
While SeaQuery has long offered a way to build dynamic queries solving basically the same problem, not every one liked SeaQuery's code structure.
I recently came across this SQLx issue, and was enticed to solve it.
I am heavily inspired by Rust's format! macro and other languages' template literals for string interpolation, and figured it could probably be achieved in Rust as well!
Challengesโ
First, the above cannot be implemented with standard macro_rules because of caller hygiene, such that expanded code cannot access the variables in the surrounding scope. But proc macros do not have this limitation.
Second, if we want to expand an array, the number of elements cannot be known until runtime. So a compile-time approach couldn't work.
Third, we have to support all of Rust's primitive and container types as well as third party data types.
Dive inโ
Let's take a look at what the above code expands into!
let a = 1;
struct B {
b: i32,
}
let b = B { b: 2 };
let c = "A";
let d = <[_]>::into_vec(#[rustc_box] ::alloc::boxed::Box::new([3, 4, 5]));
let query = {
use sea_query::raw_sql::*;
let mut builder = RawSqlQueryBuilder::new(sea_query::PostgresQueryBuilder);
builder
.push_fragment("SELECT (\"size_w\" + ")
.push_parameters(1)
.push_fragment(") * ")
.push_parameters(1)
.push_fragment(" FROM \"glyph\"\n WHERE \"image\" LIKE ")
.push_parameters(1)
.push_fragment(" AND \"id\" IN (")
.push_parameters((&d).len())
.push_fragment(")");
let sql = builder.finish();
let mut query = seaql::query(&sql);
query = query.bind(&a);
query = query.bind(&b.b);
query = query.bind(&c);
for v in (&d).iter() {
query = query.bind(v);
}
query
};
I created RawSqlQueryBuilder that is somewhat similar to SeaQuery's query building backend. It serializes the query string in a single pass.
The derive macro first tokenize and parse the raw SQL to identify the splice points, and then call a special method push_parameters to push a variable number of parameters. This can be numbered, i.e. for Postgres.
After finishing the SQL building part, it then proceeds to bind the parameters. This mechanism is designed around SQLx's query API.
If the variable has a spread .. operator, we'd loop over it and bind all items.
Other ideasโ
Originally I wanted to implement auto-expansion, meaning if the parameter is a container type, we'd expand it automagically. But there are quite a few special cases - most importantly Vec<u8> is used as bytes and should be bound as a single parameter. Moreover, in Postgres you can use arrays directly, and so we can't decide whether to expand.
More goodiesโ
Tuple expansionโ
There are two more features that'll make your life much easier.
let var = (1, "2".to_owned(), 3);
let query = sea_query::raw_query!(
PostgresQueryBuilder,
"SELECT {var.0}, {var.1}, {var.2}"
);
let new_query = sea_query::raw_query!(
PostgresQueryBuilder,
r#"SELECT {var.0:2}"#
);
assert_eq!(query, new_query);
We can already support accessing tuple members, why not offer a range operator?
The : token is chosen because it somewhat resembles the Python operator. [0:2] is un-natural because tuple members in Rust can only be accessed by .0. Feel free to offer your thoughts!
It's not possible to automatically expand a tuple like an array because its arity (the number of elements) is not known at the time the macro is expanded. If the tuple consists of elements with a uniform type, it can be made iterable like a vector by implementing the appropriate traits. However, that approach doesn't apply in the case above, where the tuple's structure is not uniform.
You can do inserts with this:
let values = (1, "2", 3);
let query = sea_query::raw_query!(
MysqlQueryBuilder,
"INSERT INTO `glyph` (`aspect`, `image`, `font_size`) VALUES ({values.0:2})"
);
assert_eq!(
query.sql,
"INSERT INTO `glyph` (`aspect`, `image`, `font_size`) VALUES (?, ?, ?)"
);
assert_eq!(query.values, Values(vec![1.into(), "2".into(), 3.into()]));
You may ask, then how do we insert multiple elements? Which brings us to the next feature:
Insert Manyโ
When working with Rust and SQL, a common pain point is figuring out how to insert multiple rows efficiently. Writing raw SQL INSERT statements for each row is verbose, and while Postgres supports UNNEST for bulk inserts, its columnar API feels awkward and error-prone.
SeaQuery solves this by letting you build multi-row insert statements easily. With the raw_query! macro, you can pass in a vector of structs or tuples and generate a single INSERT INTO .. VALUES (..) , (..) , (..) query. This keeps your code clean, while still allowing you to do bulk insertions for efficiency. This solution is database agnostic so you can use it for MySQL and SQLite as well.
With Tuplesโ
let values = vec![(1, "2", 3), (4, "5", 6)];
let query = sea_query::raw_query!(
PostgresQueryBuilder,
r#"INSERT INTO "glyph" ("aspect", "image", "font_size")
VALUES {..(values.0:2),}"#
);
assert_eq!(
query.sql,
r#"INSERT INTO "glyph" ("aspect", "image", "font_size")
VALUES ($1, $2, $3), ($4, $5, $6)"#
);
To achieve this we designed the "repeating group" syntax, inspired by regex. Please let me explain:
It's expanded upon the previous example, in which values.0:2 means tuple expansion. We want to repeat this tuple as a group, surrounded by parenthesis, so we wrap it with (). Then we apply the same spread operator .. to expand the vector of tuples. Finally, the trailing , means they should be connected with ,.
With structsโ
struct Item {
a: i32,
b: String,
c: u16,
}
let values = vec![
Item { a: 1, b: "2".to_owned(), c: 3 },
Item { a: 4, b: "5".to_owned(), c: 6 },
];
let query = sea_query::raw_query!(
PostgresQueryBuilder,
r#"INSERT INTO "glyph" ("aspect", "image", "font_size")
VALUES {..(values.a, values.b, values.c),}"#
);
This is equivalent to the previous example, but uses named parameters.
SQLx Integrationโ
SeaQuery offers tight SQLx integration. So in practice you can do (requires sqlx-utils feature flag):
let mut sql;
let res = sea_query::sqlx::sqlite::query!(
sql = r#"INSERT INTO "character"
("uuid", "font_size", "character")
VALUES {..(values.0:2),}"#
).execute(pool).await?;
Note the salient sql variable. SQLx's Query object can only borrow the SQL as &str, and so someone has to own the String. I couldn't think of a better API, suggestions welcome.
It calls the underlying Query's bind method directly, so no extra copy is involved. This is the lowest possible overhead!
One final example:
let mut character = Character { id: 1, font_size: 0 };
character.font_size = 18;
let res = sea_query::sqlx::sqlite::query!(
sql = r#"UPDATE "character"
SET "font_size" = {character.font_size}
WHERE "id" = {character.id}"#
).execute(pool).await?;
Full example can be found here.
It almost feels like a mini ORM ... although SeaORM ๐ is still highly recommended!
Lightweightnessโ
SeaQuery without default features only has a handful of dependencies. But if you want to keep dependencies to an absolute minimum, you can depends on sea-query-derive directly.
$ cargo tree --no-default-features -e normal,build
sea-query-derive v1.0.0-rc.11 (proc-macro)
โโโ proc-macro2 v1.0.94
โ โโโ unicode-ident v1.0.12
โโโ quote v1.0.40
โ โโโ proc-macro2 v1.0.94 (*)
โโโ syn v2.0.100
โโโ proc-macro2 v1.0.94 (*)
โโโ quote v1.0.40 (*)
โโโ unicode-ident v1.0.12
Then you can use it like:
let sql;
let res: Vec<_> = sea_query_derive::raw_sql!(
sqlx::sqlite::query_as,
sql = "SELECT {var.0:2}"
).fetch_all(pool).await?;
SeaQuery 1.0โ
This is just one of many new features we've added while preparing SeaQuery 1.0. This is currently an rc release, but we plan to finalize it soon. If you have ideas please join the discussion.
๐ฆ Rustacean Sticker Packโ
The Rustacean Sticker Pack is the perfect way to express your passion for Rust. Our stickers are made with a premium water-resistant vinyl with a unique matte finish.
Sticker Pack Contents:
- Logo of SeaQL projects: SeaQL, SeaORM, SeaQuery, Seaography
- Mascots: Ferris the Crab x 3, Terres the Hermit Crab
- The Rustacean wordmark
Support SeaQL and get a Sticker Pack!
