For all the examples below

import {PgDb, PgSchema, PgTable} from "pogi";

let pgdb:PgDb     = PgDb.connect(..);
let table:PgTable<User> = pgdb.schemas.test1.users;  

export interface InsertOption {
    logger?: PgDbLogger;
}

export interface UpdateDeleteOption {
    logger?: PgDbLogger;
}

export interface TruncateOptions{
    restartIdentity?: boolean,
    cascade?: boolean,
    logger?: PgDbLogger;
}

export interface Return {
    return?:string[]|'*';
}

export interface Stream {
    stream: true;
}

Properties

db

db:PgDb Back reference to the db instance

Functions

toString

toString()

Returns the fully qualified name of the table

setLogger

setLogger(logger:PgDbLogger)

Note: inherited.

Sets the logger per table (not used if the query has logger specified).

Functions - async

count

count(conditions?:{}):Promise<number>

Run a count query

let count = await table.count({id:2});
console.log(count); //most probably 1

---

delete

delete(conditions:{}, options?:UpdateDeleteOption):Promise<number>

Executes a delete-where query.

let numberOfRowsDeleted = await table.delete({id:[1,2,3]});
if (numberOfRowsDeleted!=3) {
    //alarm!!
}

---

deleteOne

deleteOne(conditions:{}, options?:UpdateDeleteOption):Promise<number>

Executes a delete-where query, but throws exception if more then one record is deleted;

let numberOfDeleted = await table.deleteOne({id:[1,2,3]}); //throws exception if more then one record is deleted
console.log(numberOfDeleted); //0 or 1

---

deleteAndGet

deleteAndGet(conditions:{}, options?:UpdateDeleteOption & Return):Promise<T[]>

Executes a delete-where query and returns with the deleted records;

let playersDeleted = await table.deleteAndGet({id:[1,2,3]});
for (let player of playersDeleted) {
    console.log(player.id); //1 then 2 then 3
}

---

deleteAndGetOne

deleteAndGetOne(conditions:{}, options?:UpdateDeleteOption & Return):Promise<T>

Executes a delete-where query, but throws exception if more then one record is deleted; Returns with the deleted record if any.

let playerDeleted = await table.deleteAndGet({id:[1,2,3]});  //throws exception if more then one record is deleted
console.log(player.id); //Either 1, 2, 3 or null if no record is deleted

---

find

find(conditions:{}, options?:QueryOptions):Promise<T[]>

find(conditions:{}, options?:QueryOptions & Stream):Promise<ReadableStream>

Executes a select-where query.

let playerList = await table.find({id:[1,2,3]});
for (let player of playerList) {
    console.log(player.id); //1..2..3
}

playerList = await table.find({id:[1,2,3]}, {fields:['id', 'name'], limit:3});

for more options for conditions and queryOptions see those sections.

If the option has {stream:true} parameter it returns a stream instead of an array. See streams for example.

---

findWhere

findWhere(where:string,params:any[]|{},options?:QueryOptions):Promise<ReadableStream>

findWhere(where:string,params:any[]|{},options?:QueryOptions & Stream):Promise<ReadableStream>

Executes a select-where query with free text where etc.

let res;

res = await table.where("permissions @> {'admin'} AND name!=username AND id=$1  LIMIT 2", [1]);

res = await table.where("permissions @> {'admin'} AND name!=username AND id=:id LIMIT 2", {id:1});

If the option has {stream:true} parameter it returns a stream instead of an array. See streams for example.

---

findAll

findAll(options?:QueryOptions):Promise<T[]>

findAll(options?:QueryOptions & Stream):Promise<ReadableStream>

Returns everything from the table. Same as table.find({})

let res = await table.findAll();

If the option has {stream:true} parameter it returns a stream instead of an array. See streams for example.

---

findOne

findOne(conditions, options?:QueryOptions):Promise<T>

Most system get this wrong, as they use it as "findFirst" instead of using as "findOnly". While 99% of the time the programmer means the latter, by default they use the formal. That is mostly just hiding bugs instead of revealing issues as soon as possible. It's hard to count how much time it saved me to find an issue, not to mention that it found earlier then otherwise would find out. Very good investment for a small bit of Defensive programming.

Therefore it throws exception if more then one record match the select query.

let res1 = await table.findOne({id:1});
let res2 = await table.findOne({'name like': 'A%'}); //most probably throws an exception

---

findFirst

findFirst(conditions, options?:QueryOptions):Promise<T>

Same as await table.find(condition, {limit:1})

let somebody = await table.findFirst({'score >':9000});

---

findOneFieldOnly

findOneFieldOnly(conditions:{}, field:string, options?:QueryOptions):Promise<any>

