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
}

[abhi-airspace-intelligence]

I'm executing some pretty complicated queries so I asked Claude to scan drizzle's internals and figure out how to do this. This is what it whipped up.

/**
 * Execute a pre-built `SQL` object as a **named** prepared statement.
 *
 * The function reaches into drizzle internals (`db.dialect` and `db._.session`)
 * to call `session.prepareQuery` with an explicit name so PostgreSQL can cache
 * the query plan across repeated invocations on the same connection.
 *
 * @param database  - A drizzle `Database` or `DatabaseTransaction` instance.
 * @param query     - A drizzle `SQL` template (may contain `sql.placeholder()` slots).
 * @param name      - A stable, unique name for the prepared statement (e.g. `"fle_nn"`).
 * @param placeholderValues - Values for each `sql.placeholder()` used in `query`.
 * @returns The raw `QueryResult<TRow>` — identical shape to `database.execute()`.
 */
export async function executeNamedRawQuery<TRow extends QueryResultRow>(
  database: DatabaseOrTransaction,
  query: SQL,
  name: string,
  placeholderValues: Record<string, unknown>
): Promise<QueryResult<TRow>> {
  // `dialect` is set in the constructor (`this.dialect = dialect`) but is not
  // part of the public type surface.
  // @ts-expect-error -- accessing internal drizzle property that exists at runtime
  // eslint-disable-next-line @typescript-eslint/no-unsafe-assignment
  const dialect: PgDialect = database.dialect;

  const builtQuery = dialect.sqlToQuery(query);

  const session = database._.session;
  const prepared = session.prepareQuery<{ execute: QueryResult<TRow>; all: unknown; values: unknown }>(
    builtQuery,
    undefined,
    name,
    false
  );

  return prepared.execute(placeholderValues);
}