SQL Templates¶

Ralsei’s template engine is based on jinja, but has a few important extensions

Type awareness¶

While base jinja pastes every {{ value }} as-is, ralsei translates types to their SQL counterpart (as well as resolving OutputOf dependencies):

str becomes a SQL string (and is appropriately escaped)
int and float become numbers
None becomes NULL
Types implementing ralsei.types.ToSql define their own SQL rendering
class Sql¶

Bases: ralsei.types.to_sql.ToSql

Raw SQL string, inserted into the template as-is

value : str = None¶

to_sql(env: ralsei.jinja.ISqlEnvironment) → str¶
class Identifier¶

Bases: ralsei.types.to_sql.ToSql

A SQL identifier, like "table_name"

value : str = None¶

to_sql(env: ralsei.jinja.ISqlEnvironment) → str¶
class Table¶

Bases: ralsei.types.to_sql.ToSql

Table identifier, like "schema_name"."table_name"

name : str = None¶

schema : Optional[str] = None¶

__str__() → str¶

to_sql(env: ralsei.jinja.ISqlEnvironment) → str¶
class Placeholder¶

Bases: ralsei.types.to_sql.ToSql

Placeholder for a bind parameter, like :value

Must not any spaces or special characters

name : str = None¶

__post_init__()¶

to_sql(env: ralsei.jinja.ISqlEnvironment) → str¶

The `{%split%}` tag¶

Some databases (like SQLite) do not support executing multiple statements within a single execute() call, at least without breaking transactions.

In order to write multiple statements in one file, the {%split%} tag was introduced to separate the statements. It’s treated as a special token by the template engine, splitting the result into multiple strings:

task.sql¶

CREATE TABLE {{table}}(
    id INTEGER PRIMARY KEY,
    name TEXT
);

{%split%}

INSERT INTO {{table}}(name)
SELECT name from {{other}}

>>> SqlEnvironment().render_split(
...     Path("task.sql").read_text(),
...     table=Table("items"),
...     other=Table("items", "tmp")
... )
['CREATE TABLE "items"(\n    id INTEGER PRIMARY KEY,\n    name TEXT\n);\n\n',
'\n\nINSERT INTO "items"(name)\nSELECT n ame from "tmp"."items"']

Environment defaults¶

Globals¶

ralsei-globals.range([start, ]stop[, step])¶: Same as jinja-globals.range()

ralsei-globals.dict(\**items)¶: Same as jinja-globals.dict()

ralsei-globals.joiner(sep: str = ', ') → collections.abc.Callable[[], ralsei.types.Sql]¶: Like jinja-globals.joiner, but outputs raw Sql

ralsei-globals.Column : type[ralsei.types.Column] = Column¶: The column class, used in AddColumnsSql templates

ralsei-globals.dialect : ralsei.dialect.DialectInfo¶: The current dialect

Filters¶

ralsei-filters.sql : type[ralsei.types.Sql] = Sql¶

Treat string as raw SQL

DROP {{ ('VIEW' if view else 'TABLE') | sql }}

ralsei-filters.identifier : type[ralsei.types.Identifier] = Identifier¶

Treat string as identifier

CREATE TABLE {{ 'My Table' | identifier }}

ralsei-filters.join(values: collections.abc.Iterable[Any], delimiter: str, attribute: str | None = None) → ralsei.types.Sql¶: Like jinja-filters.join(), but outputs raw Sql

Custom variables¶

Every task has a TaskDef.locals parameter to inject your own variables:

CreateTableSql(
    table=Table("items"),
    sql=[
        """CREATE TABLE {{table}}(
            id INTEGER PRIMARY KEY,
            name TEXT
        )""",
        """INSERT INTO {{table}}(name)
        SELECT name from {{other}}"""
    ],
    locals={"other": self.outputof("items_tmp")}
)

And through Ralsei._prepare_env() you can modify the global environment

class MyApp(Ralsei):
    def _prepare_env(env: SqlEnvironment):
        env.globals["my_global"] = my_global
        env.filters["my_filter"] = my_filter