squeal

A type-safe SQL query builder for Rust targeting PostgreSQL.

squeal provides a simple, type-safe way to construct SQL queries using Rust structures. It offers both direct struct construction and fluent builder APIs with escape hatches built in for complex use cases.

Philosophy

• Keep it simple & stupid
• No attributes, macros, or other "magic"
• Escape hatches built in for custom SQL
• Any valid construction produces syntactically valid SQL

Features

• Type-safe query construction - Catch errors at compile time
• Fluent builder API - Chain methods for readable query construction
• Direct struct construction - Full control when needed
• PostgreSQL targeting - Designed for PostgreSQL dialect
• Zero ORM runtime overhead - Queries are built, not interpreted.

Supported Operations

• SELECT queries with WHERE, GROUP BY, HAVING, ORDER BY, LIMIT, OFFSET
• JOIN operations (INNER, LEFT, RIGHT, FULL, CROSS) with table or subquery sources
• WITH / Common Table Expressions (CTEs) for complex queries
• INSERT statements with single or multiple value sets, or from SELECT queries
• INSERT ... ON CONFLICT (UPSERT) with DO NOTHING or DO UPDATE
• UPDATE statements with SET and WHERE clauses
• DELETE statements with WHERE conditions
• RETURNING clauses for INSERT, UPDATE, and DELETE
• CREATE TABLE DDL statements
• DROP TABLE DDL statements

Installation

Add to your Cargo.toml:

[dependencies]
squeal = "0.1.0"

MSRV: Rust 1.90.0

Quick Start

use squeal::*;

// SELECT query with fluent builder
let query = Q()
    .select(vec!["id", "name", "email"])
    .from("users")
    .where_(eq("active", "true"))
    .order_by(vec![OrderedColumn::Desc("created_at")])
    .limit(10)
    .build();

assert_eq!(
    query.sql(),
    "SELECT id, name, email FROM users WHERE active = true ORDER BY created_at DESC LIMIT 10"
);

// INSERT with fluent builder
let insert = I("users")
    .columns(vec!["name", "email"])
    .values(vec!["'Alice'", "'alice@example.com'"])
    .build();

assert_eq!(
    insert.sql(),
    "INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com')"
);

// UPDATE with fluent builder
let update = U("users")
    .columns(vec!["status"])
    .values(vec!["'inactive'"])
    .where_(lt("last_login", "'2024-01-01'"))
    .build();

assert_eq!(
    update.sql(),
    "UPDATE users SET status = 'inactive' WHERE last_login < '2024-01-01'"
);

// DELETE with fluent builder
let delete = D("logs")
    .where_(lt("created_at", "NOW() - INTERVAL '30 days'"))
    .build();

Direct Struct Construction

For maximum control, you can construct query structs directly:

use squeal::*;

let query = Query {
    with_clause: None,
    select: Some(Select::new(Columns::Star, None)),
    from: Some(FromSource::Table("products")),
    joins: vec![],
    where_clause: None,
    group_by: None,
    having: None,
    order_by: Some(vec![OrderedColumn::Asc("price")]),
    limit: Some(100),
    offset: Some(0),
    for_update: false,
};

assert_eq!(query.sql(), "SELECT * FROM products ORDER BY price ASC LIMIT 100 OFFSET 0");

Escape Hatches

Use custom operators and raw SQL fragments when needed:

use squeal::*;

// Use Op::O for custom PostgreSQL operators
let custom_op = Term::Condition(
    Box::new(Term::Atom("data")),
    Op::O("@>"),  // PostgreSQL JSONB contains operator
    Box::new(Term::Atom("'{\"type\": \"click\"}'"))
);

let query = Q()
    .select(vec!["*"])
    .from("events")
    .where_(custom_op)
    .build();

Advanced Features

JOINs

Combine data from multiple tables with type-safe JOIN operations:

use squeal::*;

// Find all users with their order count
let query = Q()
    .select(vec!["users.name", "users.email", "COUNT(orders.id) as order_count"])
    .from("users")
    .inner_join("orders", eq("users.id", "orders.user_id"))
    .group_by(vec!["users.id", "users.name", "users.email"])
    .order_by(vec![OrderedColumn::Desc("order_count")])
    .build();

// LEFT JOIN to include users with no orders
let query_with_nulls = Q()
    .select(vec!["users.name", "COALESCE(orders.total, 0) as total"])
    .from("users")
    .left_join("orders", eq("users.id", "orders.user_id"))
    .build();

Common Table Expressions (WITH clause)

Build complex queries with CTEs for better readability:

use squeal::*;

// Calculate monthly revenue and compare to average
let monthly_revenue = Q()
    .select(vec![
        "DATE_TRUNC('month', created_at) as month",
        "SUM(total) as revenue"
    ])
    .from("orders")
    .group_by(vec!["month"])
    .build();

let query = Q()
    .with("monthly_revenue", monthly_revenue)
    .select(vec![
        "month",
        "revenue",
        "(revenue - AVG(revenue) OVER ()) as diff_from_avg"
    ])
    .from("monthly_revenue")
    .order_by(vec![OrderedColumn::Desc("month")])
    .build();

// Result: WITH monthly_revenue AS (SELECT ...) SELECT month, revenue, ...

UPSERT (INSERT ... ON CONFLICT)

Handle unique constraint violations gracefully:

use squeal::*;

