Crate compact_sql

This crate provides the pg_sql! macro for writing SQL queries (for PostgreSQL) in Rust syntax (not Rust code), i.e. you can use convenient indents, comments, Rust’s string escaping etc. without putting the query into a single string and get some syntax highlighting (functions call, capitalized reserved words, …).

On build the macro creates an SQL string with minimum spaces and replace identifiers in curly braces “SELECT {arg1}::bigint” into sequenced argument number (“$1”, “$2”, etc.). If there are several entries of the same named argument, the same numbered argument is used.

There are two modes: the first is just returns a minificated SQL-string, the second generates additional code to be used with a query arguments struct (fields must match names and types in named arguments in the SQL query) and expected return row numbers defined as a specific trait (to be polished and documented).

You can find examples in the pg_sql! description.

[dependencies]
compact_sql = "0.0.5"

Features:

Feature	Description
pretty-errors	(requires nightly Rust) gives better errors pointing to a concrete span in the SQL query text.

Constraints:

There are several artificial constraints:

1. expanded asterisks (SELECT * FROM...) are forbidden;
2. direct numbered arguments ($1, $2, etc.) are forbidden;
3. DBMS objects (schema/table/view/index/etc. names) can not be quoted (like SELECT ... FROM "CaseSensitiveTable" ...).

Instead you can use leading commas (which are forbidden by the SQL standard) which will not appear in resulting SQL query.

Generate SQL string and struct with SQL params

The macros replaces “named parameters” with numbered parameters for Postgres, and it is reasonable to create struct where attributes names are the same as names parameters. Moreover it is easy to constraint such parameters with Rust’s types. Struct will have a reference to the declared type (see example below).

It is designed to be used with the tokio-postgres crate, the method params of the generated struct returns an iterator for tokio_postgres::query_raw.

To associate each numbered parameter with its corresponding field name, the struct contains a const slice ::PARAM_NAMES. The names are listed in the order they appear in the SQL query.

use compact_sql::pg_sql;

pg_sql! {
    impl OidFromPgClass {
        SELECT
            oid,
            relkind,
        FROM
            pg_catalog.pg_class
        WHERE
            relname = {name: String}
            AND
            relnamespace = {nsp: u32}
    }
}

fn main() {
    let name: String = "pg_attribute".to_string();
    let params = OidFromPgClass {
        nsp:  &11,
        name: &name,
    };
    assert_eq!(OidFromPgClass::PARAM_NAMES, &["name", "nsp"]);
    assert_eq!(
        OidFromPgClass::SQL_TEXT,
        concat!(
            "-- OidFromPgClass\n",
            "SELECT oid,relkind FROM pg_catalog.pg_class WHERE relname=$1 AND relnamespace=$2",
        ),
    );
    assert_eq!(params.params().len(), 2);
}

Generate SQL, struct with SQL params and traits implementation

This library can generate trait implementation bodies for those who want to build a library (or internal functions) that work with generated structs. Not all user structs are designed to be public, which is why the generated trait implementations are not referenced in a public crate. Instead, trait names are used from the local scope. However, you can still use traits from the public compact_sql_traits crate.

You can specify the expected number of rows, and different traits will be used accordingly. Additionally, you must declare the return row type. You can use the PgResultRow derive macro to generate the necessary implementation.

use compact_sql::{pg_sql, PgResultRow};
use compact_sql_traits::*;
use futures_util::{pin_mut, StreamExt, TryStreamExt};
use tokio_postgres::Client;

type PgResult<T> = Result<T, tokio_postgres::Error>;

#[derive(Debug, PgResultRow)]
struct PgClassItem {
    relname:      String,
    relnamespace: u32,
}

#[derive(Debug)]
enum QueriesId {
    GetPgClassItem,
}

pg_sql! {
        // You can add container attributes like derive to the generated struct:
        #[derive(Debug)]
        impl QueriesId::GetPgClassItem for ?PgClassItem {
            SELECT
                relname,
                relnamespace,
            FROM
                pg_catalog.pg_class
            WHERE
                oid = {class_oid}
        }
}

async fn query_opt<Q: QueryMaybeOne>(db: &Client, params: Q) -> PgResult<Option<Q::ResultRow>>
where <Q as QueryMaybeOne>::SqlIdType: std::fmt::Debug
{
    dbg!(Q::SQL_ID);
    let query_result = db
        .query_raw(Q::SQL_TEXT, params.params())
        .await?;
    let future = query_result.map_ok(Q::ResultRow::from_row);
    pin_mut!(future);

    Ok(match future.next().await {
        None => None,
        Some(v) => Some(v??),
    })
}

async fn func(db: &Client) -> PgResult<()> {
    let name: String = "pg_attribute".to_string();
    let params = GetPgClassItem {
        class_oid: &11,
    };
    let res: Option<PgClassItem> = query_opt(db, params).await?;
    dbg!(res);

    Ok(())
}

You can see more examples in the test_trait_impl.rs.

Test query in real DBMS

Your queries can be tested (via “prepare” feature of the PG’s protocol, i.e. does not run any query at all; totally safe) in a real DBMS if you set environment variables for cargo:

• TEST_PG_HOST
• TEST_PG_PORT (optional; default: 5432)
• TEST_PG_USER (optional; default: “postgres”)
• TEST_PG_PASS (optional; default: “”)
• TEST_PG_DATB

It allows to detect wrong SQL for your DB schema. E.g.:

TEST_PG_HOST=127.0.0.1 TEST_PG_DATB=test_db cargo check

Macros

pg_sql

The whole point

Derive Macros

PgResultRow