Martins Blog

Using PDF-LIB to generate PDFs in the database

Recently, someone asked if it was possible to use PDF-LIB with Multilingual Engine (MLE) and APEX. That was a fun challenge to solve, and you can read more about it here.

Turns out, it sure is possible to use PDF-LIB, but before you consider adopting it, please also review the other options available to you – APEX supports PDF generation out of the box, and there’s an entire Document Generation Service for you to use as well.

Don’t forget to ensure every 3rd party’s source code license is suitable/compatible with your project. You most likely must also get sign-off from your IT security department before adding 3rd party code to yours.

First attempt

Self-contained JavaScript modules are easy to use with MLE: just download the ESM (ECMAScript Module) from your favourite CDN (Content Delivery Network), import it into the database, and use it.

Let’s try this. Start by downloading PDF-LIB from a CDN like jsdelivr in this example:

curl -Lo pdf-lib-1.17.1.js 'https://cdn.jsdelivr.net/npm/pdf-lib@1.17.1/+esm'

Next, you create the MLE module based on the downloaded file, and an MLE environment. Start by connecting to the database using SQL Developer Command Line (sqlcl), then issue the commands to create the module and environment.

sql emily@localhost/freepdb1

...

SQLcl: Release 24.4 Production on Fri Mar 28 08:37:02 2025

Copyright (c) 1982, 2025, Oracle.  All rights reserved.

Connected to:
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.7.0.25.01

SQL> mle create-module -filename pdf-lib-1.17.1.js -module-name pdf_lib_incomplete -version 1.17.1
MLE Module pdf_lib_incomplete created

SQL> create mle env pdf_lib_incomplete_env imports ('pdf-lib' module pdf_lib_incomplete);

MLE env PDF_LIB_INCOMPLETE_ENV created.

SQLcl commands specific to MLE are documented in the SQLcl manual.

As you can see from the module and environment names, this isn’t going to work as planned, but it’s a good example of how to use third-party code in MLE—please read on.

With the MLE module loaded into the database and the corresponding environment created, it’s time for a first test. You can do so in Database Actions, APEX SQL Commands, or DBMS_MLE. The following code snippet was run in APEX’s SQL Commands.

const { PDFDocument, StandardFonts, rgb } = await import  ("pdf-lib");
console.log('command completed successfully');

Unfortunately, this doesn’t work due to a dependency problem:

ERROR at line 1:
ORA-04161: Error: Cannot load ES module: /npm/tslib@1.14.1/+esm
ORA-06512: at line 29
ORA-06512: at "SYS.DBMS_MLE", line 447
ORA-06512: at line 18

In other words, PDF-LIB depends on other modules hosted by the CDN and unavailable to MLE. At this point, rather than recursively hunting down all the missing modules by trial and error, it might be easier to use a module bundler instead.

Bundling PDF-LIB with your code

A previous article described the use of Rollup to create a bundle. At the time of writing, Rollup issue 1645 prevents its use. ESBuild is a suitable alternative.

If you want create a bundle consisting of your code and a 3rd party library, the typical workflow consists of these steps:

  • Firing up your favourite development environment, such as Visual Studio Code. If you use VSCode, don’t forget to install Oracle’s SQL Developer extension; it will make your life easier, promise!
  • Installing the NPM module using npm, yarn, or whichever solution you prefer
  • Writing code and having fun (don’t forget to enjoy yourself)
  • Creating a bundle consisting of your files and their dependencies
  • Loading this bundle into the database as a MLE module

The workflow does not divert significantly from what every JavaScript developer would do in other projects. By the way, if you prefer Typescript over JavaScript, add the transpile step to the list.

Let’s look at each of these steps in more detail.

Start your project

Create a new directory for your project, initialise it as a Git repository (don’t forget to add a suitable .gitignore), and install PDF-LIB as well as esbuild

# create the new directory
[[ -d  pdflib-test ]] || mkdir pdflib-test
cd pdflib-test

# initialise git make sure to add a suitable .gitignore file!
git init .

# install pdf-lib and esbuild using npm (part of node v20/LTS)
npm install --save pdf-lib
npm install --save-exact --save-dev esbuild

