OFN API Handbook
  • Welcome!
  • Quick Start
  • OFN API V1
    • Overview
    • Authentication
    • Customers
  • OFN API V0 - unsupported
    • Overview
    • Reports
    • Products
  • N8N - integrations server
    • Introduction
    • How to import/use a workflow from a file or API handbook
    • Authenticating Apps
    • Email a Stock Report
  • Adding Customer List to Airtable
  • Adding Product List to Airtable
  • Tips, Tricks, and Common Issues
  • Postgres Database
  • Creating Customers (with Tags) on OFN using V1 API with data from a CSV/XLSX file
  • Common SQL Queries (on-going)
  • Global Metrics Dashboard
  • DFC - n8n experiment
  • Product List Export
  • Product List Export (OFN Product Upload CSV format)
  • Import customers contacts from OFN to newsletter
  • Invite OFN customers to subscribe to a newsletter
  • Automated Quickbooks Customer Invoicing
  • Low Stock Alert for Producers
  • Enterprise Dashboard for Customers
  • Connecting Square POS with OFN for updating stock values, sales analytics etc.
  • Webhooks
    • Overview
    • Setup
    • Event types
Powered by GitBook
On this page

Postgres Database

How to connect with Postgres and run queries from n8n

PreviousTips, Tricks, and Common IssuesNextCreating Customers (with Tags) on OFN using V1 API with data from a CSV/XLSX file

Last updated 5 months ago

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 )

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

openfoodnetwork.org.au