1

I am using Supabase as a backend which provides postgres as its database and prisma for my next-js app. I wanted to seed some dummy data into the database. The first time I run the seed script, everything's fine but when I ran the script multiple times, even though I deleted all rows before seeding, the primary id (which auto increments by default) of the table is not resetting to 1. Instead, it is incrementing from the previous value. I tried something like this:

await prisma.user.deleteMany();
await prisma.post.deleteMany();
await prisma.$queryRaw`ALTER SEQUENCE user_id_seq RESTART WITH 1`;
await prisma.$queryRaw`ALTER SEQUENCE post_id_seq RESTART WITH 1`;

This is error occurred when I run raw SQL code in prisma:

PrismaClientKnownRequestError: 
Invalid `prisma.$queryRaw()` invocation:


Raw query failed. Code: `42P01`. Message: `relation "user_id_seq" does not exist`
    at RequestHandler.handleRequestError (/home/surya/projects/social-media-demo/node_modules/@prisma/client/runtime/index.js:29909:13)
    at RequestHandler.request (/home/surya/projects/social-media-demo/node_modules/@prisma/client/runtime/index.js:29892:12)
    at async Proxy._request (/home/surya/projects/social-media-demo/node_modules/@prisma/client/runtime/index.js:30864:16) {
  code: 'P2010',
  clientVersion: '4.3.1',
  meta: { code: '42P01', message: 'relation "user_id_seq" does not exist' }
}

Is deleting all the tables and running migrations again as a fresh start the only way?

surya vivek
  • 9
  • 3
  • `truncate table post reset identity`? To find out the name of the underlying sequence of an `identity` (or the outdated `serial`) column, use `pg_get_serial_sequence()` –  Sep 21 '22 at 12:07
  • Do you have a sequence called `user_id_seq`? Does the `ALTER SEQUENCE` statement work when you run it from a SQL tool? – Álvaro González Sep 21 '22 at 14:14

1 Answers1

2

Try it like this, it will delete the data and restart the sequence:

TRUNCATE TABLE <tableName> RESTART IDENTITY;
hp10
  • 602
  • 6
  • 11