For all the examples below
import {PgDb} from "pogi";
let pgdb = PgDb.connect(..);
Note: search path will be readed on connection and tables and fn properties will be populated by that. However these are not merged into PgDb object, while schemas will be.
Properties
db
db:PgDb Back reference to this instance.
schemas
schemas:{[name:string]:PgSchema} Schemas, also merged to db object.
tables
tables:{[name:string]:PgTable} Tables in the search_path.
fn
fn:{[name:string]:Function} Stored procedures and functions in the search_path.
Functions
setLogger
setLogger(logger:PgDbLogger)
Note: inherited.
Sets the fallback logger for all queries (if no schema, table or query level logger is set, this will be used).
pgdb.setLogger(console);
isTransactionActive
isTransactionActive():Promise<PgDb>
Returns true if the active connection has transaction ongoing. (Does not detect timeouts.)
Functions - async
connect
static connect(config:ConnectionOptions):Promise<PgDb>
see connection section
close
static close():Promise<void>
Close connection, useful for exiting/teardown code.
dedicatedConnectionBegin
dedicatedConnectionBegin():Promise<PgDb>
You can use dedicated connection instead of pool. It is very useful if you plan to use such a command: SET search_path TO "dev";
Similar to transactionBegin but without transaction. This function will create a new PgDb instance with the dedicated connection mode.
Use of that Pgdb instance all query will go throuth that single connection. The original pgdb instance won't touched.
For example see the transaction section.
dedicatedConnectionEnd
dedicatedConnectionEnd():Promise<PgDb>
Close dedicated connection. If there is no dedicated connection, do nothing. After that pgdb instance will work in pooled connection mode. Return value will be the same pgdb instance. For example see the transaction section.
execute
execute(fileName, transformer?:(string)=>string)):Promise<void>
Executes an sql file, with a transformer function. For more details see Executing sql files section.
for (let schemaName of ['test1', 'test2']) {
await pgdb.execute(__dirname + '/db_upgrade/all.sql', (cmd)=>cmd.replace(/__SCHEMA__/g, '"' + schemaName + '"'));
}
where the sql file is (__SCHEMA__ will be replaced to the schemaName see above)
UPDATE __SCHEMA__.webapp set lang='TS' where lang='JS';
query
query(sql:string, params?:any[]|{}, options?:SqlQueryOptions):Promise<any[]>
Executes an arbitrary sql string with parameters / named parameters.
let res1 = await schema.query('SELECT MAX(point) from game1.scores WHERE name=$1 ', ['player1']);
let res2 = await schema.query('SELECT MAX(point) from !:schema.scores WHERE name=:name ', {schema:'game1', name:'player1'});
queryFirst
queryFirst(sql:string, params?:any[]|{}, options?:SqlQueryOptions):Promise<any>
Executes an arbitrary sql string with parameters / named parameters. Return the first record.
queryOne
query(sql:string, params?:any[]|{}, options?:SqlQueryOptions):Promise<any>
Executes an arbitrary sql string with parameters / named parameters. Return the first record, throw Error if there are more.
queryOneField
queryOneField(sql:string, params?:any[]|{}, options?:SqlQueryOptions):Promise<any>
Note: inherited, uses schema level log if present (if not then the db level log).
If there is only one record and one field that we are interested in. For the params usage see query.
let winner = await schema.getOneField(`SELECT 'The winner is ' || name FROM test1.users LIMIT 1`);
console.log(winner); //The winner is Admin
queryOneColumn
queryOneColumn(sql:string, params?:any[]|{}, options?:SqlQueryOptions):Promise<any[]>
Note: inherited, uses schema level log if present (if not then the db level log).
If there is only one column that we are interested in. For the params usage see query.
let userList = await schema.getOneColumn('SELECT name FROM test1.users');
console.dir(userList); //['Admin', 'User1', 'User2']
queryAsStream
queryAsStream(sql:string, params?:any[]|{}, options?:SqlQueryOptions):Promise<any[]>
reload
reload() Rerun the queries to load the schemas, tables and special types. Need to be called after truncate(!), alter table, create schema etc.
run
run(sql:string):Promise<any[]> Executes an arbitrary sql string.
await schema.run('CREATE schema myschema');
setTypeParser
setTypeParser(typeName:string, parser:(string)=>any, schemaName?:string): Promise<void>
See the mapping database types to js types section
setPgTypeParser
setPgTypeParser(typeName:string, parser:(string)=>any, schemaName?:string): Promise<void>
See the mapping database types to js types section
setPostProcessResult
setPostProcessResult(f:(res: any[], fields: ResultFieldType[], logger:PgDbLogger)=>void): void You can add a postprocessor function that will be executed for every result (even empty ones), if you want to do something extra. If you call it twice the second function will overwrite the first. So you can easily unset also if you call it will null;
transactionBegin
transactionBegin():Promise<PgDb>
Return with a new PgDb instance with dedicated connection mode and start a transaction. (Only this connection has the transaction, can be committed or rolled back. Similar to dedicatedConnectionBegin)
For example see the transaction section.
transactionCommit
transactionCommit():Promise<PgDb>
If the PgDb instance has dedicated connection mode and has transaction it will commits that, otherwise do nothing. Returns with PgDb instance (with pool connections mode) where no transaction is taking place. For example see the transaction section.
transactionRollback
transactionRollback():Promise<PgDb>
If the PgDb instance has dedicated connection mode and has transaction it will rolls back, otherwise do nothing. Returns with PgDb instance (with pool connections mode) where no transaction is taking place. For example see the transaction section.
listen
listen(channel:string, callback:(Notification)=>void);
Creates a new dedicated connection for listeners (if it doesn't exists), and sets a callback for the channel. It is possible to set multiple callbacks for one channel. If there will be a notification from the database, the callback will be executed. For example see the notification section.
unlisten
unlisten(channel:string, callback?:(Notification)=>void);
Removes a listener. If callback parameter is set, only the given callback will be removed. If callback parameter is not set, all callbacks will be removed from the channel. If it was the last channel, the dedicated connection for listeners will be released. For example see the notification section.
notify
notify(channel:string, payload?:string;
Send a notification via postgresql. For example see the notification section.