Postgres Database

How to connect with Postgres and run queries from n8n

Connecting the Database:

With n8n you can directly run queries on the database (POSTGRES) using the Postgres module. Before running queries, you need to have the database connected to your local n8n account. To do this: Add Postgres module to your workflow. Then create new credential by filling the fields as shown below.

Host: your domain name (eg openfoodnetwork.org.au)

Database: openfoodnetwork

User: n8n

Password: See database n8n user in bitwarden (or ask in #n8n channel)

SSL: Require

Make sure you add the name/code of your instance and the type of DB connection (staging/production) in the credential name ex. OFN DB connection (au-prod), Open Food Network DB (uk-staging) etc.

Running Queries:

Connect the Postgres module in the workflow, then select the credential for your DB connection. In the “Operation” field select “Execute Query”. Enter the SQL query in the next field and Run. That's it!

Common SQL Queries (on-going/WIP):

Need to be checked/tested.

Please sense check the results while using the queries from this page.

Total Number of completed orders for the last month:

SELECT date_trunc('month', "public"."spree_orders"."completed_at") AS "completed_at", count(*) AS "value" FROM "public"."spree_orders" WHERE ("public"."spree_orders"."state" = 'complete'    AND "public"."spree_orders"."completed_at" >= date_trunc('month', CAST((CAST(now() AS timestamp) + (INTERVAL '-1 month')) AS timestamp)) AND "public"."spree_orders"."completed_at" < date_trunc('month', CAST(now() AS timestamp))) GROUP BY date_trunc('month', "public"."spree_orders"."completed_at") ORDER BY date_trunc('month', "public"."spree_orders"."completed_at") ASC

Total Sales for the last month:

SELECT date_trunc('month', "public"."spree_orders"."completed_at") AS "completed_at", sum("public"."spree_orders"."total") AS "value"
FROM "public"."spree_orders"
WHERE ("public"."spree_orders"."state" = 'complete'
   AND "public"."spree_orders"."completed_at" >= date_trunc('month', CAST((CAST(now() AS timestamp) + (INTERVAL '-1 month')) AS timestamp)) AND "public"."spree_orders"."completed_at" < date_trunc('month', CAST(now() AS timestamp)))
GROUP BY date_trunc('month', "public"."spree_orders"."completed_at")
ORDER BY date_trunc('month', "public"."spree_orders"."completed_at") ASC

Notes:

Output variable for some queries have been renamed to "value" instead of usual "count".