By using the same example on your repo. here
https://github.com/vitaly-t/pg-promise-demo/blob/master/TypeScript/db/index.ts

I'm getting this Typescript error saying: [ts] Cannot invoke an expression whose type lacks a call signature. Type 'typeof pgPromise' has no compatible call signatures. [2349] !!!

import { IMain, IDatabase, IOptions } from "pg-promise";
import * as pgPromise from "pg-promise";

const pgp: IMain = pgPromise(); // Error pop up here!!

Using ES6 in tsconfig.json

Any idea?

Hi @vitaly-t! I have a doubt in the code given below.
I have 2 functions => createTable and createTable2
When I use them as =>
db.users.createTable2({appid: '123'}); => WORKS
But db.users.createTable({appid: '123'}); => DOESNT WORK

I want my db.users.createTable to also work
createTable.sql has the query => CREATE TABLE tbl_${appid}(dt date, txt text);

[ CODE INSIDE THE USERS.ts FILE is ]
`import {IDatabase, IMain} from 'pg-promise';
import {IResult} from 'pg-promise/typescript/pg-subset';
import {users as sql} from '../sql';
export class UsersRepository {
constructor(private db: IDatabase, private pgp: IMain) {
}

async createTable(values: {appid: string}): Promise<null> {
    return this.db.none(sql.createTable, {
        appid: values.appid
    });
}

async createTable2(values: {appid: string}): Promise<null> {
    return this.db.none(sql.createTable, {
        appid: +values.appid
    });
}

}`

The postgreSQL query which is being executed is CREATE TABLE tbl_'123'(dt date, txt text); => I dont Want to add apostrophe outside my ${appid} variable.

Can you please help me?

Dear Vitaly,

Firstly, I wanted to thank you for your work on pg-promise, I have learned a lot reading your brilliantly documented example.

I'm struggling hard to get my pg-promise app to work as in your example. I have stripped it down to the minimum to identify a mistake but it seems identical to the example and yet I get the following error:

TypeError: Cannot read property 'findById' of undefined

This is what my app looks like:

// fiche.js
class ficheRepository {
    constructor(db, pgp) {
        this.db = db;
        this.pgp = pgp;
    }

    findById(numid) {
        return this.db.oneOrNone('SELECT * FROM fiche WHERE id = $1', +numid);
    }
}

module.exports = ficheRepository;

// connect.js
const promise = require('bluebird');

const repos = {
    fiches: require('./fiches')
};

const options = {
    promiseLib: promise,
    extend: (obj) => {
        obj.users = new repos.fiches(obj, pgp);
	}
};

const pgp = require('pg-promise')(options);

const username = "postgres";
const password = "postgres"; 
const host = "localhost:5432";
const database = "osi";

const cn = "postgres://"+username+":"+password+"@"+host+"/"+database;

const db = pgp(cn);

module.exports = db;

// server.js
const express = require('express');
const cors = require('cors');
const db = require('./connect');

const app = express();

app.use(cors())
app.set('port', (process.env.PORT || 3001));


GET('/fiche/id/:id', req => db.fiche.findById(req.params.id));

function GET(url, handler) {
    app.get(url, (req, res) => {
        handler(req)
            .then(data => {
                res.json({
                    data
                });
            })
            .catch(error => {
                res.json({
                    success: false,
                    error: error.message || error
                });
            });
    });
}

app.listen(app.get('port'), function () {
    console.log('Express server listening on port ' + app.get('port'));
});```

Following this issue, once it is resolved, the demo must be updated to build in strict mode also.

HI Vitaliy, First of all, I highly appreciate your work with the great library.
I have used this demo to build our solution but I did not find any done() methods to release connections in the demo.
Is it needed not 'no-tasks' queries?
In documentation, it is mentioned that connections are release for 'task' methods but I did not find any details for 'no-tasks'.

Initial tsconfig.json

{
  "compilerOptions": {
    "target": "es5",
    "module": "commonjs",
    "strict": true,
    "esModuleInterop": true
  }
}

And I get a bunch of errors in TS compiler.
However one error that I can not avoid:

node_modules/pg-promise/typescript/pg-promise.d.ts:14:27 - error TS2497: Module '"/...project folder.../node_modules/pg-promise/typescript/ext-promise"' resolves to a non-module entity and cannot be imported using this construct.

14 import * as XPromise from './ext-promise'; // External Promise Provider
                             ~~~~~~~~~~~~~~~

Because of this Error I must copy all these files into src and modify this
import * as XPromise from './ext-promise'; to this import XPromise = require('./ext-promise');

Can I avoid this error without modifying your source code?

Hi,

Can some one help me in setting up dynamic schema name.

I am new to nodejs development and using Javascript version of it and in JavaScript/db/sql/index.js file schema name can be set in sql helper function

`
///////////////////////////////////////////////
// Helper for linking to external query files;
function sql(file) {
...

var options = {
    ...

    // Showing how to use static pre-formatting parameters -
    // we have variable 'schema' in each SQL (as an example);
    params: {
        schema: 'public' // replace ${schema~} with "public"
    }
};
...

}
`

In the above I can set up schema name however is it possible to set it dynamically from JavaScript/index.js. Something like

`
var db = require('./db');

