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",
}),
};
}
DB.register(User);
DB.regitser(Role);
Basic examples
const model = new User();
const data = await model.find()
const data = await model.findOne()
const data = await model.create({email:'example@example.com',password:'12345678'});
const data = await model.createMany([{email:'example@example.com',password:'12345678'},{email:'example2@example.com',password:'12345678'}])
const data = await model.update({where:{...},update:{email:'example@updated.com'}});
const data = await model.delete({where: {...}});
const {count,max} = await model.aggregate({_count:true,max:{email:true},where:{...}})
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: "example@example.com" } },
});
Basic Filtering Operators
const model = new User();
const data = await model.find({
where: {
password: { _in: ["12345678", "123456789"] },
email: { _eq: "test@example.com" },
_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: "test2@example.com" } },
{
_or: [
{ password: { _is_not: null } },
{ password: { _is: null } },
{
_and: [
{ email: { _neq: "test2@example.com" } },
{
_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: "test@example.com" },
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",
}),
};
}
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",
}),
};
}
DB.register(User);
DB.regitser(Role);
const model = new Role();
const role = await model.findOne({
where: { name: { _eq: "admin" } },
include: {
users_aggregate: {
_count: true,
max: { grid_order: true },
min: { grid_order: true, email: true },
},
},
});
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: "test@test2.com",
password: "12345678",
role_id: "...",
});
if (!newUser) {
throw new Error("User not created");
}
return newUser;
});
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: "test@test2.com",
password: "12345678",
role_id: "...",
});
if (!newUser) {
throw new Error("User not created");
}
const roleModel = new Role(tx);
const roleData = await role.create({ name: "testRole" });
if (!roleData) {
throw new Error("Role not created");
}
return [newUser, roleData];
});
if (result instanceof Error) {
console.error(result);
} else {
console.log(result);
}
Transactions Example 3
const model = new Role();
const inputData = {
name: "newRole",
users: [
{ email: "newUser@newRole.com", password: "12345678" },
{ email: "newUser2@newRole.com", password: "12345678" },
],
};
const result = await model.createTX(inputData);
Register Effects
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) => {
});