postorm

Node js ORM for PostgreSQL database. The whole perpose of that package is to make sql requests easier to write with JS, to create "SELECT someStaff FROM staffWareHouse" by writing

staffWareHouse.select("someStaff");

etc.

How to use it?

So what do we have?

const { PgObj, compileModelsByScripts, PgUtils, PgGlob } = require("postorm")

Lets understand what is that and how it works.

PgObj

PgObj is a class that implements table object. To create it you will need a name of table in your database, db connecter(pgConnecter) and model of your table

now we don't have implementation of DB interfaces, so we use pg-promise. Btw, thanx to it's author.

let pgconnection = {
  host: "localhost",
  user: "postgres",
  database: "BotDb",
  password: "123",
  port: 5432,
  connectionTimeoutMillis: 20000,
};

const db = pgp(pgconnection);
tableObj = new PgObj("table", db, tableModel);

That might be clear. But what is model in parameters? Model is a dictionary where keys are the names of columns of real table and values are their types converted to Node.js types. For example:

let userModel = { id: Number, gender: String, isHeOrSheAwesome: Boolean };

compileModelsByScripts

Another way to create model is to use table creation script. Here compileModelsByScripts() goes.

Imagine that you have a file with sql script like this in ./scripts directory:

CREATE TABLE IF NOT EXISTS public.user
(
  	id SERIAL,
    another_id integer NOT NULL,
	  mode VARCHAR DEFAULT 'unauthorized',
	  eyes_nubmer integer DEFAULT 2,
    CONSTRAINT users_pkey PRIMARY KEY (id),
    CONSTRAINT users_user_id_key UNIQUE (user_id, bot_instance)
);

than if you write

let models = compileModelsByScripts("./scripts");

//model - {user : {model}}

let yourScriptModel = models["user"];

//yourScriptModel - {another_id: null, creationScript: theWholeScriptText, eyes_nubmer:2, mode:'unauthorized'}

Using this modules you can make things easily

 const { PgObj, compileModelsByScripts, PgUtils} = require("postorm")

 let models = compileModelsByScripts('./scripts')

 const connectionStr = 'postgres://john:pass123@localhost:5432/products'
 const db = pgp(connectionStr);

 let userModel = models['user']


 let User = new PgObj('user', db, userModel)

PgUtils

PgUtils is a class to add SQL functions to your code For examle

Users.select(PgUtils.count("another_id"))
  .where(Users.eyes_nubmer.eq(2))
  .toStr();
//returns: SELECT * FROM users WHERE users.eyes_nubmer=2;
Users.select(PgUtils.count("another_id")).where(Users.eyes_nubmer.eq(2)).exec();
//executes this request

PgGlob

PgGlob is a class to create nested request such as "select from select"

let Glob = new PgGlob(db);

Glob.select()
  .from(
    Users.select(PgUtils.count(PgUtils.distinct(Users.user_id)))
      .fullJoin(Requests, ["user_id", "user_id"])
      .limit(1)
  )
  .toStr();
//returns: SELECT  FROM (SELECT COUNT(DISTINCT users.user_id )  FROM users  FULL JOIN requests ON users.user_id=requests.user_id  LIMIT 1 ) t1 ;
Glob.select()
  .from(
    Users.select(PgUtils.count(PgUtils.distinct(Users.user_id)))
      .fullJoin(Requests, ["user_id", "user_id"])
      .limit(1)
  )
  .exec();
//executes this request

To find more functions check PgUtils file. Good luck! And you check an example