Scooby
======
[](https://crates.io/crates/scooby)
[](https://docs.rs/scooby)
An SQL query builder with a pleasant fluent API closely imitating actual SQL. Meant to comfortably build dynamic statements with a little bit of safety checks sprinkled on top to ensure you don't forget important things like `ON` clauses. Does not do quoting, does not do validation.
Supports only PostgreSQL syntax at the moment.
Requires Rust 1.54.
Consult [documentation](https://docs.rs/scooby) for details and examples.
Principles
----------
- Single responsibility: _builds SQL statements_. Everything else is out of scope.
- API designed to look _as close to actual SQL as possible_, while being a tiny bit more flexible.
- Everything is _raw SQL strings_. If you need to pass user input, please use parametrized statements.
- Obvious _mistakes should be prevented at compile time_, where possible.
- No external dependencies
Supported statements, clauses and features
------------------------------------------
1. [`SELECT`](https://docs.rs/scooby/latest/scooby/postgres/statements/fn.select.html)
- `WITH`
- `WHERE`
- `GROUP BY`
- `HAVING`
- `ALL`, `DISTINCT` and `DISTINCT ON`
- `ORDER BY`
- `ASC`
- `DESC`
- `NULLS FIRST`
- `NULLS LAST`
- `LIMIT` and `OFFSET`
- `FROM` with subselects and joins with a nice API:
- `JOIN`, `INNER JOIN` and `CROSS JOIN`
- `LEFT JOIN` and `LEFT OUTER JOIN`
- `RIGHT JOIN` and `RIGHT OUTER JOIN`
- `FULL JOIN` and `FULL OUTER JOIN`
2. [`INSERT INTO`](https://docs.rs/scooby/latest/scooby/postgres/statements/fn.insert_into.html)
- `WITH`
- `DEFAULT VALUES`
- `VALUES` with compile-time checking that lengths of all values are the same as columns
- `ON CONFLICT`
- `DO NOTHING`
- `DO UPDATE SET`
- `RETURNING`
3. [`DELETE FROM`](https://docs.rs/scooby/latest/scooby/postgres/statements/fn.delete_from.html)
- `WITH`
- `WHERE`
- `RETURNING`
4. [`UPDATE`](https://docs.rs/scooby/latest/scooby/postgres/statements/fn.update.html)
- `WITH`
- `SET` with compile-time checking that you've actually set at least something
- `WHERE`
- `RETURNING`
5. Convenient [`x AS y` aliasing](https://docs.rs/scooby/latest/scooby/postgres/trait.Aliasable.html#tymethod.as_)
6. Convenient `$1`, `$2`... [parameter placeholder builder](https://docs.rs/scooby/latest/scooby/postgres/tools/struct.Parameters.html)
Examples
--------
### `SELECT`
```rust
use scooby::postgres::{select, Aliasable, Joinable, Orderable};
// SELECT
// country.name AS name,
// COUNT(*) AS count
// FROM
// Country AS country
// INNER JOIN City AS city ON city.country_id = country.id
// WHERE
// city.population > $1
// GROUP BY country.name
// ORDER BY count DESC
// LIMIT 10
select(("country.name".as_("name"), "COUNT(*)".as_("count")))
.from(
"Country"
.as_("country")
.inner_join("City".as_("city"))
.on("city.country_id = country.id"),
)
.where_("city.population > $1")
.group_by("country.name")
.order_by("count".desc())
.limit(10)
.to_string();
```
### `INSERT INTO`
```rust
use scooby::postgres::insert_into;
// INSERT INTO Dummy (col1, col2) VALUES (a, b), (c, d), (e, f) RETURNING id
insert_into("Dummy")
.columns(("col1", "col2"))
.values([("a", "b"), ("c", "d")])
.values([("e", "f")])
.returning("id")
.to_string();
// INSERT INTO Dummy DEFAULT VALUES
insert_into("Dummy").default_values().to_string();
// INSERT INTO Dummy DEFAULT VALUES ON CONFLICT DO NOTHING
insert_into("Dummy").default_values().on_conflict().do_nothing().to_string();
```
### `DELETE FROM`
```rust
use scooby::postgres::delete_from;
// DELETE FROM Dummy WHERE x > $1 AND y > $2
delete_from("Dummy").where_(("x > $1", "y > $2")).to_string();
```
### `WITH` (CTE — Common Table Expression)
```rust
use scooby::postgres::{with, select};
// WITH regional_sales AS (
// SELECT region, SUM(amount) AS total_sales
// FROM orders
// GROUP BY region
// ), top_regions AS (
// SELECT region
// FROM regional_sales
// WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
// )
// SELECT region,
// product,
// SUM(quantity) AS product_units,
// SUM(amount) AS product_sales
// FROM orders
// WHERE region IN (SELECT region FROM top_regions)
// GROUP BY region, product;
with("regional_sales")
.as_(
select(("region", "SUM(amount)".as_("total_sales")))
.from("orders")
.group_by("region"),
)
.and("top_regions")
.as_(select("region").from("regional_sales").where_(format!(
"total_sales > ({})",
select("SUM(total_sales)/10").from("regional_sales")
)))
.select((
"region",
"product",
"SUM(quantity)".as_("product_units"),
"SUM(amount)".as_("product_sales"),
))
.from("orders")
.where_(format!(
"region IN ({})",
select("region").from("top_regions")
))
.group_by(("region", "product"))
.to_string();
```
### `Parameters`
```rust
use scooby::postgres::{select, Parameters};
let mut params = Parameters::new();
// SELECT id FROM Thing WHERE x > $1 AND y < $2 AND z IN ($3, $4, $5)
select("id")
.from("Thing")
.where_(format!("x > {}", params.next()))
.where_(format!("y < {}", params.next()))
.where_(format!("z IN ({})", params.next_n(3)))
.to_string();
```
Testing
-------
Normally:
```bash
cargo test
```
To check syntax:
1. Run a local postgresql server on your machine at default port
2. `cargo test --features validate-postgres-syntax`