Working with SQL nodes

info

SQL queries by default run on the Patterns DB which is a Postgres engine.

Developing SQL nodes in Patterns is similar to writing normal SQL queries. Instead of referencing table names directly, though, you reference a Table store using “handlebar” syntax:

select
    customer_id
, sum(amount) as total_sales
from {{ Table("transactions") }} as t
group by 1

The arguments for the Table are the same as for Python nodes. By default, a simple select statement will generate a new version of the Table store each time it is run. To run queries that modify an existing table version, you can explicitly reference the output table in write mode.

insert into {{ Table("customer_sales", mode="w") }}

select
    customer_id
  , sum(amount) as total_sales
from {{ Table("transactions") }} as t
group by 1

As with Python nodes, the transactions and customer_sales stores must be defined in your App, or the node will not execute properly.

Using parameters

Parameters work just as in Python nodes:

insert into {{ Table("customer_sales", mode="w") }}

select
    customer_id
  , sum(amount) as total_sales
from {{ Table("transactions") }} as t
group by 1
having sum(amount) > {{ Parameter("sales_threshold", type=float) }}