Creating PDF's from your MySQL data with APITemplate.io and n8n

I sounds weird but it isn't easy to create nice PDF documents without doing a lot of code.

Introduction

This has been on my list for a while for a customer and finally I got some time to solve it for now. Not completely for free for everyone (depends on the usecase) but free for some.

The problem

Although we all love online dashboards, dynamic data and charts. We sometimes need old-fashioned PDF documents. Not me but others :-)

It’s common in organisations, you have the print and read type of people and you have the read on screen type. I’m the last but many are the first type.

So the main issue is how to get structured data in an easy and nice way into a PDF.

The solution

Yes i’m lazy like everyone and I rather integrate an external service to do this instead of doing it myself. Why?

• if you buy a professional API subscription from a company, you are sure they will support you because they make money from it

• they provide the updates

• they keep the service up

All good arguments to combine API’s in your solutions.

The details

I will give you the overview of the total solution but will not tell you how to configure the credentials etc. We use the following tools:

• n8n workflow automation (we configure private cloud solutions with n8n)

• APITemplate.io (you need an account)

The workflow

It’s simple, it has 7 steps and can be shortened to 5 (if you skip the deletion of the file on the CDN).

Step 1

You need to connect n8n to your database with can be on the same server (when we create a private instance we facilitate MariaDB). Obviously we don’t share credentials but we show you some details.

In our case we created a test table invoice which contains the fields you can see on the right.

Step 2

In this step we need to created a JSON which we call invoiceLines. This is basically a variable node in N8N which stores the JSON.

We do it this way because we know N8N can be picky about the JSON you supply. This has worked for us time after time.

The JSON is like this:

{

"item_name": "{{ $json.name }}",

"unit": {{ $json.amount }},

"unit_price": {{ $json.unitprice }},

"total" :{{ $json.total }}

}

Step 3

Next step is to Aggregate the results into one JSON string. We use the Aggregate node for this.

Step 4

Then we create a variable node which combines some JSON.

{

"document_id": "Test",

"items":

{{ $json.invoiceLines.toJsonString()}}

}

The end result is this which is perfect for APITemplate.io

Step 5

We are now configuring APITemplate.io for use with the API. You need to get the API key from the dashboard and use it in the node. We use the sample invoice from the PDF editor.

In this way the JSON gets merged with the PDF and also downloaded.

Step 6

Normally you don’t need step 6 and 7 but I’m privacy minded so I don’t like my files to reside on the APITemplate.io site although it is perfectly safe.

Here we store the transaction ID of the generated PDF.

We need this transaction ID to get to the file and delete it.

Step 7

Step 7 is an HTTP node which you use to delete the created file after you downloaded it. I never leave traces anywhere with privacy related info.

You need to send your API key which you got from APITemplate.

Because we don’t ask a subscription feed yet we try to collect some money to be able to host Open source apps. to write articles about. You can support this article by visiting APITemplate.io.