# SQL Builder

The SQL builder creates parameterized SQL statements without tying query code to a
specific database driver. It currently defaults to Postgres output, while keeping
database-specific behavior behind `SqlDialect`.

```kotlin
import dev.mduchene.sql.*

val statement = Sql
  .select("id", "email")
  .from("users")
  .where(Sql.col("status").eq("active"))
  .toSql()
```

`statement` contains the SQL text and JDBC bind values separately:

```text
sql:        SELECT "id", "email" FROM "users" WHERE ("status" = $1)
parameters: ["active"]
```

## Core Types

- `SqlStatement`: rendered SQL plus ordered parameters.
- `SqlStatementBuilder`: implemented by statement builders and rendered with `toSql()`.
- `SqlDialect`: controls placeholders, identifier quoting, and dialect feature flags.
- `PostgresDialect`: default dialect. Uses `$1`, `$2`, etc.
- `AnsiDialect`: portable baseline. Uses `?` placeholders and rejects unsupported features such as `RETURNING`.

The builder only renders SQL. It does not execute JDBC statements or manage
connections.

## Identifiers And Raw SQL

Column, table, and alias names passed as plain strings are treated as identifiers.
They are validated and quoted:

```kotlin
Sql.select("u.id").from("users", "u").toSql()
```

```text
SELECT "u"."id" FROM "users" AS "u"
```

Use `Sql.raw(...)` only for trusted SQL fragments that are not identifiers or bind
values, such as database functions, casts, or explicit SQL operators.

```kotlin
Sql.raw("CURRENT_TIMESTAMP")
```

Raw SQL can still use bind values. Each `?` is converted to the active dialect's
placeholder:

```kotlin
Sql.raw("LOWER(email) = LOWER(?)", "sakura@example.com")
```

## SELECT

```kotlin
val statement = Sql
  .select(
    Sql.col("u.id"),
    Sql.col("u.email"),
    Sql.count(Sql.col("p.id")).asAlias("post_count"),
  )
  .from("users", "u")
  .leftJoin("posts", "p", Sql.col("p.user_id").eq(Sql.col("u.id")))
  .where(Sql.col("u.status").eq("active"))
  .andWhere(Sql.col("u.deleted_at").isNull())
  .groupBy("u.id", "u.email")
  .having(Sql.count(Sql.col("p.id")).gt(0))
  .orderBy(Sql.col("u.created_at").desc(NullsOrder.LAST))
  .limit(25)
  .offset(50)
  .toSql()
```

```text
SELECT "u"."id", "u"."email", COUNT("p"."id") AS "post_count"
FROM "users" AS "u"
LEFT JOIN "posts" AS "p" ON ("p"."user_id" = "u"."id")
WHERE (("u"."status" = $1) AND ("u"."deleted_at" IS NULL))
GROUP BY "u"."id", "u"."email"
HAVING (COUNT("p"."id") > $2)
ORDER BY "u"."created_at" DESC NULLS LAST
LIMIT 25 OFFSET 50

parameters: ["active", 0]
```

Supported select features include:

- `distinct()`
- `from(...)`
- `join(...)`, `leftJoin(...)`, `rightJoin(...)`, `fullJoin(...)`, `crossJoin(...)`
- `where(...)`, `andWhere(...)`, `orWhere(...)`
- `groupBy(...)`, `having(...)`, `andHaving(...)`
- `orderBy(...)`
- `limit(...)`, `offset(...)`
- `with(...)` for CTEs
- `union(...)`, `unionAll(...)`

## INSERT

```kotlin
val statement = Sql
  .insertInto("users")
  .columns("email", "display_name")
  .values("sakura@example.com", "Sakura")
  .values("naruto@example.com", "Naruto")
  .returning("id")
  .toSql()
```

```text
INSERT INTO "users" ("email", "display_name")
VALUES ($1, $2), ($3, $4)
RETURNING "id"

parameters: ["sakura@example.com", "Sakura", "naruto@example.com", "Naruto"]
```

You can also insert from a map:

```kotlin
val statement = Sql
  .insertInto("users")
  .set(
    mapOf(
      "email" to "hinata@example.com",
      "display_name" to "Hinata",
    ),
  )
  .returning("id")
  .toSql()
```

For `INSERT ... SELECT`:

