For all the examples below ```js 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). ```js 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](/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](#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](/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](/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](/executingSqlFile) section. ```js 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) ```sql 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. ```ts 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](#query). ```js 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](#query). ```js 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. ```js 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](/mappingDatabaseTypes) section --- ## setPgTypeParser setPgTypeParser(typeName:string, parser:(string)=>any, schemaName?:string): Promise<void> See the [mapping database types to js types](/mappingDatabaseTypes) 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](#dedicatedConnectionBegin)) For example see the [transaction](/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](/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](/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](/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](/notification) section. --- ## notify notify(channel:string, payload?:string; Send a notification via postgresql. For example see the [notification](/notification) section.