PGSuite

PostgreSQL tools for simple WEB applications

ru

PGSuite

PGORM

ru

Website
Description
Simple example

PGORM — HTML/JS ORM for PostgreSQL
Tool is described in more detail on a separate website pgorm.org

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>