Database functions, also known as stored procedures, have been a feature of relational databases for years. They were designed to let developers execute data or compute-intensive logic directly where the data lives—in the database—thereby reducing network latency.

However, even in 2023, this relational database feature hasn’t gained widespread adoption within developer communities.

Database communities and vendors keep enhancing the database function user experience to appeal to application developers. For example, you can now create a database function in your favorite languages, including Java, JavaScript, and Python. But, despite these advancements, the use of database functions has been in decline.

What should change to set things right? I believe we should begin by revisiting developer experience fundamentals. The initial step is to let developers create and optimize their database functions in a similar manner to how they handle the rest of their application logic: within their preferred IDE and using their favorite programming language. This is the primary goal that the first version of the PgCompute extension seeks to address.

Introducing PgCompute

PgCompute is a client-side PostgreSQL extension that allows for the execution of JavaScript functions on the database directly from the application logic. Similar to the rest of the application logic, you can create, optimize, and maintain database functions by using your preferred IDE and programming language.

As with most PostgreSQL extensions, PgCompute is open-source and was created to meet the needs of the broader PostgreSQL and developer communities. This is my part-time project that Yugabyte fully supports, allowing me to spend time on initiatives that benefit the PostgreSQL ecosystem.

To understand its practical application, let’s consider a basic Node.js program example.

Basic PgCompute Example

Begin by initializing a new Node.js project and adding the pg (the PostgreSQL driver for Node) and pg-compute modules:

npm init
npm install pg
npm install pg-compute

Next, launch your IDE of choice, whether it’s Visual Studio or WebStorm, and create an index.js file in your project’s root directory. Populate it with the following content:

// index.js

const { Client, ClientConfig } = require("pg");

const { PgCompute } = require("pg-compute");

const dbEndpoint = {
    host: "localhost",
    port: 5432,
    database: "postgres",
    user: "postgres",
    password: "password"
}

The code imports the pg and pg-compute modules and creates a configuration object with database connectivity settings. We’ll deploy a database instance shortly.

Next, add the following ordinary JavaScript function to the index.js source code. Its purpose should be self-explanatory:

// index.js

function sum(a, b) {
    let c = a + b;
    return c;
}

Finally, let’s say you want to execute this function on PostgreSQL. Not a problem! Add the following snippet to index.js:

// index.js

(async () => {
    // Open a database connection
    const dbClient = new Client(dbEndpoint);
    await dbClient.connect();

    // Create and configure a PgCompute instance
    let compute = new PgCompute();
    await compute.init(dbClient);

    // Execute the `sum` function on the database
    let result = await compute.run(dbClient, sum, 1, 2);
    console.log("Result:" + result);

    await dbClient.end();
})();

The most interesting part of this last snippet is — compute.run(dbClient, sum, 1, 2). This is where the app uses PgCompute to calculate the sum of 1 and 2 on the database end. That’s it! You just need to pass a function object and arguments (if any) to PgCompute, and it will take care of the rest.

Now, let’s test the app with a real PostgreSQL instance:

1. Start PostgreSQL with the plv8 extension. Note: PgCompute depends on plv8 which is a server-side PostgreSQL extension that enables JavaScript engine support in the database.

   mkdir ~/postgresql_data/
   
   docker run --name postgresql \
   -e POSTGRES_USER=postgres -e POSTGRES_PASSWORD=password \
   -p 5432:5432 \
   -v ~/postgresql_data/:/var/lib/postgresql/data -d sibedge/postgres-plv8

2. Connect to the database and enable plv8:

   psql -h 127.0.0.1 -U postgres
   
   create extension plv8;

Once the database is running, go to the sample app’s root directory and launch it:

node index.js

// Result:3

As expected, the app calculates that the sum of 1 and 2 is 3. This calculation took place within PostgreSQL, with PgCompute smoothly managing the sum function’s delivery and execution on the database end.

Automatic Deployment Mode

By default, PgCompute uses DeploymentMode.AUTO, which automatically deploys and updates functions in the database as needed. This is why the sum function from the previous example was transparently executed on PostgreSQL.

Now, let’s assume that we want to change the current implementation of the sum function:

As expected, the app calculates that the sum of 1 and 2 is 3. This calculation took place within PostgreSQL, with PgCompute smoothly managing the sum function’s delivery and execution on the database end.

Automatic Deployment Mode

By default, PgCompute uses DeploymentMode.AUTO, which automatically deploys and updates functions in the database as needed. This is why the sum function from the previous example was transparently executed on PostgreSQL.

Now, let’s assume that we want to change the current implementation of the sum function:

// index.js

function sum(a, b) {
    let c = a + b;
    return c;
}

to the following:

// index.js

function sum(a, b) {
    return (a + b) * 10;
}

Simply update the function’s logic and restart the app to check that the new version of the function is automatically picked up by PgCompute and executed on the database.

node index.js

// Result:30

With PgCompute, you can work with database functions just as you would with the rest of your application logic. Change and optimize the functions freely in your IDE and run them once you’re happy with the current implementation.

NOTE: For those who want more control, opt for DeploymentMode.MANUAL to predefine functions in the database and use PgCompute solely for function invocation, bypassing auto-deployment.

Advanced PgCompute Example

Now that you understand how PgCompute can improve your work with database functions, let’s explore a realistic scenario that highlights their true utility. Because–let’s face it—PostgreSQL is for much more than simple math operations.

Database functions are ideal for data and compute-intensive tasks, such as traversing and manipulating large datasets. So, let’s calculate the monthly compound interest for savings accounts at a bank. Usually, banks manage millions of such accounts. As this is a basic banking operation, sending millions of accounts to the application layer for calculations would be excessive.

To solve this task efficiently, simply add the following JavaScript function to your application logic:

/** A naive implementation of the business logic for the demo purposes. */


function addMontlyInterestRate() {
    const query = plv8.prepare('SELECT * FROM savings_account');
    let accountsCnt = 0;

    try {
        const cursor = query.cursor();

        try {
            let account, monthlyRate, interestForTheMonth;

            while (account = cursor.fetch()) {
                // Calculate monthly interest rate by divide the annual rate by 12.
                monthlyRate = (account.annual_rate / 100) / 12;

                // Calculate interest for the month
                interestForTheMonth = account.principal * monthlyRate;

                // Updating the principal by adding the calculated interest rate
                plv8.execute(
                    'UPDATE savings_account SET principal = $1 WHERE id = $2',
                    [account.principal + interestForTheMonth, account.id]);

                accountsCnt++;
            }

        } finally {
            cursor.close();
        }
    } finally {
        query.free();
    }

    return accountsCnt;
}

Use PgCompute to execute the function on the database without having to move a single customer account over the network:

// Create and configure a PgCompute instance
let compute = new PgCompute();
await compute.init(dbClient);

// Calculate and add the interest rate on the database end
const result = await compute.run(dbClient, addMontlyInterestRate);

That’s it. It’s that simple. You can find a complete example here.

This Is Just the Beginning…

My last example with savings accounts merely scratches the surface of what database functions can do. As my five years with the Apache Ignite community have shown, every sector—from finance to biotech, retail to transportation—has unique use cases of stored procedures. Many Java, C#, and C++ developers from the Ignite community are adept at using database functions (known as ‘compute tasks‘ in Ignite) for data and compute-intensive operations.

PgCompute aims to bring the same advantages and developer experience to the broader PostgreSQL and developer communities. Keep an eye out for upcoming features, including native integration with Supabase and expanded language support.