Can execute multiple statements in a single query

[alxndrsn]

Expected Behavior

Attempting to run multiple statements should throw an Error, and the statement should not be executed.

Current Behavior

Attempting to run multiple statements should throw an Error, and the statements are executed.

Possible Solution

I think a fix would require a change in node-postgres to force use of extended queries when there is no prepared statement or query parameters.

Steps to Reproduce

  test('allows multiple statements in a single query, but throws error', async (t) => {
    const pool = await createPool(t.context.dsn, {
      driverFactory,
    });

    const result1 = await pool.anyFirst(sql.unsafe`
      SELECT name FROM person
    `);

    t.deepEqual(result1, []);

    const error = await t.throwsAsync(
      pool.query(sql.unsafe`
        INSERT INTO person (name) VALUES('alice');
        INSERT INTO person (name) VALUES('bob');
      `),
    );

    t.true(error instanceof InvalidInputError);

    const result = await pool.anyFirst(sql.unsafe`
      SELECT name FROM person
    `);

    t.deepEqual(result, ['alice', 'bob']);
  });

Logs

Passing test can be seen at:

• Code: https://github.com/gajus/slonik/compare/main...alxndrsn:slonik:multiple-statements-single-query?expand=1
• Build: https://github.com/alxndrsn/slonik/actions/runs/9029972658

[alxndrsn]

Related:

• 71746d6
• Running multiple SQL commands at once throws an exception #562

[gajus]

Unfortunately, the only way to support this at Slonik-level would be if we had a SQL tokenizer.

I long wanted SQL tokenizer for other use cases, but there aren't any that have good enough coverage for production adoption.

[gajus]

@alxndrsn another option to prevent this at Slonik level (assuming brianc/node-postgres#3214 does not get merged), would be to auto generate statement name based on the query. The overhead would be pretty minimal and it would achieve more or less the same effect as the proposed pg patch.

[alxndrsn]

@alxndrsn another option to prevent this at Slonik level (assuming brianc/node-postgres#3214 does not get merged), would be to auto generate statement name based on the query.

👍 but I suspect there is some extra overhead to named prepared statements vs unnamed.

[gajus]

@alxndrsn I was going to update pg to allow queryMode: 'extended', but it only works when query has no parameters, so I am not sure how useful that is.

[alxndrsn]

it only works when query has no parameters

Simple queries do not support parameters, so extended query mode is always when parameters are supplied.

[gajus]

Released in v45.6.0.

Thank you!

[gajus]

Looks like the performance hit from enabling this is fairly substantive. Might may want to make this configurable.