pgObject - simple ORM for PostgreSQL and MySql

For PostgreSQL orm is used with PG

For MySQL orm is used with mysql or mysql2

Exaple

Quick Example (PostgreSQL). For MySql it works the same.

1. Create a table in the database:

CREATE TABLE userExample (
    id           SERIAL,
    name         VARCHAR(60),
    surname      VARCHAR(60),
    email        VARCHAR(60),
    token        VARCHAR(64),
    createdDate  timestamp(6) with time zone,

    PRIMARY KEY (id)
);

2. Create a class:

const PgObject = require('pgobject');

class UserExample extends PgObject {
    static get schema() {
        return {
            id: {
                pk: true,
            },
            name: {},
            surname: {},
            email: {
                required: true,
            },
            token: {},
            createdDate: {
                default: new Date(),
            }
        }
    }

    static get table() {
        return 'userExample';
    }
}

3. pgObject configuration:

PostgreSQL:

const { Client } = require('pg');
const PgObject = require('pgobject');

const client = new Client({
    user: process.env.DB_USER,
    host: process.env.DB_HOST,
    database: process.env.DB_NAME,
    password: process.env.DB_PASS,
    port: process.env.DB_PORT,
});

await client.connect();

PgObject.setClient(client);

MySQl:

const mysql = require('mysql2');
const PgObject = require('pgobject');

const client = mysql.createConnection({
    user: process.env.DB_USER,
    host: process.env.DB_HOST,
    database: process.env.DB_NAME,
    password: process.env.DB_PASS,
    port: process.env.DB_PORT,
});

PgObject.setClient(client, 'mysql');

4. Use this class:

const userExample = new UserExample({
    name: "nameExample",
    surname: "surnameExample",
});

console.log(userExample.f.name); // "nameExample"
console.log(userExample.f.surname); // "surnameExample"

await userExample.save(); // OR await userExample.insert();

const users1 = await UserExample.select("WHERE name = $1 LIMIT 1", ['nameExample']);
// OR await PgObject
//    .query("SELECT * FROM userExample WHERE name = $1 LIMIT 1", ['nameExample'], UserExample);

console.log(users1[0].f.name); // "nameExample"
console.log(users1[0].f.surname); // "surnameExample"

users1[0].f.surname = "newSurname";
await users1[0].save(); // OR await users1[0].update();

const users2 = await UserExample.select("WHERE name = $1 LIMIT 1", ['nameExample']);
console.log(users1[0].f.name); // "nameExample"
console.log(users1[0].f.surname); // "newSurname"

await users2[0].delete();

Query to DB

1. Static method select:

const users = await UserExample.select("WHERE name = $1 LIMIT 1", ['nameExample']);

• static select(String where, Array values) - return array of UserExample objects;

2. Static method query:

const users = await PgObject.query("SELECT * FROM userExample WHERE name = $1 LIMIT 1", ['nameExample'], UserExample);
// OR
 const users = await UserExample.query("SELECT * FROM userExample WHERE name = $1 LIMIT 1", ['nameExample'], UserExample);

• static query(String query, Array values, class PgObjecExtension) - return array of UserExample objects;

const users = await PgObject.query("SELECT * FROM userExample WHERE name = $1 LIMIT 1", ['nameExample']);
// OR
const users = await UserExample.query("SELECT * FROM userExample WHERE name = $1 LIMIT 1", ['nameExample']);

• static query(String query, Array values) - see pg query documentation

3. Method save

 const user = new UserExample({
    name: "nameExample",
    surname: "surnameExample",
});

// in this case there will be an insert
await user.save();
user.f.name = "newName";
// in this case there will be an update
await user.save();

-save() - depending on we saved this object in the database will be an insert or an update

4. Method insert

const user = new UserExample({
    name: "nameExample",
    surname: "surnameExample",
});

await user.insert();

-insert() - save this object to the database

5. Method update

const users = await UserExample.select("WHERE name = $1 LIMIT 1", ["nameExample"]);
users[0].f.name = "newName";

await users[0].update();

-update() - update this object in the database

5. Method delete

const users = await UserExample.select("WHERE name = $1 LIMIT 1", ["nameExample"]);


await users[0].delete();

-delete() - delete this object from the database

PgObject class extension

For the PgObject class extension we must define static get schema() and static get table()

const PgObject = require('pgobject');

class UserExample extends PgObject {
    static get schema() {
        return {
            id: {
                pk: true,
            },
            name: {},
            surname: {},
            email: {
                set (val, target, name) {
                    if (target[name]) {
                        return target[name];
                    }
                    return val;
                },
                required: true,
            },
            token: {
                get (val, target, name) {
                    if (!val) {
                         Math.random().toString(36).substr(2);
                    }
                    return val;
                },
            },
            createdDate: {
                default: new Date(),
            },
            fullName: {
                computed: true,
                get (val, target, name) {
                    return `${target['name']} ${target['surname']}`;
                },
            }
        }
    }

