# Query builder & formatter

SnowDDL builds a lot of raw SQL queries. In order to simplify this task while keeping the full flexibility of SQL, SnowDDL provides query builder and query formatter via its <mark style="color:red;">**engine**</mark>.

## Query builder

Query builder is used to build complex SQL queries from small fragments. Each fragment can be added to the current line or it may start a new line.

For example, consider the following query fragments:

```
['SELECT', 'id AS user_id', 'name AS user_name']
['FROM', '"MY_TABLE"']
['WHERE', 'country_id = 10']
```

Final query:

```sql
SELECT id AS user_id, name AS user_name
FROM "MY_TABLE"
WHERE country_id = 10
```

Example code:

```python
query = engine.query_builder()

# Fill the first line
query.append("SELECT")
query.append("id AS user_id")
query.append("name AS user_name")

# Add a new line with identifier as parameter
query.append_nl("FROM {table_name:i}", {
    "table_name": Ident('MY_TABLE')
})

# Add another line with filters
query.append_nl("WHERE")
query.append_nl("country_id = {country_id:d}", {
    "country_id": 10
})

# Display query
print(query)

# Execute query
engine.execute_meta(query)
```

In order to create a new empty query builder, call function `.query_builder()` of <mark style="color:red;">**engine**</mark> class.

Alternatively, you may create an instance of class `SnowDDLQueryBuilder` directly.

### Methods

* `append(sql, params=None)`\
  Appends a fragment to the current line. Optionally formats it using placeholders and dictionary with params.<br>
* `append_nl(sql, params=None)`\
  Starts a new line and append a fragment to it. Optionally formats it using placeholders and dictionary with params.<br>
* `fragment_count()`\
  Returns a number of fragments across all lines.<br>
* `add_short_hash(comment)`\
  Part of [short hash](/guides/other-guides/short-hash.md) feature. Takes the comment for object and adds a short hash at the end. Returns updated comment.<br>
* `compare_short_hash(comment)`\
  Part of [short hash](/guides/other-guides/short-hash.md) feature. Extracts short hash from existing comment and compares it with expected short hash. Returns `True` if hashes are the same, returns `False` otherwise.<br>
* `__str__()`\
  Query builder objects can be used as normal strings.

## Formatter

SnowDDL formatter uses its own custom syntax for placeholders and supports a wide range of placeholder types.

### Syntax

Only named placeholders are supported. Positional placeholders are not allowed on purpose. It is important to reduce the amount of accidental mistakes.

The placeholder syntax is: `{name:type}`

Type is optional. Default type is `:s` (string value).

### Usage notes

Query formatting is performed only when `params` are explicitly defined as `dict`. It allows you to use your own query formatter if it is necessary.

All placeholders defined in SQL query text must be present in `params`, there are no "defaults". It helps to prevent typos and accidental damage associated with it.

If you pass list of values for placeholder instead of single value, it will be represented as concatenated comma-separated string of individually formatted values, which is useful for `IN (val1, val2, val3)` syntax.

### Placeholder types

* `:s` - common value, enclosed in single quotes and escaped\
  NULL value is returned as `NULL` without quotes\
  normally used for VARCHAR, TIMESTAMP, etc.
* `:d` - safe decimal value, validated and formatted "as is"\
  normally used for NUMBER, raw integer values in LIMIT, etc.
* `:f` - safe float value, validated and formatted "as is"\
  used for FLOAT values only, supports optional exponent
* `:b` - safe boolean value, validated and formatted as `TRUE` or `FALSE` string\
  used for BOOLEAN values only
* `:i` - identifier, enclosed in double quotes and escaped\
  used for identifiers of all types
* `:ia` - identifier, but additionally enclosed in single quotes and escaped\
  used for arguments of table functions which accept identifiers in this form
* `:r` - raw value, formatted "as is", not validated, dangerous (!)\
  used to add dynamic query fragments which cannot be constructed by other means
* `:lf` - LIKE pattern looking for full match (`LIKE '{val}'`)
* `:ls` - LIKE pattern looking for match starting with (`LIKE '{val}%'`)
* `:le` - LIKE pattern looking for match ending with (`LIKE '%{val}'`)
* `:lse` - LIKE pattern looking for match starting and ending with (`LIKE '{start}%{end}'`)\
  accepts tuple with two elements

Example of SQL with placeholders:

```sql

    SELECT {common_val} AS common_val
        , {null_val} AS {col_name:i}
        , u.user_id
        , sum(gross_amt) AS gross_amt
    FROM {table_name:i} u
    WHERE u.user_rating >= {user_rating:d}
        AND u.user_score > {user_score:f}
        AND u.is_female IS {is_female:b}
        AND u.status IN ({user_statuses})
        AND u.user_rating NOT IN ({exclude_user_score:d})
    GROUP BY 1,2,3
    ORDER BY 4 DESC
    LIMIT {limit:d}
```

Query formatting code:

```python
# SQL with formatting
params = {
    'common_val': 'abc',
    'null_val': None,
    'col_name': Ident('NULL_VAL')
    'table_name': IdentWithPrefix(env_prefix='ALICE__', 'MY_DB', 'MY_SCHEMA', 'USERS'),
    'user_rating': '0.5',
    'user_score': 1e1,
    'is_female': True,
    'user_statuses': ['ACTIVE', 'PASSIVE', 'SUSPENDED'],
    'exclude_user_score': [10, 20],
    'limit': 10
}

print(engine.format(query, params))
```

Result:

```sql
    SELECT 'abc' AS common_val
        , NULL AS "NULL_VAL"
        , u.user_id
        , sum(gross_amt) AS gross_amt
    FROM "ALICE__MY_DB"."MY_SCHEMA"."USERS" u
    WHERE u.user_rating >= 0.5
        AND u.user_score > 1e1
        AND u.is_female IS TRUE
        AND u.status IN ('ACTIVE', 'PASSIVE', 'SUSPENDED')
        AND u.user_rating NOT IN (10, 20)
    GROUP BY 1,2,3
    ORDER BY 4 DESC
    LIMIT 10
```

### Overloading

You may extend or replace standard formatter by overloading `SnowDDLFormatter` class and by explicitly setting it into overloaded property `.formatter` of `SnowDDLEngine`.


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.snowddl.com/advanced/query-builder-and-formatter.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.