reinhardt-query

A type-safe SQL query builder for the Reinhardt framework.

Overview

reinhardt-query provides a fluent API for constructing SQL queries targeting PostgreSQL, MySQL, SQLite, and CockroachDB. It generates parameterized queries with proper identifier escaping and value placeholders for each backend.

Features

DML (Data Manipulation Language)

• Type-safe query construction - SELECT, INSERT, UPDATE, DELETE
• DCL (Data Control Language) support - GRANT and REVOKE statements
• Expression system - Arithmetic, comparison, logical, and pattern matching operators
• Advanced SQL - JOINs, GROUP BY, HAVING, DISTINCT, UNION, CTEs, Window functions
• Parameterized queries - $1 for PostgreSQL/CockroachDB, ? for MySQL/SQLite
• CASE WHEN expressions - Conditional expressions in queries
• Subqueries - EXISTS, IN, ALL, ANY, SOME operators

DDL (Data Definition Language)

• Table operations - CREATE TABLE, ALTER TABLE, DROP TABLE
• Index operations - CREATE INDEX, ALTER INDEX, DROP INDEX, REINDEX
• View operations - CREATE VIEW, DROP VIEW
• Schema management - CREATE/ALTER/DROP SCHEMA (PostgreSQL, CockroachDB)
• Sequence operations - CREATE/ALTER/DROP SEQUENCE (PostgreSQL, CockroachDB)
• Database operations - CREATE/ALTER/DROP DATABASE (all backends)
• Functions & Procedures - CREATE/ALTER/DROP FUNCTION/PROCEDURE (PostgreSQL, MySQL, CockroachDB)
• Custom types - CREATE/ALTER/DROP TYPE (PostgreSQL, CockroachDB)
• Materialized views - CREATE/ALTER/DROP/REFRESH MATERIALIZED VIEW (PostgreSQL, CockroachDB)
• Events - CREATE/ALTER/DROP EVENT (MySQL)
• Comments - COMMENT ON for all database objects (PostgreSQL, CockroachDB)
• Maintenance - VACUUM, ANALYZE, OPTIMIZE/REPAIR/CHECK TABLE

Multi-Backend Support

• PostgreSQL - Full DDL and DML support with advanced features
• MySQL - DML, Functions, Procedures, Events, and table maintenance
• SQLite - DML and basic DDL operations
• CockroachDB - Full PostgreSQL compatibility with distributed database features

Installation

Add to your Cargo.toml:

[dependencies]
reinhardt-query = { version = "0.1.0-alpha.1" }

Quick Start

use reinhardt_query::prelude::*;

// Build a SELECT query
let mut stmt = Query::select();
stmt.column("name")
    .column("email")
    .from("users")
    .and_where(Expr::col("active").eq(true))
    .order_by("name", Order::Asc)
    .limit(10);

// Generate SQL for PostgreSQL
let builder = PostgresQueryBuilder::new();
let (sql, values) = builder.build_select(&stmt);
// sql = r#"SELECT "name", "email" FROM "users" WHERE "active" = $1 ORDER BY "name" ASC LIMIT $2"#

Usage Examples

INSERT

use reinhardt_query::prelude::*;

let mut stmt = Query::insert();
stmt.into_table("users")
    .columns(["name", "email", "active"])
    .values(["Alice", "alice@example.com", "true"]);

let builder = PostgresQueryBuilder::new();
let (sql, values) = builder.build_insert(&stmt);

UPDATE

use reinhardt_query::prelude::*;

let mut stmt = Query::update();
stmt.table("users")
    .set("name", "Bob")
    .set("active", false)
    .and_where(Expr::col("id").eq(1i32));

let builder = PostgresQueryBuilder::new();
let (sql, values) = builder.build_update(&stmt);

DELETE

use reinhardt_query::prelude::*;

let mut stmt = Query::delete();
stmt.from_table("users")
    .and_where(Expr::col("active").eq(false));

let builder = PostgresQueryBuilder::new();
let (sql, values) = builder.build_delete(&stmt);

CREATE TABLE

use reinhardt_query::prelude::*;
use reinhardt_query::types::{ColumnDef, ColumnType};

let mut stmt = Query::create_table();
stmt.table("users")
    .if_not_exists()
    .column(ColumnDef {
        name: "id".into_iden(),
        column_type: ColumnType::Integer,
        not_null: true,
        primary_key: true,
        auto_increment: true,
        ..Default::default()
    })
    .column(ColumnDef {
        name: "email".into_iden(),
        column_type: ColumnType::String(Some(255)),
        not_null: true,
        unique: true,
        ..Default::default()
    });

