Transaction + prepared statement

Hi everyone. I was wondering how to correctly use a prepared statement inside a transaction with drizzle-orm. I obviously first looked at the documentation with no success then went to the github issues and found the issue #952 (https://github.com/drizzle-team/drizzle-orm/issues/952). I prepared an answer but eventually found that I was quite off topic and I came here.

1) In his code (issue 952), I don't see the point of using a prepared statement: he prepares a statement each time the function is executed then executes it right away. From what I understand / think, the prepared statement should be a global (with placeholders if needed), then executed in the function.

// version 1: with prepared statements inside
async function test1() {
  await db.transaction(async tx => {
    const p1 = tx.update()...prepare();
    const p2 = tx.insert()...prepare();
    await p1.execute();
    await p2.execute();
  });
}

// version 1: with prepared statements inside
async function test1() {
  await db.transaction(async tx => {
    const p1 = tx.update()...prepare();
    const p2 = tx.insert()...prepare();
    await p1.execute();
    await p2.execute();
  });
}

// version 1: with prepared statements inside
async function test1() {
  await db.transaction(async tx => {
    const p1 = tx.update()...prepare();
    const p2 = tx.insert()...prepare();
    await p1.execute();
    await p2.execute();
  });
}

// version 1: with prepared statements inside
async function test1() {
  await db.transaction(async tx => {
    const p1 = tx.update()...prepare();
    const p2 = tx.insert()...prepare();
    await p1.execute();
    await p2.execute();
  });
}

2) And here comes my issue: if the prepared statements are moved outside, they cannot be defined using "tx". But if they are defined using "db", does the transaction work? I will do some tests when I have time, but I would be interested if you know the answer. And I think this would good to clarify it in the documentation.

// version 2: with global prepared statements
const p1 = db.update()...prepare();
const p2 = db.insert()...prepare();

async function test2() {
  await db.transaction(async tx => {
    p1.execute();
    p2.execute();
  });
}

// version 2: with global prepared statements
const p1 = db.update()...prepare();
const p2 = db.insert()...prepare();

async function test2() {
  await db.transaction(async tx => {
    p1.execute();
    p2.execute();
  });
}

// version 2: with global prepared statements
const p1 = db.update()...prepare();
const p2 = db.insert()...prepare();

async function test2() {
  await db.transaction(async tx => {
    p1.execute();
    p2.execute();
  });
}

// version 2: with global prepared statements
const p1 = db.update()...prepare();
const p2 = db.insert()...prepare();

async function test2() {
  await db.transaction(async tx => {
    p1.execute();
    p2.execute();
  });
}

GitHub

Issues · drizzle-team/drizzle-orm

TypeScript ORM that feels like writing SQL. Contribute to drizzle-team/drizzle-orm development by creating an account on GitHub.

Transaction + prepared statement

Similar Threads

Similar Threads

Similar Threads