```kotlin
val source = Sql
  .select("user_id", "email")
  .from("pending_invites")
  .where(Sql.col("accepted_at").isNotNull())

val statement = Sql
  .insertInto("users")
  .fromSelect(listOf("id", "email"), source)
  .toSql()
```

## UPDATE

```kotlin
val statement = Sql
  .update("users")
  .set("display_name", "Hinata")
  .set("updated_at", Sql.raw("CURRENT_TIMESTAMP"))
  .where(Sql.col("id").eq(42))
  .returning("id", "updated_at")
  .toSql()
```

```text
UPDATE "users"
SET "display_name" = $1, "updated_at" = CURRENT_TIMESTAMP
WHERE ("id" = $2)
RETURNING "id", "updated_at"

parameters: ["Hinata", 42]
```

## DELETE

```kotlin
val statement = Sql
  .deleteFrom("sessions")
  .where(Sql.col("user_id").eq(42))
  .toSql()
```

```text
DELETE FROM "sessions" WHERE ("user_id" = $1)

parameters: [42]
```

## CTEs And Subqueries

```kotlin
val inactiveUsers = Sql
  .select("id")
  .from("users")
  .where(Sql.col("last_seen_at").lt(Sql.raw("CURRENT_DATE - INTERVAL '1 year'")))

val statement = Sql
  .deleteFrom("sessions")
  .with("inactive_users", inactiveUsers)
  .where(
    Sql.col("user_id").inSubquery(
      Sql.select("id").from("inactive_users"),
    ),
  )
  .toSql()
```

```text
WITH "inactive_users" AS (
  SELECT "id"
  FROM "users"
  WHERE ("last_seen_at" < CURRENT_DATE - INTERVAL '1 year')
)
DELETE FROM "sessions"
WHERE ("user_id" IN (SELECT "id" FROM "inactive_users"))
```

## Dynamic Conditions

Builders are mutable, so query code can add conditions as request filters are
present.

```kotlin
fun findUsers(status: String?, email: String?): SqlStatement {
  val query = Sql
    .select("id", "email", "status")
    .from("users")
    .where(Sql.col("deleted_at").isNull())

  if (status != null) {
    query.andWhere(Sql.col("status").eq(status))
  }

  if (email != null) {
    query.andWhere(Sql.raw("LOWER(email) = LOWER(?)", email))
  }

  return query.toSql()
}
```

## Dialects

Postgres is the default:

```kotlin
val postgres = Sql
  .select("id")
  .from("users")
  .where(Sql.col("email").eq("sakura@example.com"))
  .toSql()
```

```text
SELECT "id" FROM "users" WHERE ("email" = $1)
```

Render the same builder for another dialect by passing it to `toSql(...)`:

```kotlin
val builder = Sql
  .select("id")
  .from("users")
  .where(Sql.col("email").eq("sakura@example.com"))

val ansi = builder.toSql(AnsiDialect)
```

```text
SELECT "id" FROM "users" WHERE ("email" = ?)
```

If a builder uses a feature that the selected dialect does not support, rendering
fails early:

```kotlin
Sql
  .insertInto("users")
  .columns("email")
  .values("sakura@example.com")
  .returning("id")
  .toSql(AnsiDialect) // throws IllegalArgumentException
```

## Expressions

Common expression helpers:

```kotlin
Sql.col("age").gte(18)
Sql.col("email").like("%@example.com")
Sql.col("deleted_at").isNull()
Sql.col("role").inValues("admin", "moderator")
Sql.col("id").inSubquery(Sql.select("user_id").from("sessions"))
Sql.exists(Sql.select().from("sessions").where(Sql.col("sessions.user_id").eq(Sql.col("users.id"))))
Sql.and(Sql.col("status").eq("active"), Sql.col("deleted_at").isNull())
Sql.or(Sql.col("role").eq("admin"), Sql.col("role").eq("owner"))
```

Comparison helpers bind ordinary Kotlin values as parameters. Passing another
`SqlExpression` compares SQL expressions directly:

```kotlin
Sql.col("posts.user_id").eq(Sql.col("users.id"))
```

## Current Scope

The builder is intentionally small. Add new SQL features through the dialect and
fragment APIs when the application needs them, rather than introducing a larger
ORM-style abstraction early.