@egomobile/orm-pg

A PostgreSQL data adapter and other utilities for @egomobile/orm module.

Install

Execute the following command from your project folder, where your package.json file is stored:

npm install --save @egomobile/orm-pg

The following modules are defined in peerDependencies and have to be installed manually:

• @egomobile/orm
• pg

Usage

import{createDataContext,DbNullable,NULLasDbNull}from"@egomobile/orm";import{PostgreSQLDataAdapter}from"@egomobile/orm-pg";importtype{QueryResult}from"pg";classUser{// non-nullable fieldspublicid: number|null=null;publicfirst_name: string|null=null;publiclast_name: string|null=null;// nullable fieldspublicemail: DbNullable<string|null>=null;}asyncfunctionmain(){constcontext=awaitcreateDataContext({adapter: newPostgreSQLDataAdapter(),entities: {// name of the entity / tableusers: {ids: ["id"],// list of column(s) which represent the IDtype: User,// the class / type to use to create objects from},},});constlistOfUsers: User[]=awaitcontext.find(User,{// WHERE clausewhere: "is_active=$1 AND is_deleted=$2",params: [true,false],// $1, $2offset: 1,// skip the firstlimit: 100,// only return 100 rows});// return a user with ID 5979constspecificUser: User|null=awaitcontext.findOne(User,{where: "id=$1",params: [5979],// $1});if(specificUser!==null){// update with new dataspecificUser.last_name="Doe";specificUser.first_name="Jane";specificUser.email=DbNull;awaitcontext.update(specificUser);// remove from databaseawaitcontext.remove(specificUser);}else{console.log("User not found");}}// create new POCOconstnewUser=newUser();newUser.first_name="John";newUser.last_name="Doe";// ... and add it to databaseawaitcontext.insert(newUser);// do raw queriesconstresult: QueryResult<any>=awaitcontext.query("SELECT * FROM users WHERE id=$1 AND is_active=$2;",23979,true);console.log(result);main().catch(console.error);

Migrations

Before you can use migrations, first keep sure to have an existing migrations table in your database:

CREATETABLEIF NOT EXISTS public.migrations
(
"id"bigserialNOT NULL,
"timestamp"bigintNOT NULL,
"name" character varying NOT NULL,
CONSTRAINT pk_migrations_id PRIMARY KEY (id)
)
WITH (
OIDS = FALSE
);

A quick example how to use PostgreSQLDataAdapter class:

import{PostgreSQLDataAdapter}from"@egomobile/orm-pg";asyncfunctionmain(){constcontext=newPostgreSQLMigrationContext({// a default adapteradapter: newPostgreSQLDataAdapter(),// scan for .js files// inside ./migration subfolder// with the following format://// <UNIX-TIMESTAMP>-<NAME-OF-THE-MIGRATION>.js//// example: 1746942104690-CreateUserTable.jsmigrations: __dirname+"/migration",table: "migrations",});// UP-GRADE databaseawaitcontext.up();// DOWN-GRADE databaseawaitcontext.down();}main().catch(console.error);

A migration file looks like this:

/** * Function to UP-GRADE the database. */module.exports.up=async(context)=>{// context => https://egomobile.github.io/node-orm/interfaces/IDataContext.htmlawaitcontext.query(`CREATE TABLE public.tdta_user( "id" bigserial NOT NULL, "uuid" character(36) NOT NULL, "email" character varying NOT NULL, "created" timestamp with time zone NOT NULL, "updated" timestamp with time zone, CONSTRAINT "pk_tdta_user_id" PRIMARY KEY (id), CONSTRAINT "uq_tdta_user_uuid" UNIQUE (uuid), CONSTRAINT "uq_tdta_user_account_id" UNIQUE (account_id))WITH ( OIDS = FALSE);`);};/** * Function to DOWN-GRADE the database. */module.exports.down=async(context)=>{// context => https://egomobile.github.io/node-orm/interfaces/IDataContext.htmlawaitcontext.query(`DROP TABLE public.tdta_user;`);};

You are also able to create a migration file programmatically:

import{createNewMigrationFile}from"@egomobile/orm-pg";constnewFilePath=awaitcreateNewMigrationFile("the name of the migration",{// create output file inside ./migrations sub folderdir: __dirname+"/migrations",// generate and add optional header and footer to the fileheader: ({ name, timestamp })=>`// Hello, this is migration '${name}' created on ${timestamp}\n\n`,footer: "\n\n// Copyright (x) e.GO Mobile SE, Aachen, Germany\n\n",});console.log("Migration file has been created in",newFilePath);

Documentation

The API documentation can be found here.