npm package discovery and stats viewer.

Discover Tips

  • General search

    [free text search, go nuts!]

  • Package details

    pkg:[package-name]

  • User packages

    @[username]

Sponsor

Optimize Toolset

I’ve always been into building performant and accessible sites, but lately I’ve been taking it extremely seriously. So much so that I’ve been building a tool to help me optimize and monitor the sites that I build to make sure that I’m making an attempt to offer the best experience to those who visit them. If you’re into performant, accessible and SEO friendly sites, you might like it too! You can check it out at Optimize Toolset.

About

Hi, 👋, I’m Ryan Hefner  and I built this site for me, and you! The goal of this site was to provide an easy way for me to check the stats on my npm packages, both for prioritizing issues and updates, and to give me a little kick in the pants to keep up on stuff.

As I was building it, I realized that I was actually using the tool to build the tool, and figured I might as well put this out there and hopefully others will find it to be a fast and useful way to search and browse npm packages as I have.

If you’re interested in other things I’m working on, follow me on Twitter or check out the open source projects I’ve been publishing on GitHub.

I am also working on a Twitter bot for this site to tweet the most popular, newest, random packages from npm. Please follow that account now and it will start sending out packages soon–ish.

Open Software & Tools

This site wouldn’t be possible without the immense generosity and tireless efforts from the people who make contributions to the world and share their work via open source initiatives. Thank you 🙏

© 2024 – Pkg Stats / Ryan Hefner

easy-psql

v0.1.27

Published

Simple ORM for postgresql

Downloads

69

Readme

easy-psql

Description

Welcome to the easy-psql documentation! easy-psql is a simple intermediary for querying data in PostgreSQL databases. Whether you're a beginner or an experienced developer, this documentation will help you get started with easy-psql and leverage its capabilities to interact with your PostgreSQL databases efficiently.

Note: This package is intended for personal usage, and no tests have been written for it. Therefore, it is recommended to use it with caution and at your own responsibility. Like any software, there may be unforeseen bugs or issues that could affect your application. It is advisable to thoroughly review the package's functionality and integrate it into your projects with careful consideration of potential risks. (This package was developed using nodejs 16)

Installation

To install easy-psql, you can use npm:

npm install easy-psql

Establishing Connection with PostgreSQL Database

const { DB } = require("easy-psql");

DB.registerConnectionConfig({
  user: "postgres",
  database: "postgres",
  schema: "public",
  password: "postgres",
  port: 5432,
  host: "localhost",
});

Defining Models and Relations for PostgreSQL Database

const { Model, Column } = require("easy-psql");

class Role extends Model {
  constructor(connection) {
    super("roles", connection);
  }

  columns = {
    id: new Column({
      name: "id",
      type: "uuid",
      primary: true,
      defaultValue: "gen_random_uuid()",
    }),
    name: new Column({
      name: "name",
      type: "text",
      nullable: false,
    }),
  };
}

class User extends Model {
  constructor(connection) {
    super("users", connection);
  }

  columns = {
    id: new Column({
      name: "id",
      type: "uuid",
      primary: true,
      defaultValue: "gen_random_uuid()",
    }),
    role_id: new Column({
      name: "role_id",
      type: "uuid",
    }),
    email: new Column({
      name: "email",
      type: "text",
      unique: true,
    }),
    password: new Column({
      name: "password",
      type: "text",
      nullable: false,
    }),
    grid_order: new Column({
      name: "grid_order",
      type: "int",
      nullable: false,
      defaultValue: 1,
    }),
  };

  relations = {
    role: new Relation({
      from_table: "users",
      from_column: "role_id",
      to_table: "roles",
      to_column: "id",
      alias: "object", // object or array
    }),
  };
}

// Register the models in order to be able to use relations
DB.register(User);
DB.regitser(Role);

Basic examples


const model = new User();
const data = await model.find() // multiple rows
const data = await model.findOne() // single row
const data = await model.create({email:'[email protected]',password:'12345678'});
const data = await model.createMany([{email:'[email protected]',password:'12345678'},{email:'[email protected]',password:'12345678'}])
const data = await model.update({where:{...},update:{email:'[email protected]'}});
const data = await model.delete({where: {...}});
const {count,max} = await model.aggregate({_count:true,max:{email:true},where:{...}}) // _count,max,min,sum,avg

Use Relations

const model = new User();
const usersWithRoles = await model.find({ include: { role: true } });

Use Relations With Filters

const model = new User();
const usersWithRoles = await model.find({ include: { role: {where:{...}} } });

Basic Filtering Example

const model = new User();
const data = await model.find({
  where: { email: { _eq: "[email protected]" } },
});

Basic Filtering Operators

const model = new User();
const data = await model.find({
  where: {
    password: { _in: ["12345678", "123456789"] },
    email: { _eq: "[email protected]" },
    _or: [{ password: { _is_not: null } }, { password: { _is: null } }],
    _and: [
      { grid_order: { _lte: 200 } },
      { grid_order: { _lt: 201 } },
      { grid_order: { _gte: 1 } },
      { grid_order: { _gt: 0 } },
      { password: { _nin: ["12345678", "123456789"] } },
      { email: { _ilike: "test" } },
      { email: { _neq: "[email protected]" } },
      {
        _or: [
          { password: { _is_not: null } },
          { password: { _is: null } },
          {
            _and: [
              { email: { _neq: "[email protected]" } },
              {
                _or: [
                  { password: { _is_not: null } },
                  { password: { _is: null } },
                ],
              },
            ],
          },
        ],
      },
    ],
  },
});

Basic Filtering Operators Are Applied to Relations too

