HTML/JS ORM for PostgreSQL

Description
Simple example
Important qualities
Working principle
Full example
Restrictions
Security
Video

Description

PGORM is tool for working with a PostgreSQL database directly from web page, allows to connect, execute SQL queries, use ORM and receive files.

Implemented as a service that accepts HTTP requests from the browser and returns response depending on access type (see below). The database is configured with list of tables that will be mapped to JavaScript classes. For each table, JavaScript module is automatically generated, be used through the construction import { [Table],[TableArray] } from '/orm/[Schema]/[Table].js';

For simple systems, pgorm is installed on the database server and used independently, for complex systems - on database server or application server, on the main web server (for example, nginx), requests /pgorm/*,/orm/* are redirected.

Access types

ORM	-	working with PostgreSQL table rows as JavaScript objects (object-relational mapping)
SQL	-	execution of SQL queries and commands, the result is returned in JSON format with auto-casting
[GET] file	-	receiving files (.html, .js, etc.), including a JavaScript module for connecting to a database, executing SQL queries, working with tables

Create table invoice and enable object-relational mapping (ORM) for it

-- Create table
create table invoice(
  id serial primary key,
  date date not null default current_date,
  number varchar(10) not null,
  amount numeric(20,2) not null  
);

-- Enable ORM for table, JavaScript modules will be created automatically
call pgorm.orm_table_enable('invoice');

-- Create user and grant privilegies on table
create user user_1 login password 'password_1';
grant all on invoice,invoice_id_seq to user_1;

Create object of class Invoice and save it to database

<!DOCTYPE html>
<html>
<head>  <meta charset="utf-8"> <title>PGORM | Simple example</title> </head>
<body> 

<button id="buttonCreateInvoice">Create invoice</button> <br><br>

Result
<div id="result" style="border: 1px solid black; width: 500px; height: 200px;"></div>

<script type="module">

// Import class for connect to database
import { ORMConnection } from "/orm/pgorm-db.js";
// Import class Invoice from automatically generated module
import { Invoice }       from "/orm/public/Invoice.js";

function createInvoice() {
  try {
    // Create connection, connect to database and set this connection by default
    new ORMConnection().connect('user_1', 'user_1').setDefault();
    // Create object of class Invoice
    let invoice = new Invoice();
    // Set field values
    invoice.setNumber('12/34');
    invoice.setAmount(567.89);
    // Save object, after save autocomplete fields will contain values
    invoice.save();
    // Show object
    document.getElementById("result").innerHTML = "Накладная<br><br>" +
      "ID: "     + invoice.getID() + "<br>" +
      "Date: "   + invoice.getDate().toISOString() + "<br>" +
      "Number: " + invoice.getNumber() +"<br>" +
      "Amount: " + invoice.getAmount();
    // Disconnect from database
    ORMConnection.getDefault().disconnect();
  } catch (exception) {
    // If exception occurs, show it
    document.getElementById("result").innerHTML = "Ошибка<br><br>"+exception.message;
  }
}

document.getElementById("buttonCreateInvoice").addEventListener("click", createInvoice);

</script>

</body>
</html>

Regular use via SQL - ORM classes are created on the basis of existing tables, change the table structure (metadata) is performed via SQL. When performing DDL operations on table (alter table, create index, etc.), trigger fires, which updates the JavaScript module
Modules and class overriding - For each table, two modules are created: an editable one with empty inherited classes and an autogenerated one with superclasses, which is recreated when the table structure changes
Auto type conversion - Implemented automatic conversion of SQL and JavaScript data types, including arrays
Table row and array - Two classes are created for each table: table row (Record) and array of table rows (RecordArray extends Array)
Relationships - Separate methods created to work with table rows related by foreign keys. For object by foreign key (parent), methods are synchronized with class fields. For array of objects by incoming foreign key (children), it is possible to specify auto-filled sorting column
Automethods - Find by unque index methods are automatically created in class of table row, find by non-unque index methods and sorts - in class of table rows.
Naming - Class name for table, field names for columns and relationship names (parent,children) are formed automatically, but can specify them explicitly
Table definition (relation) - Class has static method that returns table definition (relation): schema, name, columns, primary key, relationships
Simplicity and sufficiency - Server sends the modules created by it via HTTP, installation consists in copying the executable file and creation of the service, no third party libraries required (libpq already installed with postgres)

Server connect to database, create JavaScript modules (files) in subdirectory /orm for selected tables, and listen for HTTP requests. For requests with URL /pgorm/* perform database operations (connect, save and load object, etc.) and return result in JSON format. For all others, file requested by URL is return, incl. created module.

Create database objects and enable object-relational mapping (ORM) for them

-- Create schema
drop schema if exists example cascade;
create schema example;

-- Create tables with various columns, view, index and foreign keys
create table example.customer(
  id serial primary key,  
  name varchar(100) not null
);

create table example.invoice(
  id serial primary key,  
  number varchar(10) not null,
  customer_id int references example.customer(id),
  amount numeric(20,2) not null,
  image bytea,
  control_dates date[],
  extended_information jsonb
);
create index on example.invoice(number);

create or replace view example.invoice_ui as
  select i.id,i.number,c.name customer_name,i.amount 
    from example.invoice i
    left join example.customer c on c.id=i.customer_id;

create table example.invoice_product(
  id serial primary key,  
  invoice_id int not null references example.invoice(id) on delete cascade,
  sort_pos int not null,
  product_name varchar(100) not null,
  quantity int not null
);

-- Enable ORM for tables and view    
call pgorm.orm_relation_enable('example','customer');
call pgorm.orm_relation_enable('example','invoice');
call pgorm.orm_relation_enable('example','invoice_ui');
call pgorm.orm_relation_enable('example','invoice_product');

-- Review ORM definitions and change them (if necessary)
select * from pgorm.orm_relation;
select * from pgorm.orm_relation_column;
select * from pgorm.orm_table_fkey;
select * from pgorm.orm_view_pkey;

-- Create simple function 
create or replace function example.power_3(x int) returns int language plpgsql as $$
begin
  return x*x*x;    
end; $$;

-- Create function: insert multiple records in one transaction and return record 
-- Variant 1: parameters - postgres records, result - postgres record, must specify list of columns    
create or replace function example.add_invoice_v1(invoice example.invoice, invoice_product_array example.invoice_product[]) returns example.invoice security definer language plpgsql as $$
begin
  insert into example.invoice(number,customer_id,amount)    
    select number,customer_id,amount from (select invoice.*) r
    returning * into invoice;
  insert into example.invoice_product(invoice_id,sort_pos,product_name,quantity)    
    select invoice.id,row_number() over (),product_name,quantity from (select unnest.* from unnest(invoice_product_array)) r;
  return invoice;  
end; $$;
-- Variant 2: parameter - one common jsonb, result - postgres record, used functions jsonb_populate_record and jsonb_populate_recordset
create or replace function example.add_invoice_v2(params jsonb) returns example.invoice security definer language plpgsql as $$
declare
  v_invoice example.invoice := jsonb_populate_record(null::example.invoice, params->'invoice');  
  v_invoice_product_array example.invoice_product[] := (select array_agg(r) from jsonb_populate_recordset(null::example.invoice_product, params->'invoice_product_array') r);
begin
  return example.add_invoice_v1(v_invoice, v_invoice_product_array);
end; $$;
-- Variant 3: parameters - records in jsonb format, result - record as jsonb, used procedures pgorm.orm_record_*      
create or replace function example.add_invoice_v3(invoice jsonb, invoice_product_array jsonb[]) returns jsonb security definer language plpgsql as $$
declare
  v_invoice_product jsonb;
  v_sort_pos int := 1;
begin
  call pgorm.orm_record_save(invoice); 
  foreach v_invoice_product in array invoice_product_array loop
    call pgorm.orm_record_set_value(v_invoice_product, 'invoice_id', invoice->>'id');
    call pgorm.orm_record_set_value(v_invoice_product, 'sort_pos', v_sort_pos); v_sort_pos:=v_sort_pos+1;   
    call pgorm.orm_record_save(v_invoice_product);
  end loop;
  return invoice;  
end; $$;

-- Create user (if not exists), grant privileges
do $$ begin
  create user user_1 login password 'password_1';
exception 
  when duplicate_object then null;
end $$;
grant usage on schema example to user_1;
grant all on all tables in schema example to user_1;
grant all on all sequences in schema example to user_1;
grant execute on all functions in schema example to user_1;
grant execute on all procedures in schema example to user_1;

Using database objects in JavaScript

<!DOCTYPE html>
<html>
<head>  
  <meta charset="utf-8">
  <title>PGORM | Full example</title> 
  <style> 
    div { border: 1px solid black; width: 500px; max-width: 500px; margin-bottom: 20px; word-wrap: break-word; padding: 5px; }
  </style>
</head>

<body> 

<div id="Error" style="display: none; border: 3px solid red;"></div>

Customer.ID                             <div id="CustomerID"></div>
Invoice                                 <div id="Invoice"></div>
Invoice.Customer - parent               <div id="InvoiceCustomer"></div>
Invoice.InvoiceProductArray - children  <div id="InvoiceProductArray"></div>
power_3(2)                              <div id="Power3"></div>
Invoice IDs                             <div id="InvoiceIDs"></div>

<script type="module">

// Import class for connect yo database
import { ORMConnection } from "/orm/pgorm-db.js";
// Import ORM classes
import { Customer,       CustomerArray }       from "/orm/example/Customer.js";
import { Invoice,        InvoiceArray }        from "/orm/example/Invoice.js";
import { InvoiceProduct, InvoiceProductArray } from "/orm/example/InvoiceProduct.js";

try {
  // Connect to database    
  new ORMConnection().connect("user_1", "password_1").setDefault();
  //
  // Create object customer, set name and save 
  var customer = new Customer().setName("Horns and hooves").save();
  // Show customer ID that was assigned when object was saved
  document.getElementById("CustomerID").innerHTML = customer.getID();
  //
  // Create object invoice, set customer as parent object, fields, save, add two children
  var invoice = new Invoice().setCustomer(customer).setNumber("12/34").setAmount(567.89).save();
  invoice.addInvoiceProduct(new InvoiceProduct().setProductName("Pensil").setQuantity(3));
  invoice.addInvoiceProduct(new InvoiceProduct().setProductName("Eraser").setQuantity(5));
  // Show invoice object and its relations
  document.getElementById("Invoice").innerHTML = JSON.stringify(invoice);
  document.getElementById("InvoiceCustomer").innerHTML = JSON.stringify(invoice.getCustomer());
  document.getElementById("InvoiceProductArray").innerHTML = JSON.stringify(invoice.getInvoiceProductArray());
  // Select and show result of function example.power_3
  document.getElementById("Power3").innerHTML = ORMConnection.getDefault().execute("select example.power_3($1)", [2]).data[0][0];
  //
  // Call functions example.add_invoice with ORM parameters, convert result to ORM object, all records are saved in one transaction
  // Parameters
  var invoiceNew = new Invoice().setNumber("12/34").setAmount(567.89);
  var invoiceProductsNew = new InvoiceProductArray(); 
  invoiceProductsNew.push( new InvoiceProduct().setProductName("Pensil").setQuantity(3) );
  invoiceProductsNew.push( new InvoiceProduct().setProductName("Eraser").setQuantity(5) );
  // Variant 1: parameters - ORM objects, result - postgres record
  var invoice1Record = ORMConnection.getDefault().execute("select example.add_invoice_v1($1, $2);", [invoiceNew,invoiceProductsNew]).data[0][0]; 
  var invoice1 = Invoice.fromRecord(invoice1Record);
  // Variant 2: parameter - ORM objects as one common json, result - postgres record
  var invoice2Record = ORMConnection.getDefault().execute("select example.add_invoice_v2($1);", { invoice: invoiceNew, invoice_product_array: invoiceProductsNew }).data[0][0];
  let invoice2 = Invoice.fromRecord(invoice2Record);
  // Variant 3: parameters - ORM objects as JSONs, result - record as JSON
  var invoice3JSON = ORMConnection.getDefault().execute("select example.add_invoice_v3($1, $2);", [invoiceNew.toJSON(),invoiceProductsNew.toArrayJSON()]).data[0][0]; 
  let invoice3 = Invoice.fromJSON(invoice3JSON);
  // Show results
  document.getElementById("InvoiceIDs").innerHTML = invoice1.getID()+","+invoice2.getID()+","+invoice3.getID();
  //
  // Disconnect from database    
  ORMConnection.getDefault().disconnect();
} catch (exception) {
  // View exception
  document.getElementById("Error").innerHTML = "<b>ERROR</b><br><br>"+exception.message;
  document.getElementById("Error").style.display = '';
}

</script>

</body>
</html>

Extended names - extended names of database objects (in double quotes) not supported
UTF-8 encoding - JavaScript modules receive data in UTF-8 encoding, database can have any encoding
Single JSON value null - postgres value 'null'::jsonb converted to null, because in JavaScript valuesJSON.parse('null') and null are equivalent

When user successfully connects to database, unique key (random string with salt) is generated and returned. This key is subsequently used in requests.

PGORM receives and transmits data in unencrypted form, security is provided by network and system means depending on network type:

Closed (corporate) network - Impossibility of interception and analysis of network traffic (using sniffers) is required, achieved by using network switches instead of hubs
Internet - Requires traffic encryption, usually using VPN connection or proxy web server

Connection under user postgres is forbidden (error will occur when trying to connect).

When user connection is explicitly specified via localhost/127.0.0.1 (not recommended), requires local connection without password allow only postgres user. To do this, in $PGDATA/pg_hba.conf file, need line

host    all             all             127.0.0.1/32            trust

replaced by

host    all             postgres        127.0.0.1/32            trust

Review posted on Youtube

PGORM

HTML/JS ORM for PostgreSQL

PGORM

About