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
1 Reply
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
}