Ghaur Posts - Answer Overflow

Ghaur

•Created by Ghaur on 3/13/2025 in #help

PGSql Batch/Merge Queries for Perforamnce

I currently have several queries that execute sequentially but they are somewhat independant of one another in that they dont need each others output. A psuedo code example is

// 1. Update resource A
// 2. Delete resources B which reference resource A
// 3. Insert (new) resources B referencing resource A

// 1. Update resource A
// 2. Delete resources B which reference resource A
// 3. Insert (new) resources B referencing resource A

Is there a way using Drizzle ORM to batch these so that it only costs one round trip to the DB?

2 replies

DTDrizzle Team

•Created by Ghaur on 9/25/2024 in #help

Create a Query and insert into sql`` template

TLDR: How do I take a query (such as PgSelectBase) and put it inside an sql template? I want to use the query builder to create a query that I will then put into a (Postgres) TEMPORARY TABLE to allow me to query it twice and not have to save on performance. I have got the following:

import { eq, sql } from "drizzle-orm";
import { drizzle } from "drizzle-orm/node-postgres";
import pg from "pg";

import * as schema from "./schema.js";

const CONNECTION_STRING = "";

const main = async () => {
  const client = new pg.Client({
    connectionString: CONNECTION_STRING,
  });

  await client.connect();

  const db = drizzle(client, { schema });

  const query = db.select().from(schema.Media).where(eq(schema.Media.type, "some-value"));

  const countAndData = sql.join([
    sql`CREATE TEMPORARY TABLE nearest_neighbours AS `,
    query,
    // This works: sql`SELECT * FROM media WHERE type = 'some-value'`,
    sql`;`,
    sql`SELECT COUNT(*) FROM nearest_neighbours;`,
    sql`SELECT * FROM nearest_neighbours offset 0 limit 5;`,
  ]);

  const result = await db.execute(countAndData);

  const { count } = result[1].rows[0]; // Second set of results is for the count
  const data = result[2].rows; // Third set of results is for the data
};

main()
  .then(() => process.exit(0))
  .catch((e) => {
    console.error(e);
    process.exit(1);
  });

import { eq, sql } from "drizzle-orm";
import { drizzle } from "drizzle-orm/node-postgres";
import pg from "pg";

import * as schema from "./schema.js";

const CONNECTION_STRING = "";

const main = async () => {
  const client = new pg.Client({
    connectionString: CONNECTION_STRING,
  });

  await client.connect();

  const db = drizzle(client, { schema });

  const query = db.select().from(schema.Media).where(eq(schema.Media.type, "some-value"));

  const countAndData = sql.join([
    sql`CREATE TEMPORARY TABLE nearest_neighbours AS `,
    query,
    // This works: sql`SELECT * FROM media WHERE type = 'some-value'`,
    sql`;`,
    sql`SELECT COUNT(*) FROM nearest_neighbours;`,
    sql`SELECT * FROM nearest_neighbours offset 0 limit 5;`,
  ]);

  const result = await db.execute(countAndData);

  const { count } = result[1].rows[0]; // Second set of results is for the count
  const data = result[2].rows; // Third set of results is for the data
};

main()
  .then(() => process.exit(0))
  .catch((e) => {
    console.error(e);
    process.exit(1);
  });

This currently results in: error: cannot insert multiple commands into a prepared statement What I think I need is to have the statement prepared fully so it can be inserted but I can not figure out how to 😦

1 replies

Gaming

Programming