let builder = PostgresQueryBuilder::new();
let (sql, values) = builder.build_create_table(&stmt);
// sql = r#"CREATE TABLE IF NOT EXISTS "users" ("id" INTEGER NOT NULL PRIMARY KEY, "email" VARCHAR(255) NOT NULL UNIQUE)"#

ALTER TABLE

use reinhardt_query::prelude::*;
use reinhardt_query::query::AlterTableOperation;
use reinhardt_query::types::{ColumnDef, ColumnType};

let mut stmt = Query::alter_table();
stmt.table("users")
    .add_column(ColumnDef {
        name: "created_at".into_iden(),
        column_type: ColumnType::Timestamp,
        not_null: true,
        ..Default::default()
    })
    .rename_column("email", "email_address");

let builder = PostgresQueryBuilder::new();
let (sql, values) = builder.build_alter_table(&stmt);
// sql = r#"ALTER TABLE "users" ADD COLUMN "created_at" TIMESTAMP NOT NULL, RENAME COLUMN "email" TO "email_address""#

DROP TABLE

use reinhardt_query::prelude::*;

let mut stmt = Query::drop_table();
stmt.table("users")
    .if_exists()
    .cascade();  // PostgreSQL only

let builder = PostgresQueryBuilder::new();
let (sql, values) = builder.build_drop_table(&stmt);
// sql = "DROP TABLE IF EXISTS \"users\" CASCADE"

CREATE INDEX

use reinhardt_query::prelude::*;

let mut stmt = Query::create_index();
stmt.name("idx_email")
    .table("users")
    .col("email")
    .unique()
    .if_not_exists();

let builder = PostgresQueryBuilder::new();
let (sql, values) = builder.build_create_index(&stmt);
// sql = r#"CREATE UNIQUE INDEX IF NOT EXISTS "idx_email" ON "users" ("email")"#

DROP INDEX

use reinhardt_query::prelude::*;

let mut stmt = Query::drop_index();
stmt.name("idx_email")
    .if_exists();

let builder = PostgresQueryBuilder::new();
let (sql, values) = builder.build_drop_index(&stmt);
// sql = "DROP INDEX IF EXISTS \"idx_email\""

JOINs

use reinhardt_query::prelude::*;
use reinhardt_query::types::{JoinType, JoinExpr, JoinOn};

let mut stmt = Query::select();
stmt.column("users.name")
    .column("orders.total")
    .from("users")
    .join(JoinExpr {
        join_type: JoinType::InnerJoin,
        table: "orders".into_table_ref(),
        on: Some(JoinOn::Condition(
            Expr::col("users.id").eq(Expr::col("orders.user_id")).into_condition()
        )),
    });

Window Functions

use reinhardt_query::prelude::*;
use reinhardt_query::types::WindowStatement;

let mut stmt = Query::select();
stmt.column("name")
    .expr(Expr::col("salary").over(WindowStatement {
        partition_by: vec![Expr::col("department").into_simple_expr()],
        order_by: vec![],
        frame: None,
    }))
    .from("employees");

Common Table Expressions (CTEs)

use reinhardt_query::prelude::*;
use reinhardt_query::query::CommonTableExpr;

let mut cte_query = Query::select();
cte_query.column("id").column("name").from("categories")
    .and_where(Expr::col("parent_id").is_null());

let mut stmt = Query::select();
stmt.column("name")
    .from("top_categories")
    .with_cte(CommonTableExpr {
        name: "top_categories".into_iden(),
        columns: vec![],
        query: cte_query,
    });

CREATE SCHEMA (PostgreSQL, CockroachDB)

use reinhardt_query::prelude::*;

let mut stmt = Query::create_schema();
stmt.name("app_schema")
    .if_not_exists()
    .authorization("owner_user");

let builder = PostgresQueryBuilder::new();
let (sql, _) = builder.build_create_schema(&stmt);
// sql = r#"CREATE SCHEMA IF NOT EXISTS "app_schema" AUTHORIZATION "owner_user""#

CREATE SEQUENCE (PostgreSQL, CockroachDB)

use reinhardt_query::prelude::*;

let mut stmt = Query::create_sequence();
stmt.name("user_id_seq")
    .if_not_exists()
    .start_with(1000)
    .increment_by(1)
    .min_value(1)
    .max_value(999999);

let builder = PostgresQueryBuilder::new();
let (sql, _) = builder.build_create_sequence(&stmt);

CREATE FUNCTION (PostgreSQL, MySQL, CockroachDB)

use reinhardt_query::prelude::*;

let mut stmt = Query::create_function();
stmt.name("add_numbers")
    .parameter("a", "INTEGER")
    .parameter("b", "INTEGER")
    .returns("INTEGER")
    .language_sql()
    .immutable()
    .body("SELECT $1 + $2");

