How to Generate PDF Files from a SQL Database

Learn How to Generate PDF Files from a SQL Database

PDF reports are an integral part of almost every business process. Typical use cases are, for example, quotations, invoices or KPI reports.

To generate a PDF file from an SQL database, all it takes are three steps: (1) create an SQL database, (2) design a dynamic PDF report, and (3) publish your PDF report to the web.

For this tutorial, we use Five, an application development environment with a built-in SQL database modeler, and an integrated PDF report generator. Make sure to sign up for free to follow along.

Using Five to Generate PDF Files from a SQL Database

Generating a PDF file from data stored in a SQL database is a common development task. Traditional SQL admin tools or GUIs, however, do not support PDF report generation.

Five is a rapid application development environment that supports database creation, SQL queries, and PDF report generation. It also comes with many other useful features to build a web app such as forms, charts, dashboards, etc.

Let’s explore how we can go from a SQL database to a PDF file in a few simple steps.

Example: Generating PDF Files on A SQL Database

Here is an example of an online quotation generator developed in Five. This template app is available when you sign up for one of Five’s subscription plans.

The quotation generator is running on a custom SQL database, created and maintaining in Five. It generates a standardized quotation PDF, including bill-to and ship-to addresses, or quotation line items.

To try out the report generator template, click Quotations in the menu on the left, select an existing quotation or create a new one, and then click the Generate PDF button.

Five generates the PDF inside the web app, and users can download the PDF.

Free Online Quotation Generator

Preview the Template App

Building a web app that turns form inputs into a PDF file stored inside a database is a straightforward exercise in Five.

Here’s a simple scenario: your business needs a new monthly inventory report (yet again). Let’s check out how quickly we can build this in Five.

1. Creating the SQL Database

First up, Five gives developers a dedicated MySQL database. This built-in MySQL can be created using Five’s Table Wizard or its visual Database Modeler.

We will generate our report on top of this integrated MySQL database. Of course, a report can also be generated on any external, pre-existing relational database. To use your existing database, connect it to Five, using a connection string.

Here’s what our database looks like for this example. We have a single table that stores information about Warehouses.

To create this database, follow these steps:

1. Sign up for Five.
2. Login to Five and create a new application by clicking on Applications.
3. Next, click the yellow Plus button.
4. Give your application a Title, and click on the Tick mark in the top right corner to save your first app.
5. Now click on the blue Manage button in the top right corner.
6. Next, click on Data > Table Wizard.

If you are struggling with any of these steps, here is a video tutorial:

2. Creating PDF File on a SQL Database

Next, click on Visual > Reports.

You can now manage how your PDF file will look. Five lets you create PDF reports on any data source, including its built-in MySQL database, any SQL query, or any external database connected to Five.

Inside Reports,

1. Click the yellow Plus icon to generate a new report.
2. Give your report a Title, such as a Warehouse Report.
3. Click on Data Sources, and select the Warehouse Table.
4. Go back to General, and click on Click to add in the Template field.

You are now inside a rich text editor that lets you design PDF files. You can do so in Five’s text editor, or by using code (HTML and CSS). The report generator also lets you select any data field from your database or data source.

For example, you can add a table to a report that contains each field stored inside the warehouse database table. To do so,

1. Add a table to the rich text editor by clicking the Table icon. Choose a 3 x 2 table, because the warehouse table only has two data fields: Location and ID.
2. Next, open up the Insert Fields functionality shown here:

You can use the Insert Fields functionality to add your database captions and records to the PDF document. Here’s how to set this up:

Let’s understand what we are doing here:

1. We are using one of Five’s helper functions: the built-in each function, which tells Five to get each record stored in the database.
2. At the bottom, we choose the fields that we would like to include in our report. In our case, we’re choosing our Warehouse SQL table as the data source, and we pick the records of our location and ID fields.

If you don’t enjoy working with the rich text editor, you can design your PDF report in HTML and CSS. To understand how this works, copy this piece of HTML into the code view of the report generator.

Open the code view by clicking on </> in the top left corner of the report generator.

<div><strong>Warehouse Report</strong></div>
<div><strong><br></strong></div>
<div><strong><br></strong></div>
<table style="width: 100%;">
    <tbody>
        <tr>
            <td>Location</td>
            <td>ID</td>
        </tr>
        <tr class="fiveReportHelper" contenteditable="false">
            <td class="fiveReportHelper">{{#each @root.[Warehouse].[Records] as | Warehouse |}}</td>
        </tr>
        <tr>
            <td>{{Warehouse.[Location]}}</td>
            <td>{{Warehouse.[ID]}}</td>
        </tr>
        <tr>
            <td>{{/each}}</td>
            <td><br></td>
        </tr>
    </tbody>
</table>
<div><strong><br></strong></div>
<div><strong><br></strong></div>

Save everything by clicking the Tick marks.

IMPORTANT: Don’t forget to add your report to the menu of your application.

1. Click on Visual > Menus > Yellow Plus Icon
2. Fill in the Caption of your menu item with Warehouse Report.
3. Select the WarehouseReport (Report) in the Action drop-down field.
4. Save.

3. Preview your PDF File

To preview your PDF report that now turns the data stored in your SQL database into an easy-to-use, printable, and shareable PDF document, launch your application by clicking the ▶️ button in the top right corner of your development environment.

After a few seconds, the application will launch, and you’ll see your PDF report right in front of you!

You might be wondering why this report is empty. Well, we didn’t add any data to our database table.

But of course, we could have done so by importing a CSV, or by creating a form in our application for users to add data. You can quickly add a form to your app using Five’s Form Wizard, which can be found inside the development environment by clicking on Visual > Form Wizard.

Last, we could have spent more time on the formatting of our SQL report (and the look of the application surrounding it) by using the rich text editor or by customizing Five’s application themes.

Generate PDF from a MySQL Database – Webinar

Watch our Developer Advocate Pranoy explain the PDF report generator in more detail in our full product demo here (to jump straight to the SQL report writer start watching from 21m and 40s):

Get Help

If you are struggling with generating your PDF file, connect with us here. Our team of in-house experts can help you with:

1. Your database model;
2. Your application design; and
3. Your PDF report design.

Generate a PDF File on a SQL Database: Getting Started

Sign up for free and get started with Five.

Sign Up Today
Generate PDF Files on a SQL Database