Error:[tyeporm migration] cannot insert multiple commands into a prepared statement

[gring2]

Summary If I run typeorm migration, i have cannot insert multiple commands into a prepared statement, I dig into typeorm code and find out it use driver's query function. and pglite query seems not support multiple commands, whilte exec function support multiple command.

Example

   const db = new PGlite()
      // typeorm uses query function in migration code.
      await db.query(`
      CREATE TABLE IF NOT EXISTS test1 (
        id SERIAL PRIMARY KEY,
        name TEXT
      );
      CREATE TABLE IF NOT EXISTS test2 (
        id SERIAL PRIMARY KEY,
        name TEXT
      );      
    `)
      })

[jadejr]

I definitely have the same problem in a similiar context. I had to write my own workaround. Kysely only exposes one interface and query is different enough from exec that it's not feasible to use exec. I ended up having to instantiate pglite myself and patching some code. I wish queryhad a similiar interface as the pg module.

[ragrag]

@gring2 did you find any workaround?

[ragrag]

@gring2 @jadejr you can use this package i recently created to act as a typeorm data source, it supports running multiple commands https://github.com/ragrag/pglite-typeorm

[tdrz]

From our docs:

There are two methods for querying the database, .query and .exec. The former supports parameters, while the latter supports multiple statements.

If those generated queries do not use parameters, I guess we could easily switch to exec instead.

[tdrz]

As a workaround, could someone try this or similar?

import { DataSource } from 'typeorm';
import { PGlite } from '@electric-sql/pglite';

const pgliteExec = await PGlite.create()
pgliteExec.query = pgliteExec.exec

const dataSource = new DataSource({
    type: 'postgres',
    synchronize: false,
    driver: pgliteExec,
});

Basically override query with exec.

[jadejr]

@ragrag I do not use typeorm. I use mikro-orm (v7) with kysely. It just happens to have the exact same issue with query(). It is likely that many migrators out there will have the exact same issue and would probably prefer that the pglite interface is closer to the way it is in libraries like pg.

[ragrag]

As a workaround, could someone try this or similar?

import { DataSource } from 'typeorm';
import { PGlite } from '@electric-sql/pglite';

const pgliteExec = await PGlite.create()
pgliteExec.query = pgliteExec.exec

const dataSource = new DataSource({
    type: 'postgres',
    synchronize: false,
    driver: pgliteExec,
});

Basically override query with exec.

@tdrz i'm not quite sure how reliable this is, since exec doesn't support params?

the way i did it in was something similar to this:

  import { parseQuerySync } from '@pg-nano/pg-parser';
   
  const parsedSqlQuery = parseQuerySync(sqlQuery);

  if (parsedSqlQuery.stmts.length > 1){
       // use exec(sqlQuery)
  }else{
       // use query(sqlQuery, params)
  }

in both cases, its most likely a happy accident that it works but its much more likely to work reliably if number of statements are checked

[tdrz]

@tdrz i'm not quite sure how reliable this is, since exec doesn't support params?

Checked with typeorm maintainers, the migrations generally do not rely on parameters. Nevertheless, my suggestion is just a workaround. Will revisit this in the future to see how we can improve it.