let builder = PostgresQueryBuilder::new();
let (sql, _) = builder.build_create_function(&stmt);

CREATE MATERIALIZED VIEW (PostgreSQL, CockroachDB)

use reinhardt_query::prelude::*;

let select = Query::select()
    .column(Expr::col("id"))
    .column(Expr::col("name"))
    .column(Expr::col("email"))
    .from("users")
    .and_where(Expr::col("active").eq(true));

let mut stmt = Query::create_materialized_view();
stmt.name("active_users")
    .as_select(select)
    .if_not_exists();

let builder = PostgresQueryBuilder::new();
let (sql, _) = builder.build_create_materialized_view(&stmt);

COMMENT ON (PostgreSQL, CockroachDB)

use reinhardt_query::prelude::*;
use reinhardt_query::types::CommentTarget;

let mut stmt = Query::comment();
stmt.target(CommentTarget::Table("users".into_iden()))
    .comment("Stores user account information");

let builder = PostgresQueryBuilder::new();
let (sql, _) = builder.build_comment(&stmt);
// sql = r#"COMMENT ON TABLE "users" IS 'Stores user account information'"#

GRANT (DCL)

use reinhardt_query::prelude::*;

let stmt = Query::grant()
    .privilege(Privilege::Select)
    .privilege(Privilege::Insert)
    .on_table("users")
    .to("app_user")
    .with_grant_option(true);

let builder = PostgresQueryBuilder::new();
let (sql, values) = builder.build_grant(&stmt);
// sql = r#"GRANT SELECT, INSERT ON TABLE "users" TO "app_user" WITH GRANT OPTION"#

REVOKE (DCL)

use reinhardt_query::prelude::*;

let stmt = Query::revoke()
    .privilege(Privilege::Insert)
    .from_table("users")
    .from("app_user")
    .cascade(true);

let builder = PostgresQueryBuilder::new();
let (sql, values) = builder.build_revoke(&stmt);
// sql = r#"REVOKE INSERT ON TABLE "users" FROM "app_user" CASCADE"#

GRANT Role Membership (DCL)

use reinhardt_query::dcl::{GrantRoleStatement, RoleSpecification};

let stmt = GrantRoleStatement::new()
    .role("developer")
    .to(RoleSpecification::new("alice"))
    .with_admin_option();

let builder = PostgresQueryBuilder::new();
let (sql, values) = builder.build_grant_role(&stmt);
// sql = r#"GRANT "developer" TO alice WITH ADMIN OPTION"#

REVOKE Role Membership (DCL)

use reinhardt_query::dcl::{RevokeRoleStatement, RoleSpecification};

let stmt = RevokeRoleStatement::new()
    .role("developer")
    .from(RoleSpecification::new("alice"))
    .cascade();

let builder = PostgresQueryBuilder::new();
let (sql, values) = builder.build_revoke_role(&stmt);
// sql = r#"REVOKE "developer" FROM alice CASCADE"#

Extended Object Types (PostgreSQL)

PostgreSQL supports additional object types beyond tables and databases:

use reinhardt_query::prelude::*;

// Grant EXECUTE on function
let stmt = Query::grant()
    .privilege(Privilege::Execute)
    .on_function("calculate_total")
    .to("app_user");

let builder = PostgresQueryBuilder::new();
let (sql, values) = builder.build_grant(&stmt);
// sql = r#"GRANT EXECUTE ON FUNCTION "calculate_total" TO "app_user""#

// Grant USAGE on type
let stmt = Query::grant()
    .privilege(Privilege::Usage)
    .on_type("custom_type")
    .to("app_user");

let (sql, values) = builder.build_grant(&stmt);
// sql = r#"GRANT USAGE ON TYPE "custom_type" TO "app_user""#

// Grant SET on parameter
let stmt = Query::grant()
    .privilege(Privilege::Set)
    .on_parameter("work_mem")
    .to("app_user");

let (sql, values) = builder.build_grant(&stmt);
// sql = r#"GRANT SET ON PARAMETER "work_mem" TO "app_user""#

Supported object types: Function, Procedure, Routine, Type, Domain, ForeignDataWrapper, ForeignServer, Language, LargeObject, Tablespace, Parameter

Backend Differences

DML Features

Feature	PostgreSQL	MySQL	SQLite	CockroachDB
Identifier quoting	"name"	`name`	"name"	"name"
Placeholders	$1, $2, ...	?, ?, ...	?, ?, ...	$1, $2, ...
NULLS FIRST/LAST	✅ Native	❌	✅ Native	✅ Native
DISTINCT ON	✅	❌	❌	✅
Window functions	✅ Full	✅ Full	✅ Full	✅ Full
CTEs (WITH)	✅	✅	✅	✅

DDL Features

