Drizzle Team•2mo ago

Transaction Query Statements

In my database transaction, how can I have three query statements that is three round-trips, as one query statement that is one round-trip, to improve on performance? My queries are not related/similar data so from my understanding a Union won't work and currently batch statements are not available within transactions with Turso

8 Replies

christradingOP•2mo ago

  const baseClientGroupRecord = await getClientGroupForUpdate(tx, clientGroupID)
  const clientChanges = await searchClients(tx, clientGroupID, baseCVR.clientVersion)
  const listMeta = await searchLists(tx, userID)

export async function getClientGroupForUpdate(
  tx: Transaction,
  clientGroupID: string,
): Promise<ClientGroupRecord> {
  const previousClientGroup = await getClientGroup(tx, clientGroupID)
  return {
    id: clientGroupID,
    clientGroupVersion: previousClientGroup.clientGroupVersion,
    cvrVersion: previousClientGroup.cvrVersion,
  }
}

async function getClientGroup(
  tx: Transaction,
  clientGroupID: string,
): Promise<Omit<ClientGroupRecord, 'id'>> {
  const clientGroupRowStatementQuery = tx
    .select({
      cvrVersion: replicacheClientGroup.cvrVersion,
      clientGroupVersion: replicacheClientGroup.clientGroupVersion,
    })
    .from(replicacheClientGroup)
    .where(eq(replicacheClientGroup.id, clientGroupID))
    .prepare()

  const clientGroupRow = await clientGroupRowStatementQuery.get() || {
    clientGroupVersion: 0,
    cvrVersion: null,
  }

  return clientGroupRow
}

  const baseClientGroupRecord = await getClientGroupForUpdate(tx, clientGroupID)
  const clientChanges = await searchClients(tx, clientGroupID, baseCVR.clientVersion)
  const listMeta = await searchLists(tx, userID)

export async function getClientGroupForUpdate(
  tx: Transaction,
  clientGroupID: string,
): Promise<ClientGroupRecord> {
  const previousClientGroup = await getClientGroup(tx, clientGroupID)
  return {
    id: clientGroupID,
    clientGroupVersion: previousClientGroup.clientGroupVersion,
    cvrVersion: previousClientGroup.cvrVersion,
  }
}

async function getClientGroup(
  tx: Transaction,
  clientGroupID: string,
): Promise<Omit<ClientGroupRecord, 'id'>> {
  const clientGroupRowStatementQuery = tx
    .select({
      cvrVersion: replicacheClientGroup.cvrVersion,
      clientGroupVersion: replicacheClientGroup.clientGroupVersion,
    })
    .from(replicacheClientGroup)
    .where(eq(replicacheClientGroup.id, clientGroupID))
    .prepare()

  const clientGroupRow = await clientGroupRowStatementQuery.get() || {
    clientGroupVersion: 0,
    cvrVersion: null,
  }

  return clientGroupRow
}

async function searchClients(tx: Transaction, clientGroupID: string, sinceClientVersion: number) {
  const clientRowStatementQuery = tx
    .select({
      id: replicacheClient.id,
      lastMutationID: replicacheClient.lastMutationID,
      clientVersion: replicacheClient.clientVersion,
    })
    .from(replicacheClient)
    .where(and(
      eq(replicacheClient.clientGroupID, clientGroupID),
      gt(replicacheClient.clientVersion, sinceClientVersion),
    ))
    .prepare()

  const clientRows = await clientRowStatementQuery.all()

  const clients = clientRows.map((row) => {
    const client: ClientRecord = {
      id: row.id,
      clientGroupID,
      lastMutationID: row.lastMutationID,
      clientVersion: row.clientVersion,
    }
    return client
  })

  return clients
}

export async function searchLists(
  tx: Transaction,
  accessibleByUserID: string,
): Promise<SearchResult[]> {
  const admin = await isAdmin(tx, accessibleByUserID)
  if (admin) {
    const listRowStatementQuery = tx
      .select({
        id: list.id,
        rowVersion: list.rowVersion,
      })
      .from(list)
      .prepare()

    const listRows = await listRowStatementQuery.all()
    return listRows
  }

  const shareRowStatementSubquery = tx
    .select({
      id: share.listID,
    })
    .from(share)
    .where(eq(share.userID, accessibleByUserID))

  const listRowStatementQuery = tx
    .select({
      id: list.id,
      rowVersion: list.rowVersion,
    })
    .from(list)
    .where(
      or(
        eq(list.ownerID, accessibleByUserID),
        inArray(list.id, shareRowStatementSubquery),
      ),
    )
    .prepare()

  const listRows = await listRowStatementQuery.all()

  return listRows
}