# optionally start VSCode
code .

Typically you install a linter and formatter alongside, and a unit test framework. These are out of scope of this article.

Coding

Next, you need to start writing the code and invoke PDF-LIB. This snippet has been taken from the PDF-LIB website and is stored in src/pdfWriter.js. It doesn’t do very much, but if it creates the PDF you’re free to use all its other features 😀

import { PDFDocument, StandardFonts, rgb } from "pdf-lib";

/**
 * Creates a PDF document using JavaScript and saves it as a byte array before
 * persisting it into a table. Relies on the "pdf-lib" library to do so.
 * 
 * A faithful representation of https://pdf-lib.js.org/#create-document
 *
 * @async
 * @returns {Promise<void>} A promise that resolves when the PDF document has been created and saved.
 */
export async function createPDFInJavaScript() {
    const pdfDoc = await PDFDocument.create();
    const timesRoman = await pdfDoc.embedFont(StandardFonts.TimesRoman);
    
    const page = pdfDoc.addPage();
    const { _, height } = page.getSize();
    const fontSize = 30;
    
    page.drawText("created by MLE/JavaScript", {
        x: 50,
        y: height - 4 * fontSize,
        size: 30,
        font: timesRoman,
        color: rgb(0, 0.53, 0.71),
    });
    
    const pdfBytes = await pdfDoc.save();
    
    // now persist the PDF as a BLOB in a table - see below for the
    // table DDL statement
    session.execute("insert into pdf_test (b) values (:pdf)", [pdfBytes]);
}

Bundling

The next step is to create a bundle using esbuild, which will be stored in dist/bundle.js. Feel free to use a better name! Naming is one of the hardest things in IT.

[[ -d dist ]] || mkdir -v dist
npx esbuild src/pdfWriter.js --bundle --outfile=dist/bundle.js --format=esm

This command is frequently used and probably best placed in package.json. Here is the package.json used for this article. All releases were current at the time of writing.

{
  "devDependencies": {
    "@biomejs/biome": "1.9.4",
    "esbuild": "0.25.1"
  },
  "type": "module",
  "dependencies": {
    "pdf-lib": "^1.17.1"
  },
  "scripts": {
    "build": "npx esbuild src/pdfWriter.js --bundle --outfile=dist/bundle.js --format=esm"
  }
}

A call to npm run build creates the bundle.

Creating a PDF inside the database

Let’s add the finishing touches to the project: loading and using the MLE module. The mle create-module command can be used to load the module:

mle create-module -filename dist/bundle.js  -module-name pdfwriter_module -replace

If you plan on exposing PDF-LIB to APEX, or Database Actions, you need an MLE environment:

create mle env pdfwriter_env imports (
    'pdflib' module pdfwriter_module
);

The import name – pdflib – maps to the newly created pdfwriter_module. You can use it in Database Actions, APEX SQL Workshop, and elsewhere.

You can optionally create a call specification to expose createPDFInJavaScript() to SQL and PL/SQL:

create or replace procedure create_pdf
as mle module pdfwriter_module
signature 'createPDFInJavaScript';
/

If you look closely at createPDFInJavaScript() you’ll notice that the PDF is persisted as a BLOB in a table. This table must be created first:

create table pdf_test (
    id number generated always as identity primary key,
   -- additional columns omitted for brevity
    b blob not null 
);

With all the work completed, you can finally create a PDF:

begin
    create_pdf;
end;
/

Use your favourite database development tool – VSCode + Oracle’s SQL Developer extension , for example – to view the contents of the PDF.

Using PDF-LIB in APEX

APEX users can use SQL Commands to create a PDF, or alternatively expose the function in Page Designer. Here is an example of how to invoke createPDFInJavaScript() in SQL Workshop:

const { createPDFInJavaScript } = await import ("pdflib");

createPDFInJavaScript();

Make the PDF available via the usual APEX pages, and you are good to go!

Summary

Community modules available from a CDN sometimes aren’t self-contained. In this case, using a module bundler is the best way to include them in MLE. The bundler is intelligent enough to detect dependencies, storing all the relevant code in a single file that’s easy to load and maintain.