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
   • 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

use scooby::postgres::{select, Aliasable, Joinable, Orderable};

// SELECT 1 + 1
select("1 + 1").to_string();

// SELECT id, name, age FROM Person
select(("id", "name", "age")).from("Person").to_string();

// SELECT * FROM (SELECT id FROM City) AS x
select("*")
    .from(select("id").from("City").as_("x"))
    .to_string();

// SELECT col1, col2 FROM SomeTable LIMIT 10 OFFSET 5
select("col1")
    .and_select("col2")
    .from("SomeTable")
    .limit(10)
    .offset(5)
    .to_string();

// 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")
            .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)
    .to_string();

INSERT INTO

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();

DELETE FROM

use scooby::postgres::delete_from;

// DELETE FROM Dummy WHERE x > 0 AND y > 30
delete_from("Dummy").where_(("x > 0", "y > 30")).to_string();

WITH (CTE — Common Table Expression)

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

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:

cargo test

To check syntax:

1. Run a local postgresql server on your machine at default port
2. cargo test --features validate-postgres-syntax