Unable to delete all rows from SQLite using Transactions?

i have a route api/delete-all that looks like:

import { NextResponse } from 'next/server'

import { users, sessions, keys, emailVerificationTokens } from '@/app/db/schema'
import { db } from '@/app/db/index'

export const DELETE = async () => {
  console.log('🏁 /api/delete')
  try {
    await db.transaction(async (tx) => {
      const deletedUsers = tx.delete(users)
      const deletedSessions = tx.delete(sessions)
      const deletedKeys = tx.delete(keys)
      const deletedEmailVerificationTokens = tx.delete(emailVerificationTokens)

      console.log({
        deletedUsers,
        deletedSessions,
        deletedKeys,
        deletedEmailVerificationTokens,
      })
    })
  } catch (error) {
    return NextResponse.json({ error, success: false })
  }
  return NextResponse.json({ success: true })
}

import { NextResponse } from 'next/server'

import { users, sessions, keys, emailVerificationTokens } from '@/app/db/schema'
import { db } from '@/app/db/index'

export const DELETE = async () => {
  console.log('🏁 /api/delete')
  try {
    await db.transaction(async (tx) => {
      const deletedUsers = tx.delete(users)
      const deletedSessions = tx.delete(sessions)
      const deletedKeys = tx.delete(keys)
      const deletedEmailVerificationTokens = tx.delete(emailVerificationTokens)

      console.log({
        deletedUsers,
        deletedSessions,
        deletedKeys,
        deletedEmailVerificationTokens,
      })
    })
  } catch (error) {
    return NextResponse.json({ error, success: false })
  }
  return NextResponse.json({ success: true })
}

i want to delete all values but i get a SQLITE_CONSTRAINT_ERROR:

error: {code: "SQLITE_CONSTRAINT", name: "LibsqlError"}

error: {code: "SQLITE_CONSTRAINT", name: "LibsqlError"}

how do i delete all rows in a table? i tried without transactions as well as using await tx.delete(users).all() but it doesn't delete from the database. i'm using https://turso.tech for sqlite on the edge.

Turso | SQLite Developer Experience in an Edge Database

Eliminate application bottlenecks by bringing the performance and data portability of open source, lightweight SQL close to your users with low overhead

1 Reply

Startup Spells 🪄 Newsletter GuyOP•2y ago

this one got solved. got the solution on turso discord. i had to either use on delete cascade or the order of deleting had to be such that leaf nodes get deleted first & then its parent nodes so no conflicts occur. i went with this:

import { NextResponse } from 'next/server'

import { users, sessions, keys, emailVerificationTokens } from '@/app/db/schema'
import { db } from '@/app/db/index'

export const DELETE = async () => {
  console.log('🏁 /api/delete-all')
  try {
    await db.transaction(async (tx) => {
      await tx.delete(emailVerificationTokens).run()
      await tx.delete(keys).run()
      await tx.delete(sessions).run()
      await tx.delete(users).run()
    })
  } catch (error) {
    return NextResponse.json({ error, success: false })
  }
  return NextResponse.json({ success: true })
}

import { NextResponse } from 'next/server'

import { users, sessions, keys, emailVerificationTokens } from '@/app/db/schema'
import { db } from '@/app/db/index'

export const DELETE = async () => {
  console.log('🏁 /api/delete-all')
  try {
    await db.transaction(async (tx) => {
      await tx.delete(emailVerificationTokens).run()
      await tx.delete(keys).run()
      await tx.delete(sessions).run()
      await tx.delete(users).run()
    })
  } catch (error) {
    return NextResponse.json({ error, success: false })
  }
  return NextResponse.json({ success: true })
}

Gaming

Programming

Unable to delete all rows from SQLite using Transactions?