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

   • 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
   • ON CONFLICT
     • DO NOTHING
     • DO UPDATE SET
   • 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
//     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

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

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)

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

cargo test

To check syntax:

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