Document generation from MySQL

MySQL is a powerhouse. It’s robust, secure, and capable of holding all your organization’s mission-critical data in one place. But raw data sitting in a database isn't enough; business users need to turn that data into tangible assets like contracts, invoices, and statements.

To make this happen, you use a software that connect to the database, query the data, and generate PDF or Word documents.

While there are many ways to achieve this, most come with baggage. We are looking for the "Holy Grail": a strategy that is friendly for business users and requires little to no code. Let's look at the common approaches and why they often fall short.

The Current Landscape

PDF Generation (The Developer-Heavy Route):

Many teams turn to coding solutions to build PDFs. This usually breaks down into two methods:

Approach 1: open-source PHP libraries

Libraries like fpdf, tcpdf, or wkhtmltopdf are popular because they are free. However, "free" comes with a hidden cost:

1. Tedious Design:Building a layout programmatically is painful. It’s difficult to make a document look good, and support for specific company fonts or styles is often lacking.
2. Maintenance Nightmares:Every time marketing wants to change a template, a developer has to rewrite the code. This creates a bottleneck and heaps maintenance work onto your tech team.

Approach 2: Puppeteer (headless chrome)

The logic here is to build an HTML page using the database data and then use a "headless" browser (Puppeteer) to take a picture of it as a PDF.

1. Performance Issues:Puppeteer tends to hang or crash when generating large files or high volumes of documents.
2. Still Code-Heavy:Just like the PHP libraries, every template is code. If the template changes, the underlying code must change. It is not a flexible solution for business users.

Word Document Generation

Surprisingly, generating an editable Word document is often harder than making a PDF. The standard method involves using an ODBC connector to run a "Mail Merge."

1. Limited and Clunky: Mail Merge is tedious and offers very basic functionality.
2. Not for Business Users: Setting up database connections is technical work. It scares off non-technical users.
3. Local Only: This usually only works inside your office network. Remote users or external-facing systems generally can't use this method.

The solution? We need to stop relying on hard-coded templates and give control back to the users. It is time to move toward DIY document generation that empowers business users to manage their own templates without writing a single line of code.

EDocGen - Generate Documents from MySQL Database

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

1. 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.
2. Generate documents in bulk in the desired format.
3. 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.

You can native MySQL queries to pull data from database. Below is the corresponding query to populate this template.

SELECT
    invoice.inv_no,
    `logo`,
    `date`,
    `trms_pymnt`,
    `po_ref`,
    `name`,
    `address`,
    `contact`,
    `Email`,
    `Phone`,
    `myhtml`,
    para,
    JSON_ARRAYAGG(
        JSON_OBJECT(
            'amnt', inv_item.amnt,
            'descrptn', inv_item.descrptn
        )
    ) AS items
FROM invoice, inv_item
where invoice.invoice.inv_no = inv_item.inv_no
GROUP BY
    invoice.inv_no;

Step 2: 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 a watermark and password protection.

Step3: E-Sign/Email

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

Automation

The process of entering the query and connection details each time you generate documents can be both time-consuming and tedious. The system offers features to streamline these steps through automation.

To create automation, follow the below steps. After that, the generation of PDF documents is as simple as clicking a button.

The system provides the functionalities to pull data from multiple sources—including databases (like SQL, Oracle, and MongoDB) and CRMs (like Salesforce and Dynamics 365)—and seamlessly merges the data for document generation.

Also, the system offers options to schedule the generation every day/month/year.

There is also an option to automatically create printer-friendly PCL5 and PostScript files and upload them to an FTP server.

The system enables you to automatically populate templates with data queried across different schemas and databases. This means you can effortlessly merge information from a variety of sources—such as multiple databases, or even a combination of databases and CRM systems—into a single, cohesive document. It simplifies complex data handling, saving time while ensuring accuracy and consistency across all generated outputs.

Why EDocGen

It offers a complete solution for the automatic generation of complex documents from your MySQL database. It makes it easy for business users to introduce new templates or make changes to existing templates and generate documents from the database without depending on IT teams.