🏗️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 engine.

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:

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

Example code:

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 engine 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.

• append_nl(sql, params=None) Starts a new line and append a fragment to it. Optionally formats it using placeholders and dictionary with params.

• fragment_count() Returns a number of fragments across all lines.

• add_short_hash(comment) Part of short hash feature. Takes the comment for object and adds a short hash at the end. Returns updated comment.

• compare_short_hash(comment) Part of short hash feature. Extracts short hash from existing comment and compares it with expected short hash. Returns True if hashes are the same, returns False otherwise.

• __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:

    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:

# 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:

    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.