DT
Drizzle Team•4d ago
El Grande Padre

Parameterization from string and array of values

I have a sql string with placeholders and an array of values for those placeholders. How can I have drizzle parameterize and execute it? for context I'm trying to have pg-boss use the same session as my drizzle transaction. pg-boss generates strings and arrays of values, but I can't find anything to render the sql in the api. I am using node-postgres. 
import type { Db as DbInterface } from 'pg-boss';
import type { db } from '~/lib/db';

class PgBossDrizzleTransactionAdapter implements DbInterface {
  tx: Parameters<Parameters<db['transaction']>[0]>[0];

  constructor(tx: Parameters<Parameters<db['transaction']>[0]>[0]) {
    this.tx = tx;
  }
  executeSql(text: string, values: any[]): Promise<{ rows: any[] }> {
    return this.tx.execute(text, values);
  }
}
import type { Db as DbInterface } from 'pg-boss';
import type { db } from '~/lib/db';

class PgBossDrizzleTransactionAdapter implements DbInterface {
  tx: Parameters<Parameters<db['transaction']>[0]>[0];

  constructor(tx: Parameters<Parameters<db['transaction']>[0]>[0]) {
    this.tx = tx;
  }
  executeSql(text: string, values: any[]): Promise<{ rows: any[] }> {
    return this.tx.execute(text, values);
  }
}
3 Replies
El Grande Padre
El Grande PadreOP•3d ago
executeSql parameters look like this: 
const text = `SELECT *
  FROM pgboss.archive;

     WITH next AS ( SELECT id
                      FROM pgboss.job
                     WHERE name = $1
                       AND state < 'active'
                       AND start_after < NOW()
                     ORDER BY priority DESC, created_on, id
                     LIMIT $2 FOR UPDATE SKIP LOCKED )
   UPDATE pgboss.job j
      SET state       = 'active'
        , started_on  = NOW()
        , retry_count = CASE WHEN started_on IS NOT NULL THEN retry_count + 1 ELSE retry_count END
     FROM next
    WHERE name = $1
      AND j.id = next.id
RETURNING j.id, name, data, EXTRACT(EPOCH FROM expire_in) AS expireInSeconds;`

const values = ['queue_name', 50]
const text = `SELECT *
  FROM pgboss.archive;

     WITH next AS ( SELECT id
                      FROM pgboss.job
                     WHERE name = $1
                       AND state < 'active'
                       AND start_after < NOW()
                     ORDER BY priority DESC, created_on, id
                     LIMIT $2 FOR UPDATE SKIP LOCKED )
   UPDATE pgboss.job j
      SET state       = 'active'
        , started_on  = NOW()
        , retry_count = CASE WHEN started_on IS NOT NULL THEN retry_count + 1 ELSE retry_count END
     FROM next
    WHERE name = $1
      AND j.id = next.id
RETURNING j.id, name, data, EXTRACT(EPOCH FROM expire_in) AS expireInSeconds;`

const values = ['queue_name', 50]
Through more debugging I found that typescript is reporting that there is no session property on the transaction object, but at runtime it is there 🤔
El Grande Padre
El Grande PadreOP•3d ago
This executes the query successfully 
import { env } from 'node:process';
import { drizzle } from 'drizzle-orm/node-postgres';
import { Pool } from 'pg';

const DB_CONFIG = {
  host: env.DATABASE_HOST!,
  port: Number.parseInt(env.DATABASE_PORT!, 10),
  user: env.DATABASE_USER!,
  password: env.DATABASE_PASSWORD!,
  database: env.DATABASE_DEFAULT_DATABASE!,
  max: 5,
  idleTimeoutMillis: 30_000,
  connectionTimeoutMillis: 2000,
  ssl: false,
};
const pgPool = new Pool(DB_CONFIG);

const db = drizzle(pgPool, { schema: {} });

await db.transaction(async (tx) => {
  const res = await tx.session.client.query('SELECT $1', [500]);
  console.log(res.rows[0]);
});
import { env } from 'node:process';
import { drizzle } from 'drizzle-orm/node-postgres';
import { Pool } from 'pg';

const DB_CONFIG = {
  host: env.DATABASE_HOST!,
  port: Number.parseInt(env.DATABASE_PORT!, 10),
  user: env.DATABASE_USER!,
  password: env.DATABASE_PASSWORD!,
  database: env.DATABASE_DEFAULT_DATABASE!,
  max: 5,
  idleTimeoutMillis: 30_000,
  connectionTimeoutMillis: 2000,
  ssl: false,
};
const pgPool = new Pool(DB_CONFIG);

const db = drizzle(pgPool, { schema: {} });

await db.transaction(async (tx) => {
  const res = await tx.session.client.query('SELECT $1', [500]);
  console.log(res.rows[0]);
});
Type checking complains that session is not available: Property 'session' does not exist on type 'PgTransaction<NodePgQueryResultHKT, {}, ExtractTablesWithRelations<{}>>'. [2339]
No description
El Grande Padre
El Grande PadreOP•3d ago
👀 https://github.com/drizzle-team/drizzle-orm/issues/3140
GitHub
[BUG]: Type Error in PgTransaction Missing $client Property After U...
What version of drizzle-orm are you using? 0.35.1 What version of drizzle-kit are you using? 0.26.2 Describe the Bug Hello Drizzle ORM Team, I&#39;m encountering a TypeScript type error after upgra...

Did you find this page helpful?