Returns directly the value of a column/field directly.

let nameOfUser = await table.findOneFieldOnly({id:1}, 'name');
console.log(nameOfUser); //most probably 'Admin'

---

insert

insert(records:T, options:InsertOption): Promise<T>

insert(records:T[], options:InsertOption): Promise<T[]>

You can insert one or multiple records, by default the new record(s) will be returned. This can be prevented if not needed;

let user = await table.insert({username:'anonymous'}); //returns the whole record
console.log(user.id); // generated by postgresql
//or
let userList = await table.insert([{username:'anonymous'},{username:'anonymous2'}], {return:['id']});
console.log(userList[0].id); // generated by postgresql

await table.insert({username:'anonymous2'}, {return:[]}); //returns [{}]

---

insertAndGet

insert(records:T, options:InsertOption & Return): Promise<T>

insert(records:T[], options:InsertOption & Return): Promise<T[]>

You can insert one or multiple records, by default the new record(s) will be returned. This can be prevented if not needed;

let user = await table.insert({username:'anonymous'}); //returns the whole record
console.log(user.id); // generated by postgresql
//or
let userList = await table.insert([{username:'anonymous'},{username:'anonymous2'}], {return:['id']});
console.log(userList[0].id); // generated by postgresql

await table.insert({username:'anonymous2'}, {return:[]}); //returns [{}]

---

update

update(conditions:{}, fields:{}, options?:UpdateDeleteOption):Promise<number>

Run an update query on the table, returns the number of records changed.

await table.update({},{score:null}); //all record is updated
await table.update({'name ~': '^G'}, {numOfLifes:4}); //all record where name starts with G has the numOfLifes set to 4. It's a G'day!

---

updateOne

updateOne(conditions:{}, fields:{}, options?:UpdateDeleteOption): Promise<number>

Run an update query, throws exception if more then one record has been updated. (Handy if you roll back on exception)

await table.updateOne({id:1},{password:null});
await table.updateOne({notUniqId:1},{password:null}); //throws exception if more then 1 rec has been updated;

---

updateAndGet

updateAndGet(conditions:{}, fields:{}, options?:UpdateDeleteOption & Return):Promise<T[]>

Run an update query on the table

let playerList = await table.updateAndGet({'score >': '9000'}, {achivement:"It's Over 9000!"}); 
//update the achievement fields for all players where the score is over 9000 then returns the updated list

let playerIdList = await table.updateAndGet({'score >': '9000'}, {achivement:"It's Over 9000!"}, {return:['id']});

---

updateAndGetOne

updateAndGetOne(conditions:{}, fields:{}, options?:UpdateDeleteOption & Return): Promise<T>

Run an update query and returns with the updated record, throws exception if more then one record has been updated. (Handy if you roll back on exception)

let user = await table.updateOne({id:1},{password:null});
console.log(user.name); //the whole record is returned

---

query

query(sql:string, params?:any[]|{}, options?:SqlQueryOptions):Promise<any[]>

Note: inherited, uses table level log if present (if not then schema, then db).

Executes an arbitrary sql string with parameters / named parameters;

let res1 = await table.query('SELECT MAX(point) from game1.scores WHERE name=$1 ', ['player1']);
let res2 = await table.query('SELECT MAX(point) from !:schema.scores WHERE name=:name ', {schema:'game1', name:'player1'});

---

queryOneField

queryOneField(sql:string, params?:any[]|{}, options?:SqlQueryOptions):Promise<any>

Note: inherited, uses table level log if present (if not then schema, then db).

If there is only one record and one field that we are interested in. For the params usage see query.

let winner = await table.getOneField(`SELECT 'The winner is ' || name FROM ${table} LIMIT 1`);
console.log(winner); //The winner is Admin

---

queryOneColumn

queryOneColumn(sql:string, params?:any[]|{}, options?:SqlQueryOptions):Promise<any[]>

Note: inherited, uses table level log if present (if not then schema, then db).

If there is only one column that we are interested in. For the params usage see query.

let userList = await table.getOneColumn(`SELECT name FROM ${table}`);
console.dir(userList); //['Admin', 'User1', 'User2']

---

queryAsStream

queryAsStream(sql:string, params?:any[]|{}, options?:SqlQueryOptions):Promise<any[]>

see streams

---

run

run(sql:string):Promise<any[]>

Note: inherited, uses table level log if present (if not then schema, then db).

Executes an arbitrary sql string;

await table.run('CREATE schema myschema');

---

truncate

truncate(options?:TruncateOptions):Promise<void>

Will truncate the table, see PostgreSQL doc.

export interface TruncateOptions{
    restartIdentity?: boolean,
    cascade?: boolean,
    logger?: PgDbLogger;
}