Pet Project: How to Run a JavaScript Code in the Database
A pet project is a project that a developer, alone or with a group of other enthusiasts, does in his or her spare time.
Sibedge encourages their engineers to have pet projects because:
Recently our .NET engineer Alex introduced a PLV8 Framework, his original development. It makes it easier to work with flexible and hierarchical data structures, especially in statically typed languages. It allows to conduct full-scale development in the databases together with your teammates. Plus, the framework helps reduce labor costs when solving typical CRUD ( i.e. Create, Read, Update, Delete) tasks. First, Alex was working on the project on his own but later three Sibedge engineers joined him.
There were no ready-to-use PostgreSQL packages for PLV8, so the engineers built the framework manually from the GitHub sources. This used to consume a lot of time, waste the traffic, and require a lot of disk space to store temporary files. To install the PLV8 extension quickly and easily, Sibedge engineers created a tool for Linux (https://github.com/sibedge-llc/plv8-build/).
The PLV8 Framework will resolve all of these issues. The idea is to run JavaScript code locally using Node.js for development, debugging, testing, and deployment.The script sends the source code to the stored procedures in the database. Node.js uses the same V8 engine as the PLV8 extension. This helps to ensure that the execution results will not differ.
PLV8 lets process SQL queries using the plv8.execute command. When executing the code locally, PLV8 Framework replaces the command to access an external DBMS using the pg-native npm library. The library must work with PostgreSQL synchronously since the PLV8 code is synchronous, too.
For unit testing, the PLV8 Framework allows to execute plv8.execute via SQLite (sqlite-sync npm package) and to use mock.
To read the data, Alex developed his own universal GraphQL server. Most of the server-side logic is written in the PLV8 Framework, and some part is in .NET. Query fields match the database tables. Nested fields correspond to table columns and tables associated by foreign key. The server returns a data schema description for GraphQL clients. It also supports:
Request example:
CRUD functions support authorization. The framework helps limit the access to database tables by letting the user see only his or her records. This works for the tables with a user ID field and related tables.
Also, it makes sense to write your own functions on PLV8 when data structure should be flexible and multi-level (e.g. to build reports). This makes development easier and faster, especially if you use statically typed languages. You no longer need to write classes for each data structure when solving such tasks.
For many tasks, a backend implementation is a smarter choice (e.g. .NET backend implementation). Say, for algorithmic tasks or interaction with other services. Still, the PLV8 Framework requires less time and effort for solving certain backend development tasks. Sometimes the difference is signifficant.
Sibedge encourages their engineers to have pet projects because:
- They bring joy and let the developers grow.
- They encourage engineers to take initiatives.
- This way the company attracts talented specialists who want to realize their potential.
Recently our .NET engineer Alex introduced a PLV8 Framework, his original development. It makes it easier to work with flexible and hierarchical data structures, especially in statically typed languages. It allows to conduct full-scale development in the databases together with your teammates. Plus, the framework helps reduce labor costs when solving typical CRUD ( i.e. Create, Read, Update, Delete) tasks. First, Alex was working on the project on his own but later three Sibedge engineers joined him.
Concept
What is PLV8? It is a PostgreSQL extension that helps to execute functions in JavaScript. The name consists of two parts — PL (Procedure Language) and V8 (open source JavaScript engine).There were no ready-to-use PostgreSQL packages for PLV8, so the engineers built the framework manually from the GitHub sources. This used to consume a lot of time, waste the traffic, and require a lot of disk space to store temporary files. To install the PLV8 extension quickly and easily, Sibedge engineers created a tool for Linux (https://github.com/sibedge-llc/plv8-build/).
Database Development Challenges and Solutions
When developing a code in the database (in any language), there are several problems:- the backend code version must match the database version (the migration mechanism partially solves this issue, but it is not optimal when the changes are frequent);
- conflicts occur when several developers edit stored procedures;
- Unit tests are not available since only backend testing can be performed;
- there are no debugging tools;
- there is no unified deployment mechanism; therefore development is usually done in a DBMS and the final version is then migrated.
The PLV8 Framework will resolve all of these issues. The idea is to run JavaScript code locally using Node.js for development, debugging, testing, and deployment.The script sends the source code to the stored procedures in the database. Node.js uses the same V8 engine as the PLV8 extension. This helps to ensure that the execution results will not differ.
PLV8 lets process SQL queries using the plv8.execute command. When executing the code locally, PLV8 Framework replaces the command to access an external DBMS using the pg-native npm library. The library must work with PostgreSQL synchronously since the PLV8 code is synchronous, too.
For unit testing, the PLV8 Framework allows to execute plv8.execute via SQLite (sqlite-sync npm package) and to use mock.
CRUD operations
The PLV8 Framework gives more than just new JS functions development tools. To perform CRUD operations, the framework has universal functions that will work in any DBMS on any project. The data is exchanged in JSON format where the keys match the columns of the database table.To read the data, Alex developed his own universal GraphQL server. Most of the server-side logic is written in the PLV8 Framework, and some part is in .NET. Query fields match the database tables. Nested fields correspond to table columns and tables associated by foreign key. The server returns a data schema description for GraphQL clients. It also supports:
- filtering;
- sorting;
- paging output;
- grouping;
- aggregate functions;
- filtering groups with HAVING.
Request example:
CRUD functions support authorization. The framework helps limit the access to database tables by letting the user see only his or her records. This works for the tables with a user ID field and related tables.
Processing capacity
The team ran a series of tests. They wanted to retrieve multi-level nested data using PLV8 and a SQL query with aggregate DBMS functions. The number of top-level returned objects varied from 1 to 20 000. Tests showed that when processing large amounts of data (18–20 thousand elements), PLV8 is slower than SQL queries by about 30%. When there are eight to 11 thousands elements, PLV8 is as good as SQL. With less data, the framework works even faster.
Practical use
There is no need to develop the entire backend on the PLV8 Framework. You can use the classic application architecture (e.g. Asp.NET Core) and apply the PLV8 functions only when you really need them. For example, for CRUD operations. Then the cost of the backend development will be minimal, since the universal functions in the framework allow to implement 90–95% of the functionality.Also, it makes sense to write your own functions on PLV8 when data structure should be flexible and multi-level (e.g. to build reports). This makes development easier and faster, especially if you use statically typed languages. You no longer need to write classes for each data structure when solving such tasks.
For many tasks, a backend implementation is a smarter choice (e.g. .NET backend implementation). Say, for algorithmic tasks or interaction with other services. Still, the PLV8 Framework requires less time and effort for solving certain backend development tasks. Sometimes the difference is signifficant.