async function searchClients(tx: Transaction, clientGroupID: string, sinceClientVersion: number) {
  const clientRowStatementQuery = tx
    .select({
      id: replicacheClient.id,
      lastMutationID: replicacheClient.lastMutationID,
      clientVersion: replicacheClient.clientVersion,
    })
    .from(replicacheClient)
    .where(and(
      eq(replicacheClient.clientGroupID, clientGroupID),
      gt(replicacheClient.clientVersion, sinceClientVersion),
    ))
    .prepare()

  const clientRows = await clientRowStatementQuery.all()

  const clients = clientRows.map((row) => {
    const client: ClientRecord = {
      id: row.id,
      clientGroupID,
      lastMutationID: row.lastMutationID,
      clientVersion: row.clientVersion,
    }
    return client
  })

  return clients
}

export async function searchLists(
  tx: Transaction,
  accessibleByUserID: string,
): Promise<SearchResult[]> {
  const admin = await isAdmin(tx, accessibleByUserID)
  if (admin) {
    const listRowStatementQuery = tx
      .select({
        id: list.id,
        rowVersion: list.rowVersion,
      })
      .from(list)
      .prepare()

    const listRows = await listRowStatementQuery.all()
    return listRows
  }

  const shareRowStatementSubquery = tx
    .select({
      id: share.listID,
    })
    .from(share)
    .where(eq(share.userID, accessibleByUserID))

  const listRowStatementQuery = tx
    .select({
      id: list.id,
      rowVersion: list.rowVersion,
    })
    .from(list)
    .where(
      or(
        eq(list.ownerID, accessibleByUserID),
        inArray(list.id, shareRowStatementSubquery),
      ),
    )
    .prepare()

  const listRows = await listRowStatementQuery.all()

  return listRows
}

Angelelz•5w ago

Transactions by definition will run your queries sequencially No need to reinvent the wheel. If the queries are truly unrelated, you might want to look into running them with something like Promise.all outside the transaction

christradingOP•4w ago

How about within my transaction rather than outside? Within my transaction I have a number of these queries that aren’t dependent of each other that I would have liked to batch together to save on round trips and latency. I dont have anything outside the transaction Id like to batch together with

Angelelz•4w ago

There is no concept of batch in a transaction. The very purpose of a transaction is to run queries sequentially such that failing in one will result in failure of all

christradingOP•4w ago

Multiple queries within a transaction still cause multiple round-trips, a round-trip per query. Is there any way to optimize this?

Angelelz•4w ago

You should read about transactions and how they work. Locking behavior, acid compliance, etc You could create a store procedure to do what you want to do, and save in round trips

christradingOP•4w ago

Okay. I recognize that transactions lock the database so other queries or transactions outside of that transaction aren't able to connect at the same time; I need the numerous queries wrapped in a transaction so that if one fails they all fail for the requirement of atomic strongly consistent serialization needed for Replicache. I do have .prepare() on my queries. Is this the equivalent of a stored procedure or do I need to write the procedure in raw sql? I didn't see too much on Drizzle stored procedures apart from these github threads: https://github.com/drizzle-team/drizzle-orm/discussions/2434

GitHub

Typesafe PostgreSQL stored procs / function calls · drizzle-team dr...

What version of drizzle-orm are you using? 0.18.0 Describe the Bug This is not a bug but a feature request / question. I noticed that #116 will be adding type-safe views and seems like a fantastic ...

Angelelz•4w ago

Drizzle will not help you if you want to go with the stored procedures. This is a backend topic and some people think that including business logic at the database level is not a good idea. But if you have a bottleneck in you transaction due to round trip latency, this could be a solution. I would advise to not attempt to optimize this unless it is a real problem.

Gaming

Programming

Transaction Query Statements