# Eloquent
**Eloquent** is a fluent, type-safe query builder for Rust, designed to make SQL query construction intuitive. It provides an expressive API, allowing developers to craft complex SQL queries through method chaining.
## Features
- Fluent API for building SQL queries.
- Type-safe query construction with method chaining.
- Support for:
- `SELECT`, `JOIN`, `WHERE`, `GROUP BY`, `HAVING`, etc.
- Conditional queries with `AND`, `OR`, `NOT`, `LIKE`, `IN`, `NOT IN`, `IS NULL`, etc.
- Aggregation functions: `AVG`, `SUM`, `MIN`, `MAX`, and `COUNT`.
- Date functions: `DATE`, `TIME`, `YEAR`, `MONTH`, `DAY`, etc.
- Function aliases and raw expressions.
- CRUD operations: `INSERT`, `UPDATE`, and `DELETE`.
- Subqueries and nested conditions using closures.
- Cursor-based pagination support via `paginate()`.
- SQL query generation as raw `sql()` or formatted output `pretty_sql()`.
- Query validation and error handling (can be skipped with `skip_validation()`).
- Use question or dollar placeholders for prepared statements.
Use your IDE to explore the available methods, or refer to the [docs.rs/eloquent - QueryBuilder](https://docs.rs/eloquent/latest/eloquent/struct.QueryBuilder.html).
## Installation
To use Eloquent, add the following to your `Cargo.toml`:
```toml
[dependencies]
eloquent = "2.0"
```
## Usage
### Simple query example
This example demonstrates a basic SQL query using Eloquent's fluent API.
```rust
use eloquent::Eloquent;
let query = Eloquent::query()
.table("users")
.select(vec!["name", "email"])
.where_not_null("verified_at")
.where_like("email", "%@gmail.com")
.limit(100);
println!("{}", query.pretty_sql()?);
```
```sql
SELECT
name,
email
FROM
users
WHERE
verified_at IS NOT NULL
AND email LIKE '%@gmail.com'
LIMIT
100
```
### Complex query example
This example demonstrates a more complex SQL query using Eloquent's fluent API.
```rust
use eloquent::Eloquent;
let query = Eloquent::query()
.table("flights")
.select("origin_airport")
.select_avg("startup_time_in_minutes", "startup_time_in_minutes_avg")
.select_as("airports.city", "destination_city")
.join(
"airports",
"flights.destination_airport",
"airports.iata_code",
)
.r#where("origin_airport", "AMS")
.where_not_in("flight_number", vec!["KL123", "KL456"])
.where_not_null("gate_number")
.where_closure(|q| {
q.where_gte("flight_duration", 120)
.or_where_like("airports.city", "%NY%")
})
.group_by(vec!["origin_airport", "airports.city"])
.having_gt("startup_time_in_minutes_avg", 120)
.order_by_asc("startup_time_in_minutes_avg")
.limit(20);
println!("{}", query.pretty_sql()?);
```
```sql
SELECT
origin_airport,
AVG(startup_time_in_minutes) AS startup_time_in_minutes_avg,
airports.city AS destination_city
FROM
flights
JOIN airports ON flights.destination_airport = airports.iata_code
WHERE
origin_airport = 'AMS'
AND flight_number NOT IN ('KL123', 'KL456')
AND gate_number IS NOT NULL
AND (
flight_duration >= 120
OR airports.city LIKE '%NY%'
)
GROUP BY
origin_airport,
airports.city
HAVING
startup_time_in_minutes_avg > 120
ORDER BY
startup_time_in_minutes_avg ASC
LIMIT
20
```
### Subquery example
This example demonstrates a subquery using Eloquent's fluent API.
```rust
use eloquent::Eloquent;
let subquery = Eloquent::subquery()
.table("tickets")
.select("event_id")
.select_avg("price", "price_avg")
.group_by("event_id")
.order_by_desc("price_avg")
.limit(1);
let query = Eloquent::query()
.table("events")
.select(vec!["event_name", "event_date"])
.r#where("event_id", subquery)
.pretty_sql()?;
```
```sql
SELECT
event_name,
event_date
FROM
EVENTS
WHERE
event_id = (
SELECT
event_id,
AVG(price) AS price_avg
FROM
tickets
GROUP BY
event_id
ORDER BY
price_avg DESC
LIMIT
1
)
```
### Pagination example
This example demonstrates cursor-based pagination using Eloquent's `paginate()` method.
```rust
use eloquent::Eloquent;
let last_id = None; // initial query
let query = Eloquent::query()
.table("departures")
.select("flight_number")
.paginate::<u64>("id", last_id, 25)
.sql()?;
```
```sql
SELECT flight_number FROM departures LIMIT 25
```
```rust
use eloquent::Eloquent;
let last_id = Some(40); // last id from previous query
let query = Eloquent::query()
.table("departures")
.select("flight_number")
.paginate("id", last_id, 25)
.sql()?;
```
```sql
SELECT flight_number FROM departures WHERE id > 40 LIMIT 25
```
### Prepared statements example
This example demonstrates using placeholders for prepared statements.
You can use the feature `bind-placeholder-questionmark` to control the use of `$` vs `?` as placeholders.
```rust
use eloquent_core::{QueryBuilder, bind};
let result = QueryBuilder::new()
.table("flights")
.insert("name", bind(4))
.sql()?;
```
```sql
INSERT INTO flights (name) VALUES ($4)
```