    static get table() {
        return 'userExample';
    }

    static get notValidateSchema() {
        // default return false
        return true;
    }
}

1. static get schema() - return object:

• pk: true - PRIMARY KEY
• computed: true - This is a calculated field - it does not exist in the database
• required: true - This fied is required
• default: new Date() - Default value
• get (val, target, name) - val: current value, target: all fields, name: name of this field
• set (val, target, name) - val: new value, target: all fields, name: name of this field

pk: true: You should define at least one field as the primary key. If your schema doesn't have a primary key you will see an error.

Error, please set the PRIMARY KEY in the UserExample class

computed: true: If you want to have a field that does not exist in the database table and will not participate in an insert or update, you must set a computed property. Example: fullName is computed field

class UserExample extends PgObject {
    static get schema() {
        return {
           ...
            fullName: {
                computed: true
            }
        }
    }
    ...
}

const user = new UserExample({
    fullName: 'Test Tets',
    ...
});

console.log(user.f.fullName); // 'Test Tets';

// fullName will not save in the database
await user.save();

required: true: If you try to save your UserExample object to the database without values of the required fields - you will see an error. Example: email is required field

class UserExample extends PgObject {
    static get schema() {
        return {
           ...
            email: {
               required: true,
            }
        }
    }
    ...
}

const user = new UserExample({
    name: "nameExample",
    surname: "surnameExample",
});

await user.save();
// console:
// Error: email field in UserExample object is required;

default: someDefaultValue You can set default values for fields

class UserExample extends PgObject {
    static get schema() {
        return {
           ...
            name: {
               default: 'Test',
            }
        }
    }
    ...
}

const user = new UserExample();
console.log(user.f.name); // 'Test';
// the name field will save with the default value ('Test');
await user.save();

user.f.name = 'newValue';
// the name field will save with the new value ('newValue');
await user.save();

get (val, target, name) - is like standart getter val - the current value of this field target - all fields of this object (this.f) name - string, name of this field Example:

