my-db-utils

npm install my-db-utils

Define a simple Db Model

const { dbModel, table, column } = require("my-db-utils");

const myDbModel = dbModel("MyModelName", table("my_table_name"), {
    id: column("id_column").pk().integer(),
    name: column("name_column").string(),
    description: column("description_column").string().nullable()
});

const MyModel = myDbModel.constructor;

Using model for factory object

const myItem1 = MyModel({
    id: 1,
    name: "name",
});

const myItem2 = MyModel({
    id: 2,
    name: "name 2",
    description: "My description 2"
});

Using model Crud

const { Pool } = require('pg');
const { crud } = require('my-db-utils');

const dbPool = new Pool();
const myModelCrud = crud(myDbModel, dbPool);

Create

myModelCrud.create([myItem1, myItem2])
    .then(items => console.log(items))
    .catch(error => concole.error(error));

Read

myModelCrud.read({id: 1})
    .then(items => console.log(items)) // items Array
    .catch(error => concole.error(error));

Update

myItem1.description = "My description";
myItem2.name = "My new name 2";

myModelCrud.update([myItem1, myItem2])
    .then(() => console.log("success"))
    .catch(error => concole.error(error))

Delete

myModelCrud.delete({id: 1})
    .then(() => console.log("success"))
    .catch(error => concole.error(error))

Advanced Model types

const { dbModel, table, column } = require("my-db-utils");

const myAdvancedDbModel = dbModel("MyAdvancedModel", table("my_advanced_table_name"), {
    // Use default Db definition if value is null, Ej: Sequence generation for PK
    id: column("id_column").pk().integer().default(), 
    // Array of strings
    names: column("names_column").array().string(), 
    // JSON column
    address: column("address_column").json().nullable(),
    // TIMESTAMP WITHOUT TIME ZONE column 
    createdAt: column("created_at_column").timeStamp(), 
    // TIMESTAMP WITH TIME ZONE column
    scheduleTime: column("schedule_time_column").timeStampTZ(), 
});

const MyAdvancedModel = myAdvancedDbModel.constructor;

const myAdvancedItem1 = MyAdvancedModel({
    names: ["name 1.1", "name 1.2"],
    address: {city: "NewYork", address: "Calle 34 # 16", intern: "Apto 920"},
    createdAt: new Date(),
    scheduleTime: new Date(2021, 05, 13, 19, 50),
})

Advanced Search (For read and delete)

IN

myModelCrud.read({ id: [1, 2]})    // WHERE id IN (1,2)
    .then(items => console.log(items))
    .catch(error => concole.error(error));

Searh by text

// WHERE name LIKE "%name%"
myModelCrud.delete({ name: "%name%" })    
    .then(() => console.log("success"))
    .catch(error => concole.error(error));

AND

// WHERE name LIKE '%name%' AND description LIKE '%pretty%'
myModelCrud.read({ name: "%name%", description: "%pretty%" }) 
    .then(items => console.log(items))
    .catch(error => concole.error(error));

Relational operators

const { condition } = require("my-db-utils");

const { equal, gretter, gretterEqual, less, lessEqual, different } = condition;

// WHERE id > 5
myModelCrud.read({ id:  gretter(5)}) 
    .then(items => console.log(items))
    .catch(error => concole.error(error));

// WHERE id <= 10
myModelCrud.read({ id:  lessEqual(10)}) 
    .then(items => console.log(items))
    .catch(error => concole.error(error));

// WHERE createdAt < '2020-01-15'
myAdvancedModelCrud.delete({createdAt: less(new Date(2020, 0, 15))}) 
    .then(() => console.log("success"))
    .catch(error => concole.error(error));