> For the complete documentation index, see [llms.txt](https://ofn-user-guide.gitbook.io/ofn-api-handbook/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://ofn-user-guide.gitbook.io/ofn-api-handbook/postgres-database.md).

# 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*](http://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.

![](https://2749008000-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FNv32980j4B1B2UoS6UMi%2Fuploads%2FAoL39Ol8JINdE32AlPCM%2Fimage.png?alt=media\&token=ef58adf8-6c59-49a5-8d05-63058bc5e84a)

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

![](https://2749008000-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FNv32980j4B1B2UoS6UMi%2Fuploads%2FbDI9abFdL67fs7gXO25f%2Fimage.png?alt=media\&token=ac989fa8-407d-4ef7-a916-60063aaf4d9b)

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

{% code overflow="wrap" %}

```sql
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
```

{% endcode %}

**Total Sales for the last month:**

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