HTML/JS ORM для PostgreSQL

Описание
Простой пример
Ключевые особенности
Принцип работы
Полный пример
Ограничения
Безопасность
Видео
Проект PGSuite

PGORM - инструмент для работы с базой данных PostgreSQL непосредственно с web-страниц, позволяет подключаться, выполнять SQL-запросы, использовать ORM и получать файлы

Реализован как служба, которая принимает HTTP-запросы от браузера и возвращает ответ в зависимости от вида доступа (см. ниже). В базе данных настраивается список таблиц, которые будут отображены в классы JavaScript. Для каждой таблицы автоматически создается модуль JavaScript, который можно использовать через конструкцию import { [Table],[TableArray] } from '/orm/[Schema]/[Table].js';

Для простых систем pgorm устанавливается на сервер базы данных и используется самостоятельно, для сложных - на сервер базы данных или сервер приложений, на основном web-сервере (например, nginx) настраивается переправление запросов /pgorm/*,/orm/*.

Виды доступа

ORM	-	работа со строками таблицы базы данных PostgreSQL как с объектами JavaScript (object-relational mapping)
SQL	-	выполнение SQL-запросов и команд, результат возвращается в формате JSON с автоприведением типов
[GET] file	-	получение файлов (.html, .js и т.д.), включая модули JavaScript для подключения к базе данных, выполнения SQL-запросов, работы с таблицами

Создание таблицы invoice и включение для нее объектно-реляционного отображения (ORM)

-- Создаем таблицу
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  
);

-- Включаем ORM для таблицы, JavaScript-модули создадутся автоматически    
call pgorm.orm_table_enable('invoice');

-- Создаем пользователя и выдаем ему права на таблицу  
create user user_1 login password 'user_1';
grant all on invoice,invoice_id_seq to user_1;

Создание объекта класса Invoice и сохранение его в базу данных

<!DOCTYPE html>
<html>
<head>  <meta charset="utf-8"> <title>PGORM | Простой пример</title> </head>
<body> 

<button id="buttonCreateInvoice">Создать накладную</button> <br><br>

Результат
<div id="result" style="border: 1px solid black; width: 500px; height: 200px;"></div>

<script type="module">

// Импортируем классы подключения и таблицы Invoice из автоматически созданных модулей
import { ORMConnection } from "/orm/pgorm-db.js";
import { Invoice }       from "/orm/public/Invoice.js";

function createInvoice() {
  try {
    // Создаем подключение, подключаемся к базе данных и устанавливаем данное подключение по умолчанию
    new ORMConnection().connect('user_1', 'user_1').setDefault();
    // Создаем объект класса Invoice
    let invoice = new Invoice();
    // Устанавливаем значения полей
    invoice.setNumber('12/34');
    invoice.setAmount(567.89);
    // Сохраняем объект, после сохранения автозаполняемые поля будут содержать значения
    invoice.save();
    // Показываем объект
    document.getElementById("result").innerHTML = "Накладная<br><br>" +
      "ID: "     + invoice.getID() + "<br>" +
      "Date: "   + invoice.getDate().toISOString() + "<br>" +
      "Number: " + invoice.getNumber() +"<br>" +
      "Amount: " + invoice.getAmount();
    // Отключаемся от базы данных
    ORMConnection.getDefault().disconnect();
  } catch (exception) {
    // При возникновении исключения показываем его
    document.getElementById("result").innerHTML = "Ошибка<br><br>"+exception.message;
  }
}

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

</script>

</body>
</html>

Штатная работа через SQL - ORM-классы создаются на основе существующих таблиц, изменение структуры таблиц (метаданных) ведется через SQL. При выполнении DDL-операций над таблицей (alter table, create index и т.д.) срабатывает триггер, который обновляет JavaScript модуль.
Модули и переопределение классов - Для каждой таблицы создаются два модуля: редактируемый с пустыми унаследованными классами и автоформируемый с суперклассами, который пересоздается при изменении структуры таблицы
Автопреобразование типов - Реализовано автоматическое преобразование типов данных SQL и JavaScript, в том числе и массивов
Строка таблицы и массив - Для каждой таблицы создаются два класса: строка таблицы (Record) и массив строк таблицы (RecordArray extends Array)
Связи (relationships) - Для работы со строками таблиц, связанным по внешним ключам, создаются отдельные методы. Для объекта по исходящему внешнему ключу (parent) методы синхронизированы с полями класса. Для массива объектов по входящему внешнему ключу (children) возможно указать автозаполняемый столбец сортировки
Автометоды - В классе строки таблицы автоматически создается методы получения по уникальным индексам, в классе массива строк - методы по неуникальным индексам и сортировок
Нейминг - Имя класса для таблицы, имена полей для столбцов и наименования связей (родитель,потомки) формируются автоматически, но можно указать их явно
Описание таблицы (relation) - Класс имеет статический метод, который возвращает определение таблицы: схема, имя, список столбцов, первичный и внешние ключи
Простота и самодостаточность - Сервер по HTTP отдает созданные им модули, установка заключается в копировании исполняемого файла и штатными созданием службы, сторонних библиотек не требуется (libpq уже установлена с postgres)

Сервер подключается к базе данных, в поддиректории /orm для выбранных таблиц создает JavaScript-модули (файлы) и слушает HTTP-запросы. Для запросов c URL /pgorm/* выполняются операции с базой данных (подключение, сохранение и загрузка объекта и т.п.) и возвращается результат в формате JSON. Для всех остальных запросов возвращается запрошенный по URL файл, в т.ч. созданные модули.

Создание объектов бд и включение для них объектно-реляционного отображения (ORM)

-- 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;

Использование объектов базы данных в 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>

Расширенные имена - не поддерживаются расширенные наименования объектов базы данных (в двойных кавычках)
Кодировка UTF-8 - модули JavaScript получают данные в кодировке UTF-8, сама бд может иметь любую кодировку
Единичное JSON-значение null - postgres-значение 'null'::jsonb преобразовывается в null, т.к. в JavaScript значения JSON.parse('null') и null эквивалентны

При успешом подключении пользователя к базе данных формируется и возвращается уникальный ключ (случайная строка с солью), который используется при дальнейших обращениях.

PGORM получает и передает данные в незашифрованном виде, безопасность обеспечивается сетевыми и системными средствами в зависимости от типа сети:

Закрытая (корпоративная) сеть - Необходимо обеспечить невозможность перехватывать и анализировать сетевой трафик (использование снифферов) как злоумышленником при несанкционированном подключении, так и пользователями сети. Обычно это обеспечивается использованием коммутаторов(свитчей) вместо концентраторов (хабов).
Internet - Необходимо шифрование трафика. Обычно используется VPN-подключение или установка проксирующего WEB-сервера (например, nginx)

Подключение под пользователем postgres запрещено (при попытке подключения возникнет ошибка).

При установке PGORM на сервер базы данных обычные пользователи по умолчанию подключаются по внешнему ip-адресу, orm-пользователь - по 127.0.0.1, дополнительной настройки файла доступа не требуется. Если явно указано подключение пользователей по localhost/127.0.0.1 (не рекомендуется), то в настройках доступа необходимо локальное подключение без пароля разрешить только пользователю postgres, для этого в файле $PGDATA/pg_hba.conf необходимо строку

host    all             all             127.0.0.1/32            trust

заменить на

host    all             postgres        127.0.0.1/32            trust

Обзорное видео размещено на Youtube

Инcтрумент является частью проекта PGSuite

PGORM

HTML/JS ORM для PostgreSQL

PGORM

О проекте