db.setSchema('mySchemaName');

// run the query and it will inject 'mySchemaName' in the query
db.users.add(req.params.name);
`

Please let how the above can be achieved.

Kind regards,
Javed Gardezi

Could you please show an example on how to handle more complex transactions using pgp that may contain a bit of "business logic" in it.

Greetings,

I've followed your instructions on how to properly separate our structure using pg-promise, and everything's working fine. I'm building a Slack slash commands using it, and when selecting data from database, I need it to display something like [username] is [status]. Following other operation's example, I suppose it would be something like this:

select: () =>
    rep.any('SELECT username, status, emoji FROM ' + table)
    .then(res => ?),

But I cannot access these columns nor I have found examples of such. Is it possible to be done?

Thank you.

Hi there,

I am new to databases and I am curious what is the meaning of repository in the context of databases and this demo. Searching on google does not render sufficient understandable information ☺️.

Thanks!

Could you show me a project structure, according to provided in this repository, which allows me to realize many-to-may relationship? Should I create an allotted repo for UserProduct or I should try to implement the logic of creation additional table in user or product class?
I mean, should the project structure looks like bellow:

/db/repos/user
/db/repos/product
/db/repose/userProduct

I would find it useful with more advanced examples involving many-to-many relations.

• Fantastic work with your library and answers on stackoverflow under [pg-promise]
• I just wanted to point out that your repo structure is solid when things are smooth
• There are some problems when there is an error though
• Say a query fails for some reason, the UserRepository is not handling any errors
• It gets straight to the API endpoint handling router in your index.js
• Here also you have not handled any errors

For example I tested a non existing user to check how the code reacts

(node:6745) UnhandledPromiseRejectionWarning: error: role "super_duper_root" does not exist
    at Parser.parseErrorMessage (/Users/zup/Desktop/code/ACTIVE/super_duper/node_modules/pg-protocol/dist/parser.js:241:15)
    at Parser.handlePacket (/Users/zup/Desktop/code/ACTIVE/super_duper/node_modules/pg-protocol/dist/parser.js:89:29)
    at Parser.parse (/Users/zup/Desktop/code/ACTIVE/super_duper/node_modules/pg-protocol/dist/parser.js:41:38)
    at Socket.<anonymous> (/Users/zup/Desktop/code/ACTIVE/super_duper/node_modules/pg-protocol/dist/index.js:8:42)
    at Socket.emit (events.js:311:20)
    at Socket.EventEmitter.emit (domain.js:482:12)
    at addChunk (_stream_readable.js:294:12)
    at readableAddChunk (_stream_readable.js:275:11)
    at Socket.Readable.push (_stream_readable.js:209:10)
    at TCP.onStreamRead (internal/stream_base_commons.js:186:23)
(node:6745) UnhandledPromiseRejectionWarning: Unhandled promise rejection. This error originated either by throwing inside of an async function without a catch block, or by rejecting a promise which was not handled with .catch(). To terminate the node process on unhandled promise rejection, use the CLI flag `--unhandled-rejections=strict` (see https://nodejs.org/api/cli.html#cli_unhandled_rejections_mode). (rejection id: 2)
(node:6745) [DEP0018] DeprecationWarning: Unhandled promise rejections are deprecated. In the future, promise rejections that are not handled will terminate the Node.js process with a non-zero exit code.

• If you use POSTMAN to query the endpoint, the request gets stuck because we did not return any response in case of an error

• Do you think the below one would be a better structure?

router.get('/', async (req, res) => {
  try {
    res.json(await db.users.all())
  } catch (error) {
    logger.error(error)
    return res.json(false)
  }
})

Hi,

I try transaction with your demo.
I added a GET which must first insert a User then a Product (if error during one, cancel all)

Maybe I'm wrong and just do not use the transactions correctly: is it possible to use 'this.add' instead of 't.products.add'? Because it's in repo products. Or is it this story of "context" specified in the doc?

// ./index.js
GET('/products/addNew/:userName/:productName', req => db.products.txTest(req.params));

the called method :

// ./db/repos/products.js
    txTest(values) {
        return this.db.tx('add-fullProduct', async (t)=> {
            const user = await t.users.add(values.userName);

            //return this.add({ userId: user.id, name: values.productName }); // ERROR : Key (user_id)=(17) is not present in table \"users\"."
            return await t.products.add({ userId: user.id, name: values.productName }); // works
        })
        .then(data => {
            data.done = true; // for example
            return data;
        })
        .catch(ex => {
            console.log(ex.message);
            return ex;
        });
    }

Hi!

I'm trying to enhance my app structure following this demo.

In repos/producs.js you return an object with one property being:
add: values => rep.one(sql.add, values, user => user.id)

I'm trying to use pgp.helpers instead, like:

const {pgp} = require('./../../../database');
const csUser = new pgp.helpers.ColumnSet([
      {name: 'email'}, 
      {name: 'first_name', prop: 'firstName', def: null}, 
      {name: 'last_name', prop: 'lastName', def: null}], {table: 'user'});

module.exports = (rep, pgp) => {
   return {
      add: values => rep.one(pgp.helpers.insert(values, csUser) + 'RETURNING id', [], user => user.id)
   }
}

I got an error because I was trying to use pgp before it was created so it was undefined. The solution I found was to simply create csUser inside module.exports:

module.exports = (rep, pgp) => {
   const csUser = new pgp.helpers.ColumnSet([...], {table: 'user'});

   return {
      add: values => rep.one(pgp.helpers.insert(values, csUser) + 'RETURNING id', [], user => user.id)
   }
}

And with this I'd like to ask you:

1. Is it valid to use pgp.helpers instead of sql approach like in this demo? I'm using pgp.helpers because of the flexibility that provides with ColumnSet and because I'm planning to do massive inserts.
2. Is it ok to define my columns set inside module.exports? I have doubt of doing this because you mention when we extend the database protocol that "Do not use 'require()' here, because this event occurs for every task and transaction being executed, which should be as fast as possible." so what I understand with this is what we place inside extend function is executed every time we make a task or tx, so creating my columns set this way is bad for performance.

Thanks!

Trying to map values as query parameters, but running into this issue:

error: syntax error at or near "$"

SQL file looks like this:

INSERT INTO products (user_id, name) VALUES(${userId}, ${productName}) RETURNING *

VSCode is also picking up the ${userId} as a syntax error... how do I fix this?

In previous versions of the repo, db\repos looked looked like this:

var sql = require('../sql').products

module.exports = rep => {
  create: () => rep.none(sql.create),
  drop: () => rep.none(sql.drop)
}

Now you've changed it to use classes.

const sql = require('../sql').products;

class ProductsRepository {
    constructor(db, pgp) {
        this.db = db;
        this.pgp = pgp;
    }

    create() {
        return this.db.none(sql.create);
    }

    drop() {
        return this.db.none(sql.drop);
    }
}

I'm curious as to why you've made this change for the demo? Thanks for the input.

In following line:

pgp is used, in Objects initialization, but "pgp" is later initialized with:

const initOptions = {
   promiseLib: promise,
    extend(obj, dc) {
        obj.users = new Users(obj, pgp);
        obj.products = new Products(obj, pgp);
    }
};

// Initializing the library:
const pgp = pgPromise(initOptions);    //// ??? pgp needs initOptions ... and initOptions extends uses pgp which is initialized below? 

// Creating the database instance:
const db = pgp(dbConfig);

pgp needs initOptions ... and initOptions extends uses pgp which is initialized below?

Can you please explain what mean by: "Do not however reuse any of the HTTP-service code, it is over-simplified, for the test, not for you to copy."

We were thinking of using this demo as a type skeleton for our DB project, and were not clear what you meant about the http-service code. Thanks.

You have an example in the query file using what looks like es6 placeholders. It would be great to see that explained in the comments or otherwise. It would also be great to have a ParameterizedQuery example added.