@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
Copy

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

• @egomobile/orm
• pg

Usage

import { createDataContext, DbNullable, NULL as DbNull } from "@egomobile/orm";
import { PostgreSQLDataAdapter } from "@egomobile/orm-pg";
import type { QueryResult } from "pg";

class User {
  // non-nullable fields
  public id: number | null = null;
  public first_name: string | null = null;
  public last_name: string | null = null;

  // nullable fields
  public email: DbNullable<string | null> = null;
}

async function main() {
  const context = await createDataContext({
    adapter: new PostgreSQLDataAdapter(),
    entities: {
      // name of the entity / table
      users: {
        ids: ["id"], // list of column(s) which represent the ID
        type: User, // the class / type to use to create objects from
      },
    },
  });

  const listOfUsers: User[] = await context.find(User, {
    // WHERE clause
    where: "is_active=$1 AND is_deleted=$2",
    params: [true, false], // $1, $2

    offset: 1, // skip the first
    limit: 100, // only return 100 rows
  });

  // return a user with ID 5979
  const specificUser: User | null = await context.findOne(User, {
    where: "id=$1",
    params: [5979], // $1
  });

  if (specificUser !== null) {
    // update with new data
    specificUser.last_name = "Doe";
    specificUser.first_name = "Jane";
    specificUser.email = DbNull;
    await context.update(specificUser);

    // remove from database
    await context.remove(specificUser);
  } else {
    console.log("User not found");
  }
}

// create new POCO
const newUser = new User();
newUser.first_name = "John";
newUser.last_name = "Doe";
// ... and add it to database
await context.insert(newUser);

// do raw queries
const result: QueryResult<any> = await context.query(
  "SELECT * FROM users WHERE id=$1 AND is_active=$2;",
  23979,
  true
);
console.log(result);

main().catch(console.error);
Copy

Migrations

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

CREATE TABLE IF NOT EXISTS public.migrations
(
    "id" bigserial NOT NULL,
    "timestamp" bigint NOT NULL,
    "name" character varying NOT NULL,
    CONSTRAINT pk_migrations_id PRIMARY KEY (id)
)
WITH (
    OIDS = FALSE
);
Copy

A quick example how to use PostgreSQLDataAdapter class:

import { PostgreSQLDataAdapter } from "@egomobile/orm-pg";

async function main() {
  const context = new PostgreSQLMigrationContext({
    // a default adapter
    adapter: new PostgreSQLDataAdapter(),

    // scan for .js files
    // inside ./migration subfolder
    // with the following format:
    //
    // <UNIX-TIMESTAMP>-<NAME-OF-THE-MIGRATION>.js
    //
    // example: 1746942104690-CreateUserTable.js
    migrations: __dirname + "/migration",

    table: "migrations",
  });

  // UP-GRADE database
  await context.up();

  // DOWN-GRADE database
  await context.down();
}

main().catch(console.error);
Copy

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.html

  await context.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.html

  await context.query(`DROP TABLE public.tdta_user;`);
};
Copy

You are also able to create a migration file programmatically:

import { createNewMigrationFile } from "@egomobile/orm-pg";

const newFilePath = await createNewMigrationFile("the name of the migration", {
  // create output file inside ./migrations sub folder
  dir: __dirname + "/migrations",

  // generate and add optional header and footer to the file
  header: ({ 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);
Copy

Documentation

The API documentation can be found here.