const model = new User();
const data = await model.find({
  where: {
    password: { _eq_: "12345678" },
    email: { _eq: "[email protected]" },
    // this is the role related entity
    role: {
      name: { _eq: "admin" },
    },
  },
});

Other Operators

const model = new Role();
const data = await model.find({where:{...},distinct:[...],groupBy:[...],limit:100,offset:0,orderBy: {
    name: 'asc',
    users_aggregate:{
        _count:true
    }
}})

Using nested aggregations

const { Model, Column } = require("easy-psql");

class Role extends Model {
  constructor(connection) {
    super("roles", connection);
  }

  columns = {
    id: new Column({
      name: "id",
      type: "uuid",
      primary: true,
      defaultValue: "gen_random_uuid()",
    }),
    name: new Column({
      name: "name",
      type: "text",
      nullable: false,
    }),
  };

  relations = {
    role: new Relation({
      from_table: "roles",
      from_column: "id",
      to_table: "users",
      to_column: "role_id",
      alias: "array", // object or array
    }),
  };
}

class User extends Model {
  constructor(connection) {
    super("users", connection);
  }

  columns = {
    id: new Column({
      name: "id",
      type: "uuid",
      primary: true,
      defaultValue: "gen_random_uuid()",
    }),
    role_id: new Column({
      name: "role_id",
      type: "uuid",
    }),
    email: new Column({
      name: "email",
      type: "text",
      unique: true,
    }),
    password: new Column({
      name: "password",
      type: "text",
      nullable: false,
    }),
    grid_order: new Column({
      name: "grid_order",
      type: "int",
      nullable: false,
      defaultValue: 1,
    }),
  };

  relations = {
    role: new Relation({
      from_table: "users",
      from_column: "role_id",
      to_table: "roles",
      to_column: "id",
      alias: "object", // object or array
    }),
  };
}

// Register the models in order to be able to use relations
DB.register(User);
DB.regitser(Role);

const model = new Role();
const role = await model.findOne({
  where: { name: { _eq: "admin" } },
  include: {
    users_aggregate: {
      // _count,max,min,avg,sum
      _count: true,
      max: { grid_order: true },
      min: { grid_order: true, email: true },
      // you can use where inside here
    },
  },
});

Using multiple nested relations

const model = new User();
const data = await model.find({
  include: {
    role: {
      include: {
        users: {
          include: {
            role: true, // .....
          },
        },
      },
    },
  },
});

Transactions Example 1

const model = new User();

const result = await model.withTransaction(async (tx) => {
  const newUser = await model.create({
    email: "[email protected]",
    password: "12345678",
    role_id: "...",
  });
  if (!newUser) {
    throw new Error("User not created");
  }

  return newUser;
});

// Errors inside withTransaction will use rollback; otherwise a commit is performed

// Check the result

if (result instanceof Error) {
  console.error(result);
} else {
  console.log(result);
}

Transactions Example 2

const model = new User();

const result = await model.withTransaction(async (tx) => {
  const newUser = await model.create({
    email: "[email protected]",
    password: "12345678",
    role_id: "...",
  });
  if (!newUser) {
    throw new Error("User not created");
  }

  const roleModel = new Role(tx); // pass the tx -> connection to postgres otherwise this operation is not be atomic

  const roleData = await role.create({ name: "testRole" });
  if (!roleData) {
    throw new Error("Role not created");
  }
  return [newUser, roleData];
});

// Errors inside withTransaction will use rollback; otherwise a commit is performed

// Check the result

if (result instanceof Error) {
  console.error(result);
} else {
  console.log(result);
}

Transactions Example 3

const model = new Role();
const inputData = {
  name: "newRole",
  users: [
    { email: "[email protected]", password: "12345678" },
    { email: "[email protected]", password: "12345678" },
  ],
};

// Over here perform the createTX operation

const result = await model.createTX(inputData);

// or for multiple inserts use await model.createManyTX([inputData])   -> use an array of objects

// The roles.id generated from this query will be assigned to each users.role_id field

Register Effects

// The following events will be triggered only by using the model classes

// e.g : find and findOne  on model User -> will trigger onSelectAsync but DB.pool.query('select * from users'); will not!

DB.onSelectAsync("users", async (data, instance) => {
  // ...
});
DB.onInsertAsync("users", async (data, instance) => {
  // ...
});
DB.onUpdateAsync("users", async (data, instance) => {
  // ...
});
DB.onDeleteAsync("users", async (data, instance) => {
  // ...
});
DB.onErrorAsync("users", async (error, instance) => {
  // ...
});

AutoDiscoverAPI

const { Postgres, Relation } = require('easy-psql');

const db = new Postgres({
  connectionConfig: {
    host: ...,
    port: ...,
    user: ...,
    password: ...,
    schema: ...,
    database: ...
  },
  relations: [new Relation({...}), {from_table:...,from_column:...,to_table:...,to_column:...,alias:...,type: 'object' | 'array'}], // not required
  options: {
      createFiles: false, // create files for the models
      skipIfDirectoryExists: true, // skip files' creation if the specified folder already exists
      dirname: "easy-psql-models", // folder name to create model files
      useESM: false, // true -> use exports / false -> require
      extension: "js", // can be js,ts or mjs
  }
});

const postgres = async () => {
  await db.init()
  return db
}


module.exports = postgres


// e.g in another file

const postgres = require('./path-of-the-file-above');

const run = async () => {
  const db = await postgres();

  const data = await db.model('my-table').find({...}); //same api as the functions in the other examples

  return data; // ([{...},{...}])
}


run();