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.

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, '"}' ) ), ']' ) ITFROM `invoice`, `inv_item`WHERE invoice.inv_no = inv_item.inv_noGroup 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 CityWHERE CountryCode = 'US'GROUP BY State;

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.

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.