PostgreSQL Functions in Typescript

This is the seventh article in a series on using the Supabase platform instead of writing a backend server. The first several articles will cover some useful PostgreSQL (a.k.a. Postgres) topics. See the first article ("Going Serverless With Supabase") in the series for more background.

This article will cover why you might need PostgreSQL functions for your project, and how to write them in Typescript using the plv8ts NPM package.

The other articles in this series covered Row-Level Security (RLS) policies, sending email from Postgres, and other related topics, but they aren't required reading for this article.

tl;dr

To try the example, copy the example files from Github into a clean directory:

curl  https://raw.githubusercontent.com/Davepar/plv8ts/main/example/package.json -O
curl  https://raw.githubusercontent.com/Davepar/plv8ts/main/example/tsconfig.json -O
mkdir src; cd src
curl https://raw.githubusercontent.com/Davepar/plv8ts/main/example/src/sample_function.ts -O

Then install and build:

npm i
npm run build

The result will be in a subdirectory called sql. To add drop statements before each create function statement, use npm run build -- -d instead.

To use the lint and fix commands, copy the other files from the example directory on Github.

Note: You may be tempted to put the example code into an existing project, but the settings inside the example tsconfig.json are necessary to build the code correctly. See the readme for the project for more details.

What is a Postgres function?

In addition to providing all of the usual database storage functionality, Postgres also allows defining functions for running any type of code that interacts with the database, or with extensions can issue HTTP requests, encrypt data, and many other useful operations.

Functions can be executed via a database query or on a regular schedule.
Trigger functions are a special type of function that are covered in a previous article. They provide a way to execute an action based on a database event.

When possible, it's best to write functions in SQL. Postgres will compile and optimize SQL functions, while other languages can't be optimized as much. However, sometimes the logic is too complicated to represent in SQL. And sometimes, e.g. when you're prototyping, you don't want to waste time on complex SQL statements. That's where PLV8 is great. Assuming you're writing your frontend in Javascript, you can use the same language for your database functions.

The downsides

Since Javascript is an interpreted language, one huge drawback is that syntax errors won't be found until run time. Which means you'll write the CREATE FUNCTION statement, execute it in your database, and then try running the function before realizing it has a syntax error. And you'll do that over and over again. Making matters worse is that editor-based linting and syntax highlighting won't recognize that you have embedded Javascript in your SQL statement.

Testing is equally difficult. How can you easily test Javascript inside a SQL statement?

Introducing plv8ts

The plv8ts NPM package addresses these problems. You can write the functions in Typescript, compile them, and run the included script to convert them into ready-to-run SQL CREATE FUNCTION statements.

The package provides Postgres-compatible type definitions and a mock plv8 library with functions that you can call from your code to mimic the behavior of the PLV8 extension. Check out the source code for a full list of types and functions defined.

Just follow the instructions for the package to get started. To use the SQL files you can execute them with the \i command in the psql Postgres console. Or if you have an UI for your database (which Supabase provides), copy/paste the SQL statement into the SQL editor.

Why use Typescript?

If you're not already familiar with Typescript, it's basically Javascript with types and some extensions. For PLV8 the main attraction is the compiler can catch some errors early, before running the code in the database. The Typescript compiler will print messages for syntax errors, invalid names, mis-matched types, and many other common issues.

The example also includes a strict linting setup that will catch other potential issues. Just run npm run lint to check your code. And the npm run fix command will auto-format your code.

Testing

The plv8ts package also shows how to use the Jasmine testing framework to test your functions before installing them in Postgres. You can mock out the PLV8 functions and test the results of your code. Check out the example for more details.

Conclusion

Hopefully this article helped you out by explaining the purpose of PostgreSQL functions and how to write them in Typescript with tests to have a tight, simple development loop.

Let me know in the comments if you have any questions or anything was unclear. And follow along for future articles on using PostgreSQL and Supabase as a serverless backend.

(Photo by Florian Klauer on Unsplash.)