// Insert user, do nothing if email already exists
let insert = I("users")
    .columns(vec!["email", "name", "created_at"])
    .values(vec!["'alice@example.com'", "'Alice'", "NOW()"])
    .on_conflict_do_nothing(vec!["email"])
    .build();

// Result: INSERT INTO users (email, name, created_at) VALUES (...)
//         ON CONFLICT (email) DO NOTHING

// Insert or update: update the name if email exists
let upsert = I("users")
    .columns(vec!["email", "name", "login_count"])
    .values(vec!["'bob@example.com'", "'Bob Smith'", "'1'"])
    .on_conflict_do_update(
        vec!["email"],
        vec![
            ("name", "'Bob Smith'"),
            ("login_count", "users.login_count + 1"),
            ("updated_at", "NOW()")
        ]
    )
    .returning(Columns::Selected(vec!["id", "email", "updated_at"]))
    .build();

// Result: INSERT INTO users (...) VALUES (...)
//         ON CONFLICT (email) DO UPDATE SET name = '...', login_count = ...
//         RETURNING id, email, updated_at

Multiple Row INSERT

Efficiently insert multiple rows in a single statement:

use squeal::*;

let insert = I("products")
    .columns(vec!["name", "price", "category"])
    .rows(vec![
        vec!["'Laptop'", "'999.99'", "'electronics'"],
        vec!["'Mouse'", "'24.99'", "'electronics'"],
        vec!["'Desk'", "'299.99'", "'furniture'"],
    ])
    .returning(Columns::Selected(vec!["id", "name"]))
    .build();

// Result: INSERT INTO products (name, price, category) VALUES
//         ('Laptop', 999.99, 'electronics'),
//         ('Mouse', 24.99, 'electronics'),
//         ('Desk', 299.99, 'furniture')
//         RETURNING id, name

INSERT ... SELECT

Copy data from one table to another:

use squeal::*;

// Archive old orders
let select_old_orders = Q()
    .select(vec!["id", "user_id", "total", "created_at"])
    .from("orders")
    .where_(lt("created_at", "'2023-01-01'"))
    .build();

let archive = I("orders_archive")
    .columns(vec!["order_id", "user_id", "total", "order_date"])
    .select(select_old_orders)
    .build();

// Result: INSERT INTO orders_archive (order_id, user_id, total, order_date)
//         SELECT id, user_id, total, created_at FROM orders
//         WHERE created_at < '2023-01-01'

RETURNING Clause

Get values from INSERT, UPDATE, or DELETE operations:

use squeal::*;

// Get the auto-generated ID after insert
let insert = I("posts")
    .columns(vec!["title", "content", "author_id"])
    .values(vec!["'Hello World'", "'First post!'", "'1'"])
    .returning(Columns::Selected(vec!["id", "created_at"]))
    .build();

// Update and return the modified rows
let update = U("users")
    .columns(vec!["status", "updated_at"])
    .values(vec!["'inactive'", "NOW()"])
    .where_(lt("last_login", "NOW() - INTERVAL '1 year'"))
    .returning(Columns::Selected(vec!["id", "email"]))
    .build();

// Delete and track what was removed
let delete = D("sessions")
    .where_(lt("expires_at", "NOW()"))
    .returning(Columns::Selected(vec!["user_id", "session_id"]))
    .build();

Development

Build and Test

# Build the project
cargo build

# Run tests (excludes Docker tests)
cargo test

# Run all tests including PostgreSQL integration tests (requires Docker)
cargo test --features postgres-docker

# Run benchmarks
cargo bench

# Generate documentation
cargo doc --open

Code Quality

# Run clippy (required before committing)
cargo clippy

# Auto-fix clippy warnings
cargo clippy --fix --lib -p squeal

Project Status

Version: 0.1.0 (pre-release)

This library is in active development. The API is stabilizing but may still change. It is suitable for experimentation and early adoption, but not recommended for production use until version 1.0.

Design Notes

A core problem of using SQL in programming is the impedence mismatch between the code of the database and the code of the client.

Efforts such as ActiveRecord exist; they are widely panned by SQL experts as producing poor SQL and tend not to "fit" the database itself. Writing SQL directly in code has also a poor history - it is notorious for defects and difficulty in maintaining.

Query builders occupy a middle point, where the power of the programming language brings to bear some maintainability, but the intermediate artifact is still recognizable and controllable by the programmer, leading to a fit with the database.

This is my contribution in this effort; I developed a Scala query builder for a company quite a few years ago now and it worked well. I am pleased to offer a ground-up cleanroom Rust query builder.

The core problem this design does not touch is the object-relation mapping code. I would advise users to write a database model layer, perform the object-relation map there, then transform into the usual in-system state from that point out.

Related Projects

• PostgreSQL Rust driver: rust-postgres
• Similar library for Go: sqlf

Repository

• GitHub: https://github.com/upside-down-research/squeal

License

LGPL 3.0 or later. See LICENSE.md for details.

Contributing

Contributions are welcome! Please ensure:

• All tests pass (cargo test)
• No clippy warnings (cargo clippy)
• New features include tests
• No decrease in code coverage (tarpaulin).
• Code follows the existing style
• Idealy no dependencies are taken outside of dev-dependencies.
• No unsafe code.

AI Note.

This library was hand written, and later AI was asked to extend it. A human contributing _takes responsibility_for their code, whether or not an AI was involved.