🛠️ SQL Utility

SQL Utility is a lightweight, flexible Rust library designed to simplify the dynamic construction of SQL queries. It provides a robust set of helper functions to programmatically generate SELECT, INSERT, UPDATE, and DELETE statements with ease.

📖 Table of Contents

• Features
• Installation
• Usage
  • Select Queries
  • Fetch Single Row
  • Insert Queries
    • Using build_insert_sql
    • Using make_insert
  • Update Queries
  • Delete Queries
• Helper Functions
• License

✨ Features

• Dynamic Query Building: Construct complex SQL queries at runtime.
• Type Safety: Leverages Rust's type system and serde_json for safe value handling.
• Flexible API: Low-level builders (build_*) and high-level helpers (make_*).
• Minimal Dependencies: Only serde_json.

📦 Installation

Add the following to your Cargo.toml file:

[dependencies]
sqlutil = "0.1.7"
serde_json = "1.0"

🚀 Usage

Select Queries

Build SELECT queries using either the granular build_select_sql or the concise make_query helper.

🔹 Using build_select_sql

use sqlutil::build_select_sql;

let sql = build_select_sql(
    "users",                 // table
    Some("id, name, email"), // columns (None = *)
    Some("active = 1"),      // where clause
    Some("created_at DESC"), // order by
    Some(0),                 // offset
    Some(10),                // limit
);

assert_eq!(
    sql,
    "SELECT id, name, email FROM \"users\" WHERE active = 1 ORDER BY created_at DESC OFFSET 0 LIMIT 10"
);

🔹 Using make_query

make_query builds clauses from slices of strings. All parameters except tables are Option. Columns, tables, and order items are joined with , (comma with spaces); WHERE conditions are joined with AND.

use sqlutil::make_query;

// Full example
let sql = make_query(
    &["users"],                        // tables
    Some(&["id", "name"]),             // columns (None = select *)
    Some(&["age > 18", "active = 1"]), // where conditions
    Some(&["name ASC"]),               // order by
    Some(0),                           // offset
    Some(20),                          // limit
);
assert_eq!(
    sql,
    "SELECT id , name FROM \"users\" WHERE age > 18 AND active = 1 ORDER BY name ASC OFFSET 0 LIMIT 20"
);

// Minimal — select all rows
let sql = make_query(&["users"], None, None, None, None, None);
assert_eq!(sql, "SELECT * FROM \"users\"");

Fetch Single Row

make_fetch is a convenience wrapper around build_select_sql that always adds LIMIT 1 and never adds OFFSET or ORDER BY. Use it when you only need one matching row.

use sqlutil::make_fetch;

// Select all columns, first matching row
let sql = make_fetch(&["users"], None, Some(&["id = 1"]));
assert_eq!(sql, "SELECT * FROM \"users\" WHERE id = 1 LIMIT 1");

// Select specific columns with multiple WHERE conditions
let sql = make_fetch(
    &["sessions"],
    Some(&["id", "token", "user_id"]),
    Some(&["expired = 0", "user_id = 42"]),
);
assert_eq!(
    sql,
    "SELECT id , token , user_id FROM \"sessions\" WHERE expired = 0 AND user_id = 42 LIMIT 1"
);

// No WHERE — first row of the table
let sql = make_fetch(&["config"], None, None);
assert_eq!(sql, "SELECT * FROM \"config\" LIMIT 1");

Insert Queries

Two functions are available for INSERT statements.

🔹 Using build_insert_sql

Accepts a HashMap<K, serde_json::Value> where K can be String or &str.

use std::collections::HashMap;
use serde_json::json;
use sqlutil::build_insert_sql;

let mut data = HashMap::new();
data.insert("name", json!("John Doe"));
data.insert("age", json!(30));
data.insert("is_admin", json!(false));
data.insert("deleted_at", json!(null));

let sql = build_insert_sql("users", &data);
// INSERT INTO "users" ("name", "age", "is_admin", "deleted_at") VALUES ('John Doe', 30, false, NULL)
// Note: column order depends on HashMap iteration order.

