Is it possible to prepare an ad hoc sql statement

[chanan]

Using MySQL is it possible to prepare a statement that is is an ad hoc statement such as:

await db.execute(sql`INSERT INTO tableA SELECT * FROM tableb`);

[gauravvpa]

@chanan You are asking about raw sql query execution. There are instances where we need to write a complex query and ORMs don't have support then you can use this.
You can only write a single statement inside db.execute

await db.execute(sql`INSERT INTO tableA SELECT * FROM tableb`); // this is wrong

await db.execute(sql`INSERT INTO tableA;`); // correct
await db.execute(sql`SELECT * FROM tableb;`); // correct

[chanan]

Yes, but I am asking is we can call prepare on it? Sorry, I realize the text of my question wasn't clear. I am hoping to be able to do something along the lines of:

const myStatement = db.execute(`Some sql`).prepare();
await myStatement.execute();

[gauravvpa]

@chanan There should be no need to use prepare() when executing raw sql query. prepare is not available for chaining when using db.execute()

prepare is used for optimisation purpose where we do not want database driver to parse the same query all the time, So

const preparedQuery = db.select().from(tableA).prepare()
// when we call prepare() method it returns us an object having a property which stores the raw query.
// this "preparedQuery" is an instance of MySql2PreparedQuery class 

const resp = await  preparedQuery.execute();
// When we call execute(), raw query & parameters passed in execute is used by database driver.

Reference: Query Perf

[chanan]

Re-reading the documentation you shared, I see that maybe prepared statements aren't doing the same thing I am used to from SQL Server and .net. I assumed that it prepared statements would also re-use the query plan. In those cases where it re-uses a query plan, the performance of the dynamic SQL (that is prepared) is similar to a stored proc. From the above, I assume that is not the case here?

[ivan-kleshnin]

Postgres has prepared statements https://www.postgresql.org/docs/current/sql-prepare.html so I don't get why "it's not applicable to raw queries". It has to be. The other thing is that Drizzle .prepare() seems to be an app-level analogy of native PREPARE with own set of pros/cons:

+ cross-database, works even for DBs without native PREPARE support
- much weaker performance improvement

It's unfortunate that two different, just somewhat related, concepts have a name clash.

Update: documentation talks about "binary" compilation, making it look like .prepare() method is utilizing native `PREPARE.
Maybe it's true, but I can't confirm that:

let pgsql = ...PostgresJS client...
let dz = ...drizzle client...

// When executed via PostgresJS directly
await pgsql`SELECT 2 * 3000`
for (let r of await pgsql`SELECT * FROM pg_prepared_statements`) {
  console.log(r) // ... `SELECT 2 * 3000` is cached
}

// When executed via Drizzle
await dz.execute(sql`SELECT 2 * 4000 AS bar`)
for (let r of await dz.execute(sql`SELECT * FROM pg_prepared_statements`)) {
  console.log(r) // ... `SELECT 2 * 4000` is NOT cached
}