DT
Drizzle Team•4mo ago
Ghaur

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 😦
0 Replies
No replies yetBe the first to reply to this messageJoin

Did you find this page helpful?