🔹 Using make_insert (high-level)

make_insert is a convenience wrapper around build_insert_sql with the same signature. Use it for a consistent naming style across make_* helpers.

use std::collections::HashMap;
use serde_json::json;
use sqlutil::make_insert;

let mut data = HashMap::new();
data.insert("name", json!("Alice"));
data.insert("active", json!(true));

let sql = make_insert("users", &data);
// INSERT INTO "users" ("name", "active") VALUES ('Alice', true)
// Note: column order depends on HashMap iteration order.

Supported value types

Update Queries

Two functions are available for UPDATE statements.

🔹 Using build_update_sql (low-level)

Accepts the same HashMap<K, serde_json::Value> as build_insert_sql. The WHERE clause is a raw pre-built string.

use std::collections::HashMap;
use serde_json::json;
use sqlutil::build_update_sql;

let mut data = HashMap::new();
data.insert("name", json!("Jane Doe"));
data.insert("updated_at", json!("2024-01-01"));

let sql = build_update_sql("users", &data, Some("id = 1"));
// UPDATE "users" SET "name" = 'Jane Doe' , "updated_at" = '2024-01-01' WHERE id = 1

// Without WHERE — updates all rows
let mut data = HashMap::new();
data.insert("active", json!(false));
let sql = build_update_sql("users", &data, None);
// UPDATE "users" SET "active" = false

🔹 Using make_update (high-level)

make_update builds the WHERE clause from a slice of condition strings (joined with AND), mirroring make_query and make_delete.

use std::collections::HashMap;
use serde_json::json;
use sqlutil::make_update;

// Single condition
let mut data = HashMap::new();
data.insert("name", json!("Alice"));
let sql = make_update("users", &data, Some(&["id = 1"]));
assert_eq!(sql, "UPDATE \"users\" SET \"name\" = 'Alice' WHERE id = 1");

// Multiple conditions
let mut data = HashMap::new();
data.insert("status", json!("inactive"));
let sql = make_update("users", &data, Some(&["active = 0", "age < 18"]));
assert_eq!(sql, "UPDATE \"users\" SET \"status\" = 'inactive' WHERE active = 0 AND age < 18");

// No WHERE clause
let mut data = HashMap::new();
data.insert("migrated", json!(true));
let sql = make_update("users", &data, None);
assert_eq!(sql, "UPDATE \"users\" SET \"migrated\" = true");

Delete Queries

Two functions are available for DELETE statements.

🔹 Using build_delete_sql (low-level)

use sqlutil::build_delete_sql;

// With WHERE clause
let sql = build_delete_sql("users", Some("id = 1"));
assert_eq!(sql, "DELETE FROM \"users\" WHERE id = 1");

// Without WHERE — deletes all rows
let sql = build_delete_sql("users", None);
assert_eq!(sql, "DELETE FROM \"users\"");

🔹 Using make_delete (high-level)

make_delete builds the WHERE clause from a slice of condition strings (joined with AND), mirroring make_query.

use sqlutil::make_delete;

// Single condition
let sql = make_delete("users", Some(&["id = 1"]));
assert_eq!(sql, "DELETE FROM \"users\" WHERE id = 1");

// Multiple conditions
let sql = make_delete("sessions", Some(&["expired = 1", "user_id = 42"]));
assert_eq!(sql, "DELETE FROM \"sessions\" WHERE expired = 1 AND user_id = 42");

// No WHERE clause
let sql = make_delete("logs", None);
assert_eq!(sql, "DELETE FROM \"logs\"");

🛠 Helper Functions

These functions build individual SQL clauses from string slices.

Example

use sqlutil::{make_where, quote, quote_identifier};

let cond = make_where(&["status = 'active'", "count > 0"]);
assert_eq!(cond, "status = 'active' AND count > 0");

assert_eq!(quote("O'Reilly"), "'O''Reilly'");
assert_eq!(quote_identifier("my table"), "\"my table\"");

📄 License

This project is licensed under the MIT License - see the LICENSE file for details.

Made with ❤️ in Rust.