The gist is as follows: the JS code that is executed on the DB side can run on the developer's local computer by using node.js, which works on the same v8 interpreter. The plv8.execute special function is replaced by a function from the pg-native npm library, which calls the outer DBMS. I will demonstrate a set of tools chosen by me that enables the following:
- writing JS code in the IDE you prefer and enjoy syntax highlight;
- code debugging in real time (with breakpoint, watch, etc.);
- writing of auto-tests (unit-tests), with a variety of options: Postgres, SQLite, mocks;
- deploying your code in the DBMS;
- usage of additional npm packages (the issue is that all code of the plv8-based function should be included in the function's body, in one file).
You can use this tool regardless of the programming language that you use for the backend. However, it becomes more flexible if you use languages with static typing (like Java, C#, etc.). For the tasks where the backend is an intermediate layer between the frontend and DBMS, logic (or its part) can be placed in plv8/js with dynamic typing, which will simplify the development process.
In addition to the development of new functions on plv8, the framework provides a set of ready-made functions for CRUD operations. These functions are universal, they aren't tied to a certain database structure, and they can work in any project. If you use them, you can do less backend development, in some projects to a very significant extent.
The installation of the plv8 extension is the most complicated part of working with it. However, I have good news: my colleagues prepared Docker files and Docker images for PostgreSQL 13 with pre-installed plv8! So your start with plv8 will be super simple: you need to deploy the Docker container using just one command!
Docker file: PostgreSQL 13 + plv8 v2.13.15
Demo project for you to participate in the tutorial
For the tutorial, you need to install the following:
Node.js (the most desirable is LTS)
IDE for JS (i.e., free Visual Studio Code)
The built-in partitioning mechanism in PostgreSQL has been developing for several years. However, there is no magic button that would transform an ordinary table into a partitioned one. I will explain how to implement partitioning in a production system, how to prepare your table and apps for partitioning, and what pitfalls can make a DBA's life harder. We will also take a deep dive into various techniques of transferring data between partitions, also considering their pros, cons and limitation.