Scooby

An SQL query builder with a pleasant fluent API closely imitating actual SQL. Meant to comfortably build dynamic queries 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.

Supported statements, clauses and features

1. SELECT

   • 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

   • WITH
   • DEFAULT VALUES
   • VALUES with compile-time checking that lengths of all values are the same as columns
   • RETURNING

3. DELETE FROM

   • WITH
   • WHERE
   • RETURNING

4. UPDATE

   • WITH
   • SET with compile-time checking that you've actually set at least something
   • WHERE
   • RETURNING

5. Convenient x AS y aliasing

6. Convenient $1, $2... parameter placeholder builder

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 > 1000000 // GROUP BY country.id // ORDER BY count DESC // LIMIT 10 select(("country.name".as_("name"), "COUNT()".as("count"))) .from( "Country" .as("country") .innerjoin("City".as("city")) .on("city.countryid = country.id"), ) .where("city.population > 1000000") .groupby("country.id") .orderby("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 insertinto("Dummy") .columns(("col1", "col2")) .values([("a", "b"), ("c", "d")]) .values([("e", "f")]) .returning("id") .tostring();

// INSERT INTO Dummy DEFAULT VALUES insertinto("Dummy").defaultvalues().to_string(); ```

DELETE FROM

```rust use scooby::postgres::delete_from;

// DELETE FROM Dummy WHERE x > 0 AND y > 30 deletefrom("Dummy").where(("x > 0", "y > 30")).to_string(); ```

WITH (CTE — Common Table Expression)

```rust use scooby::postgres::{with, select};

// WITH regionalsales AS ( // SELECT region, SUM(amount) AS totalsales // FROM orders // GROUP BY region // ), topregions AS ( // SELECT region // FROM regionalsales // WHERE totalsales > (SELECT SUM(totalsales)/10 FROM regionalsales) // ) // SELECT region, // product, // SUM(quantity) AS productunits, // SUM(amount) AS productsales // FROM orders // WHERE region IN (SELECT region FROM topregions) // GROUP BY region, product; with("regionalsales") .as( select(("region", "SUM(amount)".as("totalsales"))) .from("orders") .groupby("region"), ) .and("topregions") .as(select("region").from("regionalsales").where(format!( "totalsales > ({})", select("SUM(totalsales)/10").from("regionalsales") ))) .select(( "region", "product", "SUM(quantity)".as("productunits"), "SUM(amount)".as("productsales"), )) .from("orders") .where(format!( "region IN ({})", select("region").from("topregions") )) .groupby(("region", "product")) .tostring(); ```

Parameters

```rust use scooby::postgres::{select, Parameters};

let mut params = Parameters::new();

// SELECT id FROM Thing WHERE x > $1 AND y < $2 select("id") .from("Thing") .where(format!("x > {}", params.next())) .where(format!("y < {}", params.next())) .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