class UserExample extends PgObject {
    static get schema() {
        return {
            id: {
                pk: true,
            },
            name: {
                default: 'TEST NAME',
            },
            surname: {
                 get(val, target, name) {
                    console.log('val:', val);
                    console.log('target:', target);
                    console.log('field name:', name);
                    console.log('full name:', target.name, val);
                    return `Surname - ${val}`;
                }
            },
            ...
    }
    ...
}

const user = new UserExample();
user.f.surname = 'SURNAME'
const surname = user.f.surname;
console.log(surname);
// console:
// val: SURNAME
// target: {
//  id: { pk: true },
//  name: { default: 'TEST NAME' },
//  surname: { get: [Function: get], value: 'SURNAME' },
//  ...
// }
// field name: surname
// full name: TEST NAME SURNAME
// Surname - SURNAME

set (val, target, name) - is like standart setter val - new value of this field target - all fields of this object (this.f) name - string, name of this field Example:

class UserExample extends PgObject {
    static get schema() {
        return {
            id: {
                pk: true,
            },
            name: {
                default: 'TEST NAME',
            },
            surname: {
                 set(val, target, name) {
                    console.log('val:', val);
                    console.log('target:', target);
                    console.log('field name:', name);
                    console.log('full name:', target.name, val);
                    return `Surname - ${val}`;
                }
            },
            ...
    }
    ...
}

const user = new UserExample();
user.f.surname = 'NEW SURNAME';
// console:
// val: NEW SURNAME
// target: {
//  id: { pk: true },
//  name: { default: 'TEST NAME' },
//  surname: { set: [Function: set] },
//  ...
// }
// name: surname
// full name: TEST NAME NEW SURNAME

// the surname field will save with the 'Surname - NEW SURNAME'  value ('newValue');
await user.save();
// console:
// QueryLog:  INSERT INTO userExample ( name, surname ) VALUES ( $1, $2 ) RETURNING *
// Values:  [ 'TEST NAME', 'Surname - NEW SURNAME' ]
// Execution time: 1 ms

2. static get table() - return string with table name

3. static get notValidateSchema() - if return true - schema will be not validated

By default you can't dynamically add fields. But be careful, if you add a field outside of your schema, you may have problems saving the object in the database. Example:

const userExample = new UserExample();
userExample.f.newfield = 'test';
// console: if static get notValidateSchema() { return false } (by default)
// Error, newfield is not in the schema, check your schema getter, or notValidateSchema should return true

Display logs

1. PgObject.setLog(true) - enable the log

If you want to see your queries to the database enable the log.

PgObject.setLog(true);

And after that, you will see log in your console. Example:

PgObject.setLog(true);
const users = await UserExample.select("WHERE name = $1 LIMIT 1", ['newName']);
// console:
// QueryLog:  SELECT * FROM admin WHERE name = $1 LIMIT 1
// Values:  [ 'newName' ]
// Execution time: 1 ms

const user = new UserExample({
    name: "nameExample",
    surname: "surnameExample",
});

await user.save();
// console:
// QueryLog:  INSERT INTO admin ( name, surname, ctime ) VALUES ( $1, $2, $3 ) RETURNING *
// Values:  [ 'nameExample', 'surnameExample', 2022-09-29T13:04:34.257Z ]
// Execution time: 1 ms

2. PgObject.setLogger(console) - enable the log

If you want use your own logger you can set yor logger. By default we use console. Example:

const logger = {
    log() {
        console.log("MyLogger", arguments)
    }
};

PgObject.setLog(true);
PgObject.setLogger(logger);

const users = await UserExample.select("WHERE name = $1 LIMIT 1", ['newName']);
// console:
// MyLogger [Arguments] {
//   '0': '\x1B[32m',
//   '1': 'Query: ',
//   '2': 'DELETE FROM exapmleAdmin WHERE id = $1',
//   '3': '\x1B[0m',
//   '4': '\nValues: ',
//   '5': [ 6 ],
//   '6': '\nExecution time:',
//   '7': 3,
//   '8': 'ms'
// }

TO JSON

If you want to serialize your object to JSON you can override toJSON() method; By default we serialize all fields in schema. Example:

const users = await UserExample.select("WHERE name = $1 LIMIT 1", ['newName']);
console.log(JSON.stringify(users));
// console:
// ["{\"id\":437,\"name\":\"TEST NAME\",\"createdDate\":\"2022-09-29T22:20:59.740Z\" ...]

Transaction

Quick Example

PgObject.setLog(true);

await PgObject.createTransaction(async () => {
    const user1 = new UserExample();
    user1.f.name = 'user1';
    const user2 = new UserExample();
    user2.f.name = 'user2';
    await user1.save();
    await user2.save();
});
// console:
// QueryLog:  START TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
// Values:  undefined
// Execution time: 1 ms
// QueryLog:  INSERT INTO admin ( name, ctime ) VALUES ( $1, $2 ) RETURNING *
// Values:  [ 'user1', 2022-09-29T13:45:20.620Z ]
// Execution time: 1 ms
// QueryLog:  INSERT INTO admin ( name, ctime ) VALUES ( $1, $2 ) RETURNING *
// Values:  [ 'user2', 2022-09-29T13:45:20.620Z ]
// Execution time: 1 ms
// QueryLog:  COMMIT
// Values:  undefined
// Execution time: 1 ms

Transaction Isolation

• READ UNCOMMITTED - PgObject.mode.readUncommitted (by default)
• READ COMMITTED - PgObject.mode.readCommitted
• REPEATABLE READ - PgObject.mode.repeatableRead
• SERIALIZABLE - PgObject.mode.serializable

example:

PgObject.setLog(true);

await PgObject.create(async () => {
    const user1 = new UserExample();
    user1.f.name = 'user1';
    const user2 = new UserExample();
    user2.f.name = 'user2';
    await user1.save();
    await user2.save();
}, PgObject.mode.serializable);
// console:
// QueryLog:  START TRANSACTION ISOLATION LEVEL SERIALIZABLE
// Values:  undefined
// Execution time: 1 ms
// QueryLog:  INSERT INTO admin ( name, ctime ) VALUES ( $1, $2 ) RETURNING *
// Values:  [ 'user1', 2022-09-29T13:45:20.620Z ]
// Execution time: 1 ms
// QueryLog:  INSERT INTO admin ( name, ctime ) VALUES ( $1, $2 ) RETURNING *
// Values:  [ 'user2', 2022-09-29T13:45:20.620Z ]
// Execution time: 1 ms
// QueryLog:  COMMIT
// Values:  undefined
// Execution time: 1 ms

PgObject public methods:

• .insert() - insert data.
• .update() - update data.
• .delete() - delete data.
• .save() - insert or update data.

PgObject static methods:

• PgObject.setClient(Object client, String type) - set db client. client - db client, type - 'mysql' or 'postgresql', by default 'postgresql'.
• PgObject.setLog(Boolean log) - if true, write a log to the console.
• PgObject.setLogger(Object logger) - install your own logger.
• PgObject.query(String query, Array values, Class classObj) - create a db query
• PgObject.select(String whereString, Array values) - select data from db
• PgObject.createTransaction(Function cb, PgObject.mode mode) - create a transaction. cb - function, mode - isolation level
• PgObject.startTransaction(PgObject.mode mode) - start transaction. mode - isolation level
• PgObject.commit() - commit transaction.
• PgObject.rollback() - rollback transaction.

PgObject override methods:

• static get schema() {}
• static get table() {}
• static get notValidateSchema() { }
• toJSON() {}

License

MIT