Documents Generation from MySQL

Document generation from MySQL

MySQL is a robust, scalable, integrated, secure yet simplified DBMS that can hold all of an organization’s sensitive, complex, and mission-critical data all together in a place. Business users create various documents from this data including contracts, invoices, statements, etc.

To generate Word and PDF documents, the software needs to connect to the MySQL database, run queries to extract data, and use that data to generate DOCX/PDF documents.

There are multiple systems and approaches available. Each of them has its advantages and disadvantages. Here, we are looking for an approach that’s business user-friendly and involves no code/less code. Let’s explore the popular choices.

Popular Approaches

PDF Document Generation:

Approach1: open-source PHP libraries

There are a lot of third-party open-source PHP libraries such as fpdf, tcpdf, or wkhtmltopdf that generate PDF documents from MySQL database. While these are free, the below are their main disadvantages.

Building the PDF layout is very tedious. It’s not easy to introduce a new template or make changes to an existing template. The more complex it becomes, the harder it gets to generate good PDFs, no matter which package you pick. Often, they don’t support your company styles/fonts.
Every time a new template comes in, it has to be coded again by developers. If your organization uses a lot of templates, this approach becomes maintenance-heavy.

Approach2: puppeteer (headless chrome)

The logic was to fetch what was needed from the database, render an HTML file from a template, pass in the DB data, then get the puppeteer to load the page and render it to pdf. Then return the pdf to the web app. Using puppeteer means that any CSS you use in chrome can be rendered to a pdf.

Puppeteer hangs for large pdf generation. Hence, it’s not suitable for the generation of large PDFs or large volumes of PDFs generation.
As in the above approach, each template has to be coded. Thus, it’s a time-consuming process. Moreover, it’s maintenance-heavy. Every time a template changes, the underlying code needs to change as well.

Word Document Generation

Out of the PDF and Word, surprisingly Word document creation is harder. Let’s assume you are given a Word document template with dynamic fields (spaces) like name, address, etc. to be filled in from the database.

To do this, through an ODBC connector, create DNS and use Word mail merge to create Word documents.

Mail merge itself is tedious to use and offers very limited functionality. It’s useful only for creating simple DOCX documents.
Business users find it hard to work with database connections. This is not for business users.
It’s only available on your network and to a select few users. External users or external-facing systems can’t use this for Word documents generation.

In all the above approaches, developers have to develop templates and assist business users in document generation. How about giving the control back to them in template management through DIY document generation?

EDocGen Document Generation Software

What separates document generation software from other approaches is, that it allows the generation of Word and PDFs from existing templates. Thus, business users can generate documents themselves without needing much help from the IT team.

It is an API-first software. Users can generate documents either through the system’s UI or you can integrate it with your apps.

If you’re looking to generate complex documents containing multiple tables, lists, and nested lists then EDocGen is the answer. The generation of documents is very simple. It is a 2-step process.

Connect to the database using the connection string. Fetch the DB data to populate the PDF/Word/PPTX document templates. For fetching data, users can use the native queries for document generation.
Generate documents in bulk in the desired format.
Optionally, you can distribute the generated documents through various channels.

Step1: Connect to the database

In the connection screen, select the SQL server from the drop-down. Then enter the connection URL in "username@hostname[:port]/DatabaseName" format and the password.

Let’s see how it works. Below are the sample MSWord invoice template and associated query. The system populates the table, image, and dynamic text fields in the template.

SELECT
  invoice.inv_no,
  `logo`,
  `date`,
  `trms_pymnt`,
  `po_ref`,
  `name`,
  `address`,
  `contact`,
  `Email`,
  `Phone`,
  `myhtml`,
  para,
  CONCAT(
    '[',
    GROUP_CONCAT(
      CONCAT(
        '{"amnt":"',
        amnt,
        '", "descrptn":"',
        descrptn,
        '"}'
      )
    ),
    ']'
  ) IT
FROM
  `invoice`,
  `inv_item`
WHERE
  invoice.inv_no = inv_item.inv_no
Group BY
  invoice.inv_no;

A MySQL document Store allows developers to create either traditional SQL relational applications or schema-less document databases. This eliminates the need for a separate NoSQL document database. You can store both relational data and JSON documents together within one database. For example, both data models can be queried in the same application and results can be in a table, tabular, or JSON format.

In the below query each city becomes an element in an array. We simply pass the Name column (the name of the city) to the JSON_ARRAYAGG() function. We also use a GROUP BY clause to group the results by State. These queries are advisable for creating tables, nested tables, nested lists, etc in your documents.

SELECT
    State,
    JSON_ARRAYAGG(Name) AS 'Cities'
FROM City
WHERE CountryCode = 'US'
GROUP BY State;

Step2: Generate Documents

Users can generate documents in various formats including PDF, PPTX, DOCX, TXT, etc. Select the document format from the drop-down and input a name for your documents. You can even prefix the name with one of the dynamic fields.

Enter the file name and prefix for individual documents before clicking the generate button. The output in ZIP format would contain generated documents.

If you’re generating PDFs, the system provides options to add watermark and password protection.

Step3: E-Sign/Email

This is an optional step. You can distribute the generated documents for e-Signature and Email.

Why EDocGen

It offers a complete solution for the automatic generation of complex documents that meet the laws and regulations. It makes it easy for business users to automatically fill in previously created templates with data from the MySQL database.

Schedule a demo to explore SQL to Word generation.

MySQL to Documents - Word, PPTX, and PDF