Pg-promise: Question: is it possible to have a query file with multiple SQL statements

[agorina]

I am researching pg-promise for the new project and have a question about SQL stored in a file. I see that there is a way to do it, but it looks like it expects each file to have only one query. Is it possible to set up a file with multiple queries?

Here is an example of what I am looking for with yaml file (just to give an idea). It does not have to be yaml.

sql:
    entities:
      grade:
        sql:
          update:  >-
              UPDATE grade rg
                JOIN staging_grade sg ON sg.id = rg.id
              SET
                rg.sequence = sg.sequence,
                rg.code = sg.code;
          insert: >-
              INSERT INTO grade ( id, code, sequence)
                SELECT
                  sg.id,
                  sg.code,
                  sg.sequence
                FROM staging_grade sg
                  LEFT JOIN grade rg ON rg.id = sg.id
                WHERE rg.id IS NULL;
          delete: >-
              DELETE rg FROM grade rg
                WHERE NOT EXISTS(SELECT sg.id FROM staging_grade sg WHERE sg.id = rg.id);

Thanks in advance for the help!

[vitaly-t]

it looks like it expects each file to have only one query

Based on what? It doesn't. But if your queries are independent of each other, then it makes little sense piling them into one file. See also - pg-promise-demo.

[agorina]

Thank you for the quick response and reference to the demo. I will check it out.

To justify what I would like multiple queries in the same file: from the DB optimization point of view, it is nice to have a quick view of how the data is being queried to better understand the indexes. For a simple CRUD it does not make sense, but when queries get more complicated it is a benefit.

[vitaly-t]

it is nice to have a quick view of how the data is being queried to better understand the indexes

That absolutely does not justify putting independent queries into the same file.

when queries get more complicated it is a benefit.

I would argue the exact opposite. When queries get more complicated, you will want each query in its own file. Otherwise, you will be creating a maintenance mess.

[agorina]

Let's agree to disagree :) I have had a very successful experience doing what I said above. But I respect your opinion.

[vitaly-t]

Ok :)

[makoven]

Let's agree to disagree :) I have had a very successful experience doing what I said above. But I respect your opinion.

I agree that sometimes scattering tiny queries across files clogs up a project. I prefer to logically group queries in a file. Then, using a simple 50-line code generator, turn it into a .ts file that exports requests as strings:

/* @name createTables */
CREATE TABLE IF NOT EXISTS shoes ...

/* @name selectShoe */
SELECT ...

we get

export const createTables = `
CREATE TABLE IF NOT EXISTS shoes ...
`;

export const selectShoe = `
SELECT ...
`;

[vitaly-t]

@makoven

using a simple 50-line code generator, turn it into a .ts file that exports requests as strings

It should take way less than 50 lines, if you use the existing API - utils.enumSql.

[vitaly-t]

@makoven

Here's a complete example...

const pgp = require('pg-promise')();
const {toPostgres} = pgp.as.ctf;
const {writeFileSync} = require('fs');

let failed;

const tree = pgp.utils.enumSql('../', {recursive: true}, file => {
    const qf = new pgp.QueryFile(file, {minify: true});
    if (qf.error) {
        failed = true;
        console.error(qf.error);
    } else {
        return qf[toPostgres]();
    }
});

if (!failed) {
    const formattedJson = JSON.stringify(tree, null, 4);
    writeFileSync('./sql.json', formattedJson); // save as JSON
}

Please note however, that by choosing this approach you lose the benefit of the debug feature of QueryFile that let's you auto-reload every file without restarting the process, which can save a lot of time when developing the SQL.

[makoven]

@vitaly-t
As far as I understand, this is a great feature when there is only one query in each file. This is not exactly what I want. I can have multiple queries in one file. Each is named with a special comment. How is it done in it https://github.com/adelsz/pgtyped

I ran your script on a directory with one file in which there are several requests and they merged into one:

{
  "queries": "CREATE TABLE IF NOT EXISTS auth_keys ( key text PRIMARY KEY, role text NOT NULL, created timestamp DEFAULT NOW() ); CREATE TABLE IF NOT EXISTS auth_sids ( sid text PRIMARY KEY, key text REFERENCES auth_keys(key), seal text, expires_min integer NOT NULL, created timestamp DEFAULT NOW() ); SELECT * FROM auth_keys WHERE key = $1; SELECT * FROM auth_sids WHERE sid = $1; INSERT INTO auth_sids($[this:name]) VALUES ($[this:csv]); DELETE FROM auth_sids WHERE sid = $1; DELETE FROM auth_keys WHERE key = $1; INSERT INTO auth_keys($[this:name]) VALUES ($[this:csv]);"
}

[vitaly-t]

I can have multiple queries in one file

This is not a typical scenario. In a typical scenario, we use one query per file, and all adds up to the repository pattern of using the database, as shown in pg-promise-demo, if you want a perfectly scalable and flexible architecture that is ;)

[makoven]

@vitaly-t

I agree that your approach is more flexible and scalable. However, in my opinion, it makes sense to talk about typical scenarios only within a large framework with a built-in ORM and migrations. And when you build a project from different libraries, you are free to do as you like :)

In my case, the entire project (code and resources) is assembled into one large bundle. Therefore, auto reload and the directory with queries do not make much sense. But this is offtopic. Your project is awesome anyway. Thank you so much

[vitaly-t]

Ok 👍

I updated the example above, for completeness, to save the file ;)