Feature	PostgreSQL	MySQL	SQLite	CockroachDB
CREATE/ALTER/DROP SCHEMA	✅	❌	❌	✅
CREATE/ALTER/DROP SEQUENCE	✅	❌	❌	✅
CREATE/ALTER/DROP DATABASE	✅	✅	✅	✅
CREATE/ALTER/DROP FUNCTION	✅	✅	❌	✅
CREATE/ALTER/DROP PROCEDURE	✅	✅	❌	✅
CREATE/ALTER/DROP TYPE	✅	❌	❌	✅
CREATE/ALTER/DROP EVENT	❌	✅	❌	❌
MATERIALIZED VIEW	✅	❌	❌	✅
COMMENT ON	✅	❌	❌	✅
VACUUM/ANALYZE	✅	❌	✅	✅
OPTIMIZE/REPAIR/CHECK	❌	✅	❌	❌

DCL Features

Feature	PostgreSQL	MySQL	SQLite	CockroachDB
GRANT/REVOKE	✅	✅	❌	✅

SQL Generation Notes

Identifier Quoting

Each database backend uses different quoting styles for SQL identifiers (table names, column names, index names, etc.):

Backend	Quote Style	Example
PostgreSQL	Double quotes	"table_name"
MySQL	Backticks	`table_name`
SQLite	Double quotes	"table_name"
CockroachDB	Double quotes	"table_name"

Quoting Behavior

All backends automatically escape special characters within identifiers:

• PostgreSQL/SQLite/CockroachDB: Double quotes within identifiers are escaped by doubling (" becomes "")
• MySQL: Backticks within identifiers are escaped by doubling (` becomes ``)

Testing Generated SQL

When writing tests for generated SQL, ensure you account for identifier quoting:

use reinhardt_query::backend::{PostgresQueryBuilder, QueryBuilder};
use reinhardt_query::prelude::*;

let builder = PostgresQueryBuilder::new();
let stmt = Query::select().column("name").from("users");
let (sql, _) = builder.build_select(&stmt);

// PostgreSQL uses double quotes
assert_eq!(sql, r#"SELECT "name" FROM "users""#);

// MySQL would generate: SELECT `name` FROM `users`
// SQLite would generate: SELECT "name" FROM "users"

Escape Methods

Each query builder provides an escape_identifier method for manual escaping:

use reinhardt_query::backend::{PostgresQueryBuilder, QueryBuilder};

let builder = PostgresQueryBuilder::new();
let escaped = builder.escape_identifier("user");
assert_eq!(escaped, "\"user\"");

Parameter Placeholders

Each backend uses different placeholder styles for parameterized queries:

Backend	Placeholder Style	Example
PostgreSQL	Numbered ($1, $2, ...)	WHERE id = $1
MySQL	Question mark (?)	WHERE id = ?
SQLite	Question mark (?)	WHERE id = ?
CockroachDB	Numbered ($1, $2, ...)	WHERE id = $1

Case Sensitivity

When identifiers are quoted:

• Case is preserved exactly as specified
• PostgreSQL/MySQL/SQLite are case-sensitive for quoted identifiers
• Unquoted identifiers may be case-folded (uppercased or lowercased) depending on the backend

Reserved Keywords

All backends automatically quote identifiers, which allows using reserved keywords as identifiers:

use reinhardt_query::prelude::*;

// "user" is a reserved keyword in PostgreSQL, but quoting allows its use
let stmt = Query::select().column("name").from("user");
// Generates: SELECT "name" FROM "user"

Feature Flags

Flag	Description
thread-safe	Use Arc instead of Rc for DynIden
with-chrono	Enable chrono date/time types in Value
with-uuid	Enable UUID type in Value
with-json	Enable JSON type in Value
with-rust_decimal	Enable Decimal type in Value
with-bigdecimal	Enable BigDecimal type in Value
full	Enable all optional features

Security Considerations

SQL Injection Prevention

This library uses parameterized queries by default, which protects against SQL injection attacks. However, some APIs allow raw SQL strings that can be vulnerable if misused.

Unsafe APIs

The following APIs accept raw SQL strings and should be used with caution:

API	Severity	Alternative
Expr::cust() / SimpleExpr::Custom	Medium	Use Expr::cust_with_values()
CreateFunctionStatement::body()	High	Only use with trusted input
CreateProcedureStatement::body()	High	Only use with trusted input
ColumnType::Custom	Low	Only use with trusted input
FunctionLanguage::Custom	Low	Only use with trusted input

Best Practices

1. Never pass user input directly to custom SQL APIs
2. Always use parameterized queries (cust_with_values()) for dynamic values
3. Validate and sanitize any input before using it in custom SQL
4. Use the type-safe query builder API when possible